SET QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=541790.95..541790.97 rows=6 width=236) (actual time=25244.764..25244.766 rows=4 loops=1) Sort Key: lineitem.l_returnflag, lineitem.l_linestatus Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=541790.71..541790.87 rows=6 width=236) (actual time=25244.723..25244.737 rows=4 loops=1) Group Key: lineitem.l_returnflag, lineitem.l_linestatus Batches: 1 Memory Usage: 32kB -> Append (cost=100.00..335245.49 rows=5901292 width=25) (actual time=2.342..12993.013 rows=5900882 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..152623.94 rows=2945467 width=25) (actual time=1.322..6075.492 rows=2945324 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..153115.09 rows=2955825 width=25) (actual time=1.194..6083.693 rows=2955558 loops=1) Planning Time: 2.485 ms Execution Time: 25250.010 ms (11 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=130984.64..130984.64 rows=3 width=193) (actual time=2538.957..2538.985 rows=100 loops=1) -> Sort (cost=130984.64..130984.64 rows=3 width=193) (actual time=2538.956..2538.978 rows=100 loops=1) Sort Key: supplier.s_acctbal DESC, nation.n_name, supplier.s_name, part.p_partkey Sort Method: top-N heapsort Memory: 70kB -> Hash Join (cost=122359.58..130984.61 rows=3 width=193) (actual time=2423.360..2538.383 rows=492 loops=1) Hash Cond: ((partsupp_3.ps_partkey = part.p_partkey) AND ((min(partsupp_3.ps_supplycost)) = partsupp.ps_supplycost)) -> HashAggregate (cost=64908.83..70033.83 rows=200000 width=36) (actual time=1220.950..1312.846 rows=116165 loops=1) Group Key: partsupp_3.ps_partkey Planned Partitions: 16 Batches: 17 Memory Usage: 4241kB Disk Usage: 6952kB -> Hash Join (cost=1405.83..53158.83 rows=320000 width=10) (actual time=13.600..1147.781 rows=156400 loops=1) Hash Cond: (partsupp_3.ps_suppkey = supplier_3.s_suppkey) -> Append (cost=100.00..45653.00 rows=800000 width=14) (actual time=0.557..1015.506 rows=800000 loops=1) -> Async Foreign Scan on partsupp_1 partsupp_4 (cost=100.00..20745.92 rows=398464 width=14) (actual time=0.189..404.946 rows=398464 loops=1) -> Async Foreign Scan on partsupp_2 partsupp_5 (cost=100.00..20907.08 rows=401536 width=14) (actual time=0.217..405.543 rows=401536 loops=1) -> Hash (cost=1255.83..1255.83 rows=4000 width=4) (actual time=13.016..13.022 rows=1955 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 101kB -> Hash Join (cost=505.33..1255.83 rows=4000 width=4) (actual time=1.034..12.635 rows=1955 loops=1) Hash Cond: (supplier_3.s_nationkey = nation_3.n_nationkey) -> Append (cost=100.00..773.00 rows=10000 width=8) (actual time=0.344..10.612 rows=10000 loops=1) -> Async Foreign Scan on supplier_1 supplier_4 (cost=100.00..362.57 rows=5019 width=8) (actual time=0.214..3.460 rows=5019 loops=1) -> Async Foreign Scan on supplier_2 supplier_5 (cost=100.00..360.43 rows=4981 width=8) (actual time=0.128..3.265 rows=4981 loops=1) -> Hash (cost=405.21..405.21 rows=10 width=4) (actual time=0.680..0.684 rows=5 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Hash Join (cost=302.14..405.21 rows=10 width=4) (actual time=0.664..0.681 rows=5 loops=1) Hash Cond: (nation_3.n_regionkey = region_3.r_regionkey) -> Append (cost=100.00..202.88 rows=25 width=8) (actual time=0.282..0.293 rows=25 loops=1) -> Async Foreign Scan on nation_1 nation_4 (cost=100.00..101.30 rows=10 width=8) (actual time=0.193..0.196 rows=10 loops=1) -> Async Foreign Scan on nation_2 nation_5 (cost=100.00..101.45 rows=15 width=8) (actual time=0.086..0.089 rows=15 loops=1) -> Hash (cost=202.11..202.11 rows=2 width=4) (actual time=0.373..0.375 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Append (cost=100.00..202.11 rows=2 width=4) (actual time=0.370..0.372 rows=1 loops=1) -> Async Foreign Scan on region_1 region_4 (cost=100.00..101.06 rows=1 width=4) (actual time=0.166..0.167 rows=1 loops=1) -> Async Foreign Scan on region_2 region_5 (cost=100.00..101.05 rows=1 width=4) (actual time=0.116..0.116 rows=0 loops=1) -> Hash (cost=57431.31..57431.31 rows=1296 width=203) (actual time=1202.205..1202.215 rows=670 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 172kB -> Hash Join (cost=8731.70..57431.31 rows=1296 width=203) (actual time=98.251..1201.510 rows=670 loops=1) Hash Cond: (nation.n_regionkey = region.r_regionkey) -> Hash Join (cost=8529.56..57204.06 rows=3240 width=207) (actual time=96.874..1199.782 rows=3292 loops=1) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) -> Hash Join (cost=8326.38..56956.33 rows=3240 width=181) (actual time=96.217..1197.800 rows=3292 loops=1) Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey) -> Hash Join (cost=7428.38..56013.78 rows=3240 width=44) (actual time=74.400..1173.755 rows=3292 loops=1) Hash Cond: (partsupp.ps_partkey = part.p_partkey) -> Append (cost=100.00..45653.00 rows=800000 width=14) (actual time=0.869..1011.801 rows=800000 loops=1) -> Async Foreign Scan on partsupp_1 (cost=100.00..20745.92 rows=398464 width=14) (actual time=0.453..434.194 rows=398464 loops=1) -> Async Foreign Scan on partsupp_2 (cost=100.00..20907.08 rows=401536 width=14) (actual time=0.413..413.732 rows=401536 loops=1) -> Hash (cost=7318.25..7318.25 rows=810 width=30) (actual time=72.484..72.485 rows=823 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 58kB -> Append (cost=100.00..7318.25 rows=810 width=30) (actual time=9.539..72.134 rows=823 loops=1) -> Async Foreign Scan on part_1 (cost=100.00..3643.28 rows=402 width=30) (actual time=0.648..1.192 rows=407 loops=1) -> Async Foreign Scan on part_2 (cost=100.00..3670.92 rows=408 width=30) (actual time=0.895..1.389 rows=416 loops=1) -> Hash (cost=773.00..773.00 rows=10000 width=145) (actual time=21.777..21.778 rows=10000 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 1887kB -> Append (cost=100.00..773.00 rows=10000 width=145) (actual time=1.536..18.268 rows=10000 loops=1) -> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=145) (actual time=0.937..8.694 rows=5019 loops=1) -> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=144) (actual time=0.728..8.309 rows=4981 loops=1) -> Hash (cost=202.88..202.88 rows=25 width=34) (actual time=0.648..0.649 rows=25 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Append (cost=100.00..202.88 rows=25 width=34) (actual time=0.554..0.641 rows=25 loops=1) -> Async Foreign Scan on nation_1 (cost=100.00..101.30 rows=10 width=34) (actual time=0.334..0.336 rows=10 loops=1) -> Async Foreign Scan on nation_2 (cost=100.00..101.45 rows=15 width=34) (actual time=0.230..0.233 rows=15 loops=1) -> Hash (cost=202.11..202.11 rows=2 width=4) (actual time=1.085..1.086 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Append (cost=100.00..202.11 rows=2 width=4) (actual time=1.017..1.082 rows=1 loops=1) -> Async Foreign Scan on region_1 (cost=100.00..101.06 rows=1 width=4) (actual time=0.618..0.618 rows=1 loops=1) -> Async Foreign Scan on region_2 (cost=100.00..101.05 rows=1 width=4) (actual time=0.398..0.398 rows=0 loops=1) Planning Time: 3.578 ms Execution Time: 2541.580 ms (68 rows) QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=497159.36..497159.38 rows=10 width=44) (actual time=8096.572..8096.580 rows=10 loops=1) Output: lineitem.l_orderkey, (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))), orders.o_orderdate, orders.o_shippriority -> Sort (cost=497159.36..499634.08 rows=989888 width=44) (actual time=8096.569..8096.576 rows=10 loops=1) Output: lineitem.l_orderkey, (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))), orders.o_orderdate, orders.o_shippriority Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC, orders.o_orderdate Sort Method: top-N heapsort Memory: 26kB -> HashAggregate (cost=451794.38..475768.23 rows=989888 width=44) (actual time=8069.424..8092.794 rows=11426 loops=1) Output: lineitem.l_orderkey, sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))), orders.o_orderdate, orders.o_shippriority Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority Planned Partitions: 128 Batches: 129 Memory Usage: 4305kB Disk Usage: 8136kB -> Hash Join (cost=78641.75..403228.00 rows=989888 width=24) (actual time=1360.433..8030.329 rows=30268 loops=1) Output: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority, lineitem.l_extendedprice, lineitem.l_discount Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Append (cost=100.00..269502.91 rows=3271589 width=16) (actual time=0.925..5389.498 rows=3266925 loops=1) -> Async Foreign Scan on public.lineitem_1 (cost=100.00..126428.18 rows=1635679 width=16) (actual time=0.555..2192.645 rows=1631510 loops=1) Output: lineitem_1.l_orderkey, lineitem_1.l_extendedprice, lineitem_1.l_discount Remote SQL: SELECT l_orderkey, l_extendedprice, l_discount FROM public.lineitem_1 WHERE ((l_shipdate > '1995-03-05'::date)) -> Async Foreign Scan on public.lineitem_2 (cost=100.00..126716.79 rows=1635910 width=16) (actual time=0.366..2220.283 rows=1635415 loops=1) Output: lineitem_2.l_orderkey, lineitem_2.l_extendedprice, lineitem_2.l_discount Remote SQL: SELECT l_orderkey, l_extendedprice, l_discount FROM public.lineitem_2 WHERE ((l_shipdate > '1995-03-05'::date)) -> Hash (cost=74748.68..74748.68 rows=218166 width=12) (actual time=1358.493..1358.497 rows=143880 loops=1) Output: orders.o_orderdate, orders.o_shippriority, orders.o_orderkey Buckets: 131072 Batches: 4 Memory Usage: 2559kB -> Hash Join (cost=6892.09..74748.68 rows=218166 width=12) (actual time=114.062..1318.307 rows=143880 loops=1) Output: orders.o_orderdate, orders.o_shippriority, orders.o_orderkey Hash Cond: (orders.o_custkey = customer.c_custkey) -> Append (cost=100.00..63071.03 rows=721041 width=16) (actual time=0.824..1080.903 rows=721026 loops=1) -> Async Foreign Scan on public.orders_1 (cost=100.00..29691.65 rows=359777 width=16) (actual time=0.552..465.150 rows=359777 loops=1) Output: orders_1.o_orderdate, orders_1.o_shippriority, orders_1.o_custkey, orders_1.o_orderkey Remote SQL: SELECT o_orderkey, o_custkey, o_orderdate, o_shippriority FROM public.orders_1 WHERE ((o_orderdate < '1995-03-05'::date)) -> Async Foreign Scan on public.orders_2 (cost=100.00..29774.17 rows=361264 width=16) (actual time=0.555..463.040 rows=361249 loops=1) Output: orders_2.o_orderdate, orders_2.o_shippriority, orders_2.o_custkey, orders_2.o_orderkey Remote SQL: SELECT o_orderkey, o_custkey, o_orderdate, o_shippriority FROM public.orders_2 WHERE ((o_orderdate < '1995-03-05'::date)) -> Hash (cost=6414.72..6414.72 rows=30189 width=4) (actual time=113.214..113.216 rows=30189 loops=1) Output: customer.c_custkey Buckets: 32768 Batches: 1 Memory Usage: 1318kB -> Append (cost=100.00..6414.72 rows=30189 width=4) (actual time=0.639..104.931 rows=30189 loops=1) -> Async Foreign Scan on public.customer_1 (cost=100.00..3130.05 rows=15058 width=4) (actual time=0.333..12.782 rows=15058 loops=1) Output: customer_1.c_custkey Remote SQL: SELECT c_custkey FROM public.customer_1 WHERE ((c_mktsegment = 'HOUSEHOLD'::bpchar)) -> Async Foreign Scan on public.customer_2 (cost=100.00..3133.73 rows=15131 width=4) (actual time=0.711..13.153 rows=15131 loops=1) Output: customer_2.c_custkey Remote SQL: SELECT c_custkey FROM public.customer_2 WHERE ((c_mktsegment = 'HOUSEHOLD'::bpchar)) Planning Time: 1.536 ms Execution Time: 8099.098 ms (45 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=330646.98..330647.00 rows=5 width=24) (actual time=8999.679..8999.683 rows=5 loops=1) Sort Key: orders.o_orderpriority Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=330646.87..330646.92 rows=5 width=24) (actual time=8999.666..8999.671 rows=5 loops=1) Group Key: orders.o_orderpriority Batches: 1 Memory Usage: 24kB -> Hash Semi Join (cost=270643.38..330363.92 rows=56590 width=16) (actual time=8193.905..8979.742 rows=51942 loops=1) Hash Cond: (orders.o_orderkey = lineitem.l_orderkey) -> Append (cost=100.00..50209.75 rows=56590 width=20) (actual time=1.731..214.789 rows=56592 loops=1) -> Async Foreign Scan on orders_1 (cost=100.00..24934.46 rows=28286 width=20) (actual time=0.562..32.222 rows=28286 loops=1) -> Async Foreign Scan on orders_2 (cost=100.00..24992.35 rows=28304 width=20) (actual time=0.534..29.683 rows=28306 loops=1) -> Hash (cost=237723.31..237723.31 rows=2000405 width=4) (actual time=8191.939..8191.941 rows=3793296 loops=1) Buckets: 131072 (originally 131072) Batches: 64 (originally 32) Memory Usage: 3098kB -> Append (cost=100.00..237723.31 rows=2000405 width=4) (actual time=0.499..7352.728 rows=3793296 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..113688.26 rows=998683 width=4) (actual time=0.275..1242.253 rows=1894002 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..114033.03 rows=1001722 width=4) (actual time=1.898..1312.056 rows=1899294 loops=1) Planning Time: 0.787 ms Execution Time: 9000.667 ms (18 rows) QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=498693.90..498693.97 rows=25 width=58) (actual time=12498.406..12498.420 rows=5 loops=1) Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC Sort Method: quicksort Memory: 25kB -> GroupAggregate (cost=498473.70..498693.32 rows=25 width=58) (actual time=12494.016..12498.406 rows=5 loops=1) Group Key: nation.n_name -> Sort (cost=498473.70..498517.56 rows=17545 width=38) (actual time=12492.744..12493.191 rows=6991 loops=1) Sort Key: nation.n_name Sort Method: quicksort Memory: 793kB -> Hash Join (cost=73200.38..497236.88 rows=17545 width=38) (actual time=597.255..12487.603 rows=6991 loops=1) Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey)) -> Hash Join (cost=72277.38..492848.72 rows=438628 width=50) (actual time=574.241..12438.067 rows=181249 loops=1) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Append (cost=100.00..322740.53 rows=6001215 width=20) (actual time=0.525..9967.582 rows=6001215 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..146145.44 rows=2996048 width=20) (actual time=0.346..4327.560 rows=2996048 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..146589.01 rows=3005167 width=20) (actual time=0.146..4328.614 rows=3005167 loops=1) -> Hash (cost=70326.43..70326.43 rows=91116 width=38) (actual time=572.345..572.353 rows=45450 loops=1) Buckets: 65536 Batches: 2 Memory Usage: 2094kB -> Hash Join (cost=11922.28..70326.43 rows=91116 width=38) (actual time=180.940..559.798 rows=45450 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Append (cost=100.00..54489.78 rows=227791 width=8) (actual time=0.571..310.404 rows=227783 loops=1) -> Async Foreign Scan on orders_1 (cost=100.00..26646.11 rows=113869 width=8) (actual time=0.264..80.716 rows=113869 loops=1) -> Async Foreign Scan on orders_2 (cost=100.00..26704.71 rows=113922 width=8) (actual time=0.308..80.121 rows=113914 loops=1) -> Hash (cost=10603.28..10603.28 rows=60000 width=38) (actual time=180.257..180.263 rows=29764 loops=1) Buckets: 65536 Batches: 2 Memory Usage: 1616kB -> Hash Join (cost=505.78..10603.28 rows=60000 width=38) (actual time=1.479..170.724 rows=29764 loops=1) Hash Cond: (customer.c_nationkey = nation.n_nationkey) -> Append (cost=100.00..9035.00 rows=150000 width=8) (actual time=0.261..145.165 rows=150000 loops=1) -> Async Foreign Scan on customer_1 (cost=100.00..4140.53 rows=74951 width=8) (actual time=0.145..59.515 rows=74951 loops=1) -> Async Foreign Scan on customer_2 (cost=100.00..4144.47 rows=75049 width=8) (actual time=0.159..55.517 rows=75049 loops=1) -> Hash (cost=405.66..405.66 rows=10 width=30) (actual time=1.214..1.218 rows=5 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Hash Join (cost=302.14..405.66 rows=10 width=30) (actual time=0.834..1.215 rows=5 loops=1) Hash Cond: (nation.n_regionkey = region.r_regionkey) -> Append (cost=100.00..203.32 rows=25 width=34) (actual time=0.394..0.767 rows=25 loops=1) -> Async Foreign Scan on nation_1 (cost=100.00..101.52 rows=10 width=34) (actual time=0.307..0.310 rows=10 loops=1) -> Async Foreign Scan on nation_2 (cost=100.00..101.68 rows=15 width=34) (actual time=0.273..0.276 rows=15 loops=1) -> Hash (cost=202.11..202.11 rows=2 width=4) (actual time=0.434..0.436 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Append (cost=100.00..202.11 rows=2 width=4) (actual time=0.155..0.434 rows=1 loops=1) -> Async Foreign Scan on region_1 (cost=100.00..101.06 rows=1 width=4) (actual time=0.102..0.102 rows=1 loops=1) -> Async Foreign Scan on region_2 (cost=100.00..101.05 rows=1 width=4) (actual time=0.229..0.229 rows=0 loops=1) -> Hash (cost=773.00..773.00 rows=10000 width=8) (actual time=15.796..15.797 rows=10000 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 519kB -> Append (cost=100.00..773.00 rows=10000 width=8) (actual time=0.580..12.859 rows=10000 loops=1) -> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=8) (actual time=0.377..5.767 rows=5019 loops=1) -> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=8) (actual time=0.286..3.970 rows=4981 loops=1) Planning Time: 3.607 ms Execution Time: 12500.664 ms (48 rows) QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=251311.72..251311.73 rows=1 width=32) (actual time=1028.392..1028.394 rows=1 loops=1) -> Append (cost=100.00..250713.99 rows=119546 width=12) (actual time=4.906..951.524 rows=119466 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..124871.34 rows=59813 width=12) (actual time=0.967..107.461 rows=59780 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..125244.92 rows=59733 width=12) (actual time=0.949..107.852 rows=59686 loops=1) Planning Time: 0.477 ms Execution Time: 1028.805 ms (6 rows) QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=371693.19..372458.21 rows=23539 width=116) (actual time=6308.687..6313.391 rows=4 loops=1) Group Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate)) -> Sort (cost=371693.19..371752.04 rows=23539 width=96) (actual time=6307.089..6307.468 rows=5996 loops=1) Sort Key: n1.n_name, n2.n_name, (EXTRACT(year FROM lineitem.l_shipdate)) Sort Method: quicksort Memory: 1083kB -> Hash Join (cost=102950.05..369983.94 rows=23539 width=96) (actual time=1874.259..6300.937 rows=5996 loops=1) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) Join Filter: (((n1.n_name = 'FRANCE'::bpchar) AND (n2.n_name = 'ROMANIA'::bpchar)) OR ((n1.n_name = 'ROMANIA'::bpchar) AND (n2.n_name = 'FRANCE'::bpchar))) Rows Removed by Join Filter: 5931 -> Hash Join (cost=1149.03..259300.31 rows=292771 width=46) (actual time=16.039..4345.394 rows=145583 loops=1) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) -> Append (cost=100.00..248461.75 rows=1829821 width=24) (actual time=0.916..4091.607 rows=1828450 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..119485.96 rows=914062 width=24) (actual time=0.576..1838.677 rows=912708 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..119826.68 rows=915759 width=24) (actual time=0.336..1801.642 rows=915742 loops=1) -> Hash (cost=1029.03..1029.03 rows=1600 width=30) (actual time=15.110..15.114 rows=800 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 65kB -> Hash Join (cost=302.53..1029.03 rows=1600 width=30) (actual time=1.362..14.927 rows=800 loops=1) Hash Cond: (supplier.s_nationkey = n1.n_nationkey) -> Append (cost=100.00..773.00 rows=10000 width=8) (actual time=0.403..12.829 rows=10000 loops=1) -> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=8) (actual time=0.161..3.401 rows=5019 loops=1) -> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=8) (actual time=0.192..3.414 rows=4981 loops=1) -> Hash (cost=202.47..202.47 rows=4 width=30) (actual time=0.947..0.949 rows=2 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Append (cost=100.00..202.47 rows=4 width=30) (actual time=0.942..0.944 rows=2 loops=1) -> Async Foreign Scan on nation_1 n1_1 (cost=100.00..101.19 rows=2 width=30) (actual time=0.262..0.262 rows=0 loops=1) -> Async Foreign Scan on nation_2 n1_2 (cost=100.00..101.26 rows=2 width=30) (actual time=0.458..0.459 rows=2 loops=1) -> Hash (cost=97160.02..97160.02 rows=240000 width=30) (actual time=1852.199..1852.204 rows=122612 loops=1) Buckets: 65536 Batches: 8 Memory Usage: 1437kB -> Hash Join (cost=10440.02..97160.02 rows=240000 width=30) (actual time=164.806..1815.875 rows=122612 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Append (cost=100.00..78795.00 rows=1500000 width=8) (actual time=0.269..1449.405 rows=1500000 loops=1) -> Async Foreign Scan on orders_1 (cost=100.00..35604.47 rows=749049 width=8) (actual time=0.146..565.476 rows=749049 loops=1) -> Async Foreign Scan on orders_2 (cost=100.00..35690.53 rows=750951 width=8) (actual time=0.093..547.274 rows=750951 loops=1) -> Hash (cost=10040.02..10040.02 rows=24000 width=30) (actual time=164.507..164.510 rows=12200 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 995kB -> Hash Join (cost=302.53..10040.02 rows=24000 width=30) (actual time=0.625..161.416 rows=12200 loops=1) Hash Cond: (customer.c_nationkey = n2.n_nationkey) -> Append (cost=100.00..9035.00 rows=150000 width=8) (actual time=0.237..143.225 rows=150000 loops=1) -> Async Foreign Scan on customer_1 (cost=100.00..4140.53 rows=74951 width=8) (actual time=0.123..51.478 rows=74951 loops=1) -> Async Foreign Scan on customer_2 (cost=100.00..4144.47 rows=75049 width=8) (actual time=0.126..51.130 rows=75049 loops=1) -> Hash (cost=202.47..202.47 rows=4 width=30) (actual time=0.380..0.382 rows=2 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Append (cost=100.00..202.47 rows=4 width=30) (actual time=0.377..0.379 rows=2 loops=1) -> Async Foreign Scan on nation_1 n2_1 (cost=100.00..101.19 rows=2 width=30) (actual time=0.118..0.118 rows=0 loops=1) -> Async Foreign Scan on nation_2 n2_2 (cost=100.00..101.26 rows=2 width=30) (actual time=0.163..0.164 rows=2 loops=1) Planning Time: 2.529 ms Execution Time: 6314.653 ms (47 rows) QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=433678.24..433860.26 rows=2406 width=64) (actual time=12489.940..12491.218 rows=2 loops=1) Group Key: (EXTRACT(year FROM orders.o_orderdate)) -> Sort (cost=433678.24..433693.12 rows=5951 width=70) (actual time=12488.592..12488.765 rows=2599 loops=1) Sort Key: (EXTRACT(year FROM orders.o_orderdate)) Sort Method: quicksort Memory: 320kB -> Hash Join (cost=86173.27..433305.15 rows=5951 width=70) (actual time=986.484..12487.115 rows=2599 loops=1) Hash Cond: (supplier.s_nationkey = n2.n_nationkey) -> Hash Join (cost=85970.08..433005.25 rows=5951 width=20) (actual time=986.113..12485.007 rows=2599 loops=1) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) -> Hash Join (cost=85072.08..432025.43 rows=5951 width=20) (actual time=973.354..12470.924 rows=2599 loops=1) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Hash Join (cost=6948.55..352499.27 rows=40564 width=20) (actual time=48.158..11526.713 rows=40752 loops=1) Hash Cond: (lineitem.l_partkey = part.p_partkey) -> Append (cost=100.00..322740.53 rows=6001215 width=24) (actual time=0.545..10824.585 rows=6001215 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..146145.44 rows=2996048 width=24) (actual time=0.298..4673.747 rows=2996048 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..146589.01 rows=3005167 width=24) (actual time=0.180..4657.524 rows=3005167 loops=1) -> Hash (cost=6831.70..6831.70 rows=1348 width=4) (actual time=46.655..46.656 rows=1348 loops=1) Buckets: 2048 Batches: 1 Memory Usage: 64kB -> Append (cost=100.00..6831.70 rows=1348 width=4) (actual time=3.699..46.228 rows=1348 loops=1) -> Async Foreign Scan on part_1 (cost=100.00..3399.64 rows=672 width=4) (actual time=0.717..1.290 rows=672 loops=1) -> Async Foreign Scan on part_2 (cost=100.00..3425.32 rows=676 width=4) (actual time=1.099..1.816 rows=676 loops=1) -> Hash (cost=75122.34..75122.34 rows=182895 width=8) (actual time=912.628..912.636 rows=92715 loops=1) Buckets: 131072 Batches: 4 Memory Usage: 1923kB -> Hash Join (cost=11452.83..75122.34 rows=182895 width=8) (actual time=161.443..889.333 rows=92715 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Append (cost=100.00..60225.92 rows=457237 width=12) (actual time=1.023..657.041 rows=457263 loops=1) -> Async Foreign Scan on orders_1 (cost=100.00..28933.29 rows=228228 width=12) (actual time=0.624..266.960 rows=228228 loops=1) -> Async Foreign Scan on orders_2 (cost=100.00..29006.44 rows=229009 width=12) (actual time=0.216..267.366 rows=229035 loops=1) -> Hash (cost=10602.83..10602.83 rows=60000 width=4) (actual time=160.369..160.374 rows=30197 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 1574kB -> Hash Join (cost=505.33..10602.83 rows=60000 width=4) (actual time=1.584..154.393 rows=30197 loops=1) Hash Cond: (customer.c_nationkey = n1.n_nationkey) -> Append (cost=100.00..9035.00 rows=150000 width=8) (actual time=0.469..131.462 rows=150000 loops=1) -> Async Foreign Scan on customer_1 (cost=100.00..4140.53 rows=74951 width=8) (actual time=0.248..52.895 rows=74951 loops=1) -> Async Foreign Scan on customer_2 (cost=100.00..4144.47 rows=75049 width=8) (actual time=0.171..52.690 rows=75049 loops=1) -> Hash (cost=405.21..405.21 rows=10 width=4) (actual time=1.098..1.101 rows=5 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Hash Join (cost=302.14..405.21 rows=10 width=4) (actual time=0.911..1.098 rows=5 loops=1) Hash Cond: (n1.n_regionkey = region.r_regionkey) -> Append (cost=100.00..202.88 rows=25 width=8) (actual time=0.267..0.451 rows=25 loops=1) -> Async Foreign Scan on nation_1 n1_1 (cost=100.00..101.30 rows=10 width=8) (actual time=0.163..0.167 rows=10 loops=1) -> Async Foreign Scan on nation_2 n1_2 (cost=100.00..101.45 rows=15 width=8) (actual time=0.106..0.111 rows=15 loops=1) -> Hash (cost=202.11..202.11 rows=2 width=4) (actual time=0.627..0.628 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Append (cost=100.00..202.11 rows=2 width=4) (actual time=0.624..0.625 rows=1 loops=1) -> Async Foreign Scan on region_1 (cost=100.00..101.06 rows=1 width=4) (actual time=0.182..0.182 rows=0 loops=1) -> Async Foreign Scan on region_2 (cost=100.00..101.05 rows=1 width=4) (actual time=0.291..0.292 rows=1 loops=1) -> Hash (cost=773.00..773.00 rows=10000 width=8) (actual time=12.746..12.748 rows=10000 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 519kB -> Append (cost=100.00..773.00 rows=10000 width=8) (actual time=0.326..10.876 rows=10000 loops=1) -> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=8) (actual time=0.164..3.426 rows=5019 loops=1) -> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=8) (actual time=0.125..3.330 rows=4981 loops=1) -> Hash (cost=202.88..202.88 rows=25 width=30) (actual time=0.361..0.362 rows=25 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Append (cost=100.00..202.88 rows=25 width=30) (actual time=0.298..0.355 rows=25 loops=1) -> Async Foreign Scan on nation_1 n2_1 (cost=100.00..101.30 rows=10 width=30) (actual time=0.189..0.192 rows=10 loops=1) -> Async Foreign Scan on nation_2 n2_2 (cost=100.00..101.45 rows=15 width=30) (actual time=0.115..0.118 rows=15 loops=1) Planning Time: 2.054 ms Execution Time: 12492.715 ms (59 rows) QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=510075.80..510079.93 rows=118 width=90) (actual time=17731.007..18339.274 rows=175 loops=1) Group Key: nation.n_name, (EXTRACT(year FROM orders.o_orderdate)) -> Sort (cost=510075.80..510076.10 rows=118 width=81) (actual time=17728.562..17922.589 rows=322637 loops=1) Sort Key: nation.n_name, (EXTRACT(year FROM orders.o_orderdate)) DESC Sort Method: external merge Disk: 21016kB -> Hash Join (cost=425747.02..510071.74 rows=118 width=81) (actual time=14626.225..17345.337 rows=322637 loops=1) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) -> Hash Join (cost=425543.83..509866.63 rows=118 width=31) (actual time=14625.809..17182.229 rows=322637 loops=1) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) -> Hash Join (cost=424645.83..508967.01 rows=118 width=35) (actual time=14609.559..17064.342 rows=322637 loops=1) Hash Cond: (orders.o_orderkey = lineitem.l_orderkey) -> Append (cost=100.00..78795.00 rows=1500000 width=8) (actual time=0.419..1798.362 rows=1500000 loops=1) -> Async Foreign Scan on orders_1 (cost=100.00..35604.47 rows=749049 width=8) (actual time=0.263..781.063 rows=749049 loops=1) -> Async Foreign Scan on orders_2 (cost=100.00..35690.53 rows=750951 width=8) (actual time=0.094..781.654 rows=750951 loops=1) -> Hash (cost=424544.36..424544.36 rows=118 width=35) (actual time=14609.078..14609.085 rows=322637 loops=1) Buckets: 65536 (originally 1024) Batches: 8 (originally 1) Memory Usage: 3585kB -> Hash Join (cost=56893.55..424544.36 rows=118 width=35) (actual time=1148.708..14479.650 rows=322637 loops=1) Hash Cond: ((lineitem.l_suppkey = partsupp.ps_suppkey) AND (lineitem.l_partkey = partsupp.ps_partkey)) -> Append (cost=100.00..322740.53 rows=6001215 width=29) (actual time=0.617..12315.100 rows=6001215 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..146145.44 rows=2996048 width=29) (actual time=0.424..5488.474 rows=2996048 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..146589.01 rows=3005167 width=29) (actual time=0.509..5536.754 rows=3005167 loops=1) -> Hash (cost=56207.71..56207.71 rows=39056 width=18) (actual time=1148.040..1148.044 rows=43100 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 2701kB -> Hash Join (cost=7264.15..56207.71 rows=39056 width=18) (actual time=92.726..1134.963 rows=43100 loops=1) Hash Cond: (partsupp.ps_partkey = part.p_partkey) -> Append (cost=100.00..45653.00 rows=800000 width=14) (actual time=0.321..947.782 rows=800000 loops=1) -> Async Foreign Scan on partsupp_1 (cost=100.00..20745.92 rows=398464 width=14) (actual time=0.170..402.864 rows=398464 loops=1) -> Async Foreign Scan on partsupp_2 (cost=100.00..20907.08 rows=401536 width=14) (actual time=0.251..405.320 rows=401536 loops=1) -> Hash (cost=7042.10..7042.10 rows=9764 width=4) (actual time=92.345..92.347 rows=10775 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 507kB -> Append (cost=100.00..7042.10 rows=9764 width=4) (actual time=1.690..89.275 rows=10775 loops=1) -> Async Foreign Scan on part_1 (cost=100.00..3482.66 rows=4823 width=4) (actual time=0.715..5.719 rows=5374 loops=1) -> Async Foreign Scan on part_2 (cost=100.00..3510.62 rows=4941 width=4) (actual time=1.641..6.196 rows=5401 loops=1) -> Hash (cost=773.00..773.00 rows=10000 width=8) (actual time=16.239..16.241 rows=10000 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 519kB -> Append (cost=100.00..773.00 rows=10000 width=8) (actual time=0.280..13.510 rows=10000 loops=1) -> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=8) (actual time=0.145..4.787 rows=5019 loops=1) -> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=8) (actual time=0.151..4.680 rows=4981 loops=1) -> Hash (cost=202.88..202.88 rows=25 width=30) (actual time=0.405..0.406 rows=25 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Append (cost=100.00..202.88 rows=25 width=30) (actual time=0.317..0.399 rows=25 loops=1) -> Async Foreign Scan on nation_1 (cost=100.00..101.30 rows=10 width=30) (actual time=0.175..0.177 rows=10 loops=1) -> Async Foreign Scan on nation_2 (cost=100.00..101.45 rows=15 width=30) (actual time=0.149..0.152 rows=15 loops=1) Planning Time: 2.903 ms Execution Time: 18343.507 ms (45 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=365043.50..365043.55 rows=20 width=202) (actual time=4131.852..4131.863 rows=20 loops=1) -> Sort (cost=365043.50..365734.17 rows=276267 width=202) (actual time=4131.851..4131.860 rows=20 loops=1) Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC Sort Method: top-N heapsort Memory: 35kB -> HashAggregate (cost=340209.62..357692.14 rows=276267 width=202) (actual time=3950.770..4114.517 rows=38263 loops=1) Group Key: customer.c_custkey, customer.c_name, customer.c_acctbal, customer.c_phone, nation.n_name, customer.c_address, customer.c_comment Planned Partitions: 64 Batches: 65 Memory Usage: 4313kB Disk Usage: 28504kB -> Hash Join (cost=67885.77..310942.58 rows=276267 width=182) (actual time=816.952..3759.652 rows=114665 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Hash Join (cost=51047.09..283944.22 rows=276267 width=16) (actual time=437.957..3265.990 rows=114665 loops=1) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Append (cost=100.00..224688.21 rows=1479001 width=16) (actual time=0.873..2630.635 rows=1478870 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..108469.62 rows=737751 width=16) (actual time=0.352..1009.803 rows=738327 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..108823.59 rows=741250 width=16) (actual time=0.199..989.762 rows=740543 loops=1) -> Hash (cost=50229.72..50229.72 rows=57389 width=8) (actual time=436.965..436.967 rows=57384 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 2754kB -> Append (cost=100.00..50229.72 rows=57389 width=8) (actual time=1.491..419.850 rows=57384 loops=1) -> Async Foreign Scan on orders_1 (cost=100.00..24940.92 rows=28609 width=8) (actual time=0.342..30.189 rows=28609 loops=1) -> Async Foreign Scan on orders_2 (cost=100.00..25001.86 rows=28780 width=8) (actual time=0.533..31.017 rows=28775 loops=1) -> Hash (cost=11300.69..11300.69 rows=150000 width=170) (actual time=378.245..378.249 rows=150000 loops=1) Buckets: 32768 Batches: 16 Memory Usage: 2147kB -> Hash Join (cost=303.19..11300.69 rows=150000 width=170) (actual time=1.959..314.514 rows=150000 loops=1) Hash Cond: (customer.c_nationkey = nation.n_nationkey) -> Append (cost=100.00..9035.00 rows=150000 width=148) (actual time=1.337..265.914 rows=150000 loops=1) -> Async Foreign Scan on customer_1 (cost=100.00..4140.53 rows=74951 width=148) (actual time=0.738..122.404 rows=74951 loops=1) -> Async Foreign Scan on customer_2 (cost=100.00..4144.47 rows=75049 width=148) (actual time=0.596..122.293 rows=75049 loops=1) -> Hash (cost=202.88..202.88 rows=25 width=30) (actual time=0.617..0.619 rows=25 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Append (cost=100.00..202.88 rows=25 width=30) (actual time=0.258..0.612 rows=25 loops=1) -> Async Foreign Scan on nation_1 (cost=100.00..101.30 rows=10 width=30) (actual time=0.167..0.169 rows=10 loops=1) -> Async Foreign Scan on nation_2 (cost=100.00..101.45 rows=15 width=30) (actual time=0.261..0.264 rows=15 loops=1) Planning Time: 1.581 ms Execution Time: 4135.998 ms (33 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=106916.72..106970.05 rows=21333 width=36) (actual time=2310.374..2310.431 rows=919 loops=1) Sort Key: (sum((partsupp.ps_supplycost * (partsupp.ps_availqty)::numeric))) DESC Sort Method: quicksort Memory: 75kB InitPlan 1 (returns $0) -> Aggregate (cost=50803.89..50803.90 rows=1 width=32) (actual time=1083.909..1083.914 rows=1 loops=1) -> Hash Join (cost=1130.89..50323.89 rows=64000 width=10) (actual time=12.670..1066.241 rows=32080 loops=1) Hash Cond: (partsupp_3.ps_suppkey = supplier_3.s_suppkey) -> Append (cost=100.00..45653.00 rows=800000 width=14) (actual time=0.564..963.091 rows=800000 loops=1) -> Async Foreign Scan on partsupp_1 partsupp_4 (cost=100.00..20745.92 rows=398464 width=14) (actual time=0.182..413.416 rows=398464 loops=1) -> Async Foreign Scan on partsupp_2 partsupp_5 (cost=100.00..20907.08 rows=401536 width=14) (actual time=0.225..405.205 rows=401536 loops=1) -> Hash (cost=1020.89..1020.89 rows=800 width=4) (actual time=12.086..12.090 rows=401 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 23kB -> Hash Join (cost=302.39..1020.89 rows=800 width=4) (actual time=0.706..12.005 rows=401 loops=1) Hash Cond: (supplier_3.s_nationkey = nation_3.n_nationkey) -> Append (cost=100.00..773.00 rows=10000 width=8) (actual time=0.321..10.580 rows=10000 loops=1) -> Async Foreign Scan on supplier_1 supplier_4 (cost=100.00..362.57 rows=5019 width=8) (actual time=0.188..3.336 rows=5019 loops=1) -> Async Foreign Scan on supplier_2 supplier_5 (cost=100.00..360.43 rows=4981 width=8) (actual time=0.130..3.300 rows=4981 loops=1) -> Hash (cost=202.36..202.36 rows=2 width=4) (actual time=0.372..0.374 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Append (cost=100.00..202.36 rows=2 width=4) (actual time=0.306..0.371 rows=1 loops=1) -> Async Foreign Scan on nation_1 nation_4 (cost=100.00..101.15 rows=1 width=4) (actual time=0.186..0.186 rows=0 loops=1) -> Async Foreign Scan on nation_2 nation_5 (cost=100.00..101.21 rows=1 width=4) (actual time=0.120..0.120 rows=1 loops=1) -> HashAggregate (cost=52993.89..54578.89 rows=21333 width=36) (actual time=2272.513..2310.043 rows=919 loops=1) Group Key: partsupp.ps_partkey Filter: (sum((partsupp.ps_supplycost * (partsupp.ps_availqty)::numeric)) > $0) Planned Partitions: 8 Batches: 9 Memory Usage: 4305kB Disk Usage: 1448kB Rows Removed by Filter: 29289 -> Hash Join (cost=1130.89..50323.89 rows=64000 width=14) (actual time=14.908..1158.935 rows=32080 loops=1) Hash Cond: (partsupp.ps_suppkey = supplier.s_suppkey) -> Append (cost=100.00..45653.00 rows=800000 width=18) (actual time=0.775..1053.692 rows=800000 loops=1) -> Async Foreign Scan on partsupp_1 (cost=100.00..20745.92 rows=398464 width=18) (actual time=0.469..444.911 rows=398464 loops=1) -> Async Foreign Scan on partsupp_2 (cost=100.00..20907.08 rows=401536 width=18) (actual time=0.298..449.623 rows=401536 loops=1) -> Hash (cost=1020.89..1020.89 rows=800 width=4) (actual time=14.124..14.127 rows=401 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 23kB -> Hash Join (cost=302.39..1020.89 rows=800 width=4) (actual time=1.206..14.043 rows=401 loops=1) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) -> Append (cost=100.00..773.00 rows=10000 width=8) (actual time=0.476..12.289 rows=10000 loops=1) -> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=8) (actual time=0.254..3.522 rows=5019 loops=1) -> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=8) (actual time=0.179..3.423 rows=4981 loops=1) -> Hash (cost=202.36..202.36 rows=2 width=4) (actual time=0.724..0.726 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Append (cost=100.00..202.36 rows=2 width=4) (actual time=0.721..0.723 rows=1 loops=1) -> Async Foreign Scan on nation_1 (cost=100.00..101.15 rows=1 width=4) (actual time=0.253..0.253 rows=0 loops=1) -> Async Foreign Scan on nation_2 (cost=100.00..101.21 rows=1 width=4) (actual time=0.270..0.270 rows=1 loops=1) Planning Time: 0.763 ms Execution Time: 2311.861 ms (46 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=343142.27..343717.68 rows=7 width=27) (actual time=3888.817..3896.078 rows=2 loops=1) Group Key: lineitem.l_shipmode -> Sort (cost=343142.27..343214.19 rows=28767 width=27) (actual time=3881.413..3883.079 rows=31186 loops=1) Sort Key: lineitem.l_shipmode Sort Method: quicksort Memory: 3449kB -> Hash Join (cost=248904.10..341011.77 rows=28767 width=27) (actual time=2183.522..3868.589 rows=31186 loops=1) Hash Cond: (orders.o_orderkey = lineitem.l_orderkey) -> Append (cost=100.00..78795.00 rows=1500000 width=20) (actual time=0.479..1484.239 rows=1500000 loops=1) -> Async Foreign Scan on orders_1 (cost=100.00..35604.47 rows=749049 width=20) (actual time=0.236..585.157 rows=749049 loops=1) -> Async Foreign Scan on orders_2 (cost=100.00..35690.53 rows=750951 width=20) (actual time=0.218..576.020 rows=750951 loops=1) -> Hash (cost=248444.51..248444.51 rows=28767 width=15) (actual time=2182.943..2182.945 rows=31186 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1718kB -> Append (cost=100.00..248444.51 rows=28767 width=15) (actual time=10.230..2170.895 rows=31186 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..123961.50 rows=14321 width=15) (actual time=0.636..25.634 rows=15548 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..124339.18 rows=14446 width=15) (actual time=1.066..25.764 rows=15638 loops=1) Planning Time: 0.695 ms Execution Time: 3897.201 ms (17 rows) QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=192609.76..192610.26 rows=200 width=16) (actual time=3380.250..3380.256 rows=42 loops=1) Sort Key: (count(*)) DESC, (count(orders.o_orderkey)) DESC Sort Method: quicksort Memory: 27kB -> HashAggregate (cost=192600.11..192602.11 rows=200 width=16) (actual time=3380.218..3380.230 rows=42 loops=1) Group Key: count(orders.o_orderkey) Batches: 1 Memory Usage: 40kB -> HashAggregate (cost=177254.83..190350.11 rows=150000 width=12) (actual time=2922.466..3341.806 rows=150000 loops=1) Group Key: customer.c_custkey Planned Partitions: 4 Batches: 21 Memory Usage: 4153kB Disk Usage: 31968kB -> Hash Right Join (cost=11596.00..126235.60 rows=1484196 width=8) (actual time=158.047..2355.235 rows=1533791 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Append (cost=100.00..82149.90 rows=1484196 width=8) (actual time=3.662..1494.543 rows=1483787 loops=1) -> Async Foreign Scan on orders_1 (cost=100.00..37310.31 rows=740710 width=8) (actual time=2.184..546.274 rows=740960 loops=1) -> Async Foreign Scan on orders_2 (cost=100.00..37418.61 rows=743486 width=8) (actual time=1.523..547.115 rows=742827 loops=1) -> Hash (cost=9035.00..9035.00 rows=150000 width=4) (actual time=154.144..154.146 rows=150000 loops=1) Buckets: 131072 Batches: 2 Memory Usage: 3720kB -> Append (cost=100.00..9035.00 rows=150000 width=4) (actual time=0.377..122.678 rows=150000 loops=1) -> Async Foreign Scan on customer_1 (cost=100.00..4140.53 rows=74951 width=4) (actual time=0.222..43.296 rows=74951 loops=1) -> Async Foreign Scan on customer_2 (cost=100.00..4144.47 rows=75049 width=4) (actual time=0.177..42.983 rows=75049 loops=1) Planning Time: 2.754 ms Execution Time: 3384.378 ms (21 rows) QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=224364.96..224364.98 rows=1 width=32) (actual time=1248.208..1248.212 rows=1 loops=1) -> Hash Join (cost=15266.00..223009.53 rows=77453 width=33) (actual time=246.778..1177.942 rows=77819 loops=1) Hash Cond: (lineitem.l_partkey = part.p_partkey) -> Append (cost=100.00..204652.55 rows=77453 width=16) (actual time=5.918..854.963 rows=77819 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..101982.44 rows=38886 width=16) (actual time=0.455..77.278 rows=38927 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..102282.85 rows=38567 width=16) (actual time=0.677..77.336 rows=38892 loops=1) -> Hash (cost=11298.00..11298.00 rows=200000 width=25) (actual time=240.773..240.775 rows=200000 loops=1) Buckets: 65536 Batches: 4 Memory Usage: 3406kB -> Append (cost=100.00..11298.00 rows=200000 width=25) (actual time=0.381..188.983 rows=200000 loops=1) -> Async Foreign Scan on part_1 (cost=100.00..5129.48 rows=99616 width=25) (actual time=0.245..77.463 rows=99616 loops=1) -> Async Foreign Scan on part_2 (cost=100.00..5168.52 rows=100384 width=25) (actual time=0.184..78.740 rows=100384 loops=1) Planning Time: 0.527 ms Execution Time: 1248.789 ms (13 rows) CREATE VIEW QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=421604.90..422318.25 rows=50 width=104) (actual time=2506.593..2506.599 rows=1 loops=1) Merge Cond: (supplier.s_suppkey = revenue0.supplier_no) InitPlan 1 (returns $0) -> Aggregate (cost=210751.48..210751.49 rows=1 width=32) (actual time=1252.201..1252.203 rows=1 loops=1) -> HashAggregate (cost=210501.48..210626.48 rows=10000 width=36) (actual time=1205.858..1250.479 rows=10000 loops=1) Group Key: lineitem_3.l_suppkey Batches: 5 Memory Usage: 4273kB Disk Usage: 1696kB -> Append (cost=100.00..208277.12 rows=222436 width=16) (actual time=1.441..976.819 rows=223616 loops=1) -> Async Foreign Scan on lineitem_1 lineitem_4 (cost=100.00..103413.38 rows=110433 width=16) (actual time=0.532..172.564 rows=111275 loops=1) -> Async Foreign Scan on lineitem_2 lineitem_5 (cost=100.00..103751.57 rows=112003 width=16) (actual time=0.489..174.695 rows=112341 loops=1) -> Merge Append (cost=200.01..887.61 rows=10000 width=72) (actual time=7.482..22.094 rows=5450 loops=1) Sort Key: supplier.s_suppkey -> Foreign Scan on supplier_1 (cost=100.00..395.01 rows=5019 width=72) (actual time=3.017..9.161 rows=2729 loops=1) -> Foreign Scan on supplier_2 (cost=100.00..392.59 rows=4981 width=71) (actual time=4.463..12.318 rows=2722 loops=1) -> Sort (cost=210653.40..210653.52 rows=50 width=36) (actual time=2484.033..2484.035 rows=1 loops=1) Sort Key: revenue0.supplier_no Sort Method: quicksort Memory: 25kB -> Subquery Scan on revenue0 (cost=210501.48..210651.98 rows=50 width=36) (actual time=2483.968..2484.025 rows=1 loops=1) -> HashAggregate (cost=210501.48..210651.48 rows=50 width=36) (actual time=2483.967..2484.023 rows=1 loops=1) Group Key: lineitem.l_suppkey Filter: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))) = $0) Batches: 5 Memory Usage: 4273kB Disk Usage: 1696kB Rows Removed by Filter: 9999 -> Append (cost=100.00..208277.12 rows=222436 width=16) (actual time=1.906..970.218 rows=223616 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..103413.38 rows=110433 width=16) (actual time=0.786..167.972 rows=111275 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..103751.57 rows=112003 width=16) (actual time=0.653..169.821 rows=112341 loops=1) Planning Time: 1.135 ms Execution Time: 2508.563 ms (28 rows) DROP VIEW QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=73362.13..73369.50 rows=2946 width=44) (actual time=1430.034..1430.913 rows=18243 loops=1) Sort Key: (count(DISTINCT partsupp.ps_suppkey)) DESC, part.p_brand, part.p_type, part.p_size Sort Method: quicksort Memory: 2351kB -> GroupAggregate (cost=71689.97..73192.38 rows=2946 width=44) (actual time=1282.151..1402.153 rows=18243 loops=1) Group Key: part.p_brand, part.p_type, part.p_size -> Sort (cost=71689.97..71984.56 rows=117836 width=40) (actual time=1282.129..1332.299 rows=118012 loops=1) Sort Key: part.p_brand, part.p_type, part.p_size Sort Method: external merge Disk: 6024kB -> Hash Join (cost=11050.82..59830.29 rows=117836 width=40) (actual time=89.256..1096.015 rows=118012 loops=1) Hash Cond: (partsupp.ps_partkey = part.p_partkey) -> Append (cost=648.11..46749.22 rows=400000 width=8) (actual time=9.627..884.679 rows=799680 loops=1) -> Async Foreign Scan on partsupp_1 (cost=648.11..22290.19 rows=199232 width=8) (actual time=9.435..354.366 rows=398294 loops=1) Filter: (NOT (hashed SubPlan 1)) Rows Removed by Filter: 170 SubPlan 1 -> Append (cost=100.00..548.10 rows=4 width=4) (actual time=2.624..4.603 rows=4 loops=2) -> Async Foreign Scan on supplier_1 (cost=100.00..274.78 rows=2 width=4) (actual time=0.656..0.657 rows=1 loops=2) -> Async Foreign Scan on supplier_2 (cost=100.00..273.30 rows=2 width=4) (actual time=0.615..0.616 rows=3 loops=2) -> Async Foreign Scan on partsupp_2 (cost=648.11..22459.03 rows=200768 width=8) (actual time=0.183..347.226 rows=401386 loops=1) Filter: (NOT (hashed SubPlan 1)) Rows Removed by Filter: 150 -> Hash (cost=10034.48..10034.48 rows=29459 width=40) (actual time=79.595..79.597 rows=29514 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 2390kB -> Append (cost=100.00..10034.48 rows=29459 width=40) (actual time=1.581..72.414 rows=29514 loops=1) -> Async Foreign Scan on part_1 (cost=100.00..4925.02 rows=14681 width=40) (actual time=0.864..15.128 rows=14658 loops=1) -> Async Foreign Scan on part_2 (cost=100.00..4962.16 rows=14778 width=40) (actual time=0.473..15.048 rows=14856 loops=1) Planning Time: 1.877 ms Execution Time: 1433.416 ms (28 rows) QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=785525.21..785525.22 rows=1 width=32) (actual time=30476.615..30476.623 rows=1 loops=1) -> Hash Join (cost=433697.69..780549.51 rows=1990281 width=8) (actual time=20830.945..30476.400 rows=644 loops=1) Hash Cond: (part.p_partkey = pre.x1) Join Filter: (lineitem.l_quantity < pre.x2) Rows Removed by Join Filter: 5869 -> Hash Join (cost=7405.46..352610.42 rows=5988 width=21) (actual time=32.506..9712.837 rows=6513 loops=1) Hash Cond: (lineitem.l_partkey = part.p_partkey) -> Append (cost=100.00..322740.53 rows=6001215 width=17) (actual time=0.571..9067.430 rows=6001215 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..146145.44 rows=2996048 width=17) (actual time=0.152..3952.077 rows=2996048 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..146589.01 rows=3005167 width=17) (actual time=0.261..3981.652 rows=3005167 loops=1) -> Hash (cost=7302.97..7302.97 rows=199 width=4) (actual time=29.579..29.582 rows=213 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 16kB -> Append (cost=100.00..7302.97 rows=199 width=4) (actual time=22.479..29.492 rows=213 loops=1) -> Async Foreign Scan on part_1 (cost=100.00..3637.28 rows=102 width=4) (actual time=0.467..0.520 rows=104 loops=1) -> Async Foreign Scan on part_2 (cost=100.00..3664.70 rows=97 width=4) (actual time=0.257..0.317 rows=109 loops=1) -> Hash (cost=422240.39..422240.39 rows=199427 width=36) (actual time=20737.488..20737.490 rows=200000 loops=1) Buckets: 65536 Batches: 8 Memory Usage: 1743kB -> Subquery Scan on pre (cost=200.01..422240.39 rows=199427 width=36) (actual time=5155.633..20671.380 rows=200000 loops=1) -> GroupAggregate (cost=200.01..420246.12 rows=199427 width=36) (actual time=5155.631..20648.839 rows=200000 loops=1) Group Key: lineitem_3.l_partkey -> Merge Append (cost=200.01..387248.64 rows=6001215 width=9) (actual time=5155.577..18941.018 rows=6001215 loops=1) Sort Key: lineitem_3.l_partkey -> Foreign Scan on lineitem_1 lineitem_4 (cost=100.00..163370.34 rows=2996048 width=9) (actual time=2579.362..9381.250 rows=2996048 loops=1) -> Foreign Scan on lineitem_2 lineitem_5 (cost=100.00..163866.14 rows=3005167 width=9) (actual time=2576.209..8988.836 rows=3005167 loops=1) Planning Time: 0.542 ms Execution Time: 30487.130 ms (26 rows) QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1238242.26..1238245.26 rows=100 width=71) (actual time=33250.208..33250.264 rows=10 loops=1) -> GroupAggregate (cost=1238242.26..1288117.77 rows=1662517 width=71) (actual time=33250.206..33250.260 rows=10 loops=1) Group Key: orders.o_totalprice, orders.o_orderdate, customer.c_name, customer.c_custkey, orders.o_orderkey -> Sort (cost=1238242.26..1242398.55 rows=1662517 width=44) (actual time=33250.187..33250.200 rows=70 loops=1) Sort Key: orders.o_totalprice DESC, orders.o_orderdate, customer.c_name, customer.c_custkey, orders.o_orderkey Sort Method: quicksort Memory: 31kB -> Hash Join (cost=581063.55..1005092.81 rows=1662517 width=44) (actual time=28361.133..33250.094 rows=70 loops=1) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Append (cost=100.00..322740.53 rows=6001215 width=9) (actual time=0.808..7349.339 rows=6001215 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..146145.44 rows=2996048 width=9) (actual time=0.382..3015.045 rows=2996048 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..146589.01 rows=3005167 width=9) (actual time=0.280..3080.052 rows=3005167 loops=1) -> Hash (cost=572116.24..572116.24 rows=415545 width=43) (actual time=23961.761..23961.767 rows=10 loops=1) Buckets: 65536 Batches: 16 Memory Usage: 513kB -> Hash Join (cost=458816.99..572116.24 rows=415545 width=43) (actual time=21326.975..23961.645 rows=10 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Hash Join (cost=447027.99..548864.50 rows=415545 width=24) (actual time=21147.464..23770.420 rows=10 loops=1) Hash Cond: (orders.o_orderkey = lineitem_3.l_orderkey) -> Append (cost=100.00..78795.00 rows=1500000 width=20) (actual time=0.611..2671.277 rows=1500000 loops=1) -> Async Foreign Scan on orders_1 (cost=100.00..35604.47 rows=749049 width=20) (actual time=0.392..1203.158 rows=749049 loops=1) -> Async Foreign Scan on orders_2 (cost=100.00..35690.53 rows=750951 width=20) (actual time=0.196..1204.969 rows=750951 loops=1) -> Hash (cost=440109.68..440109.68 rows=415545 width=4) (actual time=20490.578..20490.580 rows=10 loops=1) Buckets: 131072 Batches: 8 Memory Usage: 1025kB -> GroupAggregate (cost=200.01..435954.23 rows=415545 width=4) (actual time=7538.737..20490.321 rows=10 loops=1) Group Key: lineitem_3.l_orderkey Filter: (sum(lineitem_3.l_quantity) > '312'::numeric) Rows Removed by Filter: 1499990 -> Merge Append (cost=200.01..387248.64 rows=6001215 width=9) (actual time=4136.763..17797.673 rows=6001215 loops=1) Sort Key: lineitem_3.l_orderkey -> Foreign Scan on lineitem_1 lineitem_4 (cost=100.00..163370.34 rows=2996048 width=9) (actual time=2059.370..8548.500 rows=2996048 loops=1) -> Foreign Scan on lineitem_2 lineitem_5 (cost=100.00..163866.14 rows=3005167 width=9) (actual time=2077.388..8660.137 rows=3005167 loops=1) -> Hash (cost=9035.00..9035.00 rows=150000 width=23) (actual time=179.374..179.375 rows=150000 loops=1) Buckets: 65536 Batches: 4 Memory Usage: 2637kB -> Append (cost=100.00..9035.00 rows=150000 width=23) (actual time=0.499..141.104 rows=150000 loops=1) -> Async Foreign Scan on customer_1 (cost=100.00..4140.53 rows=74951 width=23) (actual time=0.272..55.989 rows=74951 loops=1) -> Async Foreign Scan on customer_2 (cost=100.00..4144.47 rows=75049 width=23) (actual time=0.215..56.286 rows=75049 loops=1) Planning Time: 2.117 ms Execution Time: 33269.123 ms (37 rows) QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=308802.85..308802.86 rows=1 width=32) (actual time=1470.797..1470.802 rows=1 loops=1) -> Hash Join (cost=12916.23..308801.99 rows=115 width=12) (actual time=48.012..1470.525 rows=133 loops=1) Hash Cond: (lineitem.l_partkey = part.p_partkey) Join Filter: (((part.p_brand = 'Brand#41'::bpchar) AND (part.p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND (lineitem.l_quantity >= '5'::numeric) AND (lineitem.l_quantity <= '15'::numeric) AND (part.p_size <= 5)) OR ((part.p_brand = 'Brand#53'::bpchar) AND (part.p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[])) AND (lineitem.l_quantity >= '17'::numeric) AND (lineitem.l_quantity <= '27'::numeric) AND (part.p_size <= 10)) OR ((part.p_brand = 'Brand#32'::bpchar) AND (part.p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[])) AND (lineitem.l_quantity >= '30'::numeric) AND (lineitem.l_quantity <= '40'::numeric) AND (part.p_size <= 15))) Rows Removed by Join Filter: 235 -> Append (cost=100.00..295548.58 rows=112565 width=21) (actual time=3.102..1405.475 rows=141319 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..147266.40 rows=56048 width=21) (actual time=0.491..161.037 rows=70695 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..147719.35 rows=56517 width=21) (actual time=0.717..166.270 rows=70624 loops=1) -> Hash (cost=12810.15..12810.15 rows=486 width=30) (actual time=43.013..43.015 rows=507 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 40kB -> Append (cost=100.00..12810.15 rows=486 width=30) (actual time=15.291..42.863 rows=507 loops=1) -> Async Foreign Scan on part_1 (cost=100.00..6379.52 rows=242 width=30) (actual time=0.703..0.887 rows=258 loops=1) -> Async Foreign Scan on part_2 (cost=100.00..6428.20 rows=244 width=30) (actual time=0.569..0.799 rows=249 loops=1) Planning Time: 1.807 ms Execution Time: 1471.382 ms (15 rows) QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=332645.88..332645.88 rows=1 width=52) (actual time=4145.639..4145.655 rows=167 loops=1) Sort Key: supplier.s_name Sort Method: quicksort Memory: 45kB -> Hash Join (cost=332543.49..332645.87 rows=1 width=52) (actual time=4145.474..4145.514 rows=167 loops=1) Hash Cond: (nation.n_nationkey = supplier.s_nationkey) -> Append (cost=100.00..202.36 rows=2 width=4) (actual time=0.318..0.320 rows=1 loops=1) -> Async Foreign Scan on nation_1 (cost=100.00..101.15 rows=1 width=4) (actual time=0.143..0.144 rows=1 loops=1) -> Async Foreign Scan on nation_2 (cost=100.00..101.21 rows=1 width=4) (actual time=0.170..0.170 rows=0 loops=1) -> Hash (cost=332443.47..332443.47 rows=1 width=56) (actual time=4145.150..4145.156 rows=4555 loops=1) Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 463kB -> Hash Semi Join (cost=331744.21..332443.47 rows=1 width=56) (actual time=4127.884..4144.108 rows=4555 loops=1) Hash Cond: (supplier.s_suppkey = partsupp.ps_suppkey) -> Append (cost=100.00..773.00 rows=10000 width=60) (actual time=0.335..14.573 rows=10000 loops=1) -> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=60) (actual time=0.194..4.768 rows=5019 loops=1) -> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=59) (actual time=0.491..4.906 rows=4981 loops=1) -> Hash (cost=331644.20..331644.20 rows=1 width=8) (actual time=4127.536..4127.541 rows=5989 loops=1) Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 298kB -> Hash Join (cost=316241.38..331644.20 rows=1 width=8) (actual time=3010.848..4126.160 rows=5989 loops=1) Hash Cond: ((lineitem.l_partkey = partsupp.ps_partkey) AND (lineitem.l_suppkey = partsupp.ps_suppkey)) Join Filter: ((partsupp.ps_availqty)::numeric > ((0.5 * sum(lineitem.l_quantity)))) Rows Removed by Join Filter: 10 -> HashAggregate (cost=261383.52..273296.35 rows=199427 width=40) (actual time=1998.286..3036.919 rows=543808 loops=1) Group Key: lineitem.l_partkey, lineitem.l_suppkey Planned Partitions: 16 Batches: 153 Memory Usage: 4121kB Disk Usage: 31560kB -> Append (cost=100.00..225555.08 rows=913554 width=13) (actual time=0.737..1601.268 rows=911395 loops=1) -> Async Foreign Scan on lineitem_1 (cost=100.00..110328.82 rows=456205 width=13) (actual time=0.390..514.763 rows=455324 loops=1) -> Async Foreign Scan on lineitem_2 (cost=100.00..110658.49 rows=457349 width=13) (actual time=0.392..514.112 rows=456071 loops=1) -> Hash (cost=54728.20..54728.20 rows=8644 width=16) (actual time=1012.132..1012.136 rows=8848 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 543kB -> Hash Semi Join (cost=6979.04..54728.20 rows=8644 width=16) (actual time=54.895..1009.128 rows=8848 loops=1) Hash Cond: (partsupp.ps_partkey = part.p_partkey) -> Append (cost=100.00..45653.00 rows=800000 width=12) (actual time=0.321..865.743 rows=800000 loops=1) -> Async Foreign Scan on partsupp_1 (cost=100.00..20745.92 rows=398464 width=12) (actual time=0.166..303.859 rows=398464 loops=1) -> Async Foreign Scan on partsupp_2 (cost=100.00..20907.08 rows=401536 width=12) (actual time=0.246..346.767 rows=401536 loops=1) -> Hash (cost=6852.03..6852.03 rows=2161 width=4) (actual time=51.991..51.992 rows=2212 loops=1) Buckets: 4096 Batches: 1 Memory Usage: 110kB -> Append (cost=100.00..6852.03 rows=2161 width=4) (actual time=3.384..51.241 rows=2212 loops=1) -> Async Foreign Scan on part_1 (cost=100.00..3407.72 rows=1076 width=4) (actual time=0.403..1.467 rows=1108 loops=1) -> Async Foreign Scan on part_2 (cost=100.00..3433.50 rows=1085 width=4) (actual time=0.714..1.921 rows=1104 loops=1) Planning Time: 2.116 ms Execution Time: 4149.343 ms (41 rows) QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=1040976.51..1040976.52 rows=1 width=34) (actual time=18351.578..18351.601 rows=100 loops=1) -> Sort (cost=1040976.51..1040976.52 rows=1 width=34) (actual time=18351.577..18351.594 rows=100 loops=1) Sort Key: (count(*)) DESC, supplier.s_name Sort Method: top-N heapsort Memory: 38kB -> GroupAggregate (cost=1040976.48..1040976.50 rows=1 width=34) (actual time=18350.347..18351.428 rows=393 loops=1) Group Key: supplier.s_name -> Sort (cost=1040976.48..1040976.49 rows=1 width=26) (actual time=18350.329..18350.656 rows=3918 loops=1) Sort Key: supplier.s_name Sort Method: quicksort Memory: 311kB -> Hash Semi Join (cost=951615.46..1040976.47 rows=1 width=26) (actual time=16937.366..18348.092 rows=3918 loops=1) Hash Cond: (orders.o_orderkey = l2.l_orderkey) Join Filter: (l2.l_suppkey <> l1.l_suppkey) Rows Removed by Join Filter: 4130 -> Hash Join (cost=530416.75..596332.72 rows=1 width=38) (actual time=9688.130..10481.556 rows=6540 loops=1) Hash Cond: (orders.o_orderkey = l1.l_orderkey) -> Append (cost=100.00..63280.62 rows=729425 width=4) (actual time=0.615..706.791 rows=729413 loops=1) -> Async Foreign Scan on orders_1 (cost=100.00..29776.43 rows=364016 width=4) (actual time=0.356..214.117 rows=364016 loops=1) -> Async Foreign Scan on orders_2 (cost=100.00..29857.07 rows=365409 width=4) (actual time=0.254..224.783 rows=365397 loops=1) -> Hash (cost=530316.73..530316.73 rows=1 width=34) (actual time=9686.896..9686.904 rows=13416 loops=1) Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1019kB -> Hash Anti Join (cost=271674.26..530316.73 rows=1 width=34) (actual time=4734.460..9683.675 rows=13416 loops=1) Hash Cond: (l1.l_orderkey = l3.l_orderkey) Join Filter: (l3.l_suppkey <> l1.l_suppkey) Rows Removed by Join Filter: 54358 -> Hash Join (cost=1130.89..247856.04 rows=160032 width=34) (actual time=15.166..4379.082 rows=149442 loops=1) Hash Cond: (l1.l_suppkey = supplier.s_suppkey) -> Append (cost=100.00..237723.31 rows=2000405 width=8) (actual time=0.505..3936.640 rows=3793296 loops=1) -> Async Foreign Scan on lineitem_1 l1_1 (cost=100.00..113688.26 rows=998683 width=8) (actual time=0.231..1430.615 rows=1894002 loops=1) -> Async Foreign Scan on lineitem_2 l1_2 (cost=100.00..114033.03 rows=1001722 width=8) (actual time=0.229..1425.376 rows=1899294 loops=1) -> Hash (cost=1020.89..1020.89 rows=800 width=30) (actual time=14.656..14.660 rows=393 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 33kB -> Hash Join (cost=302.39..1020.89 rows=800 width=30) (actual time=1.195..14.568 rows=393 loops=1) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) -> Append (cost=100.00..773.00 rows=10000 width=34) (actual time=0.486..12.780 rows=10000 loops=1) -> Async Foreign Scan on supplier_1 (cost=100.00..362.57 rows=5019 width=34) (actual time=0.248..4.243 rows=5019 loops=1) -> Async Foreign Scan on supplier_2 (cost=100.00..360.43 rows=4981 width=34) (actual time=0.258..4.075 rows=4981 loops=1) -> Hash (cost=202.36..202.36 rows=2 width=4) (actual time=0.689..0.691 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Append (cost=100.00..202.36 rows=2 width=4) (actual time=0.686..0.688 rows=1 loops=1) -> Async Foreign Scan on nation_1 (cost=100.00..101.15 rows=1 width=4) (actual time=0.180..0.180 rows=0 loops=1) -> Async Foreign Scan on nation_2 (cost=100.00..101.21 rows=1 width=4) (actual time=0.296..0.296 rows=1 loops=1) -> Hash (cost=237723.31..237723.31 rows=2000405 width=8) (actual time=4676.888..4676.889 rows=3793296 loops=1) Buckets: 131072 (originally 131072) Batches: 64 (originally 32) Memory Usage: 3328kB -> Append (cost=100.00..237723.31 rows=2000405 width=8) (actual time=0.291..3803.896 rows=3793296 loops=1) -> Async Foreign Scan on lineitem_1 l3_1 (cost=100.00..113688.26 rows=998683 width=8) (actual time=0.155..1434.767 rows=1894002 loops=1) -> Async Foreign Scan on lineitem_2 l3_2 (cost=100.00..114033.03 rows=1001722 width=8) (actual time=0.155..1453.430 rows=1899294 loops=1) -> Hash (cost=322740.53..322740.53 rows=6001215 width=8) (actual time=7240.565..7240.566 rows=6001215 loops=1) Buckets: 131072 Batches: 128 Memory Usage: 2838kB -> Append (cost=100.00..322740.53 rows=6001215 width=8) (actual time=0.540..5893.590 rows=6001215 loops=1) -> Async Foreign Scan on lineitem_1 l2_1 (cost=100.00..146145.44 rows=2996048 width=8) (actual time=0.292..2325.692 rows=2996048 loops=1) -> Async Foreign Scan on lineitem_2 l2_2 (cost=100.00..146589.01 rows=3005167 width=8) (actual time=0.176..2252.629 rows=3005167 loops=1) Planning Time: 1.433 ms Execution Time: 18353.481 ms (53 rows) QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------ GroupAggregate (cost=124926.45..124926.48 rows=1 width=72) (actual time=3127.335..3129.487 rows=7 loops=1) Group Key: (SUBSTRING(customer.c_phone FROM 1 FOR 2)) InitPlan 1 (returns $0) -> Aggregate (cost=7853.68..7853.69 rows=1 width=32) (actual time=107.705..107.707 rows=1 loops=1) -> Append (cost=100.00..7841.75 rows=4770 width=6) (actual time=1.945..98.412 rows=38221 loops=1) -> Async Foreign Scan on customer_1 customer_4 (cost=100.00..3907.12 rows=2383 width=6) (actual time=0.777..16.216 rows=19022 loops=1) -> Async Foreign Scan on customer_2 customer_5 (cost=100.00..3910.78 rows=2387 width=6) (actual time=1.258..24.490 rows=19199 loops=1) -> Sort (cost=117072.76..117072.77 rows=1 width=38) (actual time=3126.973..3127.354 rows=6364 loops=1) Sort Key: (SUBSTRING(customer.c_phone FROM 1 FOR 2)) Sort Method: quicksort Memory: 541kB -> Hash Anti Join (cost=103505.00..117072.76 rows=1 width=38) (actual time=2902.642..3124.800 rows=6364 loops=1) Hash Cond: (customer.c_custkey = orders.o_custkey) -> Append (cost=100.00..7766.25 rows=1750 width=26) (actual time=108.858..173.567 rows=19009 loops=1) -> Async Foreign Scan on customer_1 (cost=100.00..3876.94 rows=874 width=26) (actual time=108.219..119.499 rows=9439 loops=1) -> Async Foreign Scan on customer_2 (cost=100.00..3880.56 rows=876 width=26) (actual time=0.400..11.942 rows=9570 loops=1) -> Hash (cost=78795.00..78795.00 rows=1500000 width=4) (actual time=2792.199..2792.200 rows=1500000 loops=1) Buckets: 131072 Batches: 32 Memory Usage: 2697kB -> Append (cost=100.00..78795.00 rows=1500000 width=4) (actual time=0.329..2457.260 rows=1500000 loops=1) -> Async Foreign Scan on orders_1 (cost=100.00..35604.47 rows=749049 width=4) (actual time=0.157..455.400 rows=749049 loops=1) -> Async Foreign Scan on orders_2 (cost=100.00..35690.53 rows=750951 width=4) (actual time=1.016..462.740 rows=750951 loops=1) Planning Time: 0.893 ms Execution Time: 3130.506 ms (22 rows)