Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 2ABF59FA4A5 for ; Tue, 5 Dec 2006 17:14:39 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 42260-02 for ; Tue, 5 Dec 2006 17:14:27 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id B82279FA173 for ; Tue, 5 Dec 2006 17:14:27 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id kB5LENQv011221; Tue, 5 Dec 2006 16:14:23 -0500 (EST) To: Richard Ray cc: pgsql-sql@postgresql.org Subject: Re: Can someone explain the problem with this select In-reply-to: References: Comments: In-reply-to Richard Ray message dated "Tue, 05 Dec 2006 14:33:01 -0600" Date: Tue, 05 Dec 2006 16:14:23 -0500 Message-ID: <11220.1165353263@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200612/59 X-Sequence-Number: 27015 Richard Ray 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