Re: Can someone explain the problem with this select

I've been foolin with this for a couple of days
Sometimes you just have to ask
Thanks gentlemen

On Tue, 5 Dec 2006, Tom Lane wrote:

> Richard Ray <rray@mstc.state.ms.us> writes:
>> dcc=# EXPLAIN ANALYZE select * from documents left outer join comments on
>> (documents.doc_num = comments.doc_num) where documents.doc_num in (select
>> doc_num from documents limit 10);
>> [ is slow ]
>
> This isn't your fault, it's an optimizer limitation: PG < 8.2 can't
> reorder outer joins relative to regular joins, and the IN with a
> sub-select is a kind of regular join.  So it's forming the whole
> outer-join result and then joining to the sub-select :-(
>
> This is fixed in 8.2, released today, so perhaps upgrading is the
> thing for you to do.  Alternatively, you can contort the query to
> get the IN restriction inside the outer join:
>
> select * from
>  (select * from documents
>   where documents.doc_num in (select doc_num from documents limit 10)) ss
>  left outer join comments on (ss.doc_num = comments.doc_num);
>
>             regards, tom lane
>