To: Ben Chobot <bench@silentmedia.com>
cc: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>,
	pgsql-performance@postgresql.org
Subject: Re: 8.1 -> 8.4 regression 
In-reply-to: <8D572A50-01C7-464B-8D0D-FCF19C53BC9C@silentmedia.com> 
References: <4B791AFF020000250002F2DD@gw.wicourts.gov>
	<8D572A50-01C7-464B-8D0D-FCF19C53BC9C@silentmedia.com>
Comments: In-reply-to Ben Chobot <bench@silentmedia.com>
	message dated "Mon, 15 Feb 2010 08:16:04 -0800"
Date: Mon, 15 Feb 2010 12:26:28 -0500
Message-ID: <1376.1266254788@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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