Re: More grist for the PostgreSQL vs MySQL mill

On 1/22/07, Harald Armin Massa <haraldarminmassa(at)gmail(dot)com> 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/