From: Tom Lane <tgl@sss.pgh.pa.us>
To: Richard Ray <rray@mstc.state.ms.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Can someone explain the problem with this select
Date: Tue, 05 Dec 2006 16:14:23 -0500
Message-ID: <11220.1165353263@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.LNX.4.64.0612051316260.20963@rray.drdc.mstc.ms.gov>
References: <Pine.LNX.4.64.0612051316260.20963@rray.drdc.mstc.ms.gov>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, rray@mstc.state.ms.us
Subject: Re: Can someone explain the problem with this select
In-Reply-To: <11220.1165353263@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox