Re: Weird query plan

Dmitry Tkach <dmitry(at)openratings(dot)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?

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 ...

regards, tom lane