Re: 8.1 -> 8.4 regression

Awesome, that did the trick. Thanks Tom! So I understand better, why is my case not the normal, better case?

(I assume the long-term fix is post-9.0, right?)

On Feb 15, 2010, at 9:26 AM, Tom Lane wrote:

> Ben Chobot <bench(at)silentmedia(dot)com> writes:
>> On Feb 15, 2010, at 7:59 AM, Kevin Grittner wrote:
>>> Could you show the query, along with table definitions (including
>>> indexes)?
>
>> Oh, yeah, I suppose that would help. :)
>
>> http://wood.silentmedia.com/bench/query_and_definitions
>
> It looks like the problem is that the EXISTS sub-query is getting
> converted into a join; which is usually a good thing but in this case it
> interferes with letting the users table not be scanned completely.
> The long-term fix for that is to support nestloop inner indexscans where
> the index key comes from more than one join level up, but making that
> happen isn't too easy.
>
> In the meantime, I think you could defeat the "optimization" by
> inserting LIMIT 1 in the EXISTS sub-query.
>
> regards, tom lane