On 1/22/07, Harald Armin Massa <haraldarminmassa@xxxxxxxxx> wrote:
>> select * from table where table_id in (?, ?, ?, ?, ?, ?, ?, ...)
I usually try to rewrite this kind of queries to
select whatever from table t1 join
(select table_id from xxxxx where xxxxx) t2 using (table_id)
And 3 out of 4 this performs better on Oracle and PostgreSQL.
Would be curious why it does , but usually I am happy that it does:)
Because the results would be different than a subselect, less work = faster. One thing to point out is that a query of the form:
select ...
from foo
where id in (select id from bar where n=27)
Would normally result in a SORT UNIQUE for the "select id from bar where n=27" part. Where as:
select ...
from foo f1, (select id from bar where n=27) f2
where f1.id = f2.id
is the same as...
select ...
from foo f1, bar f2
where f2.n=27
and f1.id=f2.id
which would not result in a sort unique. In order to obtain the same results as a subselect you would need to group or distinct, and I would imagine the results would be the same as the IN..SUBSELECT
--
Chad
http://www.postgresqlforums.com/
