From: Manfred Koizar <mkoi-pg@aon.at>
To: "Luis Alberto Amigo Navarro" <lamigo@atc.unican.es>
Cc: <pgsql-sql@postgresql.org>
Subject: Re: [HACKERS] please help on query
Date: Thu, 11 Jul 2002 20:44:58 +0200
Message-ID: <47jriu8760vo9n9a4ffvtl165ebe7rvksj@4ax.com>
References: <005e01c228ee$bd1913e0$cab990c1@atc.unican.es>
	<gu9riu8qp28ifp40qsva7b9puif47ihi5b@4ax.com>
	<007c01c22902$16e699c0$cab990c1@atc.unican.es>
In-Reply-To: <007c01c22902$16e699c0$cab990c1@atc.unican.es>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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