Re: please help on query

On Thu, 2002-07-11 at 17:22, Luis Alberto Amigo Navarro wrote:
> I can't improve performance on this query:

This _may_ work.

SELECT
supplier.name,
supplier.address
FROM
supplier,
nation,
WHERE supplier.suppkey IN (
SELECT part.partkey
FROM part
WHERE part.name like 'forest%'
INNER JOIN partsupp ON part.partkey=partsupp.partkey
INNER JOIN (
SELECT 0.5*(sum(lineitem.quantity)::FLOAT) as halfsum
FROM lineitem
WHERE lineitem.partkey=partsupp.partkey
AND shipdate >= '1994-01-01'
AND shipdate < '1995-01-01'
) li ON partsupp.availqty > halfsum
)
AND supplier.nationkey=nation.nationkey
AND nation.name='CANADA'
ORDER BY supplier.name;

---------------
Hannu