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 <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
