select pg_backend_pid(); pg_backend_pid ---------------- 25221 (1 row) -- using 1590755192 as a seed to the RNG explain analyze select l_returnflag, l_linestatus, sum(l_quantity) as sum_qty, sum(l_extendedprice) as sum_base_price, sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, avg(l_quantity) as avg_qty, avg(l_extendedprice) as avg_price, avg(l_discount) as avg_disc, count(*) as count_order from lineitem where l_shipdate <= date '1998-12-01' - interval '86' day group by l_returnflag, l_linestatus order by l_returnflag, l_linestatus ; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------- Sort (cost=424379.96..424379.98 rows=6 width=236) (actual time=24772.275..24772.276 rows=4 loops=1) Sort Key: l_returnflag, l_linestatus Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=424379.72..424379.88 rows=6 width=236) (actual time=24772.152..24772.180 rows=4 loops=1) Group Key: l_returnflag, l_linestatus Peak Memory Usage: 37 kB -> Seq Scan on lineitem (cost=0.00..187511.24 rows=5921712 width=25) (actual time=0.031..3352.788 rows=5923969 loops=1) Filter: (l_shipdate <= '1998-09-06 00:00:00'::timestamp without time zone) Rows Removed by Filter: 77246 Planning Time: 1.893 ms Execution Time: 24772.542 ms (11 rows) explain analyze select s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address, s_phone, s_comment from part, supplier, partsupp, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and p_size = 20 and p_type like '%TIN' and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'AFRICA' and ps_supplycost = ( select min(ps_supplycost) from partsupp, supplier, nation, region where p_partkey = ps_partkey and s_suppkey = ps_suppkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'AFRICA' ) order by s_acctbal desc, n_name, s_name, p_partkey limit 100; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=221113.04..221113.04 rows=1 width=192) (actual time=3916.956..3916.979 rows=100 loops=1) -> Sort (cost=221113.04..221113.04 rows=1 width=192) (actual time=3916.952..3916.964 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: 71kB -> Hash Join (cost=26436.41..221113.03 rows=1 width=192) (actual time=767.156..3915.803 rows=472 loops=1) Hash Cond: ((part.p_partkey = partsupp.ps_partkey) AND ((SubPlan 1) = partsupp.ps_supplycost)) -> Seq Scan on part (cost=0.00..7097.00 rows=897 width=30) (actual time=0.037..102.813 rows=784 loops=1) Filter: (((p_type)::text ~~ '%TIN'::text) AND (p_size = 20)) Rows Removed by Filter: 199216 -> Hash (cost=20129.41..20129.41 rows=160000 width=172) (actual time=718.782..718.782 rows=156400 loops=1) Buckets: 32768 Batches: 16 Memory Usage: 2205kB -> Nested Loop (cost=2.96..20129.41 rows=160000 width=172) (actual time=0.156..480.897 rows=156400 loops=1) -> Hash Join (cost=2.53..382.03 rows=2000 width=166) (actual time=0.116..9.438 rows=1955 loops=1) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) -> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=144) (actual time=0.012..3.788 rows=10000 loops=1) -> Hash (cost=2.47..2.47 rows=5 width=30) (actual time=0.084..0.084 rows=5 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Hash Join (cost=1.07..2.47 rows=5 width=30) (actual time=0.064..0.078 rows=5 loops=1) Hash Cond: (nation.n_regionkey = region.r_regionkey) -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=34) (actual time=0.011..0.014 rows=25 loops=1) -> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.025..0.026 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on region (cost=0.00..1.06 rows=1 width=4) (actual time=0.014..0.017 rows=1 loops=1) Filter: (r_name = 'AFRICA'::bpchar) Rows Removed by Filter: 4 -> Index Scan using partsupp_ps_suppkey_idx on partsupp (cost=0.42..9.07 rows=80 width=14) (actual time=0.011..0.203 rows=80 loops=1955) Index Cond: (ps_suppkey = supplier.s_suppkey) SubPlan 1 -> Result Cache (cost=409.47..409.48 rows=1 width=32) (actual time=2.422..2.422 rows=1 loops=1256) Cache Key: part.p_partkey Cache Hits: 472 Cache Misses: 784 Cache Evictions: 0 Cache Overflows: 0 -> Aggregate (cost=409.46..409.47 rows=1 width=32) (actual time=3.874..3.874 rows=1 loops=784) -> Nested Loop (cost=8.55..409.46 rows=1 width=6) (actual time=3.578..3.866 rows=1 loops=784) Join Filter: (nation_1.n_regionkey = region_1.r_regionkey) Rows Removed by Join Filter: 3 -> Seq Scan on region region_1 (cost=0.00..1.06 rows=1 width=4) (actual time=0.003..0.006 rows=1 loops=784) Filter: (r_name = 'AFRICA'::bpchar) Rows Removed by Filter: 4 -> Nested Loop (cost=8.55..408.35 rows=4 width=10) (actual time=3.554..3.857 rows=4 loops=784) Join Filter: (supplier_1.s_nationkey = nation_1.n_nationkey) Rows Removed by Join Filter: 96 -> Seq Scan on nation nation_1 (cost=0.00..1.25 rows=25 width=8) (actual time=0.002..0.006 rows=25 loops=784) -> Materialize (cost=8.55..405.61 rows=4 width=10) (actual time=0.022..0.153 rows=4 loops=19600) -> Hash Join (cost=8.55..405.59 rows=4 width=10) (actual time=0.532..3.801 rows=4 loops=784) Hash Cond: (supplier_1.s_suppkey = partsupp_1.ps_suppkey) -> Seq Scan on supplier supplier_1 (cost=0.00..322.00 rows=10000 width=8) (actual time=0.002..1.487 rows=10000 loops=784) -> Hash (cost=8.50..8.50 rows=4 width=10) (actual time=0.028..0.028 rows=4 loops=784) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Index Scan using partsupp_ps_partkey_idx on partsupp partsupp_1 (cost=0.42..8.50 rows=4 width=10) (actual time=0.019..0.022 rows=4 loops=784) Index Cond: (ps_partkey = part.p_partkey) Planning Time: 3.093 ms Execution Time: 3917.404 ms (52 rows) explain analyze select l_orderkey, sum(l_extendedprice * (1 - l_discount)) as revenue, o_orderdate, o_shippriority from customer, orders, lineitem where c_mktsegment = 'HOUSEHOLD' and c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate < date '1995-03-02' and l_shipdate > date '1995-03-02' group by l_orderkey, o_orderdate, o_shippriority order by revenue desc, o_orderdate limit 10; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=305610.12..305610.15 rows=10 width=44) (actual time=2831.028..2831.031 rows=10 loops=1) -> Sort (cost=305610.12..306413.41 rows=321317 width=44) (actual time=2831.027..2831.028 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 -> HashAggregate (cost=292767.40..298666.58 rows=321317 width=44) (actual time=2810.013..2824.176 rows=11447 loops=1) Group Key: lineitem.l_orderkey, orders.o_orderdate, orders.o_shippriority Planned Partitions: 32 Peak Memory Usage: 3729 kB -> Nested Loop (cost=5847.99..280416.78 rows=321317 width=24) (actual time=78.511..2722.232 rows=30345 loops=1) -> Hash Join (cost=5847.56..54857.30 rows=147989 width=12) (actual time=78.136..1030.698 rows=143526 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Seq Scan on orders (cost=0.00..44845.00 rows=715960 width=16) (actual time=0.018..549.129 rows=719155 loops=1) Filter: (o_orderdate < '1995-03-02'::date) Rows Removed by Filter: 780845 -> Hash (cost=5460.00..5460.00 rows=31005 width=4) (actual time=78.049..78.050 rows=30189 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1318kB -> Seq Scan on customer (cost=0.00..5460.00 rows=31005 width=4) (actual time=0.012..66.063 rows=30189 loops=1) Filter: (c_mktsegment = 'HOUSEHOLD'::bpchar) Rows Removed by Filter: 119811 -> Index Scan using lineitem_l_orderkey_idx on lineitem (cost=0.43..1.43 rows=9 width=16) (actual time=0.011..0.011 rows=0 loops=143526) Index Cond: (l_orderkey = orders.o_orderkey) Filter: (l_shipdate > '1995-03-02'::date) Rows Removed by Filter: 4 Planning Time: 1.297 ms Execution Time: 2831.631 ms (25 rows) explain analyze select o_orderpriority, count(*) as order_count from orders where o_orderdate >= date '1993-03-01' and o_orderdate < date '1993-03-01' + interval '3' month and exists ( select * from lineitem where l_orderkey = o_orderkey and l_commitdate < l_receiptdate ) group by o_orderpriority order by o_orderpriority ; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=128473.11..128578.52 rows=5 width=24) (actual time=1244.630..1263.494 rows=5 loops=1) Group Key: orders.o_orderpriority -> Sort (cost=128473.11..128508.23 rows=14048 width=16) (actual time=1240.878..1247.328 rows=52200 loops=1) Sort Key: orders.o_orderpriority Sort Method: quicksort Memory: 3813kB -> Nested Loop Semi Join (cost=0.43..127505.34 rows=14048 width=16) (actual time=0.054..1200.444 rows=52200 loops=1) -> Seq Scan on orders (cost=0.00..48595.00 rows=56751 width=20) (actual time=0.032..531.554 rows=56934 loops=1) Filter: ((o_orderdate >= '1993-03-01'::date) AND (o_orderdate < '1993-06-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 1443066 -> Index Scan using lineitem_l_orderkey_idx on lineitem (cost=0.43..4.62 rows=5 width=4) (actual time=0.011..0.011 rows=1 loops=56934) Index Cond: (l_orderkey = orders.o_orderkey) Filter: (l_commitdate < l_receiptdate) Rows Removed by Filter: 1 Planning Time: 0.643 ms Execution Time: 1264.025 ms (15 rows) explain analyze select n_name, sum(l_extendedprice * (1 - l_discount)) as revenue from customer, orders, lineitem, supplier, nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA' and o_orderdate >= date '1995-01-01' and o_orderdate < date '1995-01-01' + interval '1' year group by n_name order by revenue desc ; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=193407.80..193407.87 rows=25 width=58) (actual time=2985.378..2985.379 rows=5 loops=1) Sort Key: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount)))) DESC Sort Method: quicksort Memory: 25kB -> GroupAggregate (cost=193315.08..193407.22 rows=25 width=58) (actual time=2976.896..2985.357 rows=5 loops=1) Group Key: nation.n_name -> Sort (cost=193315.08..193333.45 rows=7346 width=38) (actual time=2974.785..2975.686 rows=7378 loops=1) Sort Key: nation.n_name Sort Method: quicksort Memory: 769kB -> Hash Join (cost=475.39..192843.37 rows=7346 width=38) (actual time=8.627..2962.671 rows=7378 loops=1) Hash Cond: ((lineitem.l_suppkey = supplier.s_suppkey) AND (customer.c_nationkey = supplier.s_nationkey)) -> Nested Loop (cost=3.39..190920.57 rows=183646 width=50) (actual time=0.171..2838.683 rows=183585 loops=1) -> Nested Loop (cost=2.96..53259.63 rows=45906 width=38) (actual time=0.150..1796.202 rows=45731 loops=1) -> Hash Join (cost=2.53..5950.03 rows=30000 width=38) (actual time=0.085..129.773 rows=30183 loops=1) Hash Cond: (customer.c_nationkey = nation.n_nationkey) -> Seq Scan on customer (cost=0.00..5085.00 rows=150000 width=8) (actual time=0.017..54.685 rows=150000 loops=1) -> Hash (cost=2.47..2.47 rows=5 width=30) (actual time=0.053..0.054 rows=5 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Hash Join (cost=1.07..2.47 rows=5 width=30) (actual time=0.041..0.050 rows=5 loops=1) Hash Cond: (nation.n_regionkey = region.r_regionkey) -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=34) (actual time=0.010..0.013 rows=25 loops=1) -> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.019..0.020 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on region (cost=0.00..1.06 rows=1 width=4) (actual time=0.013..0.015 rows=1 loops=1) Filter: (r_name = 'ASIA'::bpchar) Rows Removed by Filter: 4 -> Index Scan using orders_o_custkey_idx on orders (cost=0.43..1.55 rows=3 width=8) (actual time=0.024..0.053 rows=2 loops=30183) Index Cond: (o_custkey = customer.c_custkey) Filter: ((o_orderdate >= '1995-01-01'::date) AND (o_orderdate < '1996-01-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 8 -> Index Scan using lineitem_l_orderkey_idx on lineitem (cost=0.43..2.84 rows=16 width=20) (actual time=0.017..0.020 rows=4 loops=45731) Index Cond: (l_orderkey = orders.o_orderkey) -> Hash (cost=322.00..322.00 rows=10000 width=8) (actual time=7.828..7.828 rows=10000 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 519kB -> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=8) (actual time=0.009..3.807 rows=10000 loops=1) Planning Time: 2.903 ms Execution Time: 2985.531 ms (36 rows) explain analyze select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= date '1995-01-01' and l_shipdate < date '1995-01-01' + interval '1' year and l_discount between 0.02 - 0.01 and 0.02 + 0.01 and l_quantity < 24 ; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=248105.40..248105.42 rows=1 width=32) (actual time=2740.088..2740.088 rows=1 loops=1) -> Seq Scan on lineitem (cost=0.00..247517.83 rows=117515 width=12) (actual time=0.093..2640.760 rows=114428 loops=1) Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate < '1996-01-01 00:00:00'::timestamp without time zone) AND (l_discount >= 0.01) AND (l_discount <= 0.03) AND (l_quantity < '24'::numeric)) Rows Removed by Filter: 5886787 Planning Time: 0.193 ms Execution Time: 2740.131 ms (6 rows) explain analyze select supp_nation, cust_nation, l_year, sum(volume) as revenue from ( select n1.n_name as supp_nation, n2.n_name as cust_nation, extract(year from l_shipdate) as l_year, l_extendedprice * (1 - l_discount) as volume from supplier, lineitem, orders, customer, nation n1, nation n2 where s_suppkey = l_suppkey and o_orderkey = l_orderkey and c_custkey = o_custkey and s_nationkey = n1.n_nationkey and c_nationkey = n2.n_nationkey and ( (n1.n_name = 'ALGERIA' and n2.n_name = 'JAPAN') or (n1.n_name = 'JAPAN' and n2.n_name = 'ALGERIA') ) and l_shipdate between date '1995-01-01' and date '1996-12-31' ) as shipping group by supp_nation, cust_nation, l_year order by supp_nation, cust_nation, l_year ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=82923.89..83138.27 rows=6125 width=92) (actual time=3682.711..3689.796 rows=4 loops=1) Group Key: n1.n_name, n2.n_name, (date_part('year'::text, (lineitem.l_shipdate)::timestamp without time zone)) -> Sort (cost=82923.89..82939.21 rows=6125 width=72) (actual time=3680.311..3680.959 rows=5923 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: 1025kB -> Hash Join (cost=27675.57..82538.62 rows=6125 width=72) (actual time=710.699..3672.304 rows=5923 loops=1) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) Join Filter: (((n1.n_name = 'ALGERIA'::bpchar) AND (n2.n_name = 'JAPAN'::bpchar)) OR ((n1.n_name = 'JAPAN'::bpchar) AND (n2.n_name = 'ALGERIA'::bpchar))) Rows Removed by Join Filter: 5762 -> Nested Loop (cost=1.83..50637.81 rows=147185 width=46) (actual time=0.122..2700.822 rows=145780 loops=1) -> Hash Join (cost=1.40..368.90 rows=800 width=30) (actual time=0.067..10.613 rows=797 loops=1) Hash Cond: (supplier.s_nationkey = n1.n_nationkey) -> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=8) (actual time=0.016..4.442 rows=10000 loops=1) -> Hash (cost=1.38..1.38 rows=2 width=30) (actual time=0.032..0.033 rows=2 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on nation n1 (cost=0.00..1.38 rows=2 width=30) (actual time=0.014..0.026 rows=2 loops=1) Filter: ((n_name = 'ALGERIA'::bpchar) OR (n_name = 'JAPAN'::bpchar)) Rows Removed by Filter: 23 -> Index Scan using lineitem_l_suppkey_idx on lineitem (cost=0.43..61.00 rows=184 width=24) (actual time=0.048..3.307 rows=183 loops=797) Index Cond: (l_suppkey = supplier.s_suppkey) Filter: ((l_shipdate >= '1995-01-01'::date) AND (l_shipdate <= '1996-12-31'::date)) Rows Removed by Filter: 418 -> Hash (cost=25352.74..25352.74 rows=120000 width=30) (actual time=708.885..708.886 rows=119027 loops=1) Buckets: 65536 Batches: 4 Memory Usage: 2310kB -> Nested Loop (cost=1.83..25352.74 rows=120000 width=30) (actual time=0.073..622.688 rows=119027 loops=1) -> Hash Join (cost=1.40..5768.90 rows=12000 width=30) (actual time=0.048..90.861 rows=11873 loops=1) Hash Cond: (customer.c_nationkey = n2.n_nationkey) -> Seq Scan on customer (cost=0.00..5085.00 rows=150000 width=8) (actual time=0.012..42.796 rows=150000 loops=1) -> Hash (cost=1.38..1.38 rows=2 width=30) (actual time=0.019..0.020 rows=2 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on nation n2 (cost=0.00..1.38 rows=2 width=30) (actual time=0.006..0.016 rows=2 loops=1) Filter: ((n_name = 'JAPAN'::bpchar) OR (n_name = 'ALGERIA'::bpchar)) Rows Removed by Filter: 23 -> Index Scan using orders_o_custkey_idx on orders (cost=0.43..1.46 rows=17 width=8) (actual time=0.007..0.041 rows=10 loops=11873) Index Cond: (o_custkey = customer.c_custkey) Planning Time: 2.698 ms Execution Time: 3690.013 ms (37 rows) explain analyze select o_year, sum(case when nation = 'JAPAN' then volume else 0 end) / sum(volume) as mkt_share from ( select extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) as volume, n2.n_name as nation from part, supplier, lineitem, orders, customer, nation n1, nation n2, region where p_partkey = l_partkey and s_suppkey = l_suppkey and l_orderkey = o_orderkey and o_custkey = c_custkey and c_nationkey = n1.n_nationkey and n1.n_regionkey = r_regionkey and r_name = 'ASIA' and s_nationkey = n2.n_nationkey and o_orderdate between date '1995-01-01' and date '1996-12-31' and p_type = 'STANDARD BRUSHED NICKEL' ) as all_nations group by o_year order by o_year ; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=178115.45..178223.81 rows=2406 width=40) (actual time=932.145..933.935 rows=2 loops=1) Group Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) -> Sort (cost=178115.45..178121.48 rows=2410 width=46) (actual time=930.406..930.636 rows=2358 loops=1) Sort Key: (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) Sort Method: quicksort Memory: 281kB -> Hash Join (cost=6774.45..177980.07 rows=2410 width=46) (actual time=115.322..927.921 rows=2358 loops=1) Hash Cond: (supplier.s_nationkey = n2.n_nationkey) -> Hash Join (cost=6772.89..177933.32 rows=2410 width=20) (actual time=115.251..923.393 rows=2358 loops=1) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) -> Hash Join (cost=6325.89..177453.18 rows=2410 width=20) (actual time=105.728..911.264 rows=2358 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Nested Loop (cost=0.86..171058.87 rows=12048 width=24) (actual time=0.295..795.821 rows=11668 loops=1) -> Nested Loop (cost=0.43..151971.45 rows=39754 width=20) (actual time=0.138..312.746 rows=38455 loops=1) -> Seq Scan on part (cost=0.00..6597.00 rows=1325 width=4) (actual time=0.091..69.127 rows=1285 loops=1) Filter: ((p_type)::text = 'STANDARD BRUSHED NICKEL'::text) Rows Removed by Filter: 198715 -> Index Scan using lineitem_l_partkey_idx on lineitem (cost=0.43..109.41 rows=31 width=24) (actual time=0.015..0.174 rows=30 loops=1285) Index Cond: (l_partkey = part.p_partkey) -> Index Scan using orders_o_orderkey_idx on orders (cost=0.43..0.47 rows=1 width=12) (actual time=0.012..0.012 rows=0 loops=38455) 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 -> Hash (cost=5950.03..5950.03 rows=30000 width=4) (actual time=105.361..105.362 rows=30183 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 1318kB -> Hash Join (cost=2.53..5950.03 rows=30000 width=4) (actual time=0.078..91.842 rows=30183 loops=1) Hash Cond: (customer.c_nationkey = n1.n_nationkey) -> Seq Scan on customer (cost=0.00..5085.00 rows=150000 width=8) (actual time=0.012..43.199 rows=150000 loops=1) -> Hash (cost=2.47..2.47 rows=5 width=4) (actual time=0.056..0.056 rows=5 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Hash Join (cost=1.07..2.47 rows=5 width=4) (actual time=0.044..0.053 rows=5 loops=1) Hash Cond: (n1.n_regionkey = region.r_regionkey) -> Seq Scan on nation n1 (cost=0.00..1.25 rows=25 width=8) (actual time=0.009..0.013 rows=25 loops=1) -> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.020..0.021 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on region (cost=0.00..1.06 rows=1 width=4) (actual time=0.016..0.018 rows=1 loops=1) Filter: (r_name = 'ASIA'::bpchar) Rows Removed by Filter: 4 -> Hash (cost=322.00..322.00 rows=10000 width=8) (actual time=9.488..9.488 rows=10000 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 519kB -> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=8) (actual time=0.012..5.788 rows=10000 loops=1) -> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.048..0.048 rows=25 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Seq Scan on nation n2 (cost=0.00..1.25 rows=25 width=30) (actual time=0.021..0.030 rows=25 loops=1) Planning Time: 3.714 ms Execution Time: 934.150 ms (45 rows) explain analyze select nation, o_year, sum(amount) as sum_profit from ( select n_name as nation, extract(year from o_orderdate) as o_year, l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount from part, supplier, lineitem, partsupp, orders, nation where s_suppkey = l_suppkey and ps_suppkey = l_suppkey and ps_partkey = l_partkey and p_partkey = l_partkey and o_orderkey = l_orderkey and s_nationkey = n_nationkey and p_name like '%chartreuse%' ) as profit group by nation, o_year order by nation, o_year desc ; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=102141.53..102146.03 rows=120 width=66) (actual time=10756.440..11626.136 rows=175 loops=1) Group Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) -> Sort (cost=102141.53..102141.83 rows=120 width=57) (actual time=10752.992..10946.952 rows=325758 loops=1) Sort Key: nation.n_name, (date_part('year'::text, (orders.o_orderdate)::timestamp without time zone)) DESC Sort Method: external merge Disk: 26752kB -> Hash Join (cost=7172.68..102137.39 rows=120 width=57) (actual time=114.032..9940.619 rows=325758 loops=1) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) -> Nested Loop (cost=7171.12..102133.58 rows=120 width=31) (actual time=113.961..9371.538 rows=325758 loops=1) -> Hash Join (cost=7170.69..102076.56 rows=120 width=31) (actual time=113.929..4410.268 rows=325758 loops=1) Hash Cond: (lineitem.l_suppkey = supplier.s_suppkey) -> Nested Loop (cost=6723.69..101627.91 rows=120 width=35) (actual time=105.468..4095.256 rows=325758 loops=1) -> Hash Join (cost=6723.26..35572.98 rows=39972 width=18) (actual time=105.381..584.591 rows=43444 loops=1) Hash Cond: (partsupp.ps_partkey = part.p_partkey) -> Seq Scan on partsupp (cost=0.00..25450.00 rows=800000 width=14) (actual time=0.017..222.045 rows=800000 loops=1) -> Hash (cost=6597.00..6597.00 rows=10101 width=4) (actual time=105.222..105.222 rows=10861 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 510kB -> Seq Scan on part (cost=0.00..6597.00 rows=10101 width=4) (actual time=0.032..98.280 rows=10861 loops=1) Filter: ((p_name)::text ~~ '%chartreuse%'::text) Rows Removed by Filter: 189139 -> Index Scan using lineitem_l_partkey_idx on lineitem (cost=0.43..1.64 rows=1 width=29) (actual time=0.015..0.076 rows=7 loops=43444) Index Cond: (l_partkey = partsupp.ps_partkey) Filter: (partsupp.ps_suppkey = l_suppkey) Rows Removed by Filter: 22 -> Hash (cost=322.00..322.00 rows=10000 width=8) (actual time=8.419..8.420 rows=10000 loops=1) Buckets: 16384 Batches: 1 Memory Usage: 519kB -> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=8) (actual time=0.012..4.541 rows=10000 loops=1) -> Index Scan using orders_o_orderkey_idx on orders (cost=0.43..0.47 rows=1 width=8) (actual time=0.013..0.014 rows=1 loops=325758) Index Cond: (o_orderkey = lineitem.l_orderkey) -> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.051..0.051 rows=25 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=30) (actual time=0.024..0.032 rows=25 loops=1) Planning Time: 6.012 ms Execution Time: 11633.695 ms (33 rows) explain analyze select c_custkey, c_name, sum(l_extendedprice * (1 - l_discount)) as revenue, c_acctbal, n_name, c_address, c_phone, c_comment from customer, orders, lineitem, nation where c_custkey = o_custkey and l_orderkey = o_orderkey and o_orderdate >= date '1994-11-01' and o_orderdate < date '1994-11-01' + interval '3' month and l_returnflag = 'R' and c_nationkey = n_nationkey group by c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment order by revenue desc limit 20; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Limit (cost=267216.65..267216.70 rows=20 width=202) (actual time=4372.507..4372.512 rows=20 loops=1) -> Sort (cost=267216.65..267360.05 rows=57358 width=202) (actual time=4372.504..4372.507 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=263396.06..265690.38 rows=57358 width=202) (actual time=4006.955..4340.154 rows=38271 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 -> Sort (cost=263396.06..263539.45 rows=57358 width=182) (actual time=4006.919..4049.702 rows=115282 loops=1) Sort Key: customer.c_custkey, customer.c_name, customer.c_acctbal, customer.c_phone, nation.n_name, customer.c_address, customer.c_comment Sort Method: external merge Disk: 26048kB -> Hash Join (cost=243753.31..253763.06 rows=57358 width=182) (actual time=3361.020..3700.087 rows=115282 loops=1) Hash Cond: (customer.c_nationkey = nation.n_nationkey) -> Hash Join (cost=243751.75..252972.83 rows=57358 width=160) (actual time=3360.962..3634.639 rows=115282 loops=1) Hash Cond: (customer.c_custkey = orders.o_custkey) -> Seq Scan on customer (cost=0.00..5085.00 rows=150000 width=148) (actual time=0.010..44.478 rows=150000 loops=1) -> Hash (cost=243034.77..243034.77 rows=57358 width=16) (actual time=3360.768..3360.769 rows=115282 loops=1) Buckets: 131072 (originally 65536) Batches: 2 (originally 1) Memory Usage: 3810kB -> Hash Join (cost=49310.09..243034.77 rows=57358 width=16) (actual time=509.380..3277.979 rows=115282 loops=1) Hash Cond: (lineitem.l_orderkey = orders.o_orderkey) -> Seq Scan on lineitem (cost=0.00..187511.24 rows=1503965 width=16) (actual time=0.018..2297.092 rows=1478870 loops=1) Filter: (l_returnflag = 'R'::bpchar) Rows Removed by Filter: 4522345 -> Hash (cost=48595.00..48595.00 rows=57207 width=8) (actual time=509.045..509.046 rows=57683 loops=1) Buckets: 65536 Batches: 1 Memory Usage: 2766kB -> Seq Scan on orders (cost=0.00..48595.00 rows=57207 width=8) (actual time=0.042..478.221 rows=57683 loops=1) Filter: ((o_orderdate >= '1994-11-01'::date) AND (o_orderdate < '1995-02-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 1442317 -> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.046..0.046 rows=25 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 10kB -> Seq Scan on nation (cost=0.00..1.25 rows=25 width=30) (actual time=0.020..0.029 rows=25 loops=1) Planning Time: 1.604 ms Execution Time: 4379.934 ms (31 rows) explain analyze select ps_partkey, sum(ps_supplycost * ps_availqty) as value from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'MOZAMBIQUE' group by ps_partkey having sum(ps_supplycost * ps_availqty) > ( select sum(ps_supplycost * ps_availqty) * 0.0001000000 from partsupp, supplier, nation where ps_suppkey = s_suppkey and s_nationkey = n_nationkey and n_name = 'MOZAMBIQUE' ) order by value desc ; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=11483.54..11510.20 rows=10667 width=36) (actual time=400.815..400.907 rows=838 loops=1) Sort Key: (sum((partsupp.ps_supplycost * (partsupp.ps_availqty)::numeric))) DESC Sort Method: quicksort Memory: 64kB InitPlan 1 (returns $1) -> Aggregate (cost=4554.30..4554.32 rows=1 width=32) (actual time=100.052..100.052 rows=1 loops=1) -> Nested Loop (cost=1.75..4314.30 rows=32000 width=10) (actual time=0.081..71.156 rows=32480 loops=1) -> Hash Join (cost=1.32..364.82 rows=400 width=4) (actual time=0.062..5.190 rows=406 loops=1) Hash Cond: (supplier_1.s_nationkey = nation_1.n_nationkey) -> Seq Scan on supplier supplier_1 (cost=0.00..322.00 rows=10000 width=8) (actual time=0.013..1.963 rows=10000 loops=1) -> Hash (cost=1.31..1.31 rows=1 width=4) (actual time=0.024..0.025 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on nation nation_1 (cost=0.00..1.31 rows=1 width=4) (actual time=0.018..0.020 rows=1 loops=1) Filter: (n_name = 'MOZAMBIQUE'::bpchar) Rows Removed by Filter: 24 -> Index Scan using partsupp_ps_suppkey_idx on partsupp partsupp_1 (cost=0.42..9.07 rows=80 width=14) (actual time=0.009..0.137 rows=80 loops=406) Index Cond: (ps_suppkey = supplier_1.s_suppkey) -> HashAggregate (cost=5499.30..6215.55 rows=10667 width=36) (actual time=342.414..400.174 rows=838 loops=1) Group Key: partsupp.ps_partkey Filter: (sum((partsupp.ps_supplycost * (partsupp.ps_availqty)::numeric)) > $1) Planned Partitions: 4 Peak Memory Usage: 4017 kB Disk Usage: 768 kB HashAgg Batches: 4 Rows Removed by Filter: 29832 -> Nested Loop (cost=1.75..4314.30 rows=32000 width=14) (actual time=0.093..164.846 rows=32480 loops=1) -> Hash Join (cost=1.32..364.82 rows=400 width=4) (actual time=0.063..6.626 rows=406 loops=1) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) -> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=8) (actual time=0.015..3.210 rows=10000 loops=1) -> Hash (cost=1.31..1.31 rows=1 width=4) (actual time=0.029..0.029 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on nation (cost=0.00..1.31 rows=1 width=4) (actual time=0.019..0.023 rows=1 loops=1) Filter: (n_name = 'MOZAMBIQUE'::bpchar) Rows Removed by Filter: 24 -> Index Scan using partsupp_ps_suppkey_idx on partsupp (cost=0.42..9.07 rows=80 width=18) (actual time=0.016..0.349 rows=80 loops=406) Index Cond: (ps_suppkey = supplier.s_suppkey) Planning Time: 1.134 ms Execution Time: 401.511 ms (37 rows) explain analyze select l_shipmode, sum(case when o_orderpriority = '1-URGENT' or o_orderpriority = '2-HIGH' then 1 else 0 end) as high_line_count, sum(case when o_orderpriority <> '1-URGENT' and o_orderpriority <> '2-HIGH' then 1 else 0 end) as low_line_count from orders, lineitem where o_orderkey = l_orderkey and l_shipmode in ('MAIL', 'RAIL') and l_commitdate < l_receiptdate and l_shipdate < l_commitdate and l_receiptdate >= date '1995-01-01' and l_receiptdate < date '1995-01-01' + interval '1' year group by l_shipmode order by l_shipmode ; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=316003.57..316576.24 rows=7 width=27) (actual time=4717.020..4730.563 rows=2 loops=1) Group Key: lineitem.l_shipmode -> Sort (cost=316003.57..316075.15 rows=28630 width=27) (actual time=4703.711..4706.661 rows=30952 loops=1) Sort Key: lineitem.l_shipmode Sort Method: quicksort Memory: 3187kB -> Merge Join (cost=244645.73..313884.20 rows=28630 width=27) (actual time=3709.791..4680.796 rows=30952 loops=1) Merge Cond: (orders.o_orderkey = lineitem.l_orderkey) -> Index Scan using orders_o_orderkey_idx on orders (cost=0.43..65062.43 rows=1500000 width=20) (actual time=0.027..665.729 rows=1499856 loops=1) -> Sort (cost=244642.33..244713.90 rows=28630 width=15) (actual time=3709.677..3715.227 rows=30952 loops=1) Sort Key: lineitem.l_orderkey Sort Method: quicksort Memory: 2219kB -> Bitmap Heap Scan on lineitem (cost=23267.92..242522.95 rows=28630 width=15) (actual time=424.482..3676.636 rows=30952 loops=1) Recheck Cond: (l_shipmode = ANY ('{MAIL,RAIL}'::bpchar[])) Rows Removed by Index Recheck: 2499312 Filter: ((l_commitdate < l_receiptdate) AND (l_shipdate < l_commitdate) AND (l_receiptdate >= '1995-01-01'::date) AND (l_receiptdate < '1996-01-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 1682933 Heap Blocks: exact=46349 lossy=66154 -> Bitmap Index Scan on lineitem_l_shipmode_l_partkey_idx (cost=0.00..23260.76 rows=1687985 width=0) (actual time=405.823..405.823 rows=1713885 loops=1) Index Cond: (l_shipmode = ANY ('{MAIL,RAIL}'::bpchar[])) Planning Time: 0.873 ms Execution Time: 4732.371 ms (21 rows) explain analyze select c_count, count(*) as custdist from ( select c_custkey, count(o_orderkey) from customer left outer join orders on c_custkey = o_custkey and o_comment not like '%pending%accounts%' group by c_custkey ) as c_orders (c_custkey, c_count) group by c_count order by custdist desc, c_count desc ; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=125870.78..125871.28 rows=200 width=16) (actual time=5178.186..5178.190 rows=42 loops=1) Sort Key: (count(*)) DESC, (count(orders.o_orderkey)) DESC Sort Method: quicksort Memory: 26kB -> HashAggregate (cost=125861.13..125863.13 rows=200 width=16) (actual time=5178.120..5178.142 rows=42 loops=1) Group Key: count(orders.o_orderkey) Peak Memory Usage: 37 kB -> HashAggregate (cost=116252.34..123611.13 rows=150000 width=12) (actual time=4253.005..5105.228 rows=150000 loops=1) Group Key: customer.c_custkey Planned Partitions: 4 Peak Memory Usage: 4249 kB Disk Usage: 31488 kB HashAgg Batches: 84 -> Hash Right Join (cost=7546.00..85317.94 rows=1499850 width=8) (actual time=108.981..2928.159 rows=1533620 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Seq Scan on orders (cost=0.00..44845.00 rows=1499850 width=8) (actual time=0.023..1144.170 rows=1483615 loops=1) Filter: ((o_comment)::text !~~ '%pending%accounts%'::text) Rows Removed by Filter: 16385 -> Hash (cost=5085.00..5085.00 rows=150000 width=4) (actual time=108.680..108.681 rows=150000 loops=1) Buckets: 131072 Batches: 2 Memory Usage: 3662kB -> Seq Scan on customer (cost=0.00..5085.00 rows=150000 width=4) (actual time=0.016..51.533 rows=150000 loops=1) Planning Time: 0.620 ms Execution Time: 5187.022 ms (22 rows) explain analyze select 100.00 * sum(case when p_type like 'PROMO%' then l_extendedprice * (1 - l_discount) else 0 end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue from lineitem, part where l_partkey = p_partkey and l_shipdate >= date '1995-08-01' and l_shipdate < date '1995-08-01' + interval '1' month ; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=217111.11..217111.12 rows=1 width=32) (actual time=2816.912..2816.912 rows=1 loops=1) -> Hash Join (cost=9965.00..215718.26 rows=79591 width=33) (actual time=160.786..2692.218 rows=77505 loops=1) Hash Cond: (lineitem.l_partkey = part.p_partkey) -> Seq Scan on lineitem (cost=0.00..202512.89 rows=79591 width=16) (actual time=0.030..2339.165 rows=77505 loops=1) Filter: ((l_shipdate >= '1995-08-01'::date) AND (l_shipdate < '1995-09-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 5923710 -> Hash (cost=6097.00..6097.00 rows=200000 width=25) (actual time=160.581..160.582 rows=200000 loops=1) Buckets: 65536 Batches: 4 Memory Usage: 3406kB -> Seq Scan on part (cost=0.00..6097.00 rows=200000 width=25) (actual time=0.011..71.950 rows=200000 loops=1) Planning Time: 0.572 ms Execution Time: 2816.999 ms (11 rows) create view revenue0 (supplier_no, total_revenue) as select l_suppkey, sum(l_extendedprice * (1 - l_discount)) from lineitem where l_shipdate >= date '1996-08-01' and l_shipdate < date '1996-08-01' + interval '3' month group by l_suppkey; CREATE VIEW explain analyze select s_suppkey, s_name, s_address, s_phone, total_revenue from supplier, revenue0 where s_suppkey = supplier_no and total_revenue = ( select max(total_revenue) from revenue0 ) order by s_suppkey; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------- Merge Join (cost=412786.19..412836.94 rows=50 width=103) (actual time=5668.358..5668.360 rows=1 loops=1) Merge Cond: (supplier.s_suppkey = revenue0.supplier_no) InitPlan 1 (returns $0) -> Aggregate (cost=205070.74..205070.75 rows=1 width=32) (actual time=2894.522..2894.522 rows=1 loops=1) -> HashAggregate (cost=204821.24..204945.99 rows=9980 width=36) (actual time=2881.382..2892.621 rows=10000 loops=1) Group Key: lineitem_1.l_suppkey Peak Memory Usage: 3217 kB -> Seq Scan on lineitem lineitem_1 (cost=0.00..202512.89 rows=230835 width=16) (actual time=0.040..2388.038 rows=230590 loops=1) Filter: ((l_shipdate >= '1996-08-01'::date) AND (l_shipdate < '1996-11-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 5770625 -> Sort (cost=986.39..1011.39 rows=10000 width=71) (actual time=9.938..10.508 rows=5473 loops=1) Sort Key: supplier.s_suppkey Sort Method: quicksort Memory: 1791kB -> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=71) (actual time=0.019..4.684 rows=10000 loops=1) -> Sort (cost=206729.06..206729.18 rows=50 width=36) (actual time=5656.912..5656.913 rows=1 loops=1) Sort Key: revenue0.supplier_no Sort Method: quicksort Memory: 25kB -> Subquery Scan on revenue0 (cost=206552.50..206727.65 rows=50 width=36) (actual time=5654.186..5656.893 rows=1 loops=1) -> HashAggregate (cost=206552.50..206727.15 rows=50 width=36) (actual time=5654.181..5656.887 rows=1 loops=1) Group Key: lineitem.l_suppkey Filter: (sum((lineitem.l_extendedprice * ('1'::numeric - lineitem.l_discount))) = $0) Peak Memory Usage: 3217 kB Rows Removed by Filter: 9999 -> Seq Scan on lineitem (cost=0.00..202512.89 rows=230835 width=16) (actual time=0.040..2276.662 rows=230590 loops=1) Filter: ((l_shipdate >= '1996-08-01'::date) AND (l_shipdate < '1996-11-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 5770625 Planning Time: 0.587 ms Execution Time: 5668.661 ms (28 rows) drop view revenue0 ; DROP VIEW explain analyze select p_brand, p_type, p_size, count(distinct ps_suppkey) as supplier_cnt from partsupp, part where p_partkey = ps_partkey and p_brand <> 'Brand#24' and p_type not like 'STANDARD POLISHED%' and p_size in (19, 24, 46, 31, 42, 22, 40, 27) and ps_suppkey not in ( select s_suppkey from supplier where s_comment like '%Customer%Complaints%' ) group by p_brand, p_type, p_size order by supplier_cnt desc, p_brand, p_type, p_size ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=45845.10..45884.66 rows=15825 width=44) (actual time=1660.715..1662.576 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: 2209kB -> GroupAggregate (cost=43865.80..44741.31 rows=15825 width=44) (actual time=1361.067..1582.645 rows=18243 loops=1) Group Key: part.p_brand, part.p_type, part.p_size -> Sort (cost=43865.80..44009.25 rows=57381 width=40) (actual time=1361.013..1453.084 rows=117673 loops=1) Sort Key: part.p_brand, part.p_type, part.p_size Sort Method: external merge Disk: 6976kB -> Hash Join (cost=9806.52..39330.33 rows=57381 width=40) (actual time=149.949..859.963 rows=117673 loops=1) Hash Cond: (partsupp.ps_partkey = part.p_partkey) -> Seq Scan on partsupp (cost=347.00..27797.00 rows=400000 width=8) (actual time=6.060..456.090 rows=799680 loops=1) Filter: (NOT (hashed SubPlan 1)) Rows Removed by Filter: 320 SubPlan 1 -> Seq Scan on supplier (cost=0.00..347.00 rows=1 width=4) (actual time=0.224..6.015 rows=4 loops=1) Filter: ((s_comment)::text ~~ '%Customer%Complaints%'::text) Rows Removed by Filter: 9996 -> Hash (cost=9097.00..9097.00 rows=29001 width=40) (actual time=143.744..143.746 rows=29430 loops=1) Buckets: 32768 Batches: 1 Memory Usage: 2385kB -> Seq Scan on part (cost=0.00..9097.00 rows=29001 width=40) (actual time=0.038..125.993 rows=29430 loops=1) Filter: ((p_brand <> 'Brand#24'::bpchar) AND ((p_type)::text !~~ 'STANDARD POLISHED%'::text) AND (p_size = ANY ('{19,24,46,31,42,22,40,27}'::integer[]))) Rows Removed by Filter: 170570 Planning Time: 0.812 ms Execution Time: 1665.690 ms (24 rows) explain analyze select sum(l_extendedprice) / 7.0 as avg_yearly from lineitem, part where p_partkey = l_partkey and p_brand = 'Brand#41' and p_container = 'LG DRUM' and l_quantity < ( select 0.2 * avg(l_quantity) from lineitem where l_partkey = p_partkey ) ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=766387.05..766387.06 rows=1 width=32) (actual time=138.852..138.852 rows=1 loops=1) -> Nested Loop (cost=0.43..766382.39 rows=1860 width=8) (actual time=0.882..138.585 rows=518 loops=1) -> Seq Scan on part (cost=0.00..7097.00 rows=186 width=4) (actual time=0.559..71.978 rows=201 loops=1) Filter: ((p_brand = 'Brand#41'::bpchar) AND (p_container = 'LG DRUM'::bpchar)) Rows Removed by Filter: 199799 -> Index Scan using lineitem_l_partkey_idx on lineitem (cost=0.43..4082.08 rows=10 width=17) (actual time=0.274..0.328 rows=3 loops=201) Index Cond: (l_partkey = part.p_partkey) Filter: (l_quantity < (SubPlan 1)) Rows Removed by Filter: 27 SubPlan 1 -> Result Cache (cost=127.61..127.62 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=5899) Cache Key: part.p_partkey Cache Hits: 5698 Cache Misses: 201 Cache Evictions: 0 Cache Overflows: 0 -> Aggregate (cost=127.60..127.61 rows=1 width=32) (actual time=0.225..0.225 rows=1 loops=201) -> Bitmap Heap Scan on lineitem lineitem_1 (cost=4.67..127.52 rows=31 width=5) (actual time=0.024..0.200 rows=29 loops=201) Recheck Cond: (l_partkey = part.p_partkey) Heap Blocks: exact=5898 -> Bitmap Index Scan on lineitem_l_partkey_idx (cost=0.00..4.67 rows=31 width=0) (actual time=0.010..0.010 rows=29 loops=201) Index Cond: (l_partkey = part.p_partkey) Planning Time: 0.636 ms Execution Time: 138.977 ms (21 rows) explain analyze select c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice, sum(l_quantity) from customer, orders, lineitem where o_orderkey in ( select l_orderkey from lineitem group by l_orderkey having sum(l_quantity) > 312 ) and c_custkey = o_custkey and o_orderkey = l_orderkey group by c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice order by o_totalprice desc, o_orderdate limit 100; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=378882.01..378882.26 rows=100 width=71) (actual time=8534.046..8534.048 rows=10 loops=1) -> Sort (cost=378882.01..380119.80 rows=495118 width=71) (actual time=8534.043..8534.045 rows=10 loops=1) Sort Key: orders.o_totalprice DESC, orders.o_orderdate Sort Method: quicksort Memory: 25kB -> HashAggregate (cost=349418.36..359958.95 rows=495118 width=71) (actual time=8533.921..8534.012 rows=10 loops=1) Group Key: orders.o_totalprice, orders.o_orderdate, customer.c_name, customer.c_custkey, orders.o_orderkey Planned Partitions: 64 Peak Memory Usage: 409 kB -> Nested Loop (cost=7840.31..324585.09 rows=495118 width=44) (actual time=1949.776..8533.613 rows=70 loops=1) Join Filter: (orders.o_orderkey = lineitem.l_orderkey) -> Hash Join (cost=7839.87..292610.55 rows=123766 width=43) (actual time=1949.709..8532.986 rows=10 loops=1) Hash Cond: (orders.o_custkey = customer.c_custkey) -> Nested Loop (cost=0.87..280738.77 rows=123766 width=24) (actual time=1784.945..8345.592 rows=10 loops=1) -> GroupAggregate (cost=0.43..276121.27 rows=123766 width=4) (actual time=1784.881..8345.071 rows=10 loops=1) Group Key: lineitem_1.l_orderkey Filter: (sum(lineitem_1.l_quantity) > '312'::numeric) Rows Removed by Filter: 1499990 -> Index Scan using lineitem_l_orderkey_idx on lineitem lineitem_1 (cost=0.43..240548.52 rows=6000659 width=9) (actual time=0.050..3161.243 rows=6001215 loops=1) -> Result Cache (cost=0.44..1.43 rows=1 width=20) (actual time=0.044..0.045 rows=1 loops=10) Cache Key: lineitem_1.l_orderkey Cache Hits: 0 Cache Misses: 10 Cache Evictions: 0 Cache Overflows: 0 -> Index Scan using orders_o_orderkey_idx on orders (cost=0.43..1.42 rows=1 width=20) (actual time=0.033..0.034 rows=1 loops=10) Index Cond: (o_orderkey = lineitem_1.l_orderkey) -> Hash (cost=5085.00..5085.00 rows=150000 width=23) (actual time=164.622..164.622 rows=150000 loops=1) Buckets: 65536 Batches: 4 Memory Usage: 2569kB -> Seq Scan on customer (cost=0.00..5085.00 rows=150000 width=23) (actual time=0.020..76.100 rows=150000 loops=1) -> Result Cache (cost=0.44..3.61 rows=16 width=9) (actual time=0.041..0.054 rows=7 loops=10) Cache Key: lineitem_1.l_orderkey Cache Hits: 0 Cache Misses: 10 Cache Evictions: 0 Cache Overflows: 0 -> Index Scan using lineitem_l_orderkey_idx on lineitem (cost=0.43..3.60 rows=16 width=9) (actual time=0.033..0.041 rows=7 loops=10) Index Cond: (l_orderkey = lineitem_1.l_orderkey) Planning Time: 1.720 ms Execution Time: 8534.360 ms (33 rows) explain analyze select sum(l_extendedprice* (1 - l_discount)) as revenue from lineitem, part where ( p_partkey = l_partkey and p_brand = 'Brand#23' and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') and l_quantity >= 8 and l_quantity <= 8 + 10 and p_size between 1 and 5 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#53' and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') and l_quantity >= 14 and l_quantity <= 14 + 10 and p_size between 1 and 10 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) or ( p_partkey = l_partkey and p_brand = 'Brand#52' and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') and l_quantity >= 24 and l_quantity <= 24 + 10 and p_size between 1 and 15 and l_shipmode in ('AIR', 'AIR REG') and l_shipinstruct = 'DELIVER IN PERSON' ) ; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=24643.39..24643.40 rows=1 width=32) (actual time=155.368..155.369 rows=1 loops=1) -> Nested Loop (cost=0.43..24642.51 rows=116 width=12) (actual time=1.557..155.027 rows=141 loops=1) -> Seq Scan on part (cost=0.00..12597.00 rows=490 width=30) (actual time=0.552..126.899 rows=530 loops=1) Filter: ((p_size >= 1) AND (((p_brand = 'Brand#23'::bpchar) AND (p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND (p_size <= 5)) OR ((p_brand = 'Brand#53'::bpchar) AND (p_container = ANY ('{"MED BAG","MED BOX","MED PKG","MED PACK"}'::bpchar[])) AND (p_size <= 10)) OR ((p_brand = 'Brand#52'::bpchar) AND (p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[])) AND (p_size <= 15)))) Rows Removed by Filter: 199470 -> Index Scan using lineitem_l_shipmode_l_partkey_idx on lineitem (cost=0.43..24.57 rows=1 width=21) (actual time=0.044..0.049 rows=0 loops=530) Index Cond: ((l_shipmode = ANY ('{AIR,"AIR REG"}'::bpchar[])) AND (l_partkey = part.p_partkey)) Filter: ((l_shipinstruct = 'DELIVER IN PERSON'::bpchar) AND (((l_quantity >= '8'::numeric) AND (l_quantity <= '18'::numeric)) OR ((l_quantity >= '14'::numeric) AND (l_quantity <= '24'::numeric)) OR ((l_quantity >= '24'::numeric) AND (l_quantity <= '34'::numeric))) AND (((part.p_brand = 'Brand#23'::bpchar) AND (part.p_container = ANY ('{"SM CASE","SM BOX","SM PACK","SM PKG"}'::bpchar[])) AND (l_quantity >= '8'::numeric) AND (l_quantity <= '18'::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 (l_quantity >= '14'::numeric) AND (l_quantity <= '24'::numeric) AND (part.p_size <= 10)) OR ((part.p_brand = 'Brand#52'::bpchar) AND (part.p_container = ANY ('{"LG CASE","LG BOX","LG PACK","LG PKG"}'::bpchar[])) AND (l_quantity >= '24'::numeric) AND (l_quantity <= '34'::numeric) AND (part.p_size <= 15)))) Rows Removed by Filter: 4 Planning Time: 1.192 ms Execution Time: 155.479 ms (11 rows) explain analyze select s_name, s_address from supplier, nation where s_suppkey in ( select ps_suppkey from partsupp where ps_partkey in ( select p_partkey from part where p_name like 'lavender%' ) and ps_availqty > ( select 0.5 * sum(l_quantity) from lineitem where l_partkey = ps_partkey and l_suppkey = ps_suppkey and l_shipdate >= date '1997-01-01' and l_shipdate < date '1997-01-01' + interval '1' year ) ) and s_nationkey = n_nationkey and n_name = 'KENYA' order by s_name ; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=164779.59..164779.86 rows=107 width=51) (actual time=2293.056..2293.068 rows=152 loops=1) Sort Key: supplier.s_name Sort Method: quicksort Memory: 43kB -> Hash Semi Join (cost=164409.98..164775.99 rows=107 width=51) (actual time=2287.597..2292.564 rows=152 loops=1) Hash Cond: (supplier.s_suppkey = partsupp.ps_suppkey) -> Hash Join (cost=1.32..364.82 rows=400 width=55) (actual time=0.058..4.938 rows=376 loops=1) Hash Cond: (supplier.s_nationkey = nation.n_nationkey) -> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=59) (actual time=0.013..2.405 rows=10000 loops=1) -> Hash (cost=1.31..1.31 rows=1 width=4) (actual time=0.032..0.033 rows=1 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 9kB -> Seq Scan on nation (cost=0.00..1.31 rows=1 width=4) (actual time=0.023..0.027 rows=1 loops=1) Filter: (n_name = 'KENYA'::bpchar) Rows Removed by Filter: 24 -> Hash (cost=164375.34..164375.34 rows=2665 width=4) (actual time=2287.449..2287.449 rows=5887 loops=1) Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 271kB -> Nested Loop (cost=6602.48..164375.34 rows=2665 width=4) (actual time=64.623..2281.596 rows=5887 loops=1) -> HashAggregate (cost=6602.05..6622.25 rows=2020 width=4) (actual time=63.973..65.914 rows=2155 loops=1) Group Key: part.p_partkey Peak Memory Usage: 241 kB -> Seq Scan on part (cost=0.00..6597.00 rows=2020 width=4) (actual time=0.166..61.574 rows=2155 loops=1) Filter: ((p_name)::text ~~ 'lavender%'::text) Rows Removed by Filter: 197845 -> Index Scan using partsupp_ps_partkey_idx on partsupp (cost=0.42..78.09 rows=1 width=8) (actual time=0.382..1.025 rows=3 loops=2155) Index Cond: (ps_partkey = part.p_partkey) Filter: ((ps_availqty)::numeric > (SubPlan 1)) Rows Removed by Filter: 1 SubPlan 1 -> Result Cache (cost=17.89..17.90 rows=1 width=32) (actual time=0.249..0.249 rows=1 loops=8620) Cache Key: partsupp.ps_partkey, partsupp.ps_suppkey Cache Hits: 0 Cache Misses: 8620 Cache Evictions: 0 Cache Overflows: 0 -> Aggregate (cost=17.88..17.89 rows=1 width=32) (actual time=0.246..0.246 rows=1 loops=8620) -> Bitmap Heap Scan on lineitem (cost=13.86..17.88 rows=1 width=5) (actual time=0.221..0.240 rows=1 loops=8620) Recheck Cond: ((l_partkey = partsupp.ps_partkey) AND (l_suppkey = partsupp.ps_suppkey)) Filter: ((l_shipdate >= '1997-01-01'::date) AND (l_shipdate < '1998-01-01 00:00:00'::timestamp without time zone)) Rows Removed by Filter: 6 Heap Blocks: exact=65248 -> BitmapAnd (cost=13.86..13.86 rows=1 width=0) (actual time=0.189..0.189 rows=0 loops=8620) -> Bitmap Index Scan on lineitem_l_partkey_idx (cost=0.00..4.67 rows=31 width=0) (actual time=0.012..0.012 rows=30 loops=8620) Index Cond: (l_partkey = partsupp.ps_partkey) -> Bitmap Index Scan on lineitem_l_suppkey_idx (cost=0.00..8.94 rows=601 width=0) (actual time=0.170..0.170 rows=600 loops=8620) Index Cond: (l_suppkey = partsupp.ps_suppkey) Planning Time: 1.268 ms Execution Time: 2293.304 ms (43 rows) explain analyze select s_name, count(*) as numwait from supplier, lineitem l1, orders, nation where s_suppkey = l1.l_suppkey and o_orderkey = l1.l_orderkey and o_orderstatus = 'F' and l1.l_receiptdate > l1.l_commitdate and exists ( select * from lineitem l2 where l2.l_orderkey = l1.l_orderkey and l2.l_suppkey <> l1.l_suppkey ) and not exists ( select * from lineitem l3 where l3.l_orderkey = l1.l_orderkey and l3.l_suppkey <> l1.l_suppkey and l3.l_receiptdate > l3.l_commitdate ) and s_nationkey = n_nationkey and n_name = 'RUSSIA' group by s_name order by numwait desc, s_name limit 100; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=76037.74..76037.75 rows=1 width=34) (actual time=3352.850..3352.871 rows=100 loops=1) -> Sort (cost=76037.74..76037.75 rows=1 width=34) (actual time=3352.848..3352.858 rows=100 loops=1) Sort Key: (count(*)) DESC, supplier.s_name Sort Method: top-N heapsort Memory: 37kB -> GroupAggregate (cost=76037.71..76037.73 rows=1 width=34) (actual time=3350.571..3352.260 rows=401 loops=1) Group Key: supplier.s_name -> Sort (cost=76037.71..76037.72 rows=1 width=26) (actual time=3350.552..3350.887 rows=3970 loops=1) Sort Key: supplier.s_name Sort Method: quicksort Memory: 407kB -> Nested Loop (cost=1.72..76037.70 rows=1 width=26) (actual time=0.694..3343.831 rows=3970 loops=1) -> Nested Loop Semi Join (cost=1.30..76037.18 rows=1 width=34) (actual time=0.169..3197.456 rows=8097 loops=1) -> Nested Loop Anti Join (cost=0.86..76036.17 rows=1 width=34) (actual time=0.160..3110.915 rows=13583 loops=1) -> Nested Loop (cost=0.43..25045.77 rows=80009 width=34) (actual time=0.081..1458.019 rows=152495 loops=1) -> Nested Loop (cost=0.00..448.31 rows=400 width=30) (actual time=0.040..7.027 rows=401 loops=1) Join Filter: (supplier.s_nationkey = nation.n_nationkey) Rows Removed by Join Filter: 9599 -> Seq Scan on nation (cost=0.00..1.31 rows=1 width=4) (actual time=0.026..0.028 rows=1 loops=1) Filter: (n_name = 'RUSSIA'::bpchar) Rows Removed by Filter: 24 -> Seq Scan on supplier (cost=0.00..322.00 rows=10000 width=34) (actual time=0.004..3.847 rows=10000 loops=1) -> Index Scan using lineitem_l_suppkey_idx on lineitem l1 (cost=0.43..59.49 rows=200 width=8) (actual time=0.035..3.497 rows=380 loops=401) Index Cond: (l_suppkey = supplier.s_suppkey) Filter: (l_receiptdate > l_commitdate) Rows Removed by Filter: 221 -> Index Scan using lineitem_l_orderkey_idx on lineitem l3 (cost=0.43..1.04 rows=5 width=8) (actual time=0.010..0.010 rows=1 loops=152495) Index Cond: (l_orderkey = l1.l_orderkey) Filter: ((l_receiptdate > l_commitdate) AND (l_suppkey <> l1.l_suppkey)) Rows Removed by Filter: 1 -> Index Scan using lineitem_l_orderkey_idx on lineitem l2 (cost=0.43..1.00 rows=16 width=8) (actual time=0.005..0.005 rows=1 loops=13583) Index Cond: (l_orderkey = l1.l_orderkey) Filter: (l_suppkey <> l1.l_suppkey) Rows Removed by Filter: 1 -> Index Scan using orders_o_orderkey_idx on orders (cost=0.43..0.51 rows=1 width=4) (actual time=0.017..0.017 rows=0 loops=8097) Index Cond: (o_orderkey = l1.l_orderkey) Filter: (o_orderstatus = 'F'::bpchar) Rows Removed by Filter: 1 Planning Time: 3.000 ms Execution Time: 3353.013 ms (38 rows) explain analyze select cntrycode, count(*) as numcust, sum(c_acctbal) as totacctbal from ( select substring(c_phone from 1 for 2) as cntrycode, c_acctbal from customer where substring(c_phone from 1 for 2) in ('24', '30', '20', '29', '23', '14', '11') and c_acctbal > ( select avg(c_acctbal) from customer where c_acctbal > 0.00 and substring(c_phone from 1 for 2) in ('24', '30', '20', '29', '23', '14', '11') ) and not exists ( select * from orders where o_custkey = c_custkey ) ) as custsale group by cntrycode order by cntrycode ; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=16299.85..16320.28 rows=743 width=72) (actual time=499.525..502.688 rows=7 loops=1) Group Key: ("substring"((customer.c_phone)::text, 1, 2)) InitPlan 1 (returns $0) -> Aggregate (cost=7534.45..7534.46 rows=1 width=32) (actual time=224.285..224.286 rows=1 loops=1) -> Seq Scan on customer customer_1 (cost=0.00..7522.50 rows=4779 width=6) (actual time=0.015..211.952 rows=38163 loops=1) Filter: ((c_acctbal > 0.00) AND ("substring"((c_phone)::text, 1, 2) = ANY ('{24,30,20,29,23,14,11}'::text[]))) Rows Removed by Filter: 111837 -> Sort (cost=8765.39..8767.25 rows=743 width=38) (actual time=498.995..499.590 rows=6431 loops=1) Sort Key: ("substring"((customer.c_phone)::text, 1, 2)) Sort Method: quicksort Memory: 494kB -> Nested Loop Anti Join (cost=0.43..8729.96 rows=743 width=38) (actual time=224.353..494.374 rows=6431 loops=1) -> Seq Scan on customer (cost=0.00..7522.50 rows=1750 width=26) (actual time=224.317..387.733 rows=19083 loops=1) Filter: ((c_acctbal > $0) AND ("substring"((c_phone)::text, 1, 2) = ANY ('{24,30,20,29,23,14,11}'::text[]))) Rows Removed by Filter: 130917 -> Index Only Scan using orders_o_custkey_idx on orders (cost=0.43..3.28 rows=17 width=4) (actual time=0.004..0.004 rows=1 loops=19083) Index Cond: (o_custkey = customer.c_custkey) Heap Fetches: 0 Planning Time: 0.544 ms Execution Time: 502.787 ms (19 rows)