Sure, below is the query. I attached the plan to this posting.
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 '%green%'
) as profit
group by
nation,
o_year
order by
nation,
o_year desc;
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Wednesday, October 01, 2003 11:42 AM
To: Oleg Lebedev; scott.marlowe
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] TPC-R benchmarks
Oleg,
> The output of the query should contain about 200 rows. So, I guess the
> planer is off assuming that the query should return 1 row.
Oh, also did you post the query before? Can you re-post it with the
planner
results?
--
Josh Berkus
Aglio Database Solutions
San Francisco
*************************************
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.
*************************************