normal.log
application/octet-stream
Filename: normal.log
Type: application/octet-stream
Part: 1
select pg_backend_pid();
pg_backend_pid
----------------
13518
(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=25699.153..25699.153 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=25699.051..25699.075 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.027..3774.667 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.745 ms
Execution Time: 25699.362 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=221108.55..221108.56 rows=1 width=192) (actual time=5826.035..5826.057 rows=100 loops=1)
-> Sort (cost=221108.55..221108.56 rows=1 width=192) (actual time=5826.032..5826.043 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..221108.54 rows=1 width=192) (actual time=759.436..5823.755 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.031..104.241 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=711.033..711.033 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.131..477.482 rows=156400 loops=1)
-> Hash Join (cost=2.53..382.03 rows=2000 width=166) (actual time=0.101..9.543 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.013..3.861 rows=10000 loops=1)
-> Hash (cost=2.47..2.47 rows=5 width=30) (actual time=0.070..0.070 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.051..0.069 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.023..0.024 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.015..0.018 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.204 rows=80 loops=1955)
Index Cond: (ps_suppkey = supplier.s_suppkey)
SubPlan 1
-> Aggregate (cost=409.46..409.47 rows=1 width=32) (actual time=3.934..3.934 rows=1 loops=1256)
-> Nested Loop (cost=8.55..409.46 rows=1 width=6) (actual time=3.561..3.924 rows=1 loops=1256)
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=1256)
Filter: (r_name = 'AFRICA'::bpchar)
Rows Removed by Filter: 4
-> Nested Loop (cost=8.55..408.35 rows=4 width=10) (actual time=3.540..3.914 rows=4 loops=1256)
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=1256)
-> Materialize (cost=8.55..405.61 rows=4 width=10) (actual time=0.022..0.155 rows=4 loops=31400)
-> Hash Join (cost=8.55..405.59 rows=4 width=10) (actual time=0.545..3.857 rows=4 loops=1256)
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.519 rows=10000 loops=1256)
-> Hash (cost=8.50..8.50 rows=4 width=10) (actual time=0.026..0.026 rows=4 loops=1256)
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.018..0.021 rows=4 loops=1256)
Index Cond: (ps_partkey = part.p_partkey)
Planning Time: 2.870 ms
Execution Time: 5826.335 ms
(49 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=2698.272..2698.275 rows=10 loops=1)
-> Sort (cost=305610.12..306413.41 rows=321317 width=44) (actual time=2698.270..2698.271 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=2678.804..2692.262 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=84.500..2596.431 rows=30345 loops=1)
-> Hash Join (cost=5847.56..54857.30 rows=147989 width=12) (actual time=84.118..991.817 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.024..524.572 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=84.019..84.020 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.018..71.814 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.010..0.010 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.512 ms
Execution Time: 2698.841 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=1288.108..1306.418 rows=5 loops=1)
Group Key: orders.o_orderpriority
-> Sort (cost=128473.11..128508.23 rows=14048 width=16) (actual time=1283.749..1290.262 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.055..1240.893 rows=52200 loops=1)
-> Seq Scan on orders (cost=0.00..48595.00 rows=56751 width=20) (actual time=0.032..548.808 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.637 ms
Execution Time: 1306.948 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=2772.486..2772.486 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=2764.568..2772.467 rows=5 loops=1)
Group Key: nation.n_name
-> Sort (cost=193315.08..193333.45 rows=7346 width=38) (actual time=2762.567..2763.344 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=10.083..2752.310 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.200..2634.351 rows=183585 loops=1)
-> Nested Loop (cost=2.96..53259.63 rows=45906 width=38) (actual time=0.172..1675.952 rows=45731 loops=1)
-> Hash Join (cost=2.53..5950.03 rows=30000 width=38) (actual time=0.094..121.548 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.018..51.677 rows=150000 loops=1)
-> Hash (cost=2.47..2.47 rows=5 width=30) (actual time=0.063..0.063 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.051..0.060 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.024..0.025 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.017..0.019 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.022..0.050 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.016..0.018 rows=4 loops=45731)
Index Cond: (l_orderkey = orders.o_orderkey)
-> Hash (cost=322.00..322.00 rows=10000 width=8) (actual time=9.207..9.207 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.015..4.662 rows=10000 loops=1)
Planning Time: 3.060 ms
Execution Time: 2772.673 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=2821.212..2821.212 rows=1 loops=1)
-> Seq Scan on lineitem (cost=0.00..247517.83 rows=117515 width=12) (actual time=0.095..2711.409 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.190 ms
Execution Time: 2821.255 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=3562.800..3570.284 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=3560.250..3561.006 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=692.323..3552.201 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.102..2614.252 rows=145780 loops=1)
-> Hash Join (cost=1.40..368.90 rows=800 width=30) (actual time=0.061..10.277 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.013..4.309 rows=10000 loops=1)
-> Hash (cost=1.38..1.38 rows=2 width=30) (actual time=0.030..0.030 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.013..0.025 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.047..3.200 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=690.530..690.531 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.065..613.664 rows=119027 loops=1)
-> Hash Join (cost=1.40..5768.90 rows=12000 width=30) (actual time=0.047..89.778 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.011..43.339 rows=150000 loops=1)
-> Hash (cost=1.38..1.38 rows=2 width=30) (actual time=0.019..0.019 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.017 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.040 rows=10 loops=11873)
Index Cond: (o_custkey = customer.c_custkey)
Planning Time: 2.264 ms
Execution Time: 3570.473 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=1092.471..1094.504 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=1090.459..1090.802 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=121.536..1087.234 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=121.466..1081.562 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=112.936..1069.974 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.314..944.137 rows=11668 loops=1)
-> Nested Loop (cost=0.43..151971.45 rows=39754 width=20) (actual time=0.142..368.828 rows=38455 loops=1)
-> Seq Scan on part (cost=0.00..6597.00 rows=1325 width=4) (actual time=0.091..81.187 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.018..0.205 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.014..0.014 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=112.554..112.554 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..98.203 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.014..46.699 rows=150000 loops=1)
-> Hash (cost=2.47..2.47 rows=5 width=4) (actual time=0.051..0.052 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.040..0.049 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.012 rows=25 loops=1)
-> Hash (cost=1.06..1.06 rows=1 width=4) (actual time=0.017..0.018 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.015 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=8.494..8.494 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.574 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.019..0.029 rows=25 loops=1)
Planning Time: 3.665 ms
Execution Time: 1094.745 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=10656.694..11590.011 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=10652.736..10869.581 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=112.401..9828.587 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=112.336..9291.009 rows=325758 loops=1)
-> Hash Join (cost=7170.69..102076.56 rows=120 width=31) (actual time=112.310..4406.190 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=103.501..4080.743 rows=325758 loops=1)
-> Hash Join (cost=6723.26..35572.98 rows=39972 width=18) (actual time=103.411..589.982 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.019..226.361 rows=800000 loops=1)
-> Hash (cost=6597.00..6597.00 rows=10101 width=4) (actual time=103.261..103.262 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.037..97.031 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.773..8.773 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.738 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.013 rows=1 loops=325758)
Index Cond: (o_orderkey = lineitem.l_orderkey)
-> Hash (cost=1.25..1.25 rows=25 width=30) (actual time=0.046..0.047 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.025..0.032 rows=25 loops=1)
Planning Time: 4.668 ms
Execution Time: 11597.552 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=4753.041..4753.046 rows=20 loops=1)
-> Sort (cost=267216.65..267360.05 rows=57358 width=202) (actual time=4753.038..4753.041 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=4362.755..4717.807 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=4362.716..4412.894 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=3657.652..4041.623 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=3657.601..3967.186 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.009..53.441 rows=150000 loops=1)
-> Hash (cost=243034.77..243034.77 rows=57358 width=16) (actual time=3657.387..3657.387 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=568.691..3575.271 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.015..2532.331 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=568.358..568.359 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.040..533.417 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.037..0.037 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.016..0.023 rows=25 loops=1)
Planning Time: 1.478 ms
Execution Time: 4761.339 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=383.035..383.138 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=111.746..111.747 rows=1 loops=1)
-> Nested Loop (cost=1.75..4314.30 rows=32000 width=10) (actual time=0.086..78.557 rows=32480 loops=1)
-> Hash Join (cost=1.32..364.82 rows=400 width=4) (actual time=0.067..5.687 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.018..2.137 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.021 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.010..0.152 rows=80 loops=406)
Index Cond: (ps_suppkey = supplier_1.s_suppkey)
-> HashAggregate (cost=5499.30..6215.55 rows=10667 width=36) (actual time=321.778..382.263 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.086..142.502 rows=32480 loops=1)
-> Hash Join (cost=1.32..364.82 rows=400 width=4) (actual time=0.058..5.802 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..2.772 rows=10000 loops=1)
-> Hash (cost=1.31..1.31 rows=1 width=4) (actual time=0.023..0.023 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.015..0.018 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.014..0.304 rows=80 loops=406)
Index Cond: (ps_suppkey = supplier.s_suppkey)
Planning Time: 0.933 ms
Execution Time: 383.725 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=4614.843..4628.808 rows=2 loops=1)
Group Key: lineitem.l_shipmode
-> Sort (cost=316003.57..316075.15 rows=28630 width=27) (actual time=4600.967..4603.993 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=3713.275..4582.373 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..597.042 rows=1499856 loops=1)
-> Sort (cost=244642.33..244713.90 rows=28630 width=15) (actual time=3713.174..3717.696 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=405.631..3684.615 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=386.425..386.425 rows=1713885 loops=1)
Index Cond: (l_shipmode = ANY ('{MAIL,RAIL}'::bpchar[]))
Planning Time: 0.608 ms
Execution Time: 4630.530 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=5076.331..5076.335 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=5076.269..5076.286 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=4153.804..5005.719 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.935..2920.199 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..1192.022 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.644..108.645 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..52.450 rows=150000 loops=1)
Planning Time: 0.612 ms
Execution Time: 5084.876 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=2873.389..2873.389 rows=1 loops=1)
-> Hash Join (cost=9965.00..215718.26 rows=79591 width=33) (actual time=200.338..2752.800 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.031..2371.668 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=200.141..200.141 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..91.789 rows=200000 loops=1)
Planning Time: 0.566 ms
Execution Time: 2873.474 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=5669.177..5669.178 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=2917.840..2917.840 rows=1 loops=1)
-> HashAggregate (cost=204821.24..204945.99 rows=9980 width=36) (actual time=2904.900..2916.083 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.041..2412.264 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=11.499..11.992 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.018..5.569 rows=10000 loops=1)
-> Sort (cost=206729.06..206729.18 rows=50 width=36) (actual time=5656.305..5656.305 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=5653.714..5656.284 rows=1 loops=1)
-> HashAggregate (cost=206552.50..206727.15 rows=50 width=36) (actual time=5653.711..5656.279 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..2271.358 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.666 ms
Execution Time: 5669.492 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=1664.908..1666.753 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=1369.531..1588.097 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=1369.477..1460.776 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=165.714..876.146 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.191..453.677 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.232..6.141 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=159.380..159.382 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.045..139.538 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.794 ms
Execution Time: 1669.764 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=766329.39..766329.40 rows=1 width=32) (actual time=572.145..572.145 rows=1 loops=1)
-> Nested Loop (cost=0.43..766324.73 rows=1860 width=8) (actual time=0.907..571.809 rows=518 loops=1)
-> Seq Scan on part (cost=0.00..7097.00 rows=186 width=4) (actual time=0.581..81.532 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..4081.77 rows=10 width=17) (actual time=0.883..2.435 rows=3 loops=201)
Index Cond: (l_partkey = part.p_partkey)
Filter: (l_quantity < (SubPlan 1))
Rows Removed by Filter: 27
SubPlan 1
-> Aggregate (cost=127.60..127.61 rows=1 width=32) (actual time=0.079..0.079 rows=1 loops=5899)
-> Bitmap Heap Scan on lineitem lineitem_1 (cost=4.67..127.52 rows=31 width=5) (actual time=0.015..0.059 rows=30 loops=5899)
Recheck Cond: (l_partkey = part.p_partkey)
Heap Blocks: exact=178558
-> Bitmap Index Scan on lineitem_l_partkey_idx (cost=0.00..4.67 rows=31 width=0) (actual time=0.009..0.009 rows=30 loops=5899)
Index Cond: (l_partkey = part.p_partkey)
Planning Time: 0.802 ms
Execution Time: 572.244 ms
(18 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=556909.88..556910.13 rows=100 width=71) (actual time=9376.580..9376.582 rows=10 loops=1)
-> Sort (cost=556909.88..558147.68 rows=495118 width=71) (actual time=9376.577..9376.578 rows=10 loops=1)
Sort Key: orders.o_totalprice DESC, orders.o_orderdate
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=527446.23..537986.83 rows=495118 width=71) (actual time=9376.404..9376.543 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=287229.44..502612.97 rows=495118 width=44) (actual time=8217.609..9376.071 rows=70 loops=1)
-> Hash Join (cost=287229.00..354358.30 rows=123766 width=43) (actual time=8217.553..9375.487 rows=10 loops=1)
Hash Cond: (orders.o_custkey = customer.c_custkey)
-> Hash Join (cost=279390.00..342486.51 rows=123766 width=24) (actual time=8097.528..9230.371 rows=10 loops=1)
Hash Cond: (orders.o_orderkey = lineitem_1.l_orderkey)
-> Seq Scan on orders (cost=0.00..41095.00 rows=1500000 width=20) (actual time=0.024..538.424 rows=1500000 loops=1)
-> Hash (cost=277358.93..277358.93 rows=123766 width=4) (actual time=7834.984..7834.985 rows=10 loops=1)
Buckets: 131072 Batches: 2 Memory Usage: 1025kB
-> GroupAggregate (cost=0.43..276121.27 rows=123766 width=4) (actual time=1553.400..7834.842 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.048..2950.078 rows=6001215 loops=1)
-> Hash (cost=5085.00..5085.00 rows=150000 width=23) (actual time=119.907..119.907 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.010..51.794 rows=150000 loops=1)
-> Index Scan using lineitem_l_orderkey_idx on lineitem (cost=0.43..1.04 rows=16 width=9) (actual time=0.042..0.047 rows=7 loops=10)
Index Cond: (l_orderkey = orders.o_orderkey)
Planning Time: 1.428 ms
Execution Time: 9376.819 ms
(28 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=174.969..174.970 rows=1 loops=1)
-> Nested Loop (cost=0.43..24642.51 rows=116 width=12) (actual time=1.380..174.576 rows=141 loops=1)
-> Seq Scan on part (cost=0.00..12597.00 rows=490 width=30) (actual time=0.493..144.577 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.047..0.052 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.014 ms
Execution Time: 175.059 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=164698.79..164699.06 rows=107 width=51) (actual time=2286.920..2286.932 rows=152 loops=1)
Sort Key: supplier.s_name
Sort Method: quicksort Memory: 43kB
-> Hash Semi Join (cost=164329.18..164695.19 rows=107 width=51) (actual time=2281.492..2286.424 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.059..4.905 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.014..2.390 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=164294.54..164294.54 rows=2665 width=4) (actual time=2281.344..2281.344 rows=5887 loops=1)
Buckets: 8192 (originally 4096) Batches: 1 (originally 1) Memory Usage: 271kB
-> Nested Loop (cost=6602.48..164294.54 rows=2665 width=4) (actual time=61.691..2275.152 rows=5887 loops=1)
-> HashAggregate (cost=6602.05..6622.25 rows=2020 width=4) (actual time=61.032..63.422 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.167..58.937 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.05 rows=1 width=8) (actual time=0.382..1.024 rows=3 loops=2155)
Index Cond: (ps_partkey = part.p_partkey)
Filter: ((ps_availqty)::numeric > (SubPlan 1))
Rows Removed by Filter: 1
SubPlan 1
-> Aggregate (cost=17.88..17.89 rows=1 width=32) (actual time=0.247..0.247 rows=1 loops=8620)
-> Bitmap Heap Scan on lineitem (cost=13.86..17.88 rows=1 width=5) (actual time=0.222..0.242 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.171..0.171 rows=600 loops=8620)
Index Cond: (l_suppkey = partsupp.ps_suppkey)
Planning Time: 1.175 ms
Execution Time: 2287.151 ms
(40 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=3387.307..3387.331 rows=100 loops=1)
-> Sort (cost=76037.74..76037.75 rows=1 width=34) (actual time=3387.303..3387.315 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=3384.545..3386.581 rows=401 loops=1)
Group Key: supplier.s_name
-> Sort (cost=76037.71..76037.72 rows=1 width=26) (actual time=3384.521..3384.955 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.808..3377.416 rows=3970 loops=1)
-> Nested Loop Semi Join (cost=1.30..76037.18 rows=1 width=34) (actual time=0.195..3229.248 rows=8097 loops=1)
-> Nested Loop Anti Join (cost=0.86..76036.17 rows=1 width=34) (actual time=0.181..3141.451 rows=13583 loops=1)
-> Nested Loop (cost=0.43..25045.77 rows=80009 width=34) (actual time=0.087..1474.933 rows=152495 loops=1)
-> Nested Loop (cost=0.00..448.31 rows=400 width=30) (actual time=0.041..6.923 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.027..0.030 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.801 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.540 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.334 ms
Execution Time: 3387.498 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=523.943..527.487 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=243.857..243.857 rows=1 loops=1)
-> Seq Scan on customer customer_1 (cost=0.00..7522.50 rows=4779 width=6) (actual time=0.016..230.148 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=523.375..524.063 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=243.924..518.806 rows=6431 loops=1)
-> Seq Scan on customer (cost=0.00..7522.50 rows=1750 width=26) (actual time=243.888..412.352 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.612 ms
Execution Time: 527.602 ms
(19 rows)