Re: [HACKERS] please help on query - Mailing list pgsql-sql

I've tried
SELECTsupplier.name,supplier.address
FROMsupplier,nation,lineitem
WHEREEXISTS( 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+('1year')::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.nationkeyAND nation.name='CANADA'
 
ORDER BYsupplier.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=48width=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)       ->
NestedLoop  (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.31rows=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;
>