Received: from email04.aon.at (WARSL402PIP5.highway.telekom.at [195.3.96.79])
	by postgresql.org (Postfix) with SMTP id 18D694759BF
	for <pgsql-sql@postgresql.org>; Thu, 11 Jul 2002 12:48:16 -0400 (EDT)
Received: (qmail 335918 invoked from network); 11 Jul 2002 16:48:20 -0000
Received: from m152p011.dipool.highway.telekom.at (HELO cantor)
	([62.46.8.235]) (envelope-sender <mkoi-pg@aon.at>)
	by qmail5rs.highway.telekom.at (qmail-ldap-1.03) with SMTP
	for <lamigo@atc.unican.es>; 11 Jul 2002 16:48:20 -0000
From: Manfred Koizar <mkoi-pg@aon.at>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: [HACKERS] please help on query
Date: Thu, 11 Jul 2002 18:47:03 +0200
Message-ID: <gu9riu8qp28ifp40qsva7b9puif47ihi5b@4ax.com>
References: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
In-Reply-To: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
X-Mailer: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Archive-Number: 200207/166
X-Sequence-Number: 8545

[moving to pgsql-sql]
On Thu, 11 Jul 2002 17:22:14 +0200, "Luis Alberto Amigo Navarro"
<lamigo@atc.unican.es> wrote:
>I can't improve performance on this query:
>
>SELECT
> supplier.name,
> supplier.address
>FROM
> supplier,
> nation
>WHERE
> supplier.suppkey IN(
>  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
                                    ^^^^^^^
                                    suppkey ???
>     AND lineitem.shipdate>=('1994-01-01')::DATE
>     AND lineitem.shipdate<(('1994-01-01')::DATE+('1 year')::INTERVAL)::DATE
>     )
>  )
> AND supplier.nationkey=nation.nationkey
> AND nation.name='CANADA'
>ORDER BY
> supplier.name;

Luis,
rules of thumb: "Avoid subselects; use joins!" and "If you have to use
subselects, avoid IN, use EXISTS!"

Let's try.  If partkey is unique in part, then
|   FROM partsupp
|   WHERE partsupp.partkey IN (SELECT part.partkey

can be replaced by
    FROM partsupp ps, part p
    WHERE ps.partkey = p.partkey

or
    partsupp ps INNER JOIN part p
      ON (ps.partkey = p.partkey AND p.name LIKE '...')

When we ignore "part" for now, your subselect boils down to

|  SELECT partsupp.suppkey
|  FROM partsupp
|  WHERE partsupp.availqty > (
|    SELECT 0.5*(sum(lineitem.quantity)::FLOAT)
|    FROM lineitem
|    WHERE lineitem.partkey=partsupp.partkey
|      AND lineitem.suppkey=partsupp.suppkey
|      AND lineitem.shipdate BETWEEN ... AND ...
|     )

which can be rewritten to (untested)

  SELECT ps.suppkey
  FROM partsupp ps, lineitem li
  WHERE li.partkey=ps.partkey
    AND li.suppkey=ps.suppkey
    AND lineitem.shipdate BETWEEN ... AND ...
  GROUP BY ps.partkey, ps.suppkey
  HAVING min(ps.availqty) > 0.5*(sum(lineitem.quantity)::FLOAT)
         ^^^
         As all ps.availqty are equal in one group, you can as well
use max() or avg().

Now we have left only one IN:
| WHERE supplier.suppkey IN (
|  SELECT partsupp.suppkey FROM partsupp WHERE <condition> )

Being to lazy to find out, if this can be rewritten to a join, let`s
apply rule 2 here:

  WHERE EXISTS (
    SELECT ... FROM partsupp ps
    WHERE supplier.suppkey = ps.suppkey
      AND <condition> )

HTH, but use with a grain of salt ...

>Sort  (cost=2777810917708.17..2777810917708.17 rows=200 width=81)
             ^^^^^^^^^^^^^^^^
BTW, how many years are these? :-)

Servus
 Manfred