From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: Jim C. Nasby <jnasby@pervasive.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-hackers@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] temporary indexes
Date: Tue, 28 Feb 2006 15:15:31 -0600
Message-ID: <44046913.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <16076.1141146348@sss.pgh.pa.us>
References: <44041B68.EE98.0025.0@wicourts.gov>
<20060228164515.GS82012@pervasive.com>
<20060228164515.GS82012@pervasive.com>
<16076.1141146348@sss.pgh.pa.us>
>>> On Tue, Feb 28, 2006 at 11:05 am, in message
<16076.1141146348@sss.pgh.pa.us>,
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> The limiting factor is that EXISTS subplans
> aren't flattened ... and once that's fixed, I doubt the example
would
> need any new kind of join support.
I rewrote the query to use IN predicates rather than EXISTS predicates,
and the cost estimates look like this:
EXISTS, no index: 1.6 billion
EXISTS, with index: 0.023 billion
IN, no index: 13.7 billion
IN, with index: 10.6 billion
At least for the two EXISTS cases, the estimates were roughly accurate.
These plans were run against the data after the fix, but analyze has
not been run since then, so the estimates should be comparable with the
earlier post.
I'm not used to using the IN construct this way, so maybe someone can
spot something horribly stupid in how I tried to use it.
-Kevin
Attachments:
datafix-in-plan1.txt (application/octet-stream, 4.2K) download
datafix-in-plan2.txt (application/octet-stream, 5.3K) download
datafix-in.qry (application/octet-stream, 2.0K) download
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: Kevin.Grittner@wicourts.gov, jnasby@pervasive.com, tgl@sss.pgh.pa.us, pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] temporary indexes
In-Reply-To: <44046913.EE98.0025.0@wicourts.gov>
* 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