A TPC-H Q21 query plan
November 27th, 2011
Configuration:
PostgreSQL 9.0.4. memsize/data size = 1/8
Query
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 = 'BRAZIL'
group by
s_name
order by
numwait desc,
s_name;
Query plan
- Sort (cost=5009298.60..5009298.60 rows=1 width=26)
- Sort Key: (count(*)), supplier.s_name
- -> HashAggregate (cost=5009298.58..5009298.59 rows=1 width=26)
- -> Nested Loop Anti Join (cost=2757689.50..5009298.57 rows=1 width=26)
- Join Filter: (l3.l_suppkey <> l1.l_suppkey)
- -> Nested Loop (cost=2757689.50..5009241.82 rows=1 width=34)
- -> Hash Semi Join (cost=2757689.50..5009232.22 rows=1 width=38)
- Hash Cond: (l1.l_orderkey = l2.l_orderkey)
- Join Filter: (l2.l_suppkey <> l1.l_suppkey)
- -> Hash Join (cost=3684.32..2006488.68 rows=799814 width=34)
- Hash Cond: (l1.l_suppkey = supplier.s_suppkey)
- -> Seq Scan on lineitem l1 (cost=0.00..1919823.65 rows=19995351 width=8)
- Filter: (l_receiptdate > l_commitdate)
- -> Hash (cost=3634.32..3634.32 rows=4000 width=30)
- -> Hash Join (cost=1.32..3634.32 rows=4000 width=30)
- Hash Cond: (supplier.s_nationkey = nation.n_nationkey)
- -> Seq Scan on supplier (cost=0.00..3218.00 rows=100000 width=34)
- -> Hash (cost=1.31..1.31 rows=1 width=4)
- -> Seq Scan on nation (cost=0.00..1.31 rows=1 width=4)
- Filter: (n_name = ‘BRAZIL’::bpchar)
- -> Hash (cost=1769858.52..1769858.52 rows=59986052 width=8)
- -> Seq Scan on lineitem l2 (cost=0.00..1769858.52 rows=59986052 width=8)
- -> Index Scan using o_orderkey_idx on orders (cost=0.00..9.59 rows=1 width=4)
- Index Cond: (orders.o_orderkey = l1.l_orderkey)
- Filter: (orders.o_orderstatus = ‘F’::bpchar)
- -> Index Scan using l_orderkey_idx on lineitem l3 (cost=0.00..27.98 rows=52 width=8)
- Index Cond: (l3.l_orderkey = l1.l_orderkey)
- Filter: (l3.l_receiptdate > l3.l_commitdate)
Recent Comments