Re: Can someone explain the problem with this select

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
outerjoin comments on (ss.doc_num = comments.doc_num);
 
        regards, tom lane