Ben Chobot <bench@silentmedia.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