Received: from bonito.atc.unican.es (bonito.atc.unican.es [193.144.185.218])
	by postgresql.org (Postfix) with ESMTP id EF36B475C1E
	for <pgsql-sql@postgresql.org>; Thu, 11 Jul 2002 13:36:38 -0400 (EDT)
Received: from ALIGOTE (aligote [193.144.185.202])
	by bonito.atc.unican.es (8.11.0/8.8.7) with SMTP id g6BHaYb09187;
	Thu, 11 Jul 2002 19:36:34 +0200
Message-ID: <007c01c22902$16e699c0$cab990c1@atc.unican.es>
From: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
To: "Manfred Koizar" <mkoi-pg@aon.at>
Cc: <pgsql-sql@postgresql.org>
References: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
	<gu9riu8qp28ifp40qsva7b9puif47ihi5b@4ax.com>
Subject: Re: [HACKERS] please help on query
Date: Thu, 11 Jul 2002 19:40:46 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 6.00.2600.0000
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000
X-Archive-Number: 200207/182
X-Sequence-Number: 8561

I've tried
SELECT
 supplier.name,
 supplier.address
FROM
 supplier,
 nation,
 lineitem
WHERE
 EXISTS(
  SELECT
   partsupp.suppkey
  FROM
   partsupp,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
   AND EXISTS(
    SELECT
     part.partkey
    FROM
     part
    WHERE
     part.name like 'forest%'
     )
  GROUP BY partsupp.partkey,partsupp.suppkey
  HAVING min(availqty)>(0.5*(sum(lineitem.quantity)::FLOAT))
  )
 AND supplier.nationkey=nation.nationkey
 AND nation.name='CANADA'
ORDER BY
 supplier.name;

as you said and something is wrong
Sort  (cost=1141741215.35..1141741215.35 rows=2400490000 width=81)
  InitPlan
    ->  Aggregate  (cost=0.00..921773.85 rows=48 width=24)
          InitPlan
            ->  Seq Scan on part  (cost=0.00..8561.00 rows=1 width=4)
          ->  Group  (cost=0.00..921771.44 rows=481 width=24)
                ->  Result  (cost=0.00..921769.04 rows=481 width=24)
                      ->  Merge Join  (cost=0.00..921769.04 rows=481
width=24)
                            ->  Index Scan using partsupp_pkey on partsupp
(cost=0.00..98522.75 rows=800000 width=12)
                            ->  Index Scan using lsupp_index on lineitem
(cost=0.00..821239.91 rows=145 width=12)
  ->  Result  (cost=1.31..112888690.31 rows=2400490000 width=81)
        ->  Nested Loop  (cost=1.31..112888690.31 rows=2400490000 width=81)
              ->  Hash Join  (cost=1.31..490.31 rows=400 width=81)
                    ->  Seq Scan on supplier  (cost=0.00..434.00 rows=10000
width=77)
                    ->  Hash  (cost=1.31..1.31 rows=1 width=4)
                          ->  Seq Scan on nation  (cost=0.00..1.31 rows=1
width=4)
              ->  Seq Scan on lineitem  (cost=0.00..222208.25 rows=6001225
width=0)

where might be my mistake
Thanks and regards
----- Original Message -----
From: "Manfred Koizar" <mkoi-pg@aon.at>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: <pgsql-sql@postgresql.org>
Sent: Thursday, July 11, 2002 6:47 PM
Subject: Re: [HACKERS] please help on query


> [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;
>