From: | Richard Ray <rray(at)mstc(dot)state(dot)ms(dot)us> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: Can someone explain the problem with this select |
Date: | 2006-12-05 21:21:41 |
Message-ID: | Pine.LNX.4.64.0612051519450.20963@rray.drdc.mstc.ms.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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(at)mstc(dot)state(dot)ms(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Ragnar | 2006-12-05 23:07:50 | Re: Using Control Flow Functions in a SELECT Statement |
Previous Message | Richard Broersma Jr | 2006-12-05 21:15:28 | Re: Can someone explain the problem with this select |