Received: from taru.tm.ee (unknown [213.180.2.168])
	by postgresql.org (Postfix) with ESMTP id 1E5AD475914
	for <pgsql-hackers@postgresql.org>;
	Thu, 11 Jul 2002 15:46:41 -0400 (EDT)
Received: (from hannu@localhost) by taru.tm.ee (8.11.6/8.11.6) id
 g6BKm8L18764;
	Thu, 11 Jul 2002 22:48:08 +0200
X-Authentication-Warning: taru.tm.ee: hannu set sender to hannu@tm.ee using -f
Subject: Re: please help on query
From: Hannu Krosing <hannu@tm.ee>
To: Luis Alberto Amigo Navarro <lamigo@atc.unican.es>
Cc: pgsql-hackers@postgresql.org
In-Reply-To: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
References: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
X-Mailer: Ximian Evolution 1.0.3.99 
Date: 11 Jul 2002 22:48:08 +0200
Message-Id: <1026420488.18194.18.camel@taru.tm.ee>
Mime-Version: 1.0
X-Archive-Number: 200207/468
X-Sequence-Number: 24842

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