From: | Marten Feldtmann <marten(at)feki(dot)toppoint(dot)de> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] index usage ... strange !? |
Date: | 2000-01-18 06:53:33 |
Message-ID: | 200001180653.HAA05729@feki.toppoint.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> Marten Feldtmann <marten(at)feki(dot)toppoint(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | omid omoomi | 2000-01-18 10:48:11 | Re: [SQL] Ordering a date_part() query ... |
Previous Message | Tom Lane | 2000-01-18 05:06:04 | Re: [SQL] ordering operator for bytea |