On Thu, 2002-07-11 at 17:22, Luis Alberto Amigo Navarro wrote:
> I can't improve performance on this query:
You could try rewriting the IN's into = joins
or even use explicit INNER JOIN syntax to force certain plans
with a select inside another and depending on value of partsupp.partkey
it is really hard for optimiser to do anything else than to perform the
query for each row.
But it may help to rewrite
SELECT partsupp.suppkey FROM partsupp WHERE partsupp.partkey IN ( SELECT part.partkey FROM part WHERE
part.name like 'forest%' ) AND partsupp.availqty>( SELECT 0.5*(sum(lineitem.quantity)::FLOAT) FROM
lineitem WHERE lineitem.partkey=partsupp.partkey AND lineitem.suppkey=partsupp.partkey AND
lineitem.shipdate>=('1994-01-01')::DATE AND lineitem.shipdate<(('1994-01-01')::DATE+('1
year')::INTERVAL)::DATE ) )
into
SELECT partsupp.suppkey FROM partsupp, (SELECT part.partkey as partkey FROM part WHERE part.name like
'forest%' ) fp, (SELECT 0.5*(sum(lineitem.quantity)::FLOAT) as halfsum, partkey FROM lineitem WHERE
lineitem.partkey=partsupp.partkey AND lineitem.suppkey=partsupp.partkey AND
lineitem.shipdate>=('1994-01-01')::DATE AND lineitem.shipdate<(('1994-01-01')::DATE+('1
year')::INTERVAL)::DATE ) li WHERE partsupp.partkey = fp.partkey AND partsupp.partkey = li.partkey AND
partsupp.availqty> halfsum
if "lineitem" is significantly smaller than "partsupp"
But you really should tell us more, like how many lines does lineitem
and other tables have,
----------
Hannu