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
ONpartsupp.availqty > halfsum ) AND supplier.nationkey=nation.nationkey AND nation.name='CANADA'
ORDER BY supplier.name;
---------------
Hannu