Re: [HACKERS] please help on query

On Thu, 11 Jul 2002 19:40:46 +0200, "Luis Alberto Amigo Navarro"
<lamigo(at)atc(dot)unican(dot)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 condition
AND 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? ;-)

Servus
Manfred