ltree resolve

Log

2018-05-08 07:31:21.892 UTC [33] LOG:  duration: 924169.808 ms  plan:
	Query Text: create temporary table resolved_by_ltree as
	  select rule_id, rule_pcd, product_leaf_pcd, location_leaf_pcd from
	    (select
	       rule_id,
	       rule_pcd,
	       product_leaf_pcd,
	       location_leaf_pcd,
	       row_number()
	       over (
	         partition by product_leaf_pcd, location_leaf_pcd, rule_pcd
	         order by impact asc ) as rank
	     from
	       (select
	          rule_id,
	          rule_pcd,
	          product_leaf_pcd,
	          location_leaf_pcd,
	          count(rule_id)
	          over (
	            partition by rule_id, rule_pcd ) as impact
	        from product_location_association
	          join rules on rules.product_hierarchy_pcd_ltree @> product_location_association.product_hierarchy_pcd_ltree and
	                        rules.location_hierarchy_pcd_ltre @> product_location_association.location_hierarchy_pcd_ltree and
	                        product_location_association.is_deleted = 0
	       ) as impacts
	    ) as ranked
	  where rank = 1
	Subquery Scan on ranked  (cost=594571.94..599631.14 rows=675 width=21) (actual time=856909.712..912384.323 rows=30473786 loops=1)
	  Filter: (ranked.rank = 1)
	  Rows Removed by Filter: 30229276
	  Buffers: shared hit=20001602 read=16883874 written=1, temp read=551706 written=551708
	  ->  WindowAgg  (cost=594571.94..597944.74 rows=134912 width=37) (actual time=856907.776..906370.626 rows=60703062 loops=1)
	        Buffers: shared hit=20001602 read=16883874 written=1, temp read=551706 written=551708
	        ->  Sort  (cost=594571.94..594909.22 rows=134912 width=29) (actual time=856907.752..878561.875 rows=60703062 loops=1)
	              Sort Key: impacts.product_leaf_pcd, impacts.location_leaf_pcd, impacts.rule_pcd, impacts.impact
	              Sort Method: external merge  Disk: 2494656kB
	              Buffers: shared hit=20001602 read=16883874 written=1, temp read=551706 written=551708
	              ->  Subquery Scan on impacts  (cost=579028.96..583076.32 rows=134912 width=29) (actual time=566316.727..599813.637 rows=60703062 loops=1)
	                    Buffers: shared hit=20001602 read=16883874 written=1, temp read=239874 written=239875
	                    ->  WindowAgg  (cost=579028.96..581727.20 rows=134912 width=29) (actual time=566316.725..594487.323 rows=60703062 loops=1)
	                          Buffers: shared hit=20001602 read=16883874 written=1, temp read=239874 written=239875
	                          ->  Sort  (cost=579028.96..579366.24 rows=134912 width=21) (actual time=563107.817..570160.767 rows=60703062 loops=1)
	                                Sort Key: rules.rule_id, rules.rule_pcd
	                                Sort Method: external merge  Disk: 1918992kB
	                                Buffers: shared hit=20001602 read=16883874 written=1, temp read=239874 written=239875
	                                ->  Nested Loop  (cost=0.42..567533.34 rows=134912 width=21) (actual time=53.551..527518.035 rows=60703062 loops=1)
	                                      Buffers: shared hit=20001602 read=16883874 written=1
	                                      ->  Seq Scan on rules  (cost=0.00..993.35 rows=13635 width=86) (actual time=25.997..38.522 rows=13635 loops=1)
	                                            Buffers: shared hit=360 read=497
	                                      ->  Index Scan using pla_ltree_index_gist on product_location_association  (cost=0.42..41.45 rows=10 width=103) (actual time=0.163..38.180 rows=4452 loops=13635)
	                                            Index Cond: ((rules.product_hierarchy_pcd_ltree @> product_hierarchy_pcd_ltree) AND (rules.location_hierarchy_pcd_ltre @> location_hierarchy_pcd_ltree))
	                                            Filter: (is_deleted = 0)
	                                            Buffers: shared hit=20001242 read=16883377 written=1