Re: Weird query plan - Mailing list pgsql-general

From Dmitry Tkach
Subject Re: Weird query plan
Date
Msg-id 3F68D7FD.1020300@openratings.com
Whole thread Raw
In response to Re: Weird query plan  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
Tom Lane wrote:

>Dmitry Tkach <dmitry@openratings.com> writes:
>
>
>>>Hm.  You know, I wonder whether the query plans really are the same.
>>>One of the reasons why 7.3 and later show the qual conditions is that
>>>it was too easy to fall into the trap of assuming that plans of the same
>>>structure had the same conditions.  Try doing "explain verbose" and look
>>>to see if the plans look the same at that level of detail.
>>>
>>>
>
>
>
>>I am afraid that's too much detail for me to look at :-)
>>I have no clue what all that stuff means :-(
>>
>>
>
>Well, "diff" would've been enough to tell you they are different.  It
>looks to me like in the slow case the query plan is of the form
>
>Limit
>  ->  Nested Loop
>        ->  Index Scan using a_pkey on a
>        <<no index condition, ie full table scan>>
>        ->  Index Scan using b_pkey on b
>        b.id >= 7901288 and b.id = "outer".id
>
>which is a pretty silly plan to arrive at; are you by any chance running
>with enable_seqscan = off?
>
Ummm... yes, I am.
Sorry, I should have mentioned that earlier...

>
>I'm not certain why 7.2 would be picking this plan, esp. given that it
>is capable of generating the better plan.  It might be that it's got
>something to do with the low selectivity of the id >= 7901288 clause.
>In any case, if it's fixed in 7.3 I'm not going to worry too much about
>it ...
>
>
>
I don't know that actually - the 7.3 database I tried it on when it
worked was empty .... I just assumed, that since it generated the right
query plan it would work...
But then, I fugured out that it worked in 7.2 as well when I tried it
with just two empty tables :-(


Dima



pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Weird query plan
Next
From: Ron Johnson
Date:
Subject: High-volume shop uses PostgreSQL