plans.txt

text/plain

Download

                                                                              QUERY PLAN                                                                               
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=44577524.39..44577524.40 rows=1 width=32) (actual time=364195.843..364195.843 rows=1 loops=1)
   ->  Aggregate  (cost=44577524.39..44577524.40 rows=1 width=32) (actual time=364195.842..364195.842 rows=1 loops=1)
         ->  Merge Join  (cost=41772792.17..44577358.39 rows=66397 width=8) (actual time=283387.658..364187.088 rows=17275 loops=1)
               Merge Cond: (lineitem_1.l_partkey = part.p_partkey)
               Join Filter: (lineitem.l_quantity < ((0.2 * avg(lineitem_1.l_quantity))))
               Rows Removed by Join Filter: 175210
               ->  GroupAggregate  (cost=41772791.17..43305665.51 rows=6206695 width=36) (actual time=283378.004..335611.192 rows=6398981 loops=1)
                     Group Key: lineitem_1.l_partkey
                     ->  Sort  (cost=41772791.17..42252715.81 rows=191969856 width=9) (actual time=283377.977..306182.393 rows=191969841 loops=1)
                           Sort Key: lineitem_1.l_partkey
                           Sort Method: external merge  Disk: 3569544kB
                           ->  Seq Scan on lineitem lineitem_1  (cost=0.00..5519079.56 rows=191969856 width=9) (actual time=0.019..28253.076 rows=192000551 loops=1)
               ->  Materialize  (cost=1.00..1191105.89 rows=205371 width=21) (actual time=4.176..28117.667 rows=192485 loops=1)
                     ->  Nested Loop  (cost=1.00..1190592.46 rows=205371 width=21) (actual time=4.172..27965.259 rows=192485 loops=1)
                           ->  Index Scan using part_pkey on part  (cost=0.43..329262.21 rows=6846 width=4) (actual time=3.634..1553.847 rows=6431 loops=1)
                                 Filter: ((p_brand = 'Brand#22'::bpchar) AND (p_container = 'LG BOX'::bpchar))
                                 Rows Removed by Filter: 6393569
                           ->  Index Scan using idx_lineitem_part_supp on lineitem  (cost=0.57..125.51 rows=31 width=17) (actual time=0.415..4.090 rows=30 loops=6431)
                                 Index Cond: (l_partkey = part.p_partkey)
 Planning Time: 1.280 ms
 Execution Time: 364472.285 ms
(21 rows)

                                                                             QUERY PLAN                                                                              
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=16997740.10..16997740.12 rows=1 width=32) (actual time=1381753.445..1381754.040 rows=1 loops=1)
   ->  Aggregate  (cost=16997740.10..16997740.12 rows=1 width=32) (actual time=1381753.015..1381753.015 rows=1 loops=1)
         ->  Nested Loop  (cost=14204895.82..16997574.11 rows=66397 width=8) (actual time=203854.111..1381742.552 rows=17275 loops=1)
               Join Filter: (part.p_partkey = lineitem.l_partkey)
               ->  Hash Join  (cost=14204895.25..16251060.84 rows=6640 width=40) (actual time=203851.753..1356283.383 rows=6431 loops=1)
                     Hash Cond: (lineitem_1.l_partkey = part.p_partkey)
                     ->  HashAggregate  (cost=13977751.34..15945557.39 rows=6206695 width=36) (actual time=202952.170..1354546.897 rows=6400000 loops=1)
                           Group Key: lineitem_1.l_partkey
                           Planned Partitions: 128
                           Peak Memory Usage: 4249 kB
                           Disk Usage: 26321840 kB
                           HashAgg Batches: 16512
                           ->  Seq Scan on lineitem lineitem_1  (cost=0.00..5519079.56 rows=191969856 width=9) (actual time=0.007..22205.617 rows=192000551 loops=1)
                     ->  Hash  (cost=227058.33..227058.33 rows=6846 width=4) (actual time=899.455..899.455 rows=6431 loops=1)
                           Buckets: 8192  Batches: 1  Memory Usage: 291kB
                           ->  Seq Scan on part  (cost=0.00..227058.33 rows=6846 width=4) (actual time=1.404..897.472 rows=6431 loops=1)
                                 Filter: ((p_brand = 'Brand#22'::bpchar) AND (p_container = 'LG BOX'::bpchar))
                                 Rows Removed by Filter: 6393569
               ->  Index Scan using idx_lineitem_part_supp on lineitem  (cost=0.57..112.30 rows=10 width=17) (actual time=1.500..3.955 rows=3 loops=6431)
                     Index Cond: (l_partkey = lineitem_1.l_partkey)
                     Filter: (l_quantity < ((0.2 * avg(lineitem_1.l_quantity))))
                     Rows Removed by Filter: 27
 Planning Time: 0.828 ms
 Execution Time: 1382542.703 ms
(24 rows)