At 08:37 AM 11/30/00 -0500, mlw wrote:
>> mlw wrote:
>> > Why is a "select * from table1 where field in (select field from table2
>> > where condition )"
>> > is so dramatically bad compared to:
>> > "select * from table1, table2 where table1.field = table2.field and
>> > condition"
>Now, given the two components, each with very low costs, it chooses to
>do a sequential scan on the table. I don't get it. I have have been
>having no end of problems with Postgres' optimizer. It just seems to be
>brain dead at times. It is a huge point of frustration to me. I am tied
>to postgres in my current project, and I fear that I will not be able to
>implement certain features because of this sort of behavior.
But but but ...
Not only is the join faster, but it is more readable and cleaner SQL as
well. I would never write the query in its first form. I'd change the
second one slightly to "select table1.* from ...", though, since those
are apparently the only fields you want.
The optimizer should do a better job on your first query, sure, but why
don't you like writing joins?
- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
In response to
pgsql-hackers by date
|Next:||From: Hannu Krosing||Date: 2000-11-30 15:26:02|
|Subject: Re: SQL 'in' vs join.|
|Previous:||From: Tom Lane||Date: 2000-11-30 15:15:31|
|Subject: Re: compiling pg 7.0.3 on sco 5.0.5 |