load 'auto_explain'; LOAD set auto_explain.log_min_duration to 0; SET set auto_explain.log_analyze to on; SET set auto_explain.log_wal to on; SET set auto_explain.log_timing to on; SET set auto_explain.log_level to notice; SET BEGIN; BEGIN select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem where o_orderkey in ( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 315 ) and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate LIMIT 100; NOTICE: duration: 62746.721 ms plan: Query Text: select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem where o_orderkey in ( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 315 ) and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate LIMIT 100; Limit (cost=4125821.43..4125821.68 rows=100 width=75) (actual time=62474.200..62746.621 rows=75 loops=1) WAL: records=1515 fpi=1513 bytes=1206367 -> Sort (cost=4125821.43..4127173.46 rows=540812 width=75) (actual time=62474.197..62746.609 rows=75 loops=1) Sort Key: orders.o_totalprice DESC, orders.o_orderdate Sort Method: quicksort Memory: 35kB WAL: records=1515 fpi=1513 bytes=1206367 -> Finalize GroupAggregate (cost=4036795.78..4105151.98 rows=540812 width=75) (actual time=62473.727..62746.422 rows=75 loops=1) Group Key: customer.c_custkey, orders.o_orderkey WAL: records=1515 fpi=1513 bytes=1206367 -> Gather Merge (cost=4036795.78..4093885.07 rows=450676 width=75) (actual time=62473.700..62746.287 rows=75 loops=1) Workers Planned: 2 Workers Launched: 2 WAL: records=1515 fpi=1513 bytes=1206367 -> Partial GroupAggregate (cost=4035795.75..4040865.86 rows=225338 width=75) (actual time=62455.612..62455.977 rows=25 loops=3) Group Key: customer.c_custkey, orders.o_orderkey WAL: records=1515 fpi=1513 bytes=1206367 -> Sort (cost=4035795.75..4036359.10 rows=225338 width=48) (actual time=62455.578..62455.848 rows=175 loops=3) Sort Key: customer.c_custkey, orders.o_orderkey Sort Method: quicksort Memory: 54kB WAL: records=1515 fpi=1513 bytes=1206367 Worker 0: Sort Method: quicksort Memory: 42kB Worker 1: Sort Method: quicksort Memory: 51kB -> Nested Loop (cost=2946233.80..4008827.75 rows=225338 width=48) (actual time=57108.984..62455.610 rows=175 loops=3) WAL: records=1515 fpi=1513 bytes=1206367 -> Hash Join (cost=2946233.24..3499055.14 rows=56344 width=51) (actual time=57108.922..62454.568 rows=25 loops=3) Hash Cond: (orders.o_orderkey = lineitem_1.l_orderkey) WAL: records=1515 fpi=1513 bytes=1206367 -> Parallel Hash Join (cost=53591.95..479122.68 rows=6274351 width=43) (actual time=5460.899..8584.057 rows=5000000 loops=3) Hash Cond: (orders.o_custkey = customer.c_custkey) WAL: records=288 fpi=287 bytes=243519 -> Parallel Seq Scan on orders (cost=0.00..331869.51 rows=6274351 width=24) (actual time=0.079..2317.511 rows=5000000 loops=3) WAL: records=288 fpi=287 bytes=243519 -> Parallel Hash (cost=42116.20..42116.20 rows=625020 width=23) (actual time=411.620..411.621 rows=500000 loops=3) Buckets: 65536 Batches: 32 Memory Usage: 3136kB -> Parallel Seq Scan on customer (cost=0.00..42116.20 rows=625020 width=23) (actual time=0.036..192.184 rows=500000 loops=3) -> Hash (cost=2890421.96..2890421.96 rows=135226 width=8) (actual time=51198.419..51198.420 rows=75 loops=3) Buckets: 131072 Batches: 2 Memory Usage: 1026kB WAL: records=1227 fpi=1226 bytes=962848 -> GroupAggregate (cost=0.56..2889069.70 rows=135226 width=8) (actual time=2795.930..51198.127 rows=75 loops=3) Group Key: lineitem_1.l_orderkey Filter: (sum(lineitem_1.l_quantity) > '315'::numeric) Rows Removed by Filter: 14999925 WAL: records=1227 fpi=1226 bytes=962848 -> Index Only Scan using lineitem_l_orderkey_l_suppkey_l_quantity_idx on lineitem lineitem_1 (cost=0.56..2581867.13 rows=60223483 width=13) (actual time=0.082..19683.197 rows=59985781 loops=3) Heap Fetches: 1153760 WAL: records=1227 fpi=1226 bytes=962848 -> Index Only Scan using lineitem_l_orderkey_l_suppkey_l_quantity_idx on lineitem (cost=0.56..7.57 rows=148 width=13) (actual time=0.032..0.034 rows=7 loops=75) Index Cond: (l_orderkey = orders.o_orderkey) Heap Fetches: 0 c_name | c_custkey | o_orderkey | o_orderdate | o_totalprice | sum --------------------+-----------+------------+-------------+--------------+----- Customer#001287812 | 1287812 | 42290181 | 1997-11-26 | 558289.17 | 318 Customer#001172513 | 1172513 | 36667107 | 1997-06-06 | 550142.18 | 322 Customer#000571654 | 571654 | 21213895 | 1992-01-03 | 549380.08 | 327 Customer#000667882 | 667882 | 2199712 | 1996-09-30 | 542154.01 | 327 Customer#001471966 | 1471966 | 1263015 | 1997-02-02 | 540476.80 | 320 Customer#001101754 | 1101754 | 46794464 | 1992-04-28 | 532623.90 | 320 Customer#000591466 | 591466 | 55799200 | 1996-02-11 | 524209.45 | 327 Customer#001196317 | 1196317 | 34509573 | 1995-10-15 | 522897.01 | 318 Customer#001308376 | 1308376 | 28077922 | 1996-10-27 | 518711.99 | 319 Customer#000008782 | 8782 | 29294434 | 1993-01-05 | 518140.85 | 322 Customer#001232356 | 1232356 | 52340710 | 1993-01-01 | 512292.83 | 320 Customer#001247930 | 1247930 | 59138305 | 1993-10-31 | 509372.97 | 321 Customer#000998485 | 998485 | 51532000 | 1992-10-27 | 506996.90 | 319 Customer#001179178 | 1179178 | 2869152 | 1996-06-20 | 505142.88 | 317 Customer#000942725 | 942725 | 48881602 | 1993-09-19 | 504197.89 | 327 Customer#000212452 | 212452 | 23245603 | 1994-11-22 | 504070.63 | 321 Customer#000266747 | 266747 | 41274690 | 1992-08-10 | 503260.49 | 317 Customer#000896006 | 896006 | 24923202 | 1993-10-04 | 503198.09 | 316 Customer#000166589 | 166589 | 46685344 | 1993-12-14 | 496075.11 | 327 Customer#000324296 | 324296 | 26465922 | 1996-11-04 | 493279.72 | 331 Customer#001081807 | 1081807 | 8075589 | 1996-08-23 | 492479.61 | 316 Customer#000389473 | 389473 | 21135524 | 1992-11-22 | 487245.56 | 320 Customer#001151815 | 1151815 | 55722822 | 1995-08-24 | 485654.49 | 316 Customer#000372146 | 372146 | 48300454 | 1992-11-30 | 484962.56 | 318 Customer#000225562 | 225562 | 39492870 | 1995-08-12 | 484782.87 | 317 Customer#000109402 | 109402 | 10612710 | 1995-02-05 | 484659.70 | 319 Customer#000604861 | 604861 | 45897379 | 1994-05-30 | 481442.14 | 318 Customer#000347839 | 347839 | 53623108 | 1995-05-07 | 481251.56 | 316 Customer#001350514 | 1350514 | 43245189 | 1994-11-29 | 480988.72 | 320 Customer#000456290 | 456290 | 58000519 | 1996-01-17 | 480280.84 | 325 Customer#001076014 | 1076014 | 58919845 | 1995-07-21 | 478889.03 | 316 Customer#001379158 | 1379158 | 49474597 | 1992-03-06 | 478863.46 | 319 Customer#001115629 | 1115629 | 13165024 | 1992-06-29 | 477665.31 | 318 Customer#000598354 | 598354 | 56497062 | 1998-04-28 | 476882.56 | 320 Customer#001424164 | 1424164 | 48395044 | 1994-06-03 | 476439.88 | 316 Customer#000008846 | 8846 | 39702437 | 1995-11-23 | 475436.36 | 321 Customer#001075219 | 1075219 | 44143908 | 1992-02-24 | 472296.80 | 325 Customer#000156190 | 156190 | 3767271 | 1996-08-07 | 471132.45 | 318 Customer#000087946 | 87946 | 10805828 | 1997-07-29 | 469150.80 | 316 Customer#001209827 | 1209827 | 12314084 | 1994-05-16 | 467104.78 | 325 Customer#001268641 | 1268641 | 4702759 | 1994-11-07 | 466780.36 | 320 Customer#000366184 | 366184 | 4806726 | 1995-01-17 | 466287.90 | 328 Customer#000317285 | 317285 | 44178691 | 1998-01-24 | 459635.14 | 316 Customer#000773062 | 773062 | 38914978 | 1996-05-28 | 458849.50 | 324 Customer#001380298 | 1380298 | 13837220 | 1994-09-07 | 454998.75 | 316 Customer#000952849 | 952849 | 40944001 | 1993-12-15 | 454781.75 | 319 Customer#000109817 | 109817 | 10408580 | 1994-09-03 | 454420.76 | 320 Customer#000064357 | 64357 | 7235014 | 1998-03-09 | 454343.05 | 323 Customer#001140475 | 1140475 | 29122912 | 1996-05-08 | 453750.44 | 323 Customer#000523666 | 523666 | 32393895 | 1993-08-09 | 453417.44 | 316 Customer#001281199 | 1281199 | 4722021 | 1994-04-07 | 452129.83 | 323 Customer#000353261 | 353261 | 22017989 | 1996-06-17 | 452128.50 | 318 Customer#000631198 | 631198 | 12378086 | 1995-03-13 | 447447.22 | 320 Customer#000665395 | 665395 | 7810823 | 1998-01-05 | 447000.47 | 324 Customer#000181624 | 181624 | 17457767 | 1993-06-05 | 445836.21 | 316 Customer#001481954 | 1481954 | 43609317 | 1996-02-17 | 444565.67 | 325 Customer#001163407 | 1163407 | 11141606 | 1997-09-28 | 442694.39 | 325 Customer#000981457 | 981457 | 20971013 | 1997-08-12 | 442063.33 | 322 Customer#000500566 | 500566 | 35383045 | 1997-12-11 | 441239.00 | 317 Customer#000853729 | 853729 | 15071267 | 1994-10-22 | 440971.36 | 317 Customer#001446161 | 1446161 | 3043270 | 1997-02-12 | 440814.69 | 317 Customer#001242919 | 1242919 | 49847329 | 1995-09-20 | 440255.61 | 319 Customer#000167087 | 167087 | 17366275 | 1998-01-14 | 437804.26 | 316 Customer#001180559 | 1180559 | 19217698 | 1993-03-26 | 437603.57 | 316 Customer#000644810 | 644810 | 28184576 | 1994-01-29 | 437397.11 | 324 Customer#001207069 | 1207069 | 34201984 | 1997-06-18 | 433879.06 | 322 Customer#000773104 | 773104 | 33615872 | 1997-09-28 | 426834.17 | 317 Customer#000075070 | 75070 | 43997089 | 1994-08-07 | 424267.21 | 317 Customer#000144853 | 144853 | 11599171 | 1998-03-22 | 423978.95 | 316 Customer#001227100 | 1227100 | 35542498 | 1993-10-12 | 417981.24 | 325 Customer#001185599 | 1185599 | 54188961 | 1995-06-17 | 407873.05 | 317 Customer#001199881 | 1199881 | 1544643 | 1997-09-20 | 406906.07 | 320 Customer#001188142 | 1188142 | 42997059 | 1997-03-15 | 404132.92 | 320 Customer#001288183 | 1288183 | 48943904 | 1996-07-22 | 398081.59 | 325 Customer#000114613 | 114613 | 59930883 | 1997-05-17 | 394335.49 | 319 (75 rows) COMMIT; COMMIT