I continue struggling with the TPC-R benchmarks and wonder if anyone
could help me optimize the query below. ANALYZE statistics indicate that
the query should run relatively fast, but it takes hours to complete. I
attached the query plan to this posting.
Thanks.
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 '%aquamarine%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc;
*************************************
This e-mail may contain privileged or confidential material intended for the named
recipient only.
If you are not the named recipient, delete this message and all attachments.
Unauthorized reviewing, copying, printing, disclosing, or otherwise using information
in this e-mail is prohibited.
We reserve the right to monitor e-mail sent through our network.
*************************************
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan "temp" (cost=18237.40..18237.41 rows=1 width=121)
-> Sort (cost=18237.40..18237.41 rows=1 width=121)
Sort Key: nation.n_name, date_part('year'::text, orders.o_orderdate)
-> Aggregate (cost=18237.38..18237.39 rows=1 width=121)
-> Group (cost=18237.38..18237.39 rows=1 width=121)
-> Sort (cost=18237.38..18237.38 rows=1 width=121)
Sort Key: nation.n_name, date_part('year'::text, orders.o_orderdate)
-> Nested Loop (cost=18232.96..18237.37 rows=1 width=121)
-> Hash Join (cost=18232.96..18234.34 rows=1 width=109)
Hash Cond: ("outer".n_nationkey = "inner".s_nationkey)
-> Seq Scan on nation (cost=0.00..1.25 rows=25 width=33)
-> Hash (cost=18232.95..18232.95 rows=1 width=76)
-> Nested Loop (cost=0.00..18232.95 rows=1 width=76)
Join Filter: ("inner".s_suppkey = "outer".l_suppkey)
-> Nested Loop (cost=0.00..18229.93 rows=1 width=68)
Join Filter: ("outer".p_partkey = "inner".l_partkey)
-> Nested Loop (cost=0.00..8021.14 rows=4 width=23)
-> Seq Scan on part (cost=0.00..8018.00 rows=1 width=4)
Filter: (p_name ~~ '%aquamarine%'::text)
-> Index Scan using i_ps_partkey on partsupp (cost=0.00..3.07 rows=5 width=19)
Index Cond: ("outer".p_partkey = partsupp.ps_partkey)
-> Index Scan using i_l_suppkey on lineitem (cost=0.00..2542.69 rows=634 width=45)
Index Cond: ("outer".ps_suppkey = lineitem.l_suppkey)
-> Index Scan using pk_supplier on supplier (cost=0.00..3.01 rows=1 width=8)
Index Cond: ("outer".ps_suppkey = supplier.s_suppkey)
-> Index Scan using pk_orders on orders (cost=0.00..3.01 rows=1 width=12)
Index Cond: (orders.o_orderkey = "outer".l_orderkey)
(27 rows)
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
