From: | Manfred Koizar <mkoi-pg(at)aon(dot)at> |
---|---|
To: | "Luis Alberto Amigo Navarro" <lamigo(at)atc(dot)unican(dot)es> |
Cc: | <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: [HACKERS] please help on query |
Date: | 2002-07-11 18:44:58 |
Message-ID: | 47jriu8760vo9n9a4ffvtl165ebe7rvksj@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | knut.suebert | 2002-07-11 18:46:36 | Re: I am being interviewed by OReilly |
Previous Message | Joe Conway | 2002-07-11 18:34:04 | Re: workaround for lack of REPLACE() function |
From | Date | Subject | |
---|---|---|---|
Next Message | Jie Liang | 2002-07-11 19:48:09 | Re: pg_restore cannot restore index |
Previous Message | Jan Wieck | 2002-07-11 18:38:40 | Re: list of tables ? -update to question ... |