Received: from bender.toppoint.de (bender.toppoint.de [195.244.243.2]) by hub.org (8.9.3/8.9.3) with ESMTP id WAA72423 for ; Tue, 18 Jan 2000 22:08:32 -0500 (EST) (envelope-from marten@feki.toppoint.de) Received: (from uucp@localhost) by bender.toppoint.de (8.9.3/8.9.3) id EAA15018; Wed, 19 Jan 2000 04:04:25 +0100 (MET) >Received: (from marten@localhost) by feki.toppoint.de (8.9.3/8.9.3/SuSE Linux 8.9.3-0.1) id HAA05729; Tue, 18 Jan 2000 07:53:33 +0100 From: Marten Feldtmann Message-Id: <200001180653.HAA05729@feki.toppoint.de> Subject: Re: [SQL] index usage ... strange !? In-Reply-To: <22018.948148017@sss.pgh.pa.us> from Tom Lane at "Jan 17, 2000 05:26:57 pm" To: Tom Lane Date: Tue, 18 Jan 2000 07:53:33 +0100 (CET) CC: pgsql-sql@postgreSQL.org X-Mailer: ELM [version 2.4ME+ PL60 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII > Marten Feldtmann writes: > > SELECT AO,AT,AV FROM P3AT > > WHERE EXISTS( SELECT AO FROM P3AT WHERE AV='12' AND AT=12 AND CI=17) > > Um ... I dunno what you are trying to accomplish, but this query > almost certainly doesn't do what you want. Since the inner query > is independent of the outer, you will get back either all the rows > of P3AT (if the inner query yields rows) or none (if it doesn't). > > The plan you quote is perfectly reasonable for this query... > the machine is even bright enough to figure out that it only > needs to evaluate the subquery once. > > regards, tom lane > Yes, this seems to be a wrong statement. Actually I would write it like: select AO,AT,AV FROM P3AT WHERE AO IN (SELECT AO FROM P3AT WHERE ...) but as PostgreSQL does not like "IN" very much I rewrote it to the wrong statement above. select P3AT.AO,P3AT.AT,P3AT.AV FROM P3AT,P3AT as B WHERE (B.AO=P3AT.AO) AND (B.AV='12') AND (B.AT=12) AND (B.CI=17) Therefore I have to rewrite the sql statement above to: SELECT P3AT.AV,P3AT.AT,P3AT.AV FROM P3AT WHERE EXISTS( SELECT B.AO FROM P3AT as B WHERE B.AO=P3AT.AO AND ..... ) Right ? Marten Feldtmann