Re: More grist for the PostgreSQL vs MySQL mill
| От | Chad Wagner |
|---|---|
| Тема | Re: More grist for the PostgreSQL vs MySQL mill |
| Дата | |
| Msg-id | 81961ff50701220524u613ef4c0uc75b9f0f8c979f5c@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: More grist for the PostgreSQL vs MySQL mill ("Harald Armin Massa" <haraldarminmassa@gmail.com>) |
| Ответы |
Re: More grist for the PostgreSQL vs MySQL mill
|
| Список | pgsql-general |
On 1/22/07, Harald Armin Massa <haraldarminmassa@gmail.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/
В списке pgsql-general по дате отправления: