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_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date '1993-10-01' and o_orderdate < cast(date '1993-10-01' + interval '3 month' as date) and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20; NOTICE: duration: 18737.872 ms plan: Query Text: select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date '1993-10-01' and o_orderdate < cast(date '1993-10-01' + interval '3 month' as date) and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc LIMIT 20; Limit (cost=2147612.56..2147612.61 rows=20 width=202) (actual time=18320.580..18737.809 rows=20 loops=1) -> Sort (cost=2147612.56..2149075.05 rows=584994 width=202) (actual time=18320.577..18737.802 rows=20 loops=1) Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC Sort Method: top-N heapsort Memory: 34kB -> Finalize GroupAggregate (cost=2056886.49..2132046.08 rows=584994 width=202) (actual time=15981.979..18485.718 rows=381135 loops=1) Group Key: customer.c_custkey, nation.n_name -> Gather Merge (cost=2056886.49..2119858.70 rows=487496 width=202) (actual time=15981.961..17678.469 rows=381870 loops=1) Workers Planned: 2 Workers Launched: 2 -> Partial GroupAggregate (cost=2055886.47..2062589.54 rows=243748 width=202) (actual time=15962.422..16780.923 rows=127290 loops=3) Group Key: customer.c_custkey, nation.n_name -> Sort (cost=2055886.47..2056495.84 rows=243748 width=182) (actual time=15962.385..16119.833 rows=382361 loops=3) Sort Key: customer.c_custkey, nation.n_name Sort Method: external merge Disk: 73936kB Worker 0: Sort Method: external merge Disk: 74600kB Worker 1: Sort Method: external merge Disk: 70416kB -> Hash Join (cost=430836.12..2012415.58 rows=243748 width=182) (actual time=14815.727..15364.014 rows=382361 loops=3) Hash Cond: (customer.c_nationkey = nation.n_nationkey) -> Parallel Hash Join (cost=430834.56..2011665.71 rows=243748 width=160) (actual time=14815.604..15197.343 rows=382361 loops=3) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Parallel Hash Join (cost=367476.61..1931856.92 rows=243748 width=16) (actual time=12735.137..13895.007 rows=382361 loops=3) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Parallel Seq Scan on lineitem (cost=0.00..1473147.97 rows=6277461 width=20) (actual time=0.061..8483.683 rows=4936076 loops=3) Filter: (l_returnflag = 'R'::bpchar) Rows Removed by Filter: 15059184 -> Parallel Hash (cost=363241.27..363241.27 rows=243627 width=12) (actual time=1914.528..1914.529 rows=191051 loops=3) Buckets: 131072 Batches: 16 Memory Usage: 2752kB -> Parallel Seq Scan on orders (cost=0.00..363241.27 rows=243627 width=12) (actual time=0.051..1814.334 rows=191051 loops=3) Filter: ((o_orderdate >= '1993-10-01'::date) AND (o_orderdate < '1994-01-01'::date)) Rows Removed by Filter: 4808949 -> Parallel Hash (cost=42116.20..42116.20 rows=625020 width=148) (actual time=712.762..712.763 rows=500000 loops=3) Buckets: 32768 Batches: 128 Memory Usage: 2432kB -> Parallel Seq Scan on customer (cost=0.00..42116.20 rows=625020 width=148) (actual time=0.036..264.158 rows=500000 loops=3) -> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.059..0.059 rows=25 loops=3) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=30) (actual time=0.039..0.044 rows=25 loops=3) c_custkey | c_name | revenue | c_acctbal | n_name | c_address | c_phone | c_comment -----------+--------------------+-------------+-----------+---------------------------+---------------------------------------+-----------------+--------------------------------------------------------------------------------------------------------------------- 1237537 | Customer#001237537 | 884989.6657 | 7840.17 | RUSSIA | FNG6WgB1mopyyY,ajQTU qUPW5o | 32-367-120-4327 | nag carefully about the regular packages. carefully reg 1116802 | Customer#001116802 | 830214.1117 | 772.17 | JORDAN | JpGQT3IHoogqB3ZMrSrzhBrVFObr | 23-199-655-3770 | ests. quickly ironic deposits along the special requests a 508954 | Customer#000508954 | 826167.5138 | 3072.07 | ETHIOPIA | zgafElAXecB3I7Ee1a9B | 15-880-510-9487 | ickly regular deposits. carefully regular instructions engage deposits. foxes are caref 1487503 | Customer#001487503 | 825996.5903 | 5398.11 | IRAN | orychjo, O6c | 20-271-234-4401 | ccounts sleep carefully. furiously pending packages boost slyly after the quickly regular requests. pinto beans fro 1461247 | Customer#001461247 | 775992.0820 | 3205.93 | SAUDI ARABIA | jQZKjwNqttmC0BohJoGA4U | 30-137-949-5375 | atterns sleep blithely. fluffily pending accounts sleep furiously from the ironic deposits! blithely even i 1316338 | Customer#001316338 | 765063.7003 | 8682.95 | INDIA | bfz3FH qaULsV7oiCn66z6ezkZBpcl | 18-793-610-3832 | equests. regular, even excuses doze slyly against the blithely even accounts. slyly regular dinos 817849 | Customer#000817849 | 737664.7232 | -380.30 | MOROCCO | 7gz7HbNoVNSbv23trx | 25-111-847-3201 | fully special accounts wake slyly among the sheaves. permanently brave requests use furiously amon 1335394 | Customer#001335394 | 731259.8675 | 3978.53 | VIETNAM | mdNmISybrVNaIPSp | 31-760-271-2302 | slyly even instructions. carefully silent ideas 102358 | Customer#000102358 | 715001.8825 | 3564.90 | BRAZIL | YpDpv0sP14F5xMUQbkDV,XGcMJypFV6jH | 12-941-212-8350 | y slyly regular multipliers. furiously regular deposits nag final excuses. slyly special accounts haggle re 1432624 | Customer#001432624 | 713512.6431 | 205.08 | FRANCE | NTCV7qvJ0PgcULFci,VdAB,0ZbSEZRdnnz, | 16-550-232-2774 | ctions use regular requests. quickly bold somas cajole at the c 160528 | Customer#000160528 | 710673.3317 | 3425.52 | INDONESIA | 6 2h0BtgTVFrbGbg7a7ThCOLvfvb2a8FOm2qs | 19-670-600-6340 | sly regular theodolites. bold, regular foxes cajole. 1345237 | Customer#001345237 | 707735.5745 | 6905.22 | FRANCE | bhu14nxGOuDn2FldK6kidxkQt | 16-133-279-4001 | s according to the express, unusual acc 839816 | Customer#000839816 | 705643.2854 | 2110.25 | UNITED STATES | AROroot0eKdCxq | 34-453-282-7925 | unusual requests haggle slyly alongside of the slyly unusual ideas. f 1136716 | Customer#001136716 | 695043.5606 | 5886.01 | CHINA | uAIqfk27UKRIIBZLUvKDfUOyh | 28-729-762-5783 | e foxes. bold orbits impress slyly about the regular, regular theodolites; carefully exp 1439413 | Customer#001439413 | 690161.9783 | 8808.65 | ETHIOPIA | WweMOTcCq4A8yHDL | 15-432-961-8772 | quick foxes are. fluffily regular deposits wake besides the sl 1392475 | Customer#001392475 | 685464.4086 | 593.26 | SAUDI ARABIA | PHuNdZucsuZvpiKqcsBVPY0R jeHSBZL | 30-640-220-2095 | boldly-- slyly careful requests sleep after the slyly regular depo 616909 | Customer#000616909 | 684978.1044 | 2660.84 | EGYPT | iKRlIpRSp65EFpPY47d | 14-185-768-2185 | uests. theodolites haggle abov 314272 | Customer#000314272 | 682212.6365 | 5127.92 | UNITED STATES | sfg9XaV58yPqF7Wo0Akpif42GFh6 | 34-674-752-5199 | fix carefully deposits. even packages haggle. fluffily unusual platelets h 1472980 | Customer#001472980 | 677765.9180 | 6003.36 | IRAQ | iie M4kwzvAwMV Hpvv | 21-506-776-4657 | s use slyly thinly final deposits. ironic pinto beans poach furiously bl 1486201 | Customer#001486201 | 674878.4539 | -276.92 | JORDAN | TJhgKueFwrrtXLtenhlw sC2N | 23-611-797-4750 | ag. deposits along the blithely express instructions w (20 rows) COMMIT; COMMIT