pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Marten Feldtmann <marten@feki.toppoint.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] index usage ... strange !?
Date: Tue, 18 Jan 2000 07:53:33 +0100 (CET)
Message-ID: <200001180653.HAA05729@feki.toppoint.de> (raw)
In-Reply-To: <22018.948148017@sss.pgh.pa.us>

> Marten Feldtmann <marten@feki.toppoint.de> 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
 




view thread (5+ messages)  latest in thread

Message-ID: <200001180653.HAA05729@feki.toppoint.de>
Permalink:  ../200001180653.HAA05729@feki.toppoint.de/
Also on:    postgresql.org/message-id/200001180653.HAA05729@feki.toppoint.de

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: marten@feki.toppoint.de, tgl@sss.pgh.pa.us
  Subject: Re: [SQL] index usage ... strange !?
  In-Reply-To: <200001180653.HAA05729@feki.toppoint.de>
  References: <22018.948148017@sss.pgh.pa.us> <200001180653.HAA05729@feki.toppoint.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox