Re: [HACKERS] please help on query

On Thu, 11 Jul 2002 19:40:46 +0200, "Luis Alberto Amigo Navarro"
<lamigo@atc.unican.es> wrote:
>I've tried
[reformatted to fit on one page]
| SELECT supplier.name, supplier.address
| FROM supplier, nation, lineitem
You already found out that you do not need lineitem here.

| WHERE EXISTS(
|   SELECT partsupp.suppkey
|   FROM partsupp,lineitem
|   WHERE
|    lineitem.partkey=partsupp.partkey
|    AND lineitem.suppkey=partsupp.partkey
I still don't believe this  suppkey=partkey

|    AND lineitem.shipdate [...]
|    AND EXISTS( SELECT part.partkey
|                FROM part WHERE part.name like 'forest%')
This subselect gives either true or false, but in any case always the
same result.  You might want to add a conditionAND part.partkey=partsupp.partkey

Are you sure partkey is not unique?  If it is unique you can replace
this subselect by a join.

|   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)

The cost is now only 1141741215.35 compared to 2777810917708.17
before;  this is an improvement factor of more than 2000.  So what's
your problem? ;-)

ServusManfred