From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ben Chobot <bench@silentmedia.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: 8.1 -> 8.4 regression
Date: Mon, 15 Feb 2010 12:26:28 -0500
Message-ID: <1376.1266254788@sss.pgh.pa.us> (raw)
In-Reply-To: <8D572A50-01C7-464B-8D0D-FCF19C53BC9C@silentmedia.com>
References: <4B791AFF020000250002F2DD@gw.wicourts.gov>
<8D572A50-01C7-464B-8D0D-FCF19C53BC9C@silentmedia.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, bench@silentmedia.com, Kevin.Grittner@wicourts.gov
Subject: Re: 8.1 -> 8.4 regression
In-Reply-To: <1376.1266254788@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox