ph_results.tar
application/x-tar
ph_results/ph/3_2.out 000644 001750 001750 00000007153 13044357137 014334 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2538085.87..2538085.89 rows=10 width=48) (actual time=59128.380..59128.384 rows=10 loops=1)
-> Sort (cost=2538085.87..2553711.81 rows=6250377 width=48) (actual time=59128.379..59128.381 rows=10 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate
Sort Method: top-N heapsort Memory: 26kB
-> GroupAggregate (cost=2215506.16..2403017.47 rows=6250377 width=48) (actual time=56668.052..58893.227 rows=226765 loops=1)
Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
-> Sort (cost=2215506.16..2231132.10 rows=6250377 width=28) (actual time=56667.998..56853.404 rows=599838 loops=1)
Sort Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
Sort Method: quicksort Memory: 61987kB
-> Gather (cost=194179.48..1501433.26 rows=6250377 width=28) (actual time=10253.837..55585.046 rows=599838 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Hash Join (cost=193179.48..875395.56 rows=1562594 width=28) (actual time=10250.668..46704.418 rows=119968 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=16076611 width=20) (actual time=0.173..24608.535 rows=16109445 loops=4)
Filter: (l_shipdate > '1995-03-20'::date)
Rows Removed by Filter: 13889207
-> Parallel Shared Hash (cost=183338.34..183338.34 rows=728973 width=12) (actual time=10236.254..10236.254 rows=585334 loops=5)
Buckets: 1048576 (originally 1048576) Batches: 4 (originally 1) Memory Usage: 42592kB
-> Parallel Hash Join (cost=18807.32..183338.34 rows=728973 width=12) (actual time=678.648..7617.643 rows=585334 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Parallel Seq Scan on orders (cost=0.00..148996.90 rows=3656447 width=20) (actual time=0.025..5363.958 rows=3660000 loops=4)
Filter: (o_orderdate < '1995-03-20'::date)
Rows Removed by Filter: 3840000
-> Parallel Shared Hash (cost=16788.53..16788.53 rows=149540 width=4) (actual time=676.032..676.032 rows=119664 loops=5)
Buckets: 1048576 (originally 262144) Batches: 1 (originally 1) Memory Usage: 31680kB
-> Parallel Seq Scan on customer (cost=0.00..16788.53 rows=149540 width=4) (actual time=0.019..519.977 rows=119664 loops=5)
Filter: (c_mktsegment = 'FURNITURE'::bpchar)
Rows Removed by Filter: 480336
Planning time: 3.925 ms
Execution time: 59135.181 ms
(31 rows)
ph_results/head/7_2.out 000644 001750 001750 00000013246 13044363357 014633 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1283926.71..1283926.79 rows=1 width=92) (actual time=41251.397..41251.398 rows=1 loops=1)
-> Finalize GroupAggregate (cost=1283926.71..1284704.50 rows=10036 width=92) (actual time=41251.395..41251.395 rows=1 loops=1)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=1283926.71..1284027.07 rows=40144 width=92) (actual time=41251.352..41251.354 rows=6 loops=1)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 27kB
-> Gather (cost=1276142.56..1280857.12 rows=40144 width=92) (actual time=41137.216..41251.212 rows=20 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=1275142.56..1275842.72 rows=10036 width=92) (actual time=41133.696..41218.273 rows=4 loops=5)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=1275142.56..1275217.50 rows=29973 width=72) (actual time=41106.521..41116.089 rows=23488 loops=5)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 4099kB
-> Hash Join (cost=25864.84..1272913.87 rows=29973 width=72) (actual time=843.192..41046.251 rows=23488 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
Join Filter: (((n1.n_name = 'ARGENTINA'::bpchar) AND (n2.n_name = 'IRAN'::bpchar)) OR ((n1.n_name = 'IRAN'::bpchar) AND (n2.n_name = 'ARGENTINA'::bpchar)))
Rows Removed by Join Filter: 23456
-> Nested Loop (cost=1613.46..1244659.41 rows=720228 width=50) (actual time=48.554..39640.866 rows=586259 loops=5)
-> Hash Join (cost=1612.90..735594.78 rows=720228 width=50) (actual time=48.488..29261.591 rows=586259 loops=5)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
-> Parallel Seq Scan on lineitem (cost=0.00..693018.90 rows=9002853 width=32) (actual time=0.045..25292.426 rows=7292378 loops=5)
Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate <= '1996-12-31'::date))
Rows Removed by Filter: 16706543
-> Hash (cost=1412.90..1412.90 rows=16000 width=30) (actual time=48.231..48.231 rows=16082 loops=5)
Buckets: 16384 Batches: 1 Memory Usage: 1102kB
-> Nested Loop (cost=0.42..1412.90 rows=16000 width=30) (actual time=0.092..37.148 rows=16082 loops=5)
-> Seq Scan on nation n1 (cost=0.00..0.47 rows=2 width=30) (actual time=0.017..0.047 rows=2 loops=5)
Filter: ((n_name = 'ARGENTINA'::bpchar) OR (n_name = 'IRAN'::bpchar))
Rows Removed by Filter: 23
-> Index Scan using idx_supplier_nation_key on supplier (cost=0.42..626.21 rows=8000 width=12) (actual time=0.047..14.612 rows=8041 loops=10)
Index Cond: (s_nationkey = n1.n_nationkey)
-> Index Scan using orders_pkey on orders (cost=0.56..0.70 rows=1 width=12) (actual time=0.015..0.016 rows=1 loops=2931293)
Index Cond: (o_orderkey = lineitem.l_orderkey)
-> Hash (cost=21251.08..21251.08 rows=240024 width=30) (actual time=792.532..792.532 rows=240035 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 16582kB
-> Nested Loop (cost=0.43..21251.08 rows=240024 width=30) (actual time=0.057..612.706 rows=240035 loops=5)
-> Seq Scan on nation n2 (cost=0.00..0.47 rows=2 width=30) (actual time=0.011..0.050 rows=2 loops=5)
Filter: ((n_name = 'IRAN'::bpchar) OR (n_name = 'ARGENTINA'::bpchar))
Rows Removed by Filter: 23
-> Index Scan using idx_customer_nationkey on customer (cost=0.43..9425.18 rows=120012 width=12) (actual time=0.051..246.588 rows=120018 loops=10)
Index Cond: (c_nationkey = n2.n_nationkey)
Planning time: 9.164 ms
Execution time: 41346.711 ms
(44 rows)
ph_results/ph/7_1.out 000644 001750 001750 00000013752 13044357123 014334 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=896037.66..896037.74 rows=1 width=92) (actual time=48864.547..48864.547 rows=1 loops=1)
-> Finalize GroupAggregate (cost=896037.66..896815.45 rows=10036 width=92) (actual time=48864.544..48864.544 rows=1 loops=1)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=896037.66..896138.02 rows=40144 width=92) (actual time=48864.502..48864.504 rows=5 loops=1)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 27kB
-> Gather (cost=888253.52..892968.07 rows=40144 width=92) (actual time=48721.002..48864.386 rows=16 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=887253.52..887953.67 rows=10036 width=92) (actual time=38991.779..39063.418 rows=3 loops=5)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=887253.52..887328.45 rows=29973 width=72) (actual time=38968.290..38976.538 rows=23488 loops=5)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 25kB
-> Parallel Hash Join (cost=153113.77..885024.82 rows=29973 width=72) (actual time=5228.467..38920.048 rows=23488 loops=5)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
Join Filter: (((n1.n_name = 'ARGENTINA'::bpchar) AND (n2.n_name = 'IRAN'::bpchar)) OR ((n1.n_name = 'IRAN'::bpchar) AND (n2.n_name = 'ARGENTINA'::bpchar)))
Rows Removed by Join Filter: 23456
-> Parallel Hash Join (cost=151350.97..879931.13 rows=720228 width=50) (actual time=6463.633..47910.766 rows=730365 loops=4)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..693018.90 rows=9002853 width=32) (actual time=0.070..27527.188 rows=9115473 loops=4)
Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate <= '1996-12-31'::date))
Rows Removed by Filter: 20883179
-> Parallel Shared Hash (cost=143250.99..143250.99 rows=599998 width=30) (actual time=6450.038..6450.038 rows=600598 loops=4)
-> Nested Loop (cost=1.06..143250.99 rows=599998 width=30) (actual time=0.274..5574.329 rows=600598 loops=4)
-> Hash Join (cost=0.50..18326.68 rows=60006 width=30) (actual time=0.113..925.769 rows=60009 loops=4)
Hash Cond: (customer.c_nationkey = n2.n_nationkey)
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=12) (actual time=0.014..365.177 rows=750000 loops=4)
-> Hash (cost=0.47..0.47 rows=2 width=30) (actual time=0.068..0.068 rows=2 loops=4)
-> Seq Scan on nation n2 (cost=0.00..0.47 rows=2 width=30) (actual time=0.017..0.052 rows=2 loops=4)
Filter: ((n_name = 'IRAN'::bpchar) OR (n_name = 'ARGENTINA'::bpchar))
Rows Removed by Filter: 23
-> Index Scan using idx_orders_custkey on orders (cost=0.56..1.91 rows=17 width=12) (actual time=0.023..0.070 rows=10 loops=240035)
Index Cond: (o_custkey = customer.c_custkey)
-> Parallel Shared Hash (cost=1672.80..1672.80 rows=6667 width=30) (actual time=55.859..55.859 rows=3216 loops=5)
Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1216kB
-> Hash Join (cost=0.50..1672.80 rows=6667 width=30) (actual time=0.106..51.171 rows=3216 loops=5)
Hash Cond: (supplier.s_nationkey = n1.n_nationkey)
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.029..28.281 rows=40000 loops=5)
-> Hash (cost=0.47..0.47 rows=2 width=30) (actual time=0.050..0.050 rows=2 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on nation n1 (cost=0.00..0.47 rows=2 width=30) (actual time=0.021..0.046 rows=2 loops=5)
Filter: ((n_name = 'ARGENTINA'::bpchar) OR (n_name = 'IRAN'::bpchar))
Rows Removed by Filter: 23
Planning time: 9.572 ms
Execution time: 49019.346 ms
(46 rows)
ph_results/ph/7_4.out 000644 001750 001750 00000013754 13044357127 014345 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=896037.66..896037.74 rows=1 width=92) (actual time=45854.009..45854.009 rows=1 loops=1)
-> Finalize GroupAggregate (cost=896037.66..896815.45 rows=10036 width=92) (actual time=45854.007..45854.007 rows=1 loops=1)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=896037.66..896138.02 rows=40144 width=92) (actual time=45853.961..45853.962 rows=5 loops=1)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 27kB
-> Gather (cost=888253.52..892968.07 rows=40144 width=92) (actual time=45586.605..45853.794 rows=16 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=887253.52..887953.67 rows=10036 width=92) (actual time=36500.150..36587.744 rows=3 loops=5)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=887253.52..887328.45 rows=29973 width=72) (actual time=36471.284..36481.165 rows=23488 loops=5)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 25kB
-> Parallel Hash Join (cost=153113.77..885024.82 rows=29973 width=72) (actual time=4048.002..36413.859 rows=23488 loops=5)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
Join Filter: (((n1.n_name = 'ARGENTINA'::bpchar) AND (n2.n_name = 'IRAN'::bpchar)) OR ((n1.n_name = 'IRAN'::bpchar) AND (n2.n_name = 'ARGENTINA'::bpchar)))
Rows Removed by Join Filter: 23456
-> Parallel Hash Join (cost=151350.97..879931.13 rows=720228 width=50) (actual time=4986.187..44816.467 rows=730365 loops=4)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..693018.90 rows=9002853 width=32) (actual time=38.529..26897.297 rows=9115473 loops=4)
Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate <= '1996-12-31'::date))
Rows Removed by Filter: 20883179
-> Parallel Shared Hash (cost=143250.99..143250.99 rows=599998 width=30) (actual time=4895.860..4895.860 rows=600598 loops=4)
-> Nested Loop (cost=1.06..143250.99 rows=599998 width=30) (actual time=0.189..4187.642 rows=600598 loops=4)
-> Hash Join (cost=0.50..18326.68 rows=60006 width=30) (actual time=0.090..835.996 rows=60009 loops=4)
Hash Cond: (customer.c_nationkey = n2.n_nationkey)
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=12) (actual time=0.010..244.396 rows=750000 loops=4)
-> Hash (cost=0.47..0.47 rows=2 width=30) (actual time=0.055..0.055 rows=2 loops=4)
-> Seq Scan on nation n2 (cost=0.00..0.47 rows=2 width=30) (actual time=0.014..0.041 rows=2 loops=4)
Filter: ((n_name = 'IRAN'::bpchar) OR (n_name = 'ARGENTINA'::bpchar))
Rows Removed by Filter: 23
-> Index Scan using idx_orders_custkey on orders (cost=0.56..1.91 rows=17 width=12) (actual time=0.016..0.050 rows=10 loops=240035)
Index Cond: (o_custkey = customer.c_custkey)
-> Parallel Shared Hash (cost=1672.80..1672.80 rows=6667 width=30) (actual time=49.435..49.435 rows=3216 loops=5)
Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1216kB
-> Hash Join (cost=0.50..1672.80 rows=6667 width=30) (actual time=0.081..45.004 rows=3216 loops=5)
Hash Cond: (supplier.s_nationkey = n1.n_nationkey)
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.014..16.992 rows=40000 loops=5)
-> Hash (cost=0.47..0.47 rows=2 width=30) (actual time=0.051..0.051 rows=2 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on nation n1 (cost=0.00..0.47 rows=2 width=30) (actual time=0.017..0.044 rows=2 loops=5)
Filter: ((n_name = 'ARGENTINA'::bpchar) OR (n_name = 'IRAN'::bpchar))
Rows Removed by Filter: 23
Planning time: 11.143 ms
Execution time: 46022.868 ms
(46 rows)
ph_results/ph/10_3.out 000644 001750 001750 00000010376 13044357136 014413 0 ustar 00edb edb 000000 000000 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1068412.51..1068412.56 rows=20 width=201) (actual time=63519.803..63519.811 rows=20 loops=1)
-> Sort (cost=1068412.51..1071193.68 rows=1112470 width=201) (actual time=63519.801..63519.806 rows=20 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 34kB
-> GroupAggregate (cost=1008217.16..1038810.08 rows=1112470 width=201) (actual time=52034.142..62336.245 rows=765020 loops=1)
Group Key: customer.c_custkey, nation.n_name
-> Sort (cost=1008217.16..1010998.33 rows=1112470 width=181) (actual time=52034.064..53795.359 rows=2298951 loops=1)
Sort Key: customer.c_custkey, nation.n_name
Sort Method: external merge Disk: 438448kB
-> Gather (cost=125807.02..890846.09 rows=1112470 width=181) (actual time=7850.098..46847.494 rows=2298951 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=124807.02..778599.09 rows=278118 width=181) (actual time=6608.366..37055.071 rows=459790 loops=5)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Parallel Hash Join (cost=124806.36..774774.31 rows=278118 width=163) (actual time=6608.078..36546.729 rows=459790 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Parallel Hash Join (cost=99767.01..747996.74 rows=278118 width=20) (actual time=6194.226..39763.869 rows=574738 loops=4)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=7313668 width=20) (actual time=0.105..24676.583 rows=7402949 loops=4)
Filter: (l_returnflag = 'R'::bpchar)
Rows Removed by Filter: 22595703
-> Shared Hash (cost=84366.09..84366.09 rows=1140809 width=12) (actual time=6163.211..6163.211 rows=286991 loops=4)
-> Bitmap Heap Scan on orders (cost=12006.75..84366.09 rows=1140809 width=12) (actual time=1880.952..4861.870 rows=1147963 loops=1)
Recheck Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..11721.55 rows=1140809 width=0) (actual time=1409.410..1409.410 rows=1147963 loops=1)
Index Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Parallel Shared Hash (cost=14913.35..14913.35 rows=750074 width=151) (actual time=1640.122..1640.122 rows=600000 loops=5)
Buckets: 1048576 (originally 1048576) Batches: 16 (originally 1) Memory Usage: 43072kB
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=151) (actual time=0.012..597.135 rows=600000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.049..0.049 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.010..0.021 rows=25 loops=5)
Planning time: 4.908 ms
Execution time: 63585.394 ms
(34 rows)
ph_results/head/5_2.out 000644 001750 001750 00000014477 13044363355 014636 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=993197.98..993197.98 rows=1 width=58) (actual time=55111.960..55111.960 rows=1 loops=1)
-> Sort (cost=993197.98..993198.04 rows=25 width=58) (actual time=55111.957..55111.957 rows=1 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize GroupAggregate (cost=993196.54..993197.86 rows=25 width=58) (actual time=55111.773..55111.903 rows=5 loops=1)
Group Key: nation.n_name
-> Sort (cost=993196.54..993196.79 rows=100 width=58) (actual time=55111.717..55111.730 rows=25 loops=1)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 28kB
-> Gather (cost=992723.87..993193.22 rows=100 width=58) (actual time=54412.669..55111.630 rows=25 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=991723.87..992183.22 rows=25 width=58) (actual time=54291.251..54369.868 rows=5 loops=5)
Group Key: nation.n_name
-> Sort (cost=991723.87..991815.68 rows=36723 width=38) (actual time=54271.348..54280.701 rows=29012 loops=5)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 3079kB
-> Hash Join (cost=275164.82..988939.48 rows=36723 width=38) (actual time=14267.230..54224.575 rows=29012 loops=5)
Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))
-> Hash Join (cost=269705.02..976227.01 rows=918060 width=58) (actual time=13982.701..53342.522 rows=728180 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..543025.70 rows=29998640 width=28) (actual time=0.073..18295.808 rows=23998922 loops=5)
-> Hash (cost=257421.79..257421.79 rows=918098 width=42) (actual time=13980.291..13980.291 rows=910215 loops=5)
Buckets: 1048576 Batches: 2 Memory Usage: 41956kB
-> Hash Join (cost=106921.90..257421.79 rows=918098 width=42) (actual time=5237.721..13196.308 rows=910215 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Bitmap Heap Scan on orders (cost=48311.90..172416.46 rows=4590491 width=12) (actual time=3145.948..7612.922 rows=4549846 loops=5)
Recheck Cond: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
Heap Blocks: exact=552373
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..47164.27 rows=4590491 width=0) (actual time=2700.951..2700.951 rows=4549846 loops=5)
Index Cond: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Hash (cost=51109.25..51109.25 rows=600060 width=42) (actual time=2090.741..2090.741 rows=599436 loops=5)
Buckets: 1048576 Batches: 1 Memory Usage: 55023kB
-> Nested Loop (cost=0.43..51109.25 rows=600060 width=42) (actual time=0.310..1586.710 rows=599436 loops=5)
-> Nested Loop (cost=0.00..0.82 rows=5 width=30) (actual time=0.124..0.178 rows=5 loops=5)
Join Filter: (nation.n_regionkey = region.r_regionkey)
Rows Removed by Join Filter: 20
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.106..0.113 rows=1 loops=5)
Filter: (r_name = 'AFRICA'::bpchar)
Rows Removed by Filter: 4
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=38) (actual time=0.010..0.028 rows=25 loops=5)
-> Index Scan using idx_customer_nationkey on customer (cost=0.43..9021.57 rows=120012 width=12) (actual time=0.071..245.405 rows=119887 loops=25)
Index Cond: (c_nationkey = nation.n_nationkey)
-> Hash (cost=2459.80..2459.80 rows=200000 width=12) (actual time=280.716..280.716 rows=200000 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 11423kB
-> Seq Scan on supplier (cost=0.00..2459.80 rows=200000 width=12) (actual time=0.013..127.702 rows=200000 loops=5)
Planning time: 12.169 ms
Execution time: 55190.565 ms
(48 rows)
ph_results/ph/5_1.out 000644 001750 001750 00000013552 13044357124 014331 0 ustar 00edb edb 000000 000000 QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=868714.16..868714.16 rows=1 width=58) (actual time=55706.529..55706.529 rows=1 loops=1)
-> Sort (cost=868714.16..868714.22 rows=25 width=58) (actual time=55706.526..55706.526 rows=1 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize GroupAggregate (cost=868712.72..868714.03 rows=25 width=58) (actual time=55706.372..55706.442 rows=5 loops=1)
Group Key: nation.n_name
-> Sort (cost=868712.72..868712.97 rows=100 width=58) (actual time=55706.326..55706.332 rows=20 loops=1)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 27kB
-> Gather (cost=868240.05..868709.40 rows=100 width=58) (actual time=55504.433..55706.213 rows=20 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=867240.05..867699.40 rows=25 width=58) (actual time=44451.868..44529.760 rows=4 loops=5)
Group Key: nation.n_name
-> Sort (cost=867240.05..867331.86 rows=36723 width=38) (actual time=44432.998..44442.658 rows=29012 loops=5)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 25kB
-> Parallel Hash Join (cost=199601.45..864455.66 rows=36723 width=38) (actual time=7685.086..44386.155 rows=29012 loops=5)
Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))
-> Parallel Hash Join (cost=196974.99..854790.74 rows=918060 width=58) (actual time=9478.901..54495.228 rows=910222 loops=4)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..543025.70 rows=29998640 width=28) (actual time=0.115..10804.196 rows=29998652 loops=4)
-> Parallel Shared Hash (cost=193876.41..193876.41 rows=229524 width=42) (actual time=9471.140..9471.140 rows=227554 loops=4)
-> Parallel Hash Join (cost=21252.18..193876.41 rows=229524 width=42) (actual time=962.286..9129.980 rows=227554 loops=4)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Parallel Seq Scan on orders (cost=0.00..167746.84 rows=1147623 width=12) (actual time=0.063..6999.427 rows=1137462 loops=4)
Filter: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 6362538
-> Parallel Shared Hash (cost=19226.97..19226.97 rows=150015 width=42) (actual time=959.058..959.058 rows=149859 loops=4)
-> Hash Join (cost=0.70..19226.97 rows=150015 width=42) (actual time=0.161..746.990 rows=149859 loops=4)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=12) (actual time=0.038..254.462 rows=750000 loops=4)
-> Hash (cost=0.64..0.64 rows=5 width=30) (actual time=0.111..0.111 rows=5 loops=4)
-> Nested Loop (cost=0.14..0.64 rows=5 width=30) (actual time=0.095..0.106 rows=5 loops=4)
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.021..0.025 rows=1 loops=4)
Filter: (r_name = 'AFRICA'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using idx_nation_regionkey on nation (cost=0.14..0.43 rows=5 width=38) (actual time=0.070..0.074 rows=5 loops=4)
Index Cond: (n_regionkey = region.r_regionkey)
-> Parallel Shared Hash (cost=1293.13..1293.13 rows=83333 width=12) (actual time=96.706..96.706 rows=40000 loops=5)
Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 11520kB
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.016..32.852 rows=40000 loops=5)
Planning time: 25.372 ms
Execution time: 55851.172 ms
(44 rows)
ph_results/ph/5_4.out 000644 001750 001750 00000013560 13044357125 014334 0 ustar 00edb edb 000000 000000 QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=868714.16..868714.16 rows=1 width=58) (actual time=55607.832..55607.833 rows=1 loops=1)
-> Sort (cost=868714.16..868714.22 rows=25 width=58) (actual time=55607.829..55607.829 rows=1 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize GroupAggregate (cost=868712.72..868714.03 rows=25 width=58) (actual time=55607.658..55607.745 rows=5 loops=1)
Group Key: nation.n_name
-> Sort (cost=868712.72..868712.97 rows=100 width=58) (actual time=55607.597..55607.604 rows=20 loops=1)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 27kB
-> Gather (cost=868240.05..868709.40 rows=100 width=58) (actual time=55443.727..55607.503 rows=20 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=867240.05..867699.40 rows=25 width=58) (actual time=44382.274..44456.126 rows=4 loops=5)
Group Key: nation.n_name
-> Sort (cost=867240.05..867331.86 rows=36723 width=38) (actual time=44363.902..44373.104 rows=29012 loops=5)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 25kB
-> Parallel Hash Join (cost=199601.45..864455.66 rows=36723 width=38) (actual time=8105.285..44317.274 rows=29012 loops=5)
Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))
-> Parallel Hash Join (cost=196974.99..854790.74 rows=918060 width=58) (actual time=10008.968..54424.458 rows=910223 loops=4)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..543025.70 rows=29998640 width=28) (actual time=0.345..10567.394 rows=29998652 loops=4)
-> Parallel Shared Hash (cost=193876.41..193876.41 rows=229524 width=42) (actual time=10003.667..10003.667 rows=227554 loops=4)
-> Parallel Hash Join (cost=21252.18..193876.41 rows=229524 width=42) (actual time=1179.545..9645.741 rows=227554 loops=4)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Parallel Seq Scan on orders (cost=0.00..167746.84 rows=1147623 width=12) (actual time=0.087..7298.657 rows=1137462 loops=4)
Filter: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 6362538
-> Parallel Shared Hash (cost=19226.97..19226.97 rows=150015 width=42) (actual time=1176.264..1176.264 rows=149859 loops=4)
-> Hash Join (cost=0.70..19226.97 rows=150015 width=42) (actual time=0.180..920.233 rows=149859 loops=4)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=12) (actual time=0.014..299.895 rows=750000 loops=4)
-> Hash (cost=0.64..0.64 rows=5 width=30) (actual time=0.152..0.152 rows=5 loops=4)
-> Nested Loop (cost=0.14..0.64 rows=5 width=30) (actual time=0.136..0.146 rows=5 loops=4)
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.025..0.029 rows=1 loops=4)
Filter: (r_name = 'AFRICA'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using idx_nation_regionkey on nation (cost=0.14..0.43 rows=5 width=38) (actual time=0.104..0.108 rows=5 loops=4)
Index Cond: (n_regionkey = region.r_regionkey)
-> Parallel Shared Hash (cost=1293.13..1293.13 rows=83333 width=12) (actual time=93.904..93.904 rows=40000 loops=5)
Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 11488kB
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.019..34.680 rows=40000 loops=5)
Planning time: 15.304 ms
Execution time: 55751.214 ms
(44 rows)
ph_results/head/10_2.out 000644 001750 001750 00000010544 13044363362 014677 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1142835.84..1142835.89 rows=20 width=201) (actual time=62389.365..62389.374 rows=20 loops=1)
-> Sort (cost=1142835.84..1145617.02 rows=1112470 width=201) (actual time=62389.362..62389.368 rows=20 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 34kB
-> GroupAggregate (cost=1082640.49..1113233.42 rows=1112470 width=201) (actual time=50544.201..61177.982 rows=765020 loops=1)
Group Key: customer.c_custkey, nation.n_name
-> Sort (cost=1082640.49..1085421.67 rows=1112470 width=181) (actual time=50544.120..52358.557 rows=2298951 loops=1)
Sort Key: customer.c_custkey, nation.n_name
Sort Method: external merge Disk: 438440kB
-> Gather (cost=181549.27..965269.43 rows=1112470 width=181) (actual time=11643.302..44969.539 rows=2298951 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=180549.27..853022.43 rows=278118 width=181) (actual time=12203.930..42683.489 rows=459790 loops=5)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Hash Join (cost=180548.61..849197.65 rows=278118 width=163) (actual time=12203.641..42196.609 rows=459790 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Hash Join (cost=99183.30..756540.93 rows=278118 width=20) (actual time=5287.775..32899.446 rows=459790 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=7313668 width=20) (actual time=0.139..21569.881 rows=5922359 loops=5)
Filter: (l_returnflag = 'R'::bpchar)
Rows Removed by Filter: 18076562
-> Hash (cost=84366.09..84366.09 rows=1140809 width=12) (actual time=5284.567..5284.567 rows=1147963 loops=5)
Buckets: 2097152 Batches: 2 Memory Usage: 41041kB
-> Bitmap Heap Scan on orders (cost=12006.75..84366.09 rows=1140809 width=12) (actual time=1278.472..4420.877 rows=1147963 loops=5)
Recheck Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
Heap Blocks: exact=486100
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..11721.55 rows=1140809 width=0) (actual time=876.813..876.813 rows=1147963 loops=5)
Index Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Hash (cost=37415.58..37415.58 rows=3000298 width=151) (actual time=6910.709..6910.709 rows=3000000 loops=5)
Buckets: 524288 Batches: 16 Memory Usage: 38667kB
-> Seq Scan on customer (cost=0.00..37415.58 rows=3000298 width=151) (actual time=0.012..2787.959 rows=3000000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.043..0.043 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.012..0.023 rows=25 loops=5)
Planning time: 5.989 ms
Execution time: 62446.136 ms
(36 rows)
ph_results/head/9_1.out 000644 001750 001750 00000011125 13044363364 014624 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=793072.62..793072.62 rows=1 width=66) (actual time=64231.570..64231.570 rows=1 loops=1)
-> Sort (cost=793072.62..793223.00 rows=60150 width=66) (actual time=64231.566..64231.566 rows=1 loops=1)
Sort Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize HashAggregate (cost=791719.25..792771.87 rows=60150 width=66) (actual time=64228.520..64231.147 rows=175 loops=1)
Group Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Gather (cost=764200.62..789313.25 rows=240600 width=66) (actual time=64218.659..64223.456 rows=875 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial HashAggregate (cost=763200.62..764253.25 rows=60150 width=66) (actual time=64211.852..64214.581 rows=175 loops=5)
Group Key: nation.n_name, date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)
-> Nested Loop (cost=4962.03..749053.20 rows=808424 width=57) (actual time=304.211..55240.672 rows=1300798 loops=5)
-> Nested Loop (cost=4961.47..166557.58 rows=808424 width=57) (actual time=304.071..20663.848 rows=1300798 loops=5)
Join Filter: (supplier.s_suppkey = lineitem.l_suppkey)
-> Hash Join (cost=4960.90..77590.12 rows=121213 width=56) (actual time=303.930..3298.954 rows=173494 loops=5)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Hash Join (cost=4960.24..75922.78 rows=121213 width=38) (actual time=303.763..3053.043 rows=173494 loops=5)
Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
-> Nested Loop (cost=0.43..69296.30 rows=121213 width=26) (actual time=0.187..2298.553 rows=173494 loops=5)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=30303 width=4) (actual time=0.066..998.795 rows=43373 loops=5)
Filter: ((p_name)::text ~~ '%grey%'::text)
Rows Removed by Filter: 756627
-> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..1.23 rows=37 width=22) (actual time=0.022..0.026 rows=4 loops=216867)
Index Cond: (ps_partkey = part.p_partkey)
-> Hash (cost=2459.80..2459.80 rows=200000 width=12) (actual time=303.230..303.230 rows=200000 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 11423kB
-> Seq Scan on supplier (cost=0.00..2459.80 rows=200000 width=12) (actual time=0.015..153.565 rows=200000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.055..0.055 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.018..0.032 rows=25 loops=5)
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..0.72 rows=1 width=41) (actual time=0.025..0.090 rows=7 loops=867468)
Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
-> Index Scan using orders_pkey on orders (cost=0.56..0.71 rows=1 width=8) (actual time=0.023..0.023 rows=1 loops=6503988)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Planning time: 25.369 ms
Execution time: 64367.626 ms
(36 rows)
ph_results/head/9_4.out 000644 001750 001750 00000011125 13044363366 014631 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=793072.62..793072.62 rows=1 width=66) (actual time=63439.860..63439.860 rows=1 loops=1)
-> Sort (cost=793072.62..793223.00 rows=60150 width=66) (actual time=63439.855..63439.855 rows=1 loops=1)
Sort Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize HashAggregate (cost=791719.25..792771.87 rows=60150 width=66) (actual time=63437.721..63439.476 rows=175 loops=1)
Group Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Gather (cost=764200.62..789313.25 rows=240600 width=66) (actual time=63429.671..63433.035 rows=875 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial HashAggregate (cost=763200.62..764253.25 rows=60150 width=66) (actual time=63421.996..63424.222 rows=175 loops=5)
Group Key: nation.n_name, date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)
-> Nested Loop (cost=4962.03..749053.20 rows=808424 width=57) (actual time=289.997..54520.194 rows=1300798 loops=5)
-> Nested Loop (cost=4961.47..166557.58 rows=808424 width=57) (actual time=289.862..20380.324 rows=1300798 loops=5)
Join Filter: (supplier.s_suppkey = lineitem.l_suppkey)
-> Hash Join (cost=4960.90..77590.12 rows=121213 width=56) (actual time=289.735..3259.129 rows=173494 loops=5)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Hash Join (cost=4960.24..75922.78 rows=121213 width=38) (actual time=289.558..3013.066 rows=173494 loops=5)
Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
-> Nested Loop (cost=0.43..69296.30 rows=121213 width=26) (actual time=0.157..2275.467 rows=173494 loops=5)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=30303 width=4) (actual time=0.048..991.143 rows=43373 loops=5)
Filter: ((p_name)::text ~~ '%grey%'::text)
Rows Removed by Filter: 756627
-> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..1.23 rows=37 width=22) (actual time=0.021..0.026 rows=4 loops=216867)
Index Cond: (ps_partkey = part.p_partkey)
-> Hash (cost=2459.80..2459.80 rows=200000 width=12) (actual time=288.980..288.980 rows=200000 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 11423kB
-> Seq Scan on supplier (cost=0.00..2459.80 rows=200000 width=12) (actual time=0.014..155.478 rows=200000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.066..0.066 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.017..0.031 rows=25 loops=5)
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..0.72 rows=1 width=41) (actual time=0.025..0.089 rows=7 loops=867468)
Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
-> Index Scan using orders_pkey on orders (cost=0.56..0.71 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=6503988)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Planning time: 25.642 ms
Execution time: 63584.255 ms
(36 rows)
ph_results/ph/9_3.out 000644 001750 001750 00000011277 13044357140 014337 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=478636.60..478636.60 rows=1 width=66) (actual time=59684.403..59684.404 rows=1 loops=1)
-> Sort (cost=478636.60..478786.98 rows=60150 width=66) (actual time=59684.398..59684.398 rows=1 loops=1)
Sort Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize HashAggregate (cost=477283.23..478335.85 rows=60150 width=66) (actual time=59677.002..59683.585 rows=175 loops=1)
Group Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Gather (cost=449764.60..474877.23 rows=240600 width=66) (actual time=59611.272..59668.724 rows=700 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial HashAggregate (cost=448764.60..449817.23 rows=60150 width=66) (actual time=49564.621..49567.758 rows=140 loops=5)
Group Key: nation.n_name, date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)
-> Parallel Hash Join (cost=246781.06..434617.18 rows=808424 width=57) (actual time=9522.479..41213.211 rows=1300798 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Nested Loop (cost=3565.63..162787.99 rows=808424 width=57) (actual time=156.278..22717.217 rows=1625997 loops=4)
Join Filter: (supplier.s_suppkey = lineitem.l_suppkey)
-> Parallel Hash Join (cost=3565.06..73820.53 rows=121213 width=56) (actual time=156.203..3429.489 rows=216867 loops=4)
Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
-> Nested Loop (cost=0.43..69296.30 rows=121213 width=26) (actual time=0.280..2804.075 rows=216867 loops=4)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=30303 width=4) (actual time=0.084..1204.905 rows=54217 loops=4)
Filter: ((p_name)::text ~~ '%grey%'::text)
Rows Removed by Filter: 945783
-> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..1.23 rows=37 width=22) (actual time=0.021..0.025 rows=4 loops=216867)
Index Cond: (ps_partkey = part.p_partkey)
-> Parallel Shared Hash (cost=2439.62..2439.62 rows=83333 width=30) (actual time=154.293..154.293 rows=50000 loops=4)
-> Hash Join (cost=0.66..2439.62 rows=83333 width=30) (actual time=0.123..84.250 rows=50000 loops=4)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.040..25.654 rows=50000 loops=4)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.057..0.057 rows=25 loops=4)
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.023..0.037 rows=25 loops=4)
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..0.72 rows=1 width=41) (actual time=0.023..0.079 rows=7 loops=867468)
Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
-> Parallel Shared Hash (cost=130246.96..130246.96 rows=7499976 width=8) (actual time=9317.414..9317.414 rows=6000000 loops=5)
Buckets: 8388608 (originally 8388608) Batches: 4 (originally 2) Memory Usage: 359136kB
-> Parallel Seq Scan on orders (cost=0.00..130246.96 rows=7499976 width=8) (actual time=0.031..4408.961 rows=6000000 loops=5)
Planning time: 18.728 ms
Execution time: 59890.495 ms
(36 rows)
ph_results/head/8_1.out 000644 001750 001750 00000014621 13044363356 014630 0 ustar 00edb edb 000000 000000 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=212957.25..212957.33 rows=1 width=40) (actual time=8265.668..8265.668 rows=1 loops=1)
-> Finalize GroupAggregate (cost=212957.25..213155.74 rows=2406 width=40) (actual time=8265.664..8265.664 rows=1 loops=1)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=212957.25..212981.31 rows=9624 width=72) (actual time=8265.562..8265.565 rows=6 loops=1)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 26kB
-> Gather (cost=211067.02..212320.50 rows=9624 width=72) (actual time=8231.871..8265.503 rows=10 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=210067.02..210358.10 rows=2406 width=72) (actual time=8215.464..8240.556 rows=2 loops=5)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=210067.02..210094.01 rows=10798 width=46) (actual time=8190.443..8194.764 rows=9676 loops=5)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 1145kB
-> Hash Join (cost=4962.72..209343.59 rows=10798 width=46) (actual time=249.666..8178.174 rows=9676 loops=5)
Hash Cond: (supplier.s_nationkey = n2.n_nationkey)
-> Hash Join (cost=4962.06..209140.47 rows=10798 width=24) (actual time=249.512..8151.742 rows=9676 loops=5)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
-> Hash Join (cost=2.26..204032.18 rows=10798 width=24) (actual time=2.866..7880.198 rows=9676 loops=5)
Hash Cond: (customer.c_nationkey = n1.n_nationkey)
-> Nested Loop (cost=1.56..203721.03 rows=53992 width=32) (actual time=0.343..7847.600 rows=48453 loops=5)
-> Nested Loop (cost=1.13..172706.85 rows=53992 width=32) (actual time=0.287..6908.777 rows=48453 loops=5)
-> Nested Loop (cost=0.57..45756.33 rows=177854 width=28) (actual time=0.169..3143.813 rows=159834 loops=5)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=6667 width=4) (actual time=0.097..738.807 rows=5325 loops=5)
Filter: ((p_type)::text = 'MEDIUM BURNISHED TIN'::text)
Rows Removed by Filter: 794675
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..3.49 rows=27 width=36) (actual time=0.039..0.427 rows=30 loops=26626)
Index Cond: (l_partkey = part.p_partkey)
-> Index Scan using orders_pkey on orders (cost=0.56..0.70 rows=1 width=16) (actual time=0.022..0.022 rows=0 loops=799171)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Filter: ((o_orderdate >= '1995-01-01'::date) AND (o_orderdate <= '1996-12-31'::date))
Rows Removed by Filter: 1
-> Index Scan using customer_pkey on customer (cost=0.43..0.56 rows=1 width=12) (actual time=0.017..0.018 rows=1 loops=242266)
Index Cond: (c_custkey = orders.o_custkey)
-> Hash (cost=0.64..0.64 rows=5 width=4) (actual time=0.071..0.071 rows=5 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Nested Loop (cost=0.14..0.64 rows=5 width=4) (actual time=0.058..0.067 rows=5 loops=5)
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.016..0.018 rows=1 loops=5)
Filter: (r_name = 'AMERICA'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using idx_nation_regionkey on nation n1 (cost=0.14..0.43 rows=5 width=12) (actual time=0.040..0.044 rows=5 loops=5)
Index Cond: (n_regionkey = region.r_regionkey)
-> Hash (cost=2459.80..2459.80 rows=200000 width=12) (actual time=246.290..246.290 rows=200000 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 11423kB
-> Seq Scan on supplier (cost=0.00..2459.80 rows=200000 width=12) (actual time=0.009..124.102 rows=200000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.051..0.051 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation n2 (cost=0.00..0.35 rows=25 width=30) (actual time=0.013..0.025 rows=25 loops=5)
Planning time: 15.971 ms
Execution time: 8405.406 ms
(50 rows)
ph_results/head/8_4.out 000644 001750 001750 00000014621 13044363354 014631 0 ustar 00edb edb 000000 000000 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=212957.25..212957.33 rows=1 width=40) (actual time=8948.856..8948.857 rows=1 loops=1)
-> Finalize GroupAggregate (cost=212957.25..213155.74 rows=2406 width=40) (actual time=8948.853..8948.853 rows=1 loops=1)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=212957.25..212981.31 rows=9624 width=72) (actual time=8948.798..8948.800 rows=6 loops=1)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 26kB
-> Gather (cost=211067.02..212320.50 rows=9624 width=72) (actual time=8918.374..8948.742 rows=10 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=210067.02..210358.10 rows=2406 width=72) (actual time=8912.909..8932.519 rows=2 loops=5)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=210067.02..210094.01 rows=10798 width=46) (actual time=8893.291..8896.563 rows=9676 loops=5)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 1144kB
-> Hash Join (cost=4962.72..209343.59 rows=10798 width=46) (actual time=324.896..8880.133 rows=9676 loops=5)
Hash Cond: (supplier.s_nationkey = n2.n_nationkey)
-> Hash Join (cost=4962.06..209140.47 rows=10798 width=24) (actual time=324.710..8850.911 rows=9676 loops=5)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
-> Hash Join (cost=2.26..204032.18 rows=10798 width=24) (actual time=2.944..8502.594 rows=9676 loops=5)
Hash Cond: (customer.c_nationkey = n1.n_nationkey)
-> Nested Loop (cost=1.56..203721.03 rows=53992 width=32) (actual time=0.428..8466.529 rows=48453 loops=5)
-> Nested Loop (cost=1.13..172706.85 rows=53992 width=32) (actual time=0.376..7383.199 rows=48453 loops=5)
-> Nested Loop (cost=0.57..45756.33 rows=177854 width=28) (actual time=0.237..2878.823 rows=159834 loops=5)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=6667 width=4) (actual time=0.141..777.327 rows=5325 loops=5)
Filter: ((p_type)::text = 'MEDIUM BURNISHED TIN'::text)
Rows Removed by Filter: 794675
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..3.49 rows=27 width=36) (actual time=0.039..0.367 rows=30 loops=26626)
Index Cond: (l_partkey = part.p_partkey)
-> Index Scan using orders_pkey on orders (cost=0.56..0.70 rows=1 width=16) (actual time=0.027..0.027 rows=0 loops=799171)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Filter: ((o_orderdate >= '1995-01-01'::date) AND (o_orderdate <= '1996-12-31'::date))
Rows Removed by Filter: 1
-> Index Scan using customer_pkey on customer (cost=0.43..0.56 rows=1 width=12) (actual time=0.020..0.021 rows=1 loops=242266)
Index Cond: (c_custkey = orders.o_custkey)
-> Hash (cost=0.64..0.64 rows=5 width=4) (actual time=0.078..0.078 rows=5 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Nested Loop (cost=0.14..0.64 rows=5 width=4) (actual time=0.063..0.074 rows=5 loops=5)
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.017..0.020 rows=1 loops=5)
Filter: (r_name = 'AMERICA'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using idx_nation_regionkey on nation n1 (cost=0.14..0.43 rows=5 width=12) (actual time=0.043..0.047 rows=5 loops=5)
Index Cond: (n_regionkey = region.r_regionkey)
-> Hash (cost=2459.80..2459.80 rows=200000 width=12) (actual time=321.416..321.416 rows=200000 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 11423kB
-> Seq Scan on supplier (cost=0.00..2459.80 rows=200000 width=12) (actual time=0.012..165.496 rows=200000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.064..0.064 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation n2 (cost=0.00..0.35 rows=25 width=30) (actual time=0.016..0.032 rows=25 loops=5)
Planning time: 19.296 ms
Execution time: 9075.840 ms
(50 rows)
ph_results/head/3_2.out 000644 001750 001750 00000010204 13044363366 014616 0 ustar 00edb edb 000000 000000 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2921622.69..2921622.72 rows=10 width=48) (actual time=72822.156..72822.160 rows=10 loops=1)
-> Sort (cost=2921622.69..2937248.64 rows=6250377 width=48) (actual time=72822.154..72822.156 rows=10 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate
Sort Method: top-N heapsort Memory: 26kB
-> GroupAggregate (cost=2599042.98..2786554.29 rows=6250377 width=48) (actual time=70287.178..72584.661 rows=226765 loops=1)
Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
-> Sort (cost=2599042.98..2614668.93 rows=6250377 width=28) (actual time=70287.100..70477.437 rows=599838 loops=1)
Sort Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
Sort Method: quicksort Memory: 61987kB
-> Gather (cost=505541.73..1884970.09 rows=6250377 width=28) (actual time=32273.009..69328.821 rows=599838 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=504541.73..1258932.39 rows=1562594 width=28) (actual time=31567.659..68046.590 rows=119968 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=16076611 width=20) (actual time=0.048..21956.395 rows=12887556 loops=5)
Filter: (l_shipdate > '1995-03-20'::date)
Rows Removed by Filter: 11111365
-> Hash (cost=466669.27..466669.27 rows=2915893 width=12) (actual time=31563.005..31563.005 rows=2926672 loops=5)
Buckets: 2097152 Batches: 4 Memory Usage: 47877kB
-> Hash Join (cost=144594.06..466669.27 rows=2915893 width=12) (actual time=9584.296..29351.860 rows=2926672 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Bitmap Heap Scan on orders (cost=117360.93..355430.49 rows=14625789 width=20) (actual time=8079.278..16542.740 rows=14640000 loops=5)
Recheck Cond: (o_orderdate < '1995-03-20'::date)
Heap Blocks: exact=552472
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..113704.48 rows=14625789 width=0) (actual time=7663.710..7663.710 rows=14640000 loops=5)
Index Cond: (o_orderdate < '1995-03-20'::date)
-> Hash (cost=19756.15..19756.15 rows=598159 width=4) (actual time=1503.737..1503.737 rows=598321 loops=5)
Buckets: 1048576 Batches: 1 Memory Usage: 29227kB
-> Bitmap Heap Scan on customer (cost=4866.56..19756.15 rows=598159 width=4) (actual time=359.318..1125.930 rows=598321 loops=5)
Recheck Cond: (c_mktsegment = 'FURNITURE'::bpchar)
Heap Blocks: exact=74114
-> Bitmap Index Scan on idx_c_mktsegment (cost=0.00..4717.02 rows=598159 width=0) (actual time=323.244..323.244 rows=598321 loops=5)
Index Cond: (c_mktsegment = 'FURNITURE'::bpchar)
Planning time: 3.806 ms
Execution time: 72829.921 ms
(35 rows)
ph_results/ph/8_3.out 000644 001750 001750 00000015763 13044357123 014343 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=199823.61..199823.67 rows=1 width=40) (actual time=16319.260..16319.260 rows=1 loops=1)
-> Finalize GroupAggregate (cost=199823.61..199949.93 rows=2406 width=40) (actual time=16319.257..16319.257 rows=1 loops=1)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=199823.61..199835.64 rows=4812 width=72) (actual time=16319.198..16319.199 rows=3 loops=1)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 25kB
-> Gather (cost=198595.04..199529.30 rows=4812 width=72) (actual time=16266.439..16319.130 rows=4 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial GroupAggregate (cost=197595.04..198048.10 rows=2406 width=72) (actual time=16179.975..16210.578 rows=1 loops=3)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=197595.04..197640.03 rows=17998 width=46) (actual time=16149.522..16154.649 rows=16126 loops=3)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=194334.94..196323.02 rows=17998 width=46) (actual time=16015.600..16136.280 rows=16126 loops=3)
Hash Cond: (supplier.s_nationkey = n2.n_nationkey)
-> Parallel Hash Join (cost=194334.28..195984.90 rows=17998 width=24) (actual time=16015.414..16105.176 rows=16126 loops=3)
Hash Cond: (supplier.s_suppkey = lineitem.l_suppkey)
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.029..40.963 rows=100000 loops=2)
-> Parallel Shared Hash (cost=194188.50..194188.50 rows=10798 width=24) (actual time=16014.850..16014.850 rows=16126 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 3200kB
-> Parallel Hash Join (cost=21253.31..194188.50 rows=10798 width=24) (actual time=1409.762..11123.406 rows=16126 loops=3)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Nested Loop (cost=1.13..172706.85 rows=53992 width=32) (actual time=0.429..14425.957 rows=121133 loops=2)
-> Nested Loop (cost=0.57..45756.33 rows=177854 width=28) (actual time=0.278..5446.777 rows=399586 loops=2)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=6667 width=4) (actual time=0.173..1544.075 rows=13313 loops=2)
Filter: ((p_type)::text = 'MEDIUM BURNISHED TIN'::text)
Rows Removed by Filter: 1986687
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..3.49 rows=27 width=36) (actual time=0.029..0.271 rows=30 loops=26626)
Index Cond: (l_partkey = part.p_partkey)
-> Index Scan using orders_pkey on orders (cost=0.56..0.70 rows=1 width=16) (actual time=0.021..0.021 rows=0 loops=799171)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Filter: ((o_orderdate >= '1995-01-01'::date) AND (o_orderdate <= '1996-12-31'::date))
Rows Removed by Filter: 1
-> Parallel Shared Hash (cost=19226.97..19226.97 rows=150015 width=4) (actual time=1401.037..1401.037 rows=199896 loops=3)
Buckets: 1048576 (originally 262144) Batches: 1 (originally 1) Memory Usage: 31712kB
-> Hash Join (cost=0.70..19226.97 rows=150015 width=4) (actual time=0.117..1150.985 rows=199896 loops=3)
Hash Cond: (customer.c_nationkey = n1.n_nationkey)
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=12) (actual time=0.030..529.253 rows=1000000 loops=3)
-> Hash (cost=0.64..0.64 rows=5 width=4) (actual time=0.070..0.070 rows=5 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Nested Loop (cost=0.14..0.64 rows=5 width=4) (actual time=0.055..0.065 rows=5 loops=3)
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.014..0.017 rows=1 loops=3)
Filter: (r_name = 'AMERICA'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using idx_nation_regionkey on nation n1 (cost=0.14..0.43 rows=5 width=12) (actual time=0.038..0.042 rows=5 loops=3)
Index Cond: (n_regionkey = region.r_regionkey)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.056..0.056 rows=25 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on nation n2 (cost=0.00..0.35 rows=25 width=30) (actual time=0.013..0.032 rows=25 loops=3)
Planning time: 19.491 ms
Execution time: 16451.677 ms
(52 rows)
ph_results/ph/3_1.out 000644 001750 001750 00000007147 13044357132 014331 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2538085.87..2538085.89 rows=10 width=48) (actual time=58184.308..58184.312 rows=10 loops=1)
-> Sort (cost=2538085.87..2553711.81 rows=6250377 width=48) (actual time=58184.305..58184.307 rows=10 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate
Sort Method: top-N heapsort Memory: 26kB
-> GroupAggregate (cost=2215506.16..2403017.47 rows=6250377 width=48) (actual time=55723.771..57948.726 rows=226764 loops=1)
Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
-> Sort (cost=2215506.16..2231132.10 rows=6250377 width=28) (actual time=55723.704..55908.763 rows=599834 loops=1)
Sort Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
Sort Method: quicksort Memory: 61986kB
-> Gather (cost=194179.48..1501433.26 rows=6250377 width=28) (actual time=8418.995..54668.025 rows=599834 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Hash Join (cost=193179.48..875395.56 rows=1562594 width=28) (actual time=8414.876..45563.398 rows=119967 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=16076611 width=20) (actual time=0.124..24540.958 rows=16109445 loops=4)
Filter: (l_shipdate > '1995-03-20'::date)
Rows Removed by Filter: 13889207
-> Parallel Shared Hash (cost=183338.34..183338.34 rows=728973 width=12) (actual time=8399.089..8399.089 rows=585334 loops=5)
Buckets: 1048576 (originally 1048576) Batches: 4 (originally 1) Memory Usage: 42624kB
-> Parallel Hash Join (cost=18807.32..183338.34 rows=728973 width=12) (actual time=766.222..7682.394 rows=585334 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Parallel Seq Scan on orders (cost=0.00..148996.90 rows=3656447 width=20) (actual time=0.052..4308.238 rows=2928000 loops=5)
Filter: (o_orderdate < '1995-03-20'::date)
Rows Removed by Filter: 3072000
-> Parallel Shared Hash (cost=16788.53..16788.53 rows=149540 width=4) (actual time=763.075..763.075 rows=119664 loops=5)
Buckets: 1048576 (originally 262144) Batches: 1 (originally 1) Memory Usage: 31680kB
-> Parallel Seq Scan on customer (cost=0.00..16788.53 rows=149540 width=4) (actual time=0.022..576.502 rows=119664 loops=5)
Filter: (c_mktsegment = 'FURNITURE'::bpchar)
Rows Removed by Filter: 480336
Planning time: 4.863 ms
Execution time: 58194.966 ms
(31 rows)
ph_results/ph/3_4.out 000644 001750 001750 00000007153 13044357133 014332 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2538085.87..2538085.89 rows=10 width=48) (actual time=61358.467..61358.471 rows=10 loops=1)
-> Sort (cost=2538085.87..2553711.81 rows=6250377 width=48) (actual time=61358.464..61358.466 rows=10 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate
Sort Method: top-N heapsort Memory: 26kB
-> GroupAggregate (cost=2215506.16..2403017.47 rows=6250377 width=48) (actual time=58895.451..61122.722 rows=226765 loops=1)
Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
-> Sort (cost=2215506.16..2231132.10 rows=6250377 width=28) (actual time=58895.353..59081.480 rows=599838 loops=1)
Sort Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
Sort Method: quicksort Memory: 61987kB
-> Gather (cost=194179.48..1501433.26 rows=6250377 width=28) (actual time=11479.217..57879.570 rows=599838 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Hash Join (cost=193179.48..875395.56 rows=1562594 width=28) (actual time=11475.914..48722.656 rows=119968 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=16076611 width=20) (actual time=0.184..25056.743 rows=16109445 loops=4)
Filter: (l_shipdate > '1995-03-20'::date)
Rows Removed by Filter: 13889207
-> Parallel Shared Hash (cost=183338.34..183338.34 rows=728973 width=12) (actual time=11461.911..11461.911 rows=585334 loops=5)
Buckets: 1048576 (originally 1048576) Batches: 4 (originally 1) Memory Usage: 42656kB
-> Parallel Hash Join (cost=18807.32..183338.34 rows=728973 width=12) (actual time=802.181..8526.528 rows=585334 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Parallel Seq Scan on orders (cost=0.00..148996.90 rows=3656447 width=20) (actual time=0.062..6251.332 rows=3660000 loops=4)
Filter: (o_orderdate < '1995-03-20'::date)
Rows Removed by Filter: 3840000
-> Parallel Shared Hash (cost=16788.53..16788.53 rows=149540 width=4) (actual time=799.523..799.523 rows=119664 loops=5)
Buckets: 1048576 (originally 262144) Batches: 1 (originally 1) Memory Usage: 31648kB
-> Parallel Seq Scan on customer (cost=0.00..16788.53 rows=149540 width=4) (actual time=0.019..620.966 rows=119664 loops=5)
Filter: (c_mktsegment = 'FURNITURE'::bpchar)
Rows Removed by Filter: 480336
Planning time: 3.938 ms
Execution time: 61365.250 ms
(31 rows)
ph_results/ 000775 001750 001750 00000000000 13044357163 012604 5 ustar 00edb edb 000000 000000 ph_results/head/7_1.out 000644 001750 001750 00000013246 13044363353 014626 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1283926.71..1283926.79 rows=1 width=92) (actual time=41425.290..41425.291 rows=1 loops=1)
-> Finalize GroupAggregate (cost=1283926.71..1284704.50 rows=10036 width=92) (actual time=41425.288..41425.288 rows=1 loops=1)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=1283926.71..1284027.07 rows=40144 width=92) (actual time=41425.251..41425.252 rows=6 loops=1)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 27kB
-> Gather (cost=1276142.56..1280857.12 rows=40144 width=92) (actual time=41333.148..41425.146 rows=20 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=1275142.56..1275842.72 rows=10036 width=92) (actual time=41328.212..41397.749 rows=4 loops=5)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=1275142.56..1275217.50 rows=29973 width=72) (actual time=41305.213..41312.370 rows=23488 loops=5)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 4317kB
-> Hash Join (cost=25864.84..1272913.87 rows=29973 width=72) (actual time=822.503..41252.340 rows=23488 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
Join Filter: (((n1.n_name = 'ARGENTINA'::bpchar) AND (n2.n_name = 'IRAN'::bpchar)) OR ((n1.n_name = 'IRAN'::bpchar) AND (n2.n_name = 'ARGENTINA'::bpchar)))
Rows Removed by Join Filter: 23456
-> Nested Loop (cost=1613.46..1244659.41 rows=720228 width=50) (actual time=42.569..39906.733 rows=586259 loops=5)
-> Hash Join (cost=1612.90..735594.78 rows=720228 width=50) (actual time=42.516..29323.555 rows=586259 loops=5)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
-> Parallel Seq Scan on lineitem (cost=0.00..693018.90 rows=9002853 width=32) (actual time=0.040..25394.880 rows=7292378 loops=5)
Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate <= '1996-12-31'::date))
Rows Removed by Filter: 16706543
-> Hash (cost=1412.90..1412.90 rows=16000 width=30) (actual time=42.295..42.295 rows=16082 loops=5)
Buckets: 16384 Batches: 1 Memory Usage: 1102kB
-> Nested Loop (cost=0.42..1412.90 rows=16000 width=30) (actual time=0.082..32.165 rows=16082 loops=5)
-> Seq Scan on nation n1 (cost=0.00..0.47 rows=2 width=30) (actual time=0.014..0.042 rows=2 loops=5)
Filter: ((n_name = 'ARGENTINA'::bpchar) OR (n_name = 'IRAN'::bpchar))
Rows Removed by Filter: 23
-> Index Scan using idx_supplier_nation_key on supplier (cost=0.42..626.21 rows=8000 width=12) (actual time=0.043..12.680 rows=8041 loops=10)
Index Cond: (s_nationkey = n1.n_nationkey)
-> Index Scan using orders_pkey on orders (cost=0.56..0.70 rows=1 width=12) (actual time=0.016..0.016 rows=1 loops=2931293)
Index Cond: (o_orderkey = lineitem.l_orderkey)
-> Hash (cost=21251.08..21251.08 rows=240024 width=30) (actual time=778.878..778.878 rows=240035 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 16582kB
-> Nested Loop (cost=0.43..21251.08 rows=240024 width=30) (actual time=0.048..615.551 rows=240035 loops=5)
-> Seq Scan on nation n2 (cost=0.00..0.47 rows=2 width=30) (actual time=0.009..0.043 rows=2 loops=5)
Filter: ((n_name = 'IRAN'::bpchar) OR (n_name = 'ARGENTINA'::bpchar))
Rows Removed by Filter: 23
-> Index Scan using idx_customer_nationkey on customer (cost=0.43..9425.18 rows=120012 width=12) (actual time=0.064..255.567 rows=120018 loops=10)
Index Cond: (c_nationkey = n2.n_nationkey)
Planning time: 9.066 ms
Execution time: 41517.821 ms
(44 rows)
ph_results/head/7_4.out 000644 001750 001750 00000013246 13044363356 014634 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1283926.71..1283926.79 rows=1 width=92) (actual time=42304.638..42304.638 rows=1 loops=1)
-> Finalize GroupAggregate (cost=1283926.71..1284704.50 rows=10036 width=92) (actual time=42304.635..42304.635 rows=1 loops=1)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=1283926.71..1284027.07 rows=40144 width=92) (actual time=42304.584..42304.586 rows=6 loops=1)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 27kB
-> Gather (cost=1276142.56..1280857.12 rows=40144 width=92) (actual time=42207.575..42304.452 rows=20 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=1275142.56..1275842.72 rows=10036 width=92) (actual time=42195.320..42269.098 rows=4 loops=5)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=1275142.56..1275217.50 rows=29973 width=72) (actual time=42170.930..42178.672 rows=23488 loops=5)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 4131kB
-> Hash Join (cost=25864.84..1272913.87 rows=29973 width=72) (actual time=873.239..42115.410 rows=23488 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
Join Filter: (((n1.n_name = 'ARGENTINA'::bpchar) AND (n2.n_name = 'IRAN'::bpchar)) OR ((n1.n_name = 'IRAN'::bpchar) AND (n2.n_name = 'ARGENTINA'::bpchar)))
Rows Removed by Join Filter: 23456
-> Nested Loop (cost=1613.46..1244659.41 rows=720228 width=50) (actual time=50.736..40675.873 rows=586259 loops=5)
-> Hash Join (cost=1612.90..735594.78 rows=720228 width=50) (actual time=50.673..30019.179 rows=586259 loops=5)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
-> Parallel Seq Scan on lineitem (cost=0.00..693018.90 rows=9002853 width=32) (actual time=0.046..25963.355 rows=7292378 loops=5)
Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate <= '1996-12-31'::date))
Rows Removed by Filter: 16706543
-> Hash (cost=1412.90..1412.90 rows=16000 width=30) (actual time=50.415..50.415 rows=16082 loops=5)
Buckets: 16384 Batches: 1 Memory Usage: 1102kB
-> Nested Loop (cost=0.42..1412.90 rows=16000 width=30) (actual time=0.090..38.457 rows=16082 loops=5)
-> Seq Scan on nation n1 (cost=0.00..0.47 rows=2 width=30) (actual time=0.017..0.048 rows=2 loops=5)
Filter: ((n_name = 'ARGENTINA'::bpchar) OR (n_name = 'IRAN'::bpchar))
Rows Removed by Filter: 23
-> Index Scan using idx_supplier_nation_key on supplier (cost=0.42..626.21 rows=8000 width=12) (actual time=0.046..15.009 rows=8041 loops=10)
Index Cond: (s_nationkey = n1.n_nationkey)
-> Index Scan using orders_pkey on orders (cost=0.56..0.70 rows=1 width=12) (actual time=0.016..0.017 rows=1 loops=2931293)
Index Cond: (o_orderkey = lineitem.l_orderkey)
-> Hash (cost=21251.08..21251.08 rows=240024 width=30) (actual time=820.821..820.821 rows=240035 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 16582kB
-> Nested Loop (cost=0.43..21251.08 rows=240024 width=30) (actual time=0.057..636.848 rows=240035 loops=5)
-> Seq Scan on nation n2 (cost=0.00..0.47 rows=2 width=30) (actual time=0.011..0.059 rows=2 loops=5)
Filter: ((n_name = 'IRAN'::bpchar) OR (n_name = 'ARGENTINA'::bpchar))
Rows Removed by Filter: 23
-> Index Scan using idx_customer_nationkey on customer (cost=0.43..9425.18 rows=120012 width=12) (actual time=0.055..258.875 rows=120018 loops=10)
Index Cond: (c_nationkey = n2.n_nationkey)
Planning time: 9.006 ms
Execution time: 42398.726 ms
(44 rows)
ph_results/ph/7_3.out 000644 001750 001750 00000014351 13044357127 014336 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=896037.66..896037.74 rows=1 width=92) (actual time=46026.900..46026.900 rows=1 loops=1)
-> Finalize GroupAggregate (cost=896037.66..896815.45 rows=10036 width=92) (actual time=46026.898..46026.898 rows=1 loops=1)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=896037.66..896138.02 rows=40144 width=92) (actual time=46026.848..46026.850 rows=5 loops=1)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 27kB
-> Gather (cost=888253.52..892968.07 rows=40144 width=92) (actual time=45904.967..46026.729 rows=16 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=887253.52..887953.67 rows=10036 width=92) (actual time=37577.670..37648.633 rows=3 loops=5)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=887253.52..887328.45 rows=29973 width=72) (actual time=37554.199..37562.088 rows=23488 loops=5)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 25kB
-> Parallel Hash Join (cost=153113.77..885024.82 rows=29973 width=72) (actual time=4375.177..37504.857 rows=23488 loops=5)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
Join Filter: (((n1.n_name = 'ARGENTINA'::bpchar) AND (n2.n_name = 'IRAN'::bpchar)) OR ((n1.n_name = 'IRAN'::bpchar) AND (n2.n_name = 'ARGENTINA'::bpchar)))
Rows Removed by Join Filter: 23456
-> Parallel Hash Join (cost=151350.97..879931.13 rows=720228 width=50) (actual time=4262.241..36944.535 rows=584293 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..693018.90 rows=9002853 width=32) (actual time=34.630..27528.259 rows=9115473 loops=4)
Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate <= '1996-12-31'::date))
Rows Removed by Filter: 20883179
-> Parallel Shared Hash (cost=143250.99..143250.99 rows=599998 width=30) (actual time=4191.217..4191.217 rows=480479 loops=5)
Buckets: 1048576 (originally 1048576) Batches: 4 (originally 1) Memory Usage: 45920kB
-> Nested Loop (cost=1.06..143250.99 rows=599998 width=30) (actual time=0.210..3573.688 rows=480479 loops=5)
-> Hash Join (cost=0.50..18326.68 rows=60006 width=30) (actual time=0.085..668.306 rows=48007 loops=5)
Hash Cond: (customer.c_nationkey = n2.n_nationkey)
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=12) (actual time=0.011..198.140 rows=600000 loops=5)
-> Hash (cost=0.47..0.47 rows=2 width=30) (actual time=0.053..0.053 rows=2 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on nation n2 (cost=0.00..0.47 rows=2 width=30) (actual time=0.015..0.041 rows=2 loops=5)
Filter: ((n_name = 'IRAN'::bpchar) OR (n_name = 'ARGENTINA'::bpchar))
Rows Removed by Filter: 23
-> Index Scan using idx_orders_custkey on orders (cost=0.56..1.91 rows=17 width=12) (actual time=0.017..0.054 rows=10 loops=240035)
Index Cond: (o_custkey = customer.c_custkey)
-> Parallel Shared Hash (cost=1672.80..1672.80 rows=6667 width=30) (actual time=44.052..44.052 rows=3216 loops=5)
Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1216kB
-> Hash Join (cost=0.50..1672.80 rows=6667 width=30) (actual time=0.071..40.040 rows=3216 loops=5)
Hash Cond: (supplier.s_nationkey = n1.n_nationkey)
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.011..15.399 rows=40000 loops=5)
-> Hash (cost=0.47..0.47 rows=2 width=30) (actual time=0.045..0.045 rows=2 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on nation n1 (cost=0.00..0.47 rows=2 width=30) (actual time=0.014..0.039 rows=2 loops=5)
Filter: ((n_name = 'ARGENTINA'::bpchar) OR (n_name = 'IRAN'::bpchar))
Rows Removed by Filter: 23
Planning time: 9.365 ms
Execution time: 46149.742 ms
(48 rows)
ph_results/ph/10_2.out 000644 001750 001750 00000010370 13044357133 014401 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1068412.51..1068412.56 rows=20 width=201) (actual time=61871.434..61871.443 rows=20 loops=1)
-> Sort (cost=1068412.51..1071193.68 rows=1112470 width=201) (actual time=61871.432..61871.438 rows=20 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 34kB
-> GroupAggregate (cost=1008217.16..1038810.08 rows=1112470 width=201) (actual time=50391.903..60688.419 rows=765019 loops=1)
Group Key: customer.c_custkey, nation.n_name
-> Sort (cost=1008217.16..1010998.33 rows=1112470 width=181) (actual time=50391.804..52146.352 rows=2298947 loops=1)
Sort Key: customer.c_custkey, nation.n_name
Sort Method: external merge Disk: 438456kB
-> Gather (cost=125807.02..890846.09 rows=1112470 width=181) (actual time=7076.318..45202.798 rows=2298947 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=124807.02..778599.09 rows=278118 width=181) (actual time=5999.655..35915.841 rows=459789 loops=5)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Parallel Hash Join (cost=124806.36..774774.31 rows=278118 width=163) (actual time=5999.349..35356.367 rows=459789 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Parallel Hash Join (cost=99767.01..747996.74 rows=278118 width=20) (actual time=5368.986..38785.644 rows=574738 loops=4)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=7313668 width=20) (actual time=0.124..24547.110 rows=7402949 loops=4)
Filter: (l_returnflag = 'R'::bpchar)
Rows Removed by Filter: 22595703
-> Shared Hash (cost=84366.09..84366.09 rows=1140809 width=12) (actual time=5342.067..5342.067 rows=286991 loops=4)
-> Bitmap Heap Scan on orders (cost=12006.75..84366.09 rows=1140809 width=12) (actual time=1346.457..4104.409 rows=1147963 loops=1)
Recheck Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..11721.55 rows=1140809 width=0) (actual time=965.278..965.278 rows=1147963 loops=1)
Index Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Parallel Shared Hash (cost=14913.35..14913.35 rows=750074 width=151) (actual time=1691.841..1691.841 rows=600000 loops=5)
Buckets: 1048576 (originally 1048576) Batches: 16 (originally 1) Memory Usage: 43072kB
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=151) (actual time=0.010..634.995 rows=600000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.050..0.050 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.010..0.021 rows=25 loops=5)
Planning time: 4.878 ms
Execution time: 61933.278 ms
(34 rows)
ph_results/head/5_1.out 000644 001750 001750 00000014477 13044363354 014634 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=993197.98..993197.98 rows=1 width=58) (actual time=54955.045..54955.045 rows=1 loops=1)
-> Sort (cost=993197.98..993198.04 rows=25 width=58) (actual time=54955.043..54955.043 rows=1 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize GroupAggregate (cost=993196.54..993197.86 rows=25 width=58) (actual time=54954.910..54954.994 rows=5 loops=1)
Group Key: nation.n_name
-> Sort (cost=993196.54..993196.79 rows=100 width=58) (actual time=54954.877..54954.884 rows=25 loops=1)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 28kB
-> Gather (cost=992723.87..993193.22 rows=100 width=58) (actual time=54878.643..54954.791 rows=25 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=991723.87..992183.22 rows=25 width=58) (actual time=54252.916..54324.884 rows=5 loops=5)
Group Key: nation.n_name
-> Sort (cost=991723.87..991815.68 rows=36723 width=38) (actual time=54234.888..54243.176 rows=29012 loops=5)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 3160kB
-> Hash Join (cost=275164.82..988939.48 rows=36723 width=38) (actual time=14099.362..54189.267 rows=29012 loops=5)
Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))
-> Hash Join (cost=269705.02..976227.01 rows=918060 width=58) (actual time=13821.426..53274.039 rows=728180 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..543025.70 rows=29998640 width=28) (actual time=0.058..18193.510 rows=23998922 loops=5)
-> Hash (cost=257421.79..257421.79 rows=918098 width=42) (actual time=13819.272..13819.272 rows=910215 loops=5)
Buckets: 1048576 Batches: 2 Memory Usage: 41956kB
-> Hash Join (cost=106921.90..257421.79 rows=918098 width=42) (actual time=5186.695..13066.759 rows=910215 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Bitmap Heap Scan on orders (cost=48311.90..172416.46 rows=4590491 width=12) (actual time=3263.310..7780.177 rows=4549846 loops=5)
Recheck Cond: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
Heap Blocks: exact=552373
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..47164.27 rows=4590491 width=0) (actual time=2779.402..2779.402 rows=4549846 loops=5)
Index Cond: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Hash (cost=51109.25..51109.25 rows=600060 width=42) (actual time=1922.107..1922.107 rows=599436 loops=5)
Buckets: 1048576 Batches: 1 Memory Usage: 55023kB
-> Nested Loop (cost=0.43..51109.25 rows=600060 width=42) (actual time=0.306..1460.934 rows=599436 loops=5)
-> Nested Loop (cost=0.00..0.82 rows=5 width=30) (actual time=0.116..0.164 rows=5 loops=5)
Join Filter: (nation.n_regionkey = region.r_regionkey)
Rows Removed by Join Filter: 20
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.097..0.104 rows=1 loops=5)
Filter: (r_name = 'AFRICA'::bpchar)
Rows Removed by Filter: 4
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=38) (actual time=0.010..0.028 rows=25 loops=5)
-> Index Scan using idx_customer_nationkey on customer (cost=0.43..9021.57 rows=120012 width=12) (actual time=0.068..226.881 rows=119887 loops=25)
Index Cond: (c_nationkey = nation.n_nationkey)
-> Hash (cost=2459.80..2459.80 rows=200000 width=12) (actual time=277.008..277.008 rows=200000 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 11423kB
-> Seq Scan on supplier (cost=0.00..2459.80 rows=200000 width=12) (actual time=0.015..130.448 rows=200000 loops=5)
Planning time: 12.287 ms
Execution time: 54957.372 ms
(48 rows)
ph_results/head/5_4.out 000644 001750 001750 00000014477 13044363355 014640 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=993197.98..993197.98 rows=1 width=58) (actual time=54028.941..54028.942 rows=1 loops=1)
-> Sort (cost=993197.98..993198.04 rows=25 width=58) (actual time=54028.939..54028.939 rows=1 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize GroupAggregate (cost=993196.54..993197.86 rows=25 width=58) (actual time=54028.779..54028.886 rows=5 loops=1)
Group Key: nation.n_name
-> Sort (cost=993196.54..993196.79 rows=100 width=58) (actual time=54028.725..54028.734 rows=25 loops=1)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 28kB
-> Gather (cost=992723.87..993193.22 rows=100 width=58) (actual time=53493.030..54028.641 rows=25 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=991723.87..992183.22 rows=25 width=58) (actual time=53792.404..53864.934 rows=5 loops=5)
Group Key: nation.n_name
-> Sort (cost=991723.87..991815.68 rows=36723 width=38) (actual time=53774.528..53782.880 rows=29012 loops=5)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 3010kB
-> Hash Join (cost=275164.82..988939.48 rows=36723 width=38) (actual time=14742.212..53729.800 rows=29012 loops=5)
Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))
-> Hash Join (cost=269705.02..976227.01 rows=918060 width=58) (actual time=14444.422..52832.833 rows=728180 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..543025.70 rows=29998640 width=28) (actual time=0.076..17789.722 rows=23998922 loops=5)
-> Hash (cost=257421.79..257421.79 rows=918098 width=42) (actual time=14441.750..14441.750 rows=910215 loops=5)
Buckets: 1048576 Batches: 2 Memory Usage: 41956kB
-> Hash Join (cost=106921.90..257421.79 rows=918098 width=42) (actual time=5367.408..13646.164 rows=910215 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Bitmap Heap Scan on orders (cost=48311.90..172416.46 rows=4590491 width=12) (actual time=3298.238..8050.443 rows=4549846 loops=5)
Recheck Cond: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
Heap Blocks: exact=552373
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..47164.27 rows=4590491 width=0) (actual time=2883.253..2883.253 rows=4549846 loops=5)
Index Cond: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Hash (cost=51109.25..51109.25 rows=600060 width=42) (actual time=2067.953..2067.953 rows=599436 loops=5)
Buckets: 1048576 Batches: 1 Memory Usage: 55023kB
-> Nested Loop (cost=0.43..51109.25 rows=600060 width=42) (actual time=0.252..1568.198 rows=599436 loops=5)
-> Nested Loop (cost=0.00..0.82 rows=5 width=30) (actual time=0.103..0.157 rows=5 loops=5)
Join Filter: (nation.n_regionkey = region.r_regionkey)
Rows Removed by Join Filter: 20
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.088..0.095 rows=1 loops=5)
Filter: (r_name = 'AFRICA'::bpchar)
Rows Removed by Filter: 4
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=38) (actual time=0.009..0.028 rows=25 loops=5)
-> Index Scan using idx_customer_nationkey on customer (cost=0.43..9021.57 rows=120012 width=12) (actual time=0.062..245.052 rows=119887 loops=25)
Index Cond: (c_nationkey = nation.n_nationkey)
-> Hash (cost=2459.80..2459.80 rows=200000 width=12) (actual time=295.307..295.307 rows=200000 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 11423kB
-> Seq Scan on supplier (cost=0.00..2459.80 rows=200000 width=12) (actual time=0.015..133.164 rows=200000 loops=5)
Planning time: 12.109 ms
Execution time: 54102.467 ms
(48 rows)
ph_results/ph/5_3.out 000644 001750 001750 00000013552 13044357135 014335 0 ustar 00edb edb 000000 000000 QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=868714.16..868714.16 rows=1 width=58) (actual time=56271.792..56271.792 rows=1 loops=1)
-> Sort (cost=868714.16..868714.22 rows=25 width=58) (actual time=56271.789..56271.789 rows=1 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize GroupAggregate (cost=868712.72..868714.03 rows=25 width=58) (actual time=56271.652..56271.721 rows=5 loops=1)
Group Key: nation.n_name
-> Sort (cost=868712.72..868712.97 rows=100 width=58) (actual time=56271.615..56271.621 rows=20 loops=1)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 27kB
-> Gather (cost=868240.05..868709.40 rows=100 width=58) (actual time=55876.234..56271.531 rows=20 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=867240.05..867699.40 rows=25 width=58) (actual time=44762.609..44854.448 rows=4 loops=5)
Group Key: nation.n_name
-> Sort (cost=867240.05..867331.86 rows=36723 width=38) (actual time=44739.685..44750.922 rows=29012 loops=5)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 25kB
-> Parallel Hash Join (cost=199601.45..864455.66 rows=36723 width=38) (actual time=7427.926..44689.996 rows=29012 loops=5)
Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))
-> Parallel Hash Join (cost=196974.99..854790.74 rows=918060 width=58) (actual time=9169.731..54872.729 rows=910225 loops=4)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..543025.70 rows=29998640 width=28) (actual time=0.135..11045.230 rows=29998652 loops=4)
-> Parallel Shared Hash (cost=193876.41..193876.41 rows=229524 width=42) (actual time=9164.421..9164.421 rows=227554 loops=4)
-> Parallel Hash Join (cost=21252.18..193876.41 rows=229524 width=42) (actual time=950.821..8827.490 rows=227554 loops=4)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Parallel Seq Scan on orders (cost=0.00..167746.84 rows=1147623 width=12) (actual time=0.067..6737.979 rows=1137462 loops=4)
Filter: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 6362538
-> Parallel Shared Hash (cost=19226.97..19226.97 rows=150015 width=42) (actual time=947.584..947.584 rows=149859 loops=4)
-> Hash Join (cost=0.70..19226.97 rows=150015 width=42) (actual time=0.136..737.051 rows=149859 loops=4)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=12) (actual time=0.011..236.837 rows=750000 loops=4)
-> Hash (cost=0.64..0.64 rows=5 width=30) (actual time=0.113..0.113 rows=5 loops=4)
-> Nested Loop (cost=0.14..0.64 rows=5 width=30) (actual time=0.096..0.107 rows=5 loops=4)
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.026..0.030 rows=1 loops=4)
Filter: (r_name = 'AFRICA'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using idx_nation_regionkey on nation (cost=0.14..0.43 rows=5 width=38) (actual time=0.065..0.070 rows=5 loops=4)
Index Cond: (n_regionkey = region.r_regionkey)
-> Parallel Shared Hash (cost=1293.13..1293.13 rows=83333 width=12) (actual time=86.888..86.888 rows=40000 loops=5)
Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 11552kB
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.015..31.663 rows=40000 loops=5)
Planning time: 14.949 ms
Execution time: 56438.452 ms
(44 rows)
ph_results/head/10_1.out 000644 001750 001750 00000010544 13044363357 014702 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1142835.84..1142835.89 rows=20 width=201) (actual time=62907.175..62907.184 rows=20 loops=1)
-> Sort (cost=1142835.84..1145617.02 rows=1112470 width=201) (actual time=62907.173..62907.178 rows=20 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 34kB
-> GroupAggregate (cost=1082640.49..1113233.42 rows=1112470 width=201) (actual time=51082.489..61694.729 rows=765020 loops=1)
Group Key: customer.c_custkey, nation.n_name
-> Sort (cost=1082640.49..1085421.67 rows=1112470 width=181) (actual time=51082.406..52870.867 rows=2298951 loops=1)
Sort Key: customer.c_custkey, nation.n_name
Sort Method: external merge Disk: 438448kB
-> Gather (cost=181549.27..965269.43 rows=1112470 width=181) (actual time=14271.631..45824.285 rows=2298951 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=180549.27..853022.43 rows=278118 width=181) (actual time=14128.318..43991.831 rows=459790 loops=5)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Hash Join (cost=180548.61..849197.65 rows=278118 width=163) (actual time=14128.010..43504.817 rows=459790 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Hash Join (cost=99183.30..756540.93 rows=278118 width=20) (actual time=5894.873..32893.335 rows=459790 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=7313668 width=20) (actual time=0.085..21144.400 rows=5922359 loops=5)
Filter: (l_returnflag = 'R'::bpchar)
Rows Removed by Filter: 18076562
-> Hash (cost=84366.09..84366.09 rows=1140809 width=12) (actual time=5891.283..5891.283 rows=1147963 loops=5)
Buckets: 2097152 Batches: 2 Memory Usage: 41041kB
-> Bitmap Heap Scan on orders (cost=12006.75..84366.09 rows=1140809 width=12) (actual time=1381.308..4992.235 rows=1147963 loops=5)
Recheck Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
Heap Blocks: exact=486100
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..11721.55 rows=1140809 width=0) (actual time=961.365..961.365 rows=1147963 loops=5)
Index Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Hash (cost=37415.58..37415.58 rows=3000298 width=151) (actual time=8228.812..8228.812 rows=3000000 loops=5)
Buckets: 524288 Batches: 16 Memory Usage: 38667kB
-> Seq Scan on customer (cost=0.00..37415.58 rows=3000298 width=151) (actual time=0.014..3386.674 rows=3000000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.047..0.047 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.014..0.026 rows=25 loops=5)
Planning time: 5.714 ms
Execution time: 62961.968 ms
(36 rows)
ph_results/head/10_4.out 000644 001750 001750 00000010544 13044363363 014702 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1142835.84..1142835.89 rows=20 width=201) (actual time=61330.114..61330.122 rows=20 loops=1)
-> Sort (cost=1142835.84..1145617.02 rows=1112470 width=201) (actual time=61330.111..61330.116 rows=20 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 34kB
-> GroupAggregate (cost=1082640.49..1113233.42 rows=1112470 width=201) (actual time=49469.821..60117.872 rows=765020 loops=1)
Group Key: customer.c_custkey, nation.n_name
-> Sort (cost=1082640.49..1085421.67 rows=1112470 width=181) (actual time=49469.746..51274.183 rows=2298951 loops=1)
Sort Key: customer.c_custkey, nation.n_name
Sort Method: external merge Disk: 438448kB
-> Gather (cost=181549.27..965269.43 rows=1112470 width=181) (actual time=12232.471..44018.297 rows=2298951 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=180549.27..853022.43 rows=278118 width=181) (actual time=11879.022..41673.712 rows=459790 loops=5)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Hash Join (cost=180548.61..849197.65 rows=278118 width=163) (actual time=11878.721..41178.714 rows=459790 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Hash Join (cost=99183.30..756540.93 rows=278118 width=20) (actual time=5168.901..32063.981 rows=459790 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=7313668 width=20) (actual time=0.146..20922.307 rows=5922359 loops=5)
Filter: (l_returnflag = 'R'::bpchar)
Rows Removed by Filter: 18076562
-> Hash (cost=84366.09..84366.09 rows=1140809 width=12) (actual time=5165.837..5165.837 rows=1147963 loops=5)
Buckets: 2097152 Batches: 2 Memory Usage: 41041kB
-> Bitmap Heap Scan on orders (cost=12006.75..84366.09 rows=1140809 width=12) (actual time=1326.939..4332.943 rows=1147963 loops=5)
Recheck Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
Heap Blocks: exact=486100
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..11721.55 rows=1140809 width=0) (actual time=931.162..931.162 rows=1147963 loops=5)
Index Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Hash (cost=37415.58..37415.58 rows=3000298 width=151) (actual time=6705.205..6705.205 rows=3000000 loops=5)
Buckets: 524288 Batches: 16 Memory Usage: 38667kB
-> Seq Scan on customer (cost=0.00..37415.58 rows=3000298 width=151) (actual time=0.020..2663.250 rows=3000000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.044..0.044 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.013..0.024 rows=25 loops=5)
Planning time: 5.011 ms
Execution time: 61379.663 ms
(36 rows)
ph_results/head/9_3.out 000644 001750 001750 00000011125 13044363367 014631 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=793072.62..793072.62 rows=1 width=66) (actual time=62642.925..62642.926 rows=1 loops=1)
-> Sort (cost=793072.62..793223.00 rows=60150 width=66) (actual time=62642.924..62642.924 rows=1 loops=1)
Sort Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize HashAggregate (cost=791719.25..792771.87 rows=60150 width=66) (actual time=62640.018..62642.537 rows=175 loops=1)
Group Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Gather (cost=764200.62..789313.25 rows=240600 width=66) (actual time=62630.420..62635.021 rows=875 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial HashAggregate (cost=763200.62..764253.25 rows=60150 width=66) (actual time=62623.335..62625.822 rows=175 loops=5)
Group Key: nation.n_name, date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)
-> Nested Loop (cost=4962.03..749053.20 rows=808424 width=57) (actual time=266.717..53856.066 rows=1300798 loops=5)
-> Nested Loop (cost=4961.47..166557.58 rows=808424 width=57) (actual time=266.596..20153.205 rows=1300798 loops=5)
Join Filter: (supplier.s_suppkey = lineitem.l_suppkey)
-> Hash Join (cost=4960.90..77590.12 rows=121213 width=56) (actual time=266.485..3223.359 rows=173494 loops=5)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Hash Join (cost=4960.24..75922.78 rows=121213 width=38) (actual time=266.340..2980.868 rows=173494 loops=5)
Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
-> Nested Loop (cost=0.43..69296.30 rows=121213 width=26) (actual time=0.141..2238.787 rows=173494 loops=5)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=30303 width=4) (actual time=0.045..972.730 rows=43373 loops=5)
Filter: ((p_name)::text ~~ '%grey%'::text)
Rows Removed by Filter: 756627
-> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..1.23 rows=37 width=22) (actual time=0.021..0.025 rows=4 loops=216867)
Index Cond: (ps_partkey = part.p_partkey)
-> Hash (cost=2459.80..2459.80 rows=200000 width=12) (actual time=265.853..265.853 rows=200000 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 11423kB
-> Seq Scan on supplier (cost=0.00..2459.80 rows=200000 width=12) (actual time=0.014..139.854 rows=200000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.046..0.046 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.014..0.025 rows=25 loops=5)
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..0.72 rows=1 width=41) (actual time=0.024..0.088 rows=7 loops=867468)
Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
-> Index Scan using orders_pkey on orders (cost=0.56..0.71 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=6503988)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Planning time: 18.250 ms
Execution time: 62767.590 ms
(36 rows)
ph_results/ph/9_2.out 000644 001750 001750 00000011277 13044357132 014337 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=478636.60..478636.60 rows=1 width=66) (actual time=58383.006..58383.006 rows=1 loops=1)
-> Sort (cost=478636.60..478786.98 rows=60150 width=66) (actual time=58383.002..58383.002 rows=1 loops=1)
Sort Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize HashAggregate (cost=477283.23..478335.85 rows=60150 width=66) (actual time=58380.881..58382.693 rows=175 loops=1)
Group Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Gather (cost=449764.60..474877.23 rows=240600 width=66) (actual time=58337.421..58376.375 rows=700 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial HashAggregate (cost=448764.60..449817.23 rows=60150 width=66) (actual time=48466.650..48469.338 rows=140 loops=5)
Group Key: nation.n_name, date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)
-> Parallel Hash Join (cost=246781.06..434617.18 rows=808424 width=57) (actual time=9115.106..40714.698 rows=1300798 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Nested Loop (cost=3565.63..162787.99 rows=808424 width=57) (actual time=141.767..21092.269 rows=1625997 loops=4)
Join Filter: (supplier.s_suppkey = lineitem.l_suppkey)
-> Parallel Hash Join (cost=3565.06..73820.53 rows=121213 width=56) (actual time=141.705..3146.109 rows=216867 loops=4)
Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
-> Nested Loop (cost=0.43..69296.30 rows=121213 width=26) (actual time=0.251..2575.983 rows=216867 loops=4)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=30303 width=4) (actual time=0.077..1099.393 rows=54217 loops=4)
Filter: ((p_name)::text ~~ '%grey%'::text)
Rows Removed by Filter: 945783
-> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..1.23 rows=37 width=22) (actual time=0.020..0.024 rows=4 loops=216867)
Index Cond: (ps_partkey = part.p_partkey)
-> Parallel Shared Hash (cost=2439.62..2439.62 rows=83333 width=30) (actual time=139.806..139.806 rows=50000 loops=4)
-> Hash Join (cost=0.66..2439.62 rows=83333 width=30) (actual time=0.108..75.913 rows=50000 loops=4)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.038..22.742 rows=50000 loops=4)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.050..0.050 rows=25 loops=4)
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.020..0.031 rows=25 loops=4)
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..0.72 rows=1 width=41) (actual time=0.022..0.074 rows=7 loops=867468)
Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
-> Parallel Shared Hash (cost=130246.96..130246.96 rows=7499976 width=8) (actual time=8921.629..8921.629 rows=6000000 loops=5)
Buckets: 8388608 (originally 8388608) Batches: 4 (originally 2) Memory Usage: 359104kB
-> Parallel Seq Scan on orders (cost=0.00..130246.96 rows=7499976 width=8) (actual time=0.032..4200.523 rows=6000000 loops=5)
Planning time: 18.758 ms
Execution time: 58537.621 ms
(36 rows)
ph_results/head/3_1.out 000644 001750 001750 00000010204 13044363361 014610 0 ustar 00edb edb 000000 000000 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2921622.69..2921622.72 rows=10 width=48) (actual time=70980.245..70980.249 rows=10 loops=1)
-> Sort (cost=2921622.69..2937248.64 rows=6250377 width=48) (actual time=70980.243..70980.245 rows=10 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate
Sort Method: top-N heapsort Memory: 26kB
-> GroupAggregate (cost=2599042.98..2786554.29 rows=6250377 width=48) (actual time=68387.824..70736.489 rows=226765 loops=1)
Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
-> Sort (cost=2599042.98..2614668.93 rows=6250377 width=28) (actual time=68387.751..68583.988 rows=599838 loops=1)
Sort Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
Sort Method: quicksort Memory: 61987kB
-> Gather (cost=505541.73..1884970.09 rows=6250377 width=28) (actual time=31401.899..67498.476 rows=599838 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=504541.73..1258932.39 rows=1562594 width=28) (actual time=31336.994..67145.127 rows=119968 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=16076611 width=20) (actual time=0.027..22049.271 rows=12887556 loops=5)
Filter: (l_shipdate > '1995-03-20'::date)
Rows Removed by Filter: 11111365
-> Hash (cost=466669.27..466669.27 rows=2915893 width=12) (actual time=31330.618..31330.618 rows=2926672 loops=5)
Buckets: 2097152 Batches: 4 Memory Usage: 47877kB
-> Hash Join (cost=144594.06..466669.27 rows=2915893 width=12) (actual time=9761.603..29174.262 rows=2926672 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Bitmap Heap Scan on orders (cost=117360.93..355430.49 rows=14625789 width=20) (actual time=7896.461..16237.601 rows=14640000 loops=5)
Recheck Cond: (o_orderdate < '1995-03-20'::date)
Heap Blocks: exact=552472
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..113704.48 rows=14625789 width=0) (actual time=7343.616..7343.616 rows=14640000 loops=5)
Index Cond: (o_orderdate < '1995-03-20'::date)
-> Hash (cost=19756.15..19756.15 rows=598159 width=4) (actual time=1863.305..1863.305 rows=598321 loops=5)
Buckets: 1048576 Batches: 1 Memory Usage: 29227kB
-> Bitmap Heap Scan on customer (cost=4866.56..19756.15 rows=598159 width=4) (actual time=357.462..1478.356 rows=598321 loops=5)
Recheck Cond: (c_mktsegment = 'FURNITURE'::bpchar)
Heap Blocks: exact=74114
-> Bitmap Index Scan on idx_c_mktsegment (cost=0.00..4717.02 rows=598159 width=0) (actual time=322.313..322.313 rows=598321 loops=5)
Index Cond: (c_mktsegment = 'FURNITURE'::bpchar)
Planning time: 4.123 ms
Execution time: 70983.962 ms
(35 rows)
ph_results/head/8_3.out 000644 001750 001750 00000014621 13044363353 014627 0 ustar 00edb edb 000000 000000 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=212957.25..212957.33 rows=1 width=40) (actual time=8136.135..8136.136 rows=1 loops=1)
-> Finalize GroupAggregate (cost=212957.25..213155.74 rows=2406 width=40) (actual time=8136.131..8136.131 rows=1 loops=1)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=212957.25..212981.31 rows=9624 width=72) (actual time=8136.062..8136.064 rows=6 loops=1)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 26kB
-> Gather (cost=211067.02..212320.50 rows=9624 width=72) (actual time=8111.654..8135.989 rows=10 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=210067.02..210358.10 rows=2406 width=72) (actual time=8103.466..8122.264 rows=2 loops=5)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=210067.02..210094.01 rows=10798 width=46) (actual time=8084.610..8087.712 rows=9676 loops=5)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 1172kB
-> Hash Join (cost=4962.72..209343.59 rows=10798 width=46) (actual time=298.865..8072.759 rows=9676 loops=5)
Hash Cond: (supplier.s_nationkey = n2.n_nationkey)
-> Hash Join (cost=4962.06..209140.47 rows=10798 width=24) (actual time=298.648..8046.525 rows=9676 loops=5)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
-> Hash Join (cost=2.26..204032.18 rows=10798 width=24) (actual time=3.420..7725.323 rows=9676 loops=5)
Hash Cond: (customer.c_nationkey = n1.n_nationkey)
-> Nested Loop (cost=1.56..203721.03 rows=53992 width=32) (actual time=0.493..7693.097 rows=48453 loops=5)
-> Nested Loop (cost=1.13..172706.85 rows=53992 width=32) (actual time=0.435..6702.707 rows=48453 loops=5)
-> Nested Loop (cost=0.57..45756.33 rows=177854 width=28) (actual time=0.258..2609.781 rows=159834 loops=5)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=6667 width=4) (actual time=0.150..685.610 rows=5325 loops=5)
Filter: ((p_type)::text = 'MEDIUM BURNISHED TIN'::text)
Rows Removed by Filter: 794675
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..3.49 rows=27 width=36) (actual time=0.035..0.337 rows=30 loops=26626)
Index Cond: (l_partkey = part.p_partkey)
-> Index Scan using orders_pkey on orders (cost=0.56..0.70 rows=1 width=16) (actual time=0.024..0.024 rows=0 loops=799171)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Filter: ((o_orderdate >= '1995-01-01'::date) AND (o_orderdate <= '1996-12-31'::date))
Rows Removed by Filter: 1
-> Index Scan using customer_pkey on customer (cost=0.43..0.56 rows=1 width=12) (actual time=0.018..0.019 rows=1 loops=242266)
Index Cond: (c_custkey = orders.o_custkey)
-> Hash (cost=0.64..0.64 rows=5 width=4) (actual time=0.089..0.089 rows=5 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Nested Loop (cost=0.14..0.64 rows=5 width=4) (actual time=0.069..0.083 rows=5 loops=5)
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.019..0.025 rows=1 loops=5)
Filter: (r_name = 'AMERICA'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using idx_nation_regionkey on nation n1 (cost=0.14..0.43 rows=5 width=12) (actual time=0.046..0.051 rows=5 loops=5)
Index Cond: (n_regionkey = region.r_regionkey)
-> Hash (cost=2459.80..2459.80 rows=200000 width=12) (actual time=294.861..294.861 rows=200000 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 11423kB
-> Seq Scan on supplier (cost=0.00..2459.80 rows=200000 width=12) (actual time=0.013..154.015 rows=200000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.079..0.079 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation n2 (cost=0.00..0.35 rows=25 width=30) (actual time=0.018..0.043 rows=25 loops=5)
Planning time: 25.703 ms
Execution time: 8262.482 ms
(50 rows)
ph_results/ph/8_2.out 000644 001750 001750 00000016022 13044357136 014333 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=199823.61..199823.67 rows=1 width=40) (actual time=11548.238..11548.238 rows=1 loops=1)
-> Finalize GroupAggregate (cost=199823.61..199949.93 rows=2406 width=40) (actual time=11548.236..11548.236 rows=1 loops=1)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=199823.61..199835.64 rows=4812 width=72) (actual time=11548.158..11548.159 rows=3 loops=1)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 25kB
-> Gather (cost=198595.04..199529.30 rows=4812 width=72) (actual time=11503.594..11548.120 rows=4 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial GroupAggregate (cost=197595.04..198048.10 rows=2406 width=72) (actual time=11426.676..11454.722 rows=1 loops=3)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=197595.04..197640.03 rows=17998 width=46) (actual time=11398.701..11403.533 rows=16126 loops=3)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=194334.94..196323.02 rows=17998 width=46) (actual time=11281.614..11386.976 rows=16126 loops=3)
Hash Cond: (supplier.s_nationkey = n2.n_nationkey)
-> Parallel Hash Join (cost=194334.28..195984.90 rows=17998 width=24) (actual time=11281.459..11358.641 rows=16126 loops=3)
Hash Cond: (supplier.s_suppkey = lineitem.l_suppkey)
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.026..42.877 rows=100000 loops=2)
-> Parallel Shared Hash (cost=194188.50..194188.50 rows=10798 width=24) (actual time=11281.117..11281.117 rows=16126 loops=3)
Buckets: 65536 (originally 16384) Batches: 1 (originally 1) Memory Usage: 3616kB
-> Parallel Hash Join (cost=21253.31..194188.50 rows=10798 width=24) (actual time=1129.540..11253.444 rows=16126 loops=3)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Nested Loop (cost=1.13..172706.85 rows=53992 width=32) (actual time=0.315..10028.420 rows=80755 loops=3)
-> Nested Loop (cost=0.57..45756.33 rows=177854 width=28) (actual time=0.215..3893.734 rows=266390 loops=3)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=6667 width=4) (actual time=0.134..1021.297 rows=8875 loops=3)
Filter: ((p_type)::text = 'MEDIUM BURNISHED TIN'::text)
Rows Removed by Filter: 1324458
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..3.49 rows=27 width=36) (actual time=0.031..0.302 rows=30 loops=26626)
Index Cond: (l_partkey = part.p_partkey)
-> Index Scan using orders_pkey on orders (cost=0.56..0.70 rows=1 width=16) (actual time=0.022..0.022 rows=0 loops=799171)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Filter: ((o_orderdate >= '1995-01-01'::date) AND (o_orderdate <= '1996-12-31'::date))
Rows Removed by Filter: 1
-> Parallel Shared Hash (cost=19226.97..19226.97 rows=150015 width=4) (actual time=1123.958..1123.958 rows=199896 loops=3)
Buckets: 1048576 (originally 262144) Batches: 1 (originally 1) Memory Usage: 31680kB
-> Hash Join (cost=0.70..19226.97 rows=150015 width=4) (actual time=0.102..907.924 rows=199896 loops=3)
Hash Cond: (customer.c_nationkey = n1.n_nationkey)
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=12) (actual time=0.028..379.685 rows=1000000 loops=3)
-> Hash (cost=0.64..0.64 rows=5 width=4) (actual time=0.059..0.059 rows=5 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Nested Loop (cost=0.14..0.64 rows=5 width=4) (actual time=0.047..0.055 rows=5 loops=3)
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.014..0.017 rows=1 loops=3)
Filter: (r_name = 'AMERICA'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using idx_nation_regionkey on nation n1 (cost=0.14..0.43 rows=5 width=12) (actual time=0.031..0.035 rows=5 loops=3)
Index Cond: (n_regionkey = region.r_regionkey)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.041..0.041 rows=25 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on nation n2 (cost=0.00..0.35 rows=25 width=30) (actual time=0.012..0.023 rows=25 loops=3)
Planning time: 13.858 ms
Execution time: 11683.000 ms
(52 rows)
ph_results/head/3_4.out 000644 001750 001750 00000010204 13044363362 014614 0 ustar 00edb edb 000000 000000 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2921622.69..2921622.72 rows=10 width=48) (actual time=73945.328..73945.332 rows=10 loops=1)
-> Sort (cost=2921622.69..2937248.64 rows=6250377 width=48) (actual time=73945.326..73945.329 rows=10 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate
Sort Method: top-N heapsort Memory: 26kB
-> GroupAggregate (cost=2599042.98..2786554.29 rows=6250377 width=48) (actual time=71413.456..73708.059 rows=226765 loops=1)
Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
-> Sort (cost=2599042.98..2614668.93 rows=6250377 width=28) (actual time=71413.347..71603.304 rows=599838 loops=1)
Sort Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
Sort Method: quicksort Memory: 61987kB
-> Gather (cost=505541.73..1884970.09 rows=6250377 width=28) (actual time=31694.951..70342.246 rows=599838 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=504541.73..1258932.39 rows=1562594 width=28) (actual time=31978.684..68843.600 rows=119968 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=16076611 width=20) (actual time=0.047..22424.826 rows=12887556 loops=5)
Filter: (l_shipdate > '1995-03-20'::date)
Rows Removed by Filter: 11111365
-> Hash (cost=466669.27..466669.27 rows=2915893 width=12) (actual time=31972.656..31972.656 rows=2926672 loops=5)
Buckets: 2097152 Batches: 4 Memory Usage: 47877kB
-> Hash Join (cost=144594.06..466669.27 rows=2915893 width=12) (actual time=9447.912..29738.934 rows=2926672 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Bitmap Heap Scan on orders (cost=117360.93..355430.49 rows=14625789 width=20) (actual time=7918.701..16644.086 rows=14640000 loops=5)
Recheck Cond: (o_orderdate < '1995-03-20'::date)
Heap Blocks: exact=552472
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..113704.48 rows=14625789 width=0) (actual time=7406.144..7406.144 rows=14640000 loops=5)
Index Cond: (o_orderdate < '1995-03-20'::date)
-> Hash (cost=19756.15..19756.15 rows=598159 width=4) (actual time=1527.885..1527.885 rows=598321 loops=5)
Buckets: 1048576 Batches: 1 Memory Usage: 29227kB
-> Bitmap Heap Scan on customer (cost=4866.56..19756.15 rows=598159 width=4) (actual time=373.716..1131.011 rows=598321 loops=5)
Recheck Cond: (c_mktsegment = 'FURNITURE'::bpchar)
Heap Blocks: exact=74114
-> Bitmap Index Scan on idx_c_mktsegment (cost=0.00..4717.02 rows=598159 width=0) (actual time=336.812..336.812 rows=598321 loops=5)
Index Cond: (c_mktsegment = 'FURNITURE'::bpchar)
Planning time: 3.768 ms
Execution time: 73947.956 ms
(35 rows)
ph_results/ph/ 000755 001750 001750 00000000000 13044357437 013215 5 ustar 00edb edb 000000 000000 ph_results/ph/3_3.out 000644 001750 001750 00000007147 13044357131 014332 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2538085.87..2538085.89 rows=10 width=48) (actual time=59905.602..59905.606 rows=10 loops=1)
-> Sort (cost=2538085.87..2553711.81 rows=6250377 width=48) (actual time=59905.600..59905.602 rows=10 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate
Sort Method: top-N heapsort Memory: 26kB
-> GroupAggregate (cost=2215506.16..2403017.47 rows=6250377 width=48) (actual time=57207.334..59647.989 rows=226765 loops=1)
Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
-> Sort (cost=2215506.16..2231132.10 rows=6250377 width=28) (actual time=57207.261..57411.265 rows=599838 loops=1)
Sort Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
Sort Method: quicksort Memory: 61987kB
-> Gather (cost=194179.48..1501433.26 rows=6250377 width=28) (actual time=9280.085..55948.026 rows=599838 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Hash Join (cost=193179.48..875395.56 rows=1562594 width=28) (actual time=9276.644..46815.609 rows=119968 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=16076611 width=20) (actual time=0.187..24589.182 rows=16109445 loops=4)
Filter: (l_shipdate > '1995-03-20'::date)
Rows Removed by Filter: 13889207
-> Parallel Shared Hash (cost=183338.34..183338.34 rows=728973 width=12) (actual time=9262.727..9262.727 rows=585334 loops=5)
Buckets: 1048576 (originally 1048576) Batches: 4 (originally 1) Memory Usage: 42656kB
-> Parallel Hash Join (cost=18807.32..183338.34 rows=728973 width=12) (actual time=723.850..8467.497 rows=585334 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Parallel Seq Scan on orders (cost=0.00..148996.90 rows=3656447 width=20) (actual time=0.033..4996.097 rows=2928000 loops=5)
Filter: (o_orderdate < '1995-03-20'::date)
Rows Removed by Filter: 3072000
-> Parallel Shared Hash (cost=16788.53..16788.53 rows=149540 width=4) (actual time=721.286..721.286 rows=119664 loops=5)
Buckets: 1048576 (originally 262144) Batches: 1 (originally 1) Memory Usage: 31648kB
-> Parallel Seq Scan on customer (cost=0.00..16788.53 rows=149540 width=4) (actual time=0.019..556.210 rows=119664 loops=5)
Filter: (c_mktsegment = 'FURNITURE'::bpchar)
Rows Removed by Filter: 480336
Planning time: 3.901 ms
Execution time: 59915.961 ms
(31 rows)
ph_results/head/ 000755 001750 001750 00000000000 13044364060 013475 5 ustar 00edb edb 000000 000000 ph_results/head/7_3.out 000644 001750 001750 00000013247 13044363356 014634 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1283926.71..1283926.79 rows=1 width=92) (actual time=40226.514..40226.515 rows=1 loops=1)
-> Finalize GroupAggregate (cost=1283926.71..1284704.50 rows=10036 width=92) (actual time=40226.511..40226.511 rows=1 loops=1)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=1283926.71..1284027.07 rows=40144 width=92) (actual time=40226.467..40226.469 rows=6 loops=1)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 27kB
-> Gather (cost=1276142.56..1280857.12 rows=40144 width=92) (actual time=40137.261..40226.346 rows=20 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=1275142.56..1275842.72 rows=10036 width=92) (actual time=40128.298..40203.025 rows=4 loops=5)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=1275142.56..1275217.50 rows=29973 width=72) (actual time=40103.009..40111.061 rows=23488 loops=5)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 4078kB
-> Hash Join (cost=25864.84..1272913.87 rows=29973 width=72) (actual time=866.280..40047.942 rows=23488 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
Join Filter: (((n1.n_name = 'ARGENTINA'::bpchar) AND (n2.n_name = 'IRAN'::bpchar)) OR ((n1.n_name = 'IRAN'::bpchar) AND (n2.n_name = 'ARGENTINA'::bpchar)))
Rows Removed by Join Filter: 23456
-> Nested Loop (cost=1613.46..1244659.41 rows=720228 width=50) (actual time=51.572..38652.895 rows=586259 loops=5)
-> Hash Join (cost=1612.90..735594.78 rows=720228 width=50) (actual time=51.509..28480.541 rows=586259 loops=5)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
-> Parallel Seq Scan on lineitem (cost=0.00..693018.90 rows=9002853 width=32) (actual time=0.045..24645.439 rows=7292378 loops=5)
Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate <= '1996-12-31'::date))
Rows Removed by Filter: 16706543
-> Hash (cost=1412.90..1412.90 rows=16000 width=30) (actual time=51.266..51.266 rows=16082 loops=5)
Buckets: 16384 Batches: 1 Memory Usage: 1102kB
-> Nested Loop (cost=0.42..1412.90 rows=16000 width=30) (actual time=0.086..39.937 rows=16082 loops=5)
-> Seq Scan on nation n1 (cost=0.00..0.47 rows=2 width=30) (actual time=0.015..0.046 rows=2 loops=5)
Filter: ((n_name = 'ARGENTINA'::bpchar) OR (n_name = 'IRAN'::bpchar))
Rows Removed by Filter: 23
-> Index Scan using idx_supplier_nation_key on supplier (cost=0.42..626.21 rows=8000 width=12) (actual time=0.050..15.996 rows=8041 loops=10)
Index Cond: (s_nationkey = n1.n_nationkey)
-> Index Scan using orders_pkey on orders (cost=0.56..0.70 rows=1 width=12) (actual time=0.015..0.016 rows=1 loops=2931293)
Index Cond: (o_orderkey = lineitem.l_orderkey)
-> Hash (cost=21251.08..21251.08 rows=240024 width=30) (actual time=812.114..812.114 rows=240035 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 16582kB
-> Nested Loop (cost=0.43..21251.08 rows=240024 width=30) (actual time=0.056..636.297 rows=240035 loops=5)
-> Seq Scan on nation n2 (cost=0.00..0.47 rows=2 width=30) (actual time=0.011..0.047 rows=2 loops=5)
Filter: ((n_name = 'IRAN'::bpchar) OR (n_name = 'ARGENTINA'::bpchar))
Rows Removed by Filter: 23
-> Index Scan using idx_customer_nationkey on customer (cost=0.43..9425.18 rows=120012 width=12) (actual time=0.050..260.263 rows=120018 loops=10)
Index Cond: (c_nationkey = n2.n_nationkey)
Planning time: 10.856 ms
Execution time: 40319.155 ms
(44 rows)
ph_results/ph/8_4.out 000644 001750 001750 00000015763 13044357124 014345 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=199823.61..199823.67 rows=1 width=40) (actual time=14916.092..14916.092 rows=1 loops=1)
-> Finalize GroupAggregate (cost=199823.61..199949.93 rows=2406 width=40) (actual time=14916.090..14916.090 rows=1 loops=1)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=199823.61..199835.64 rows=4812 width=72) (actual time=14916.053..14916.054 rows=4 loops=1)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 25kB
-> Gather (cost=198595.04..199529.30 rows=4812 width=72) (actual time=14883.896..14916.011 rows=6 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial GroupAggregate (cost=197595.04..198048.10 rows=2406 width=72) (actual time=14877.717..14908.635 rows=2 loops=3)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=197595.04..197640.03 rows=17998 width=46) (actual time=14846.854..14851.958 rows=16126 loops=3)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 2208kB
-> Hash Join (cost=194334.94..196323.02 rows=17998 width=46) (actual time=14706.807..14833.202 rows=16126 loops=3)
Hash Cond: (supplier.s_nationkey = n2.n_nationkey)
-> Parallel Hash Join (cost=194334.28..195984.90 rows=17998 width=24) (actual time=14706.664..14800.964 rows=16126 loops=3)
Hash Cond: (supplier.s_suppkey = lineitem.l_suppkey)
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.038..30.329 rows=66667 loops=3)
-> Parallel Shared Hash (cost=194188.50..194188.50 rows=10798 width=24) (actual time=14706.259..14706.259 rows=16126 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 3200kB
-> Parallel Hash Join (cost=21253.31..194188.50 rows=10798 width=24) (actual time=1201.239..10184.119 rows=16126 loops=3)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Nested Loop (cost=1.13..172706.85 rows=53992 width=32) (actual time=0.338..13345.546 rows=121133 loops=2)
-> Nested Loop (cost=0.57..45756.33 rows=177854 width=28) (actual time=0.232..5033.524 rows=399586 loops=2)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=6667 width=4) (actual time=0.149..1373.653 rows=13313 loops=2)
Filter: ((p_type)::text = 'MEDIUM BURNISHED TIN'::text)
Rows Removed by Filter: 1986687
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..3.49 rows=27 width=36) (actual time=0.027..0.255 rows=30 loops=26626)
Index Cond: (l_partkey = part.p_partkey)
-> Index Scan using orders_pkey on orders (cost=0.56..0.70 rows=1 width=16) (actual time=0.020..0.020 rows=0 loops=799171)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Filter: ((o_orderdate >= '1995-01-01'::date) AND (o_orderdate <= '1996-12-31'::date))
Rows Removed by Filter: 1
-> Parallel Shared Hash (cost=19226.97..19226.97 rows=150015 width=4) (actual time=1195.810..1195.810 rows=199896 loops=3)
Buckets: 1048576 (originally 262144) Batches: 1 (originally 1) Memory Usage: 31680kB
-> Hash Join (cost=0.70..19226.97 rows=150015 width=4) (actual time=0.105..974.025 rows=199896 loops=3)
Hash Cond: (customer.c_nationkey = n1.n_nationkey)
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=12) (actual time=0.026..410.445 rows=1000000 loops=3)
-> Hash (cost=0.64..0.64 rows=5 width=4) (actual time=0.060..0.060 rows=5 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Nested Loop (cost=0.14..0.64 rows=5 width=4) (actual time=0.048..0.056 rows=5 loops=3)
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.013..0.016 rows=1 loops=3)
Filter: (r_name = 'AMERICA'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using idx_nation_regionkey on nation n1 (cost=0.14..0.43 rows=5 width=12) (actual time=0.032..0.036 rows=5 loops=3)
Index Cond: (n_regionkey = region.r_regionkey)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.042..0.042 rows=25 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on nation n2 (cost=0.00..0.35 rows=25 width=30) (actual time=0.011..0.022 rows=25 loops=3)
Planning time: 14.443 ms
Execution time: 15049.155 ms
(52 rows)
ph_results/ph/7_2.out 000644 001750 001750 00000014350 13044357130 014326 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=896037.66..896037.74 rows=1 width=92) (actual time=47835.198..47835.198 rows=1 loops=1)
-> Finalize GroupAggregate (cost=896037.66..896815.45 rows=10036 width=92) (actual time=47835.196..47835.196 rows=1 loops=1)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=896037.66..896138.02 rows=40144 width=92) (actual time=47835.150..47835.152 rows=5 loops=1)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 27kB
-> Gather (cost=888253.52..892968.07 rows=40144 width=92) (actual time=47737.174..47835.046 rows=16 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=887253.52..887953.67 rows=10036 width=92) (actual time=39170.704..39235.816 rows=3 loops=5)
Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
-> Sort (cost=887253.52..887328.45 rows=29973 width=72) (actual time=39149.203..39156.790 rows=23488 loops=5)
Sort Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone))
Sort Method: quicksort Memory: 25kB
-> Parallel Hash Join (cost=153113.77..885024.82 rows=29973 width=72) (actual time=4922.543..39101.502 rows=23488 loops=5)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
Join Filter: (((n1.n_name = 'ARGENTINA'::bpchar) AND (n2.n_name = 'IRAN'::bpchar)) OR ((n1.n_name = 'IRAN'::bpchar) AND (n2.n_name = 'ARGENTINA'::bpchar)))
Rows Removed by Join Filter: 23456
-> Parallel Hash Join (cost=151350.97..879931.13 rows=720228 width=50) (actual time=4852.008..38523.546 rows=584293 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..693018.90 rows=9002853 width=32) (actual time=0.090..27765.793 rows=9115473 loops=4)
Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate <= '1996-12-31'::date))
Rows Removed by Filter: 20883179
-> Parallel Shared Hash (cost=143250.99..143250.99 rows=599998 width=30) (actual time=4841.351..4841.351 rows=480479 loops=5)
Buckets: 1048576 (originally 1048576) Batches: 4 (originally 1) Memory Usage: 45952kB
-> Nested Loop (cost=1.06..143250.99 rows=599998 width=30) (actual time=0.232..4121.668 rows=480479 loops=5)
-> Hash Join (cost=0.50..18326.68 rows=60006 width=30) (actual time=0.107..769.550 rows=48007 loops=5)
Hash Cond: (customer.c_nationkey = n2.n_nationkey)
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=12) (actual time=0.012..302.532 rows=600000 loops=5)
-> Hash (cost=0.47..0.47 rows=2 width=30) (actual time=0.066..0.066 rows=2 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on nation n2 (cost=0.00..0.47 rows=2 width=30) (actual time=0.016..0.052 rows=2 loops=5)
Filter: ((n_name = 'IRAN'::bpchar) OR (n_name = 'ARGENTINA'::bpchar))
Rows Removed by Filter: 23
-> Index Scan using idx_orders_custkey on orders (cost=0.56..1.91 rows=17 width=12) (actual time=0.019..0.063 rows=10 loops=240035)
Index Cond: (o_custkey = customer.c_custkey)
-> Parallel Shared Hash (cost=1672.80..1672.80 rows=6667 width=30) (actual time=68.550..68.550 rows=3216 loops=5)
Buckets: 16384 (originally 8192) Batches: 1 (originally 1) Memory Usage: 1216kB
-> Hash Join (cost=0.50..1672.80 rows=6667 width=30) (actual time=0.106..63.691 rows=3216 loops=5)
Hash Cond: (supplier.s_nationkey = n1.n_nationkey)
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.034..38.875 rows=40000 loops=5)
-> Hash (cost=0.47..0.47 rows=2 width=30) (actual time=0.043..0.043 rows=2 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on nation n1 (cost=0.00..0.47 rows=2 width=30) (actual time=0.014..0.039 rows=2 loops=5)
Filter: ((n_name = 'ARGENTINA'::bpchar) OR (n_name = 'IRAN'::bpchar))
Rows Removed by Filter: 23
Planning time: 9.481 ms
Execution time: 47949.721 ms
(48 rows)
ph_results/head/5_3.out 000644 001750 001750 00000014477 13044363364 014637 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=993197.98..993197.98 rows=1 width=58) (actual time=54740.666..54740.667 rows=1 loops=1)
-> Sort (cost=993197.98..993198.04 rows=25 width=58) (actual time=54740.664..54740.664 rows=1 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize GroupAggregate (cost=993196.54..993197.86 rows=25 width=58) (actual time=54740.533..54740.617 rows=5 loops=1)
Group Key: nation.n_name
-> Sort (cost=993196.54..993196.79 rows=100 width=58) (actual time=54740.498..54740.506 rows=25 loops=1)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 28kB
-> Gather (cost=992723.87..993193.22 rows=100 width=58) (actual time=54609.004..54740.425 rows=25 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=991723.87..992183.22 rows=25 width=58) (actual time=54499.778..54580.116 rows=5 loops=5)
Group Key: nation.n_name
-> Sort (cost=991723.87..991815.68 rows=36723 width=38) (actual time=54479.703..54489.439 rows=29012 loops=5)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 3010kB
-> Hash Join (cost=275164.82..988939.48 rows=36723 width=38) (actual time=14930.907..54433.154 rows=29012 loops=5)
Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))
-> Hash Join (cost=269705.02..976227.01 rows=918060 width=58) (actual time=14651.696..53542.902 rows=728180 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..543025.70 rows=29998640 width=28) (actual time=0.072..18031.151 rows=23998922 loops=5)
-> Hash (cost=257421.79..257421.79 rows=918098 width=42) (actual time=14649.155..14649.155 rows=910215 loops=5)
Buckets: 1048576 Batches: 2 Memory Usage: 41956kB
-> Hash Join (cost=106921.90..257421.79 rows=918098 width=42) (actual time=5500.806..13855.041 rows=910215 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Bitmap Heap Scan on orders (cost=48311.90..172416.46 rows=4590491 width=12) (actual time=3383.078..8036.347 rows=4549846 loops=5)
Recheck Cond: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
Heap Blocks: exact=552373
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..47164.27 rows=4590491 width=0) (actual time=2890.238..2890.238 rows=4549846 loops=5)
Index Cond: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Hash (cost=51109.25..51109.25 rows=600060 width=42) (actual time=2116.055..2116.055 rows=599436 loops=5)
Buckets: 1048576 Batches: 1 Memory Usage: 55023kB
-> Nested Loop (cost=0.43..51109.25 rows=600060 width=42) (actual time=0.249..1586.747 rows=599436 loops=5)
-> Nested Loop (cost=0.00..0.82 rows=5 width=30) (actual time=0.096..0.149 rows=5 loops=5)
Join Filter: (nation.n_regionkey = region.r_regionkey)
Rows Removed by Join Filter: 20
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.079..0.087 rows=1 loops=5)
Filter: (r_name = 'AFRICA'::bpchar)
Rows Removed by Filter: 4
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=38) (actual time=0.010..0.028 rows=25 loops=5)
-> Index Scan using idx_customer_nationkey on customer (cost=0.43..9021.57 rows=120012 width=12) (actual time=0.067..243.177 rows=119887 loops=25)
Index Cond: (c_nationkey = nation.n_nationkey)
-> Hash (cost=2459.80..2459.80 rows=200000 width=12) (actual time=276.777..276.777 rows=200000 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 11423kB
-> Seq Scan on supplier (cost=0.00..2459.80 rows=200000 width=12) (actual time=0.016..130.151 rows=200000 loops=5)
Planning time: 12.077 ms
Execution time: 54815.123 ms
(48 rows)
ph_results/ph/10_1.out 000644 001750 001750 00000010611 13044357130 014373 0 ustar 00edb edb 000000 000000 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1068412.51..1068412.56 rows=20 width=201) (actual time=66117.117..66117.126 rows=20 loops=1)
-> Sort (cost=1068412.51..1071193.68 rows=1112470 width=201) (actual time=66117.115..66117.121 rows=20 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 34kB
-> GroupAggregate (cost=1008217.16..1038810.08 rows=1112470 width=201) (actual time=54649.270..64930.422 rows=765020 loops=1)
Group Key: customer.c_custkey, nation.n_name
-> Sort (cost=1008217.16..1010998.33 rows=1112470 width=181) (actual time=54649.199..56383.159 rows=2298951 loops=1)
Sort Key: customer.c_custkey, nation.n_name
Sort Method: external merge Disk: 438448kB
-> Gather (cost=125807.02..890846.09 rows=1112470 width=181) (actual time=9563.711..48581.571 rows=2298951 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=124807.02..778599.09 rows=278118 width=181) (actual time=9559.600..40394.610 rows=459790 loops=5)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Parallel Hash Join (cost=124806.36..774774.31 rows=278118 width=163) (actual time=9559.250..39739.675 rows=459790 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Parallel Hash Join (cost=99767.01..747996.74 rows=278118 width=20) (actual time=7708.905..34573.168 rows=459790 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=7313668 width=20) (actual time=0.061..24589.326 rows=7402949 loops=4)
Filter: (l_returnflag = 'R'::bpchar)
Rows Removed by Filter: 22595703
-> Shared Hash (cost=84366.09..84366.09 rows=1140809 width=12) (actual time=7688.520..7688.520 rows=229593 loops=5)
Buckets: 2097152 (originally 2097152) Batches: 2 (originally 1) Memory Usage: 43328kB
-> Bitmap Heap Scan on orders (cost=12006.75..84366.09 rows=1140809 width=12) (actual time=1479.762..6399.801 rows=1147963 loops=1)
Recheck Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..11721.55 rows=1140809 width=0) (actual time=1048.075..1048.075 rows=1147963 loops=1)
Index Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Parallel Shared Hash (cost=14913.35..14913.35 rows=750074 width=151) (actual time=1825.967..1825.967 rows=600000 loops=5)
Buckets: 1048576 (originally 1048576) Batches: 16 (originally 1) Memory Usage: 43040kB
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=151) (actual time=0.038..683.102 rows=600000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.065..0.065 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.015..0.031 rows=25 loops=5)
Planning time: 5.579 ms
Execution time: 66180.270 ms
(35 rows)
ph_results/ph/5_2.out 000644 001750 001750 00000013552 13044357125 014333 0 ustar 00edb edb 000000 000000 QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=868714.16..868714.16 rows=1 width=58) (actual time=55020.671..55020.672 rows=1 loops=1)
-> Sort (cost=868714.16..868714.22 rows=25 width=58) (actual time=55020.670..55020.670 rows=1 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize GroupAggregate (cost=868712.72..868714.03 rows=25 width=58) (actual time=55020.542..55020.612 rows=5 loops=1)
Group Key: nation.n_name
-> Sort (cost=868712.72..868712.97 rows=100 width=58) (actual time=55020.511..55020.517 rows=20 loops=1)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 27kB
-> Gather (cost=868240.05..868709.40 rows=100 width=58) (actual time=54810.461..55020.427 rows=20 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=867240.05..867699.40 rows=25 width=58) (actual time=43877.891..43954.921 rows=4 loops=5)
Group Key: nation.n_name
-> Sort (cost=867240.05..867331.86 rows=36723 width=38) (actual time=43858.859..43868.378 rows=29012 loops=5)
Sort Key: nation.n_name
Sort Method: quicksort Memory: 25kB
-> Parallel Hash Join (cost=199601.45..864455.66 rows=36723 width=38) (actual time=7896.020..43813.241 rows=29012 loops=5)
Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey))
-> Parallel Hash Join (cost=196974.99..854790.74 rows=918060 width=58) (actual time=9759.613..53846.193 rows=910222 loops=4)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..543025.70 rows=29998640 width=28) (actual time=0.151..10695.292 rows=29998652 loops=4)
-> Parallel Shared Hash (cost=193876.41..193876.41 rows=229524 width=42) (actual time=9753.677..9753.677 rows=227554 loops=4)
-> Parallel Hash Join (cost=21252.18..193876.41 rows=229524 width=42) (actual time=936.015..9386.333 rows=227554 loops=4)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Parallel Seq Scan on orders (cost=0.00..167746.84 rows=1147623 width=12) (actual time=0.045..7234.244 rows=1137462 loops=4)
Filter: ((o_orderdate >= '1994-01-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 6362538
-> Parallel Shared Hash (cost=19226.97..19226.97 rows=150015 width=42) (actual time=932.801..932.801 rows=149859 loops=4)
-> Hash Join (cost=0.70..19226.97 rows=150015 width=42) (actual time=0.135..722.200 rows=149859 loops=4)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=12) (actual time=0.010..235.419 rows=750000 loops=4)
-> Hash (cost=0.64..0.64 rows=5 width=30) (actual time=0.112..0.112 rows=5 loops=4)
-> Nested Loop (cost=0.14..0.64 rows=5 width=30) (actual time=0.095..0.106 rows=5 loops=4)
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.024..0.028 rows=1 loops=4)
Filter: (r_name = 'AFRICA'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using idx_nation_regionkey on nation (cost=0.14..0.43 rows=5 width=38) (actual time=0.065..0.069 rows=5 loops=4)
Index Cond: (n_regionkey = region.r_regionkey)
-> Parallel Shared Hash (cost=1293.13..1293.13 rows=83333 width=12) (actual time=80.989..80.989 rows=40000 loops=5)
Buckets: 262144 (originally 131072) Batches: 1 (originally 1) Memory Usage: 11520kB
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.015..28.830 rows=40000 loops=5)
Planning time: 12.627 ms
Execution time: 55174.539 ms
(44 rows)
ph_results/head/10_3.out 000644 001750 001750 00000010544 13044363365 014703 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1142835.84..1142835.89 rows=20 width=201) (actual time=63382.136..63382.145 rows=20 loops=1)
-> Sort (cost=1142835.84..1145617.02 rows=1112470 width=201) (actual time=63382.134..63382.139 rows=20 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 34kB
-> GroupAggregate (cost=1082640.49..1113233.42 rows=1112470 width=201) (actual time=51532.191..62171.684 rows=765020 loops=1)
Group Key: customer.c_custkey, nation.n_name
-> Sort (cost=1082640.49..1085421.67 rows=1112470 width=181) (actual time=51532.105..53343.304 rows=2298951 loops=1)
Sort Key: customer.c_custkey, nation.n_name
Sort Method: external merge Disk: 438448kB
-> Gather (cost=181549.27..965269.43 rows=1112470 width=181) (actual time=13827.899..45930.070 rows=2298951 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=180549.27..853022.43 rows=278118 width=181) (actual time=12498.937..42562.550 rows=459790 loops=5)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Hash Join (cost=180548.61..849197.65 rows=278118 width=163) (actual time=12498.653..42067.155 rows=459790 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Hash Join (cost=99183.30..756540.93 rows=278118 width=20) (actual time=5460.863..32642.865 rows=459790 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=7313668 width=20) (actual time=0.102..21166.436 rows=5922359 loops=5)
Filter: (l_returnflag = 'R'::bpchar)
Rows Removed by Filter: 18076562
-> Hash (cost=84366.09..84366.09 rows=1140809 width=12) (actual time=5457.335..5457.335 rows=1147963 loops=5)
Buckets: 2097152 Batches: 2 Memory Usage: 41041kB
-> Bitmap Heap Scan on orders (cost=12006.75..84366.09 rows=1140809 width=12) (actual time=1363.179..4570.404 rows=1147963 loops=5)
Recheck Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
Heap Blocks: exact=486100
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..11721.55 rows=1140809 width=0) (actual time=915.775..915.775 rows=1147963 loops=5)
Index Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Hash (cost=37415.58..37415.58 rows=3000298 width=151) (actual time=7033.697..7033.697 rows=3000000 loops=5)
Buckets: 524288 Batches: 16 Memory Usage: 38667kB
-> Seq Scan on customer (cost=0.00..37415.58 rows=3000298 width=151) (actual time=0.013..2827.588 rows=3000000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.043..0.043 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.012..0.023 rows=25 loops=5)
Planning time: 4.974 ms
Execution time: 63430.941 ms
(36 rows)
ph_results/head/9_2.out 000644 001750 001750 00000011125 13044363361 014622 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=793072.62..793072.62 rows=1 width=66) (actual time=62785.694..62785.695 rows=1 loops=1)
-> Sort (cost=793072.62..793223.00 rows=60150 width=66) (actual time=62785.690..62785.690 rows=1 loops=1)
Sort Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize HashAggregate (cost=791719.25..792771.87 rows=60150 width=66) (actual time=62779.742..62785.009 rows=175 loops=1)
Group Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Gather (cost=764200.62..789313.25 rows=240600 width=66) (actual time=62765.446..62772.547 rows=875 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial HashAggregate (cost=763200.62..764253.25 rows=60150 width=66) (actual time=62760.059..62763.758 rows=175 loops=5)
Group Key: nation.n_name, date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)
-> Nested Loop (cost=4962.03..749053.20 rows=808424 width=57) (actual time=283.240..53982.080 rows=1300798 loops=5)
-> Nested Loop (cost=4961.47..166557.58 rows=808424 width=57) (actual time=283.109..20209.056 rows=1300798 loops=5)
Join Filter: (supplier.s_suppkey = lineitem.l_suppkey)
-> Hash Join (cost=4960.90..77590.12 rows=121213 width=56) (actual time=282.992..3228.618 rows=173494 loops=5)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Hash Join (cost=4960.24..75922.78 rows=121213 width=38) (actual time=282.832..2985.584 rows=173494 loops=5)
Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
-> Nested Loop (cost=0.43..69296.30 rows=121213 width=26) (actual time=0.147..2247.725 rows=173494 loops=5)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=30303 width=4) (actual time=0.054..976.400 rows=43373 loops=5)
Filter: ((p_name)::text ~~ '%grey%'::text)
Rows Removed by Filter: 756627
-> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..1.23 rows=37 width=22) (actual time=0.021..0.025 rows=4 loops=216867)
Index Cond: (ps_partkey = part.p_partkey)
-> Hash (cost=2459.80..2459.80 rows=200000 width=12) (actual time=282.292..282.292 rows=200000 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 11423kB
-> Seq Scan on supplier (cost=0.00..2459.80 rows=200000 width=12) (actual time=0.015..145.038 rows=200000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.051..0.051 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.014..0.027 rows=25 loops=5)
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..0.72 rows=1 width=41) (actual time=0.025..0.088 rows=7 loops=867468)
Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
-> Index Scan using orders_pkey on orders (cost=0.56..0.71 rows=1 width=8) (actual time=0.022..0.023 rows=1 loops=6503988)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Planning time: 21.837 ms
Execution time: 62928.880 ms
(36 rows)
ph_results/ph/9_1.out 000644 001750 001750 00000011277 13044357135 014341 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=478636.60..478636.60 rows=1 width=66) (actual time=60486.448..60486.449 rows=1 loops=1)
-> Sort (cost=478636.60..478786.98 rows=60150 width=66) (actual time=60486.446..60486.446 rows=1 loops=1)
Sort Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize HashAggregate (cost=477283.23..478335.85 rows=60150 width=66) (actual time=60484.328..60486.139 rows=175 loops=1)
Group Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Gather (cost=449764.60..474877.23 rows=240600 width=66) (actual time=60433.790..60480.476 rows=700 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial HashAggregate (cost=448764.60..449817.23 rows=60150 width=66) (actual time=50129.079..50131.151 rows=140 loops=5)
Group Key: nation.n_name, date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)
-> Parallel Hash Join (cost=246781.06..434617.18 rows=808424 width=57) (actual time=9035.501..41830.930 rows=1300798 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Nested Loop (cost=3565.63..162787.99 rows=808424 width=57) (actual time=131.417..23107.089 rows=1625997 loops=4)
Join Filter: (supplier.s_suppkey = lineitem.l_suppkey)
-> Parallel Hash Join (cost=3565.06..73820.53 rows=121213 width=56) (actual time=131.353..3484.666 rows=216867 loops=4)
Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
-> Nested Loop (cost=0.43..69296.30 rows=121213 width=26) (actual time=0.249..2884.425 rows=216867 loops=4)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=30303 width=4) (actual time=0.060..1248.390 rows=54217 loops=4)
Filter: ((p_name)::text ~~ '%grey%'::text)
Rows Removed by Filter: 945783
-> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..1.23 rows=37 width=22) (actual time=0.022..0.026 rows=4 loops=216867)
Index Cond: (ps_partkey = part.p_partkey)
-> Parallel Shared Hash (cost=2439.62..2439.62 rows=83333 width=30) (actual time=129.471..129.471 rows=50000 loops=4)
-> Hash Join (cost=0.66..2439.62 rows=83333 width=30) (actual time=0.109..70.502 rows=50000 loops=4)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.035..20.260 rows=50000 loops=4)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.048..0.048 rows=25 loops=4)
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.016..0.029 rows=25 loops=4)
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..0.72 rows=1 width=41) (actual time=0.024..0.080 rows=7 loops=867468)
Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
-> Parallel Shared Hash (cost=130246.96..130246.96 rows=7499976 width=8) (actual time=8850.435..8850.435 rows=6000000 loops=5)
Buckets: 8388608 (originally 8388608) Batches: 4 (originally 2) Memory Usage: 359104kB
-> Parallel Seq Scan on orders (cost=0.00..130246.96 rows=7499976 width=8) (actual time=0.031..4162.649 rows=6000000 loops=5)
Planning time: 18.590 ms
Execution time: 60660.300 ms
(36 rows)
ph_results/head/8_2.out 000644 001750 001750 00000014621 13044363365 014631 0 ustar 00edb edb 000000 000000 QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=212957.25..212957.33 rows=1 width=40) (actual time=8645.155..8645.155 rows=1 loops=1)
-> Finalize GroupAggregate (cost=212957.25..213155.74 rows=2406 width=40) (actual time=8645.150..8645.150 rows=1 loops=1)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=212957.25..212981.31 rows=9624 width=72) (actual time=8645.066..8645.068 rows=6 loops=1)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 26kB
-> Gather (cost=211067.02..212320.50 rows=9624 width=72) (actual time=8600.269..8644.989 rows=10 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial GroupAggregate (cost=210067.02..210358.10 rows=2406 width=72) (actual time=8592.431..8616.429 rows=2 loops=5)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=210067.02..210094.01 rows=10798 width=46) (actual time=8568.429..8572.557 rows=9676 loops=5)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 1153kB
-> Hash Join (cost=4962.72..209343.59 rows=10798 width=46) (actual time=338.249..8555.110 rows=9676 loops=5)
Hash Cond: (supplier.s_nationkey = n2.n_nationkey)
-> Hash Join (cost=4962.06..209140.47 rows=10798 width=24) (actual time=338.043..8526.397 rows=9676 loops=5)
Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey)
-> Hash Join (cost=2.26..204032.18 rows=10798 width=24) (actual time=3.265..8165.549 rows=9676 loops=5)
Hash Cond: (customer.c_nationkey = n1.n_nationkey)
-> Nested Loop (cost=1.56..203721.03 rows=53992 width=32) (actual time=0.407..8130.297 rows=48453 loops=5)
-> Nested Loop (cost=1.13..172706.85 rows=53992 width=32) (actual time=0.351..7075.735 rows=48453 loops=5)
-> Nested Loop (cost=0.57..45756.33 rows=177854 width=28) (actual time=0.223..2873.146 rows=159834 loops=5)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=6667 width=4) (actual time=0.124..756.535 rows=5325 loops=5)
Filter: ((p_type)::text = 'MEDIUM BURNISHED TIN'::text)
Rows Removed by Filter: 794675
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..3.49 rows=27 width=36) (actual time=0.038..0.371 rows=30 loops=26626)
Index Cond: (l_partkey = part.p_partkey)
-> Index Scan using orders_pkey on orders (cost=0.56..0.70 rows=1 width=16) (actual time=0.025..0.025 rows=0 loops=799171)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Filter: ((o_orderdate >= '1995-01-01'::date) AND (o_orderdate <= '1996-12-31'::date))
Rows Removed by Filter: 1
-> Index Scan using customer_pkey on customer (cost=0.43..0.56 rows=1 width=12) (actual time=0.019..0.020 rows=1 loops=242266)
Index Cond: (c_custkey = orders.o_custkey)
-> Hash (cost=0.64..0.64 rows=5 width=4) (actual time=0.091..0.091 rows=5 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Nested Loop (cost=0.14..0.64 rows=5 width=4) (actual time=0.074..0.086 rows=5 loops=5)
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.021..0.025 rows=1 loops=5)
Filter: (r_name = 'AMERICA'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using idx_nation_regionkey on nation n1 (cost=0.14..0.43 rows=5 width=12) (actual time=0.049..0.054 rows=5 loops=5)
Index Cond: (n_regionkey = region.r_regionkey)
-> Hash (cost=2459.80..2459.80 rows=200000 width=12) (actual time=334.437..334.437 rows=200000 loops=5)
Buckets: 262144 Batches: 1 Memory Usage: 11423kB
-> Seq Scan on supplier (cost=0.00..2459.80 rows=200000 width=12) (actual time=0.012..173.344 rows=200000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.072..0.072 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on nation n2 (cost=0.00..0.35 rows=25 width=30) (actual time=0.015..0.033 rows=25 loops=5)
Planning time: 26.681 ms
Execution time: 8801.814 ms
(50 rows)
ph_results/ph/9_4.out 000644 001750 001750 00000011277 13044357137 014346 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=478636.60..478636.60 rows=1 width=66) (actual time=58929.335..58929.335 rows=1 loops=1)
-> Sort (cost=478636.60..478786.98 rows=60150 width=66) (actual time=58929.333..58929.333 rows=1 loops=1)
Sort Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) DESC
Sort Method: top-N heapsort Memory: 25kB
-> Finalize HashAggregate (cost=477283.23..478335.85 rows=60150 width=66) (actual time=58927.216..58929.029 rows=175 loops=1)
Group Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Gather (cost=449764.60..474877.23 rows=240600 width=66) (actual time=58879.466..58923.375 rows=700 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Partial HashAggregate (cost=448764.60..449817.23 rows=60150 width=66) (actual time=49014.146..49016.126 rows=140 loops=5)
Group Key: nation.n_name, date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)
-> Parallel Hash Join (cost=246781.06..434617.18 rows=808424 width=57) (actual time=9684.031..40997.910 rows=1300798 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Nested Loop (cost=3565.63..162787.99 rows=808424 width=57) (actual time=136.445..20945.590 rows=1625997 loops=4)
Join Filter: (supplier.s_suppkey = lineitem.l_suppkey)
-> Parallel Hash Join (cost=3565.06..73820.53 rows=121213 width=56) (actual time=136.389..3120.794 rows=216867 loops=4)
Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey)
-> Nested Loop (cost=0.43..69296.30 rows=121213 width=26) (actual time=0.208..2563.113 rows=216867 loops=4)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=30303 width=4) (actual time=0.066..1099.437 rows=54217 loops=4)
Filter: ((p_name)::text ~~ '%grey%'::text)
Rows Removed by Filter: 945783
-> Index Scan using idx_partsupp_partkey on partsupp (cost=0.43..1.23 rows=37 width=22) (actual time=0.020..0.023 rows=4 loops=216867)
Index Cond: (ps_partkey = part.p_partkey)
-> Parallel Shared Hash (cost=2439.62..2439.62 rows=83333 width=30) (actual time=134.574..134.574 rows=50000 loops=4)
-> Hash Join (cost=0.66..2439.62 rows=83333 width=30) (actual time=0.099..71.889 rows=50000 loops=4)
Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.036..21.596 rows=50000 loops=4)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.045..0.045 rows=25 loops=4)
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.017..0.028 rows=25 loops=4)
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..0.72 rows=1 width=41) (actual time=0.021..0.073 rows=7 loops=867468)
Index Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey))
-> Parallel Shared Hash (cost=130246.96..130246.96 rows=7499976 width=8) (actual time=9494.580..9494.580 rows=6000000 loops=5)
Buckets: 8388608 (originally 8388608) Batches: 4 (originally 2) Memory Usage: 359072kB
-> Parallel Seq Scan on orders (cost=0.00..130246.96 rows=7499976 width=8) (actual time=0.031..4517.366 rows=6000000 loops=5)
Planning time: 18.725 ms
Execution time: 59077.909 ms
(36 rows)
ph_results/ph/10_4.out 000644 001750 001750 00000010603 13044357134 014403 0 ustar 00edb edb 000000 000000 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1068412.51..1068412.56 rows=20 width=201) (actual time=61597.302..61597.311 rows=20 loops=1)
-> Sort (cost=1068412.51..1071193.68 rows=1112470 width=201) (actual time=61597.299..61597.305 rows=20 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC
Sort Method: top-N heapsort Memory: 34kB
-> GroupAggregate (cost=1008217.16..1038810.08 rows=1112470 width=201) (actual time=50106.105..60410.936 rows=765019 loops=1)
Group Key: customer.c_custkey, nation.n_name
-> Sort (cost=1008217.16..1010998.33 rows=1112470 width=181) (actual time=50106.033..51860.867 rows=2298946 loops=1)
Sort Key: customer.c_custkey, nation.n_name
Sort Method: external merge Disk: 438448kB
-> Gather (cost=125807.02..890846.09 rows=1112470 width=181) (actual time=7217.435..44390.139 rows=2298946 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=124807.02..778599.09 rows=278118 width=181) (actual time=7216.432..41137.226 rows=459789 loops=5)
Hash Cond: (customer.c_nationkey = nation.n_nationkey)
-> Parallel Hash Join (cost=124806.36..774774.31 rows=278118 width=163) (actual time=7216.135..40587.692 rows=459789 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Parallel Hash Join (cost=99767.01..747996.74 rows=278118 width=20) (actual time=5301.135..35647.053 rows=459790 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=7313668 width=20) (actual time=0.127..20327.315 rows=5922359 loops=5)
Filter: (l_returnflag = 'R'::bpchar)
Rows Removed by Filter: 18076562
-> Shared Hash (cost=84366.09..84366.09 rows=1140809 width=12) (actual time=5258.438..5258.438 rows=229593 loops=5)
Buckets: 2097152 (originally 2097152) Batches: 2 (originally 1) Memory Usage: 43328kB
-> Bitmap Heap Scan on orders (cost=12006.75..84366.09 rows=1140809 width=12) (actual time=1162.882..3967.020 rows=1147963 loops=1)
Recheck Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..11721.55 rows=1140809 width=0) (actual time=804.259..804.259 rows=1147963 loops=1)
Index Cond: ((o_orderdate >= '1994-10-01'::date) AND (o_orderdate < '1995-01-01 00:00:00'::timestamp without time zone))
-> Parallel Shared Hash (cost=14913.35..14913.35 rows=750074 width=151) (actual time=1901.204..1901.204 rows=600000 loops=5)
Buckets: 1048576 (originally 1048576) Batches: 16 (originally 1) Memory Usage: 43008kB
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=151) (actual time=0.010..721.452 rows=600000 loops=5)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.049..0.049 rows=25 loops=5)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on nation (cost=0.00..0.35 rows=25 width=30) (actual time=0.010..0.021 rows=25 loops=5)
Planning time: 4.919 ms
Execution time: 61708.127 ms
(35 rows)
ph_results/ph/8_1.out 000644 001750 001750 00000015763 13044357126 014344 0 ustar 00edb edb 000000 000000 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=199823.61..199823.67 rows=1 width=40) (actual time=21638.465..21638.465 rows=1 loops=1)
-> Finalize GroupAggregate (cost=199823.61..199949.93 rows=2406 width=40) (actual time=21638.463..21638.463 rows=1 loops=1)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=199823.61..199835.64 rows=4812 width=72) (actual time=21638.417..21638.418 rows=3 loops=1)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 25kB
-> Gather (cost=198595.04..199529.30 rows=4812 width=72) (actual time=21587.574..21638.377 rows=4 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial GroupAggregate (cost=197595.04..198048.10 rows=2406 width=72) (actual time=21504.578..21535.258 rows=1 loops=3)
Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
-> Sort (cost=197595.04..197640.03 rows=17998 width=46) (actual time=21474.040..21479.157 rows=16126 loops=3)
Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone))
Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=194334.94..196323.02 rows=17998 width=46) (actual time=21346.004..21460.859 rows=16126 loops=3)
Hash Cond: (supplier.s_nationkey = n2.n_nationkey)
-> Parallel Hash Join (cost=194334.28..195984.90 rows=17998 width=24) (actual time=21345.807..21429.703 rows=16126 loops=3)
Hash Cond: (supplier.s_suppkey = lineitem.l_suppkey)
-> Parallel Seq Scan on supplier (cost=0.00..1293.13 rows=83333 width=12) (actual time=0.028..37.793 rows=100000 loops=2)
-> Parallel Shared Hash (cost=194188.50..194188.50 rows=10798 width=24) (actual time=21345.455..21345.455 rows=16126 loops=3)
Buckets: 16384 Batches: 1 Memory Usage: 3168kB
-> Parallel Hash Join (cost=21253.31..194188.50 rows=10798 width=24) (actual time=1328.618..14640.761 rows=16126 loops=3)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Nested Loop (cost=1.13..172706.85 rows=53992 width=32) (actual time=0.427..19792.557 rows=121133 loops=2)
-> Nested Loop (cost=0.57..45756.33 rows=177854 width=28) (actual time=0.284..7407.424 rows=399586 loops=2)
-> Parallel Seq Scan on part (cost=0.00..20696.21 rows=6667 width=4) (actual time=0.179..2336.650 rows=13313 loops=2)
Filter: ((p_type)::text = 'MEDIUM BURNISHED TIN'::text)
Rows Removed by Filter: 1986687
-> Index Scan using idx_lineitem_part_supp on lineitem (cost=0.57..3.49 rows=27 width=36) (actual time=0.040..0.347 rows=30 loops=26626)
Index Cond: (l_partkey = part.p_partkey)
-> Index Scan using orders_pkey on orders (cost=0.56..0.70 rows=1 width=16) (actual time=0.029..0.029 rows=0 loops=799171)
Index Cond: (o_orderkey = lineitem.l_orderkey)
Filter: ((o_orderdate >= '1995-01-01'::date) AND (o_orderdate <= '1996-12-31'::date))
Rows Removed by Filter: 1
-> Parallel Shared Hash (cost=19226.97..19226.97 rows=150015 width=4) (actual time=1322.723..1322.723 rows=199896 loops=3)
Buckets: 1048576 (originally 262144) Batches: 1 (originally 1) Memory Usage: 31712kB
-> Hash Join (cost=0.70..19226.97 rows=150015 width=4) (actual time=0.089..1077.115 rows=199896 loops=3)
Hash Cond: (customer.c_nationkey = n1.n_nationkey)
-> Parallel Seq Scan on customer (cost=0.00..14913.35 rows=750074 width=12) (actual time=0.019..436.333 rows=1000000 loops=3)
-> Hash (cost=0.64..0.64 rows=5 width=4) (actual time=0.057..0.057 rows=5 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Nested Loop (cost=0.14..0.64 rows=5 width=4) (actual time=0.045..0.053 rows=5 loops=3)
-> Seq Scan on region (cost=0.00..0.16 rows=1 width=4) (actual time=0.011..0.013 rows=1 loops=3)
Filter: (r_name = 'AMERICA'::bpchar)
Rows Removed by Filter: 4
-> Index Scan using idx_nation_regionkey on nation n1 (cost=0.14..0.43 rows=5 width=12) (actual time=0.032..0.035 rows=5 loops=3)
Index Cond: (n_regionkey = region.r_regionkey)
-> Hash (cost=0.35..0.35 rows=25 width=30) (actual time=0.040..0.040 rows=25 loops=3)
Buckets: 1024 Batches: 1 Memory Usage: 32kB
-> Seq Scan on nation n2 (cost=0.00..0.35 rows=25 width=30) (actual time=0.009..0.020 rows=25 loops=3)
Planning time: 13.973 ms
Execution time: 21773.333 ms
(52 rows)
ph_results/head/3_3.out 000644 001750 001750 00000010204 13044363360 014611 0 ustar 00edb edb 000000 000000 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2921622.69..2921622.72 rows=10 width=48) (actual time=70795.682..70795.686 rows=10 loops=1)
-> Sort (cost=2921622.69..2937248.64 rows=6250377 width=48) (actual time=70795.680..70795.682 rows=10 loops=1)
Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate
Sort Method: top-N heapsort Memory: 26kB
-> GroupAggregate (cost=2599042.98..2786554.29 rows=6250377 width=48) (actual time=68271.032..70558.634 rows=226765 loops=1)
Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
-> Sort (cost=2599042.98..2614668.93 rows=6250377 width=28) (actual time=68270.972..68453.142 rows=599838 loops=1)
Sort Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority
Sort Method: quicksort Memory: 61987kB
-> Gather (cost=505541.73..1884970.09 rows=6250377 width=28) (actual time=30264.616..67356.769 rows=599838 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Hash Join (cost=504541.73..1258932.39 rows=1562594 width=28) (actual time=30444.182..66832.890 rows=119968 loops=5)
Hash Cond: (lineitem.l_orderkey = orders.o_orderkey)
-> Parallel Seq Scan on lineitem (cost=0.00..618022.30 rows=16076611 width=20) (actual time=0.050..22567.566 rows=12887556 loops=5)
Filter: (l_shipdate > '1995-03-20'::date)
Rows Removed by Filter: 11111365
-> Hash (cost=466669.27..466669.27 rows=2915893 width=12) (actual time=30439.629..30439.629 rows=2926672 loops=5)
Buckets: 2097152 Batches: 4 Memory Usage: 47877kB
-> Hash Join (cost=144594.06..466669.27 rows=2915893 width=12) (actual time=9208.478..28284.122 rows=2926672 loops=5)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Bitmap Heap Scan on orders (cost=117360.93..355430.49 rows=14625789 width=20) (actual time=7823.658..15864.687 rows=14640000 loops=5)
Recheck Cond: (o_orderdate < '1995-03-20'::date)
Heap Blocks: exact=552472
-> Bitmap Index Scan on idx_o_orderdate (cost=0.00..113704.48 rows=14625789 width=0) (actual time=7365.568..7365.568 rows=14640000 loops=5)
Index Cond: (o_orderdate < '1995-03-20'::date)
-> Hash (cost=19756.15..19756.15 rows=598159 width=4) (actual time=1383.474..1383.474 rows=598321 loops=5)
Buckets: 1048576 Batches: 1 Memory Usage: 29227kB
-> Bitmap Heap Scan on customer (cost=4866.56..19756.15 rows=598159 width=4) (actual time=330.633..1024.531 rows=598321 loops=5)
Recheck Cond: (c_mktsegment = 'FURNITURE'::bpchar)
Heap Blocks: exact=74114
-> Bitmap Index Scan on idx_c_mktsegment (cost=0.00..4717.02 rows=598159 width=0) (actual time=296.352..296.352 rows=598321 loops=5)
Index Cond: (c_mktsegment = 'FURNITURE'::bpchar)
Planning time: 3.840 ms
Execution time: 70798.751 ms
(35 rows)