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 11:36:28 -0600
Message-ID: <440435BC.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 issue at hand really has nothing to do with temp indexes, it's
with
> the constrained way that the planner deals with EXISTS subplans.
Yet when the index exists, the query is optimized well.
> The
> subplans themselves are cheap enough, even in the poorly- indexed
> variant, that the planner would certainly never have decided to
create
> an index to use for them.
That depends. If the planner was able to generate hypothetical index
descriptions which might be useful, and analyze everything based on
those (adding in creation cost, of course) -- why would it not be able
to come up with the plan which it DID use when the index existed.
> 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.
<digression>
I'm all for that. So far, we've been going after the low-hanging fruit
in our use of PostgreSQL. When we get to the main applications, we're
going to be dealing with a lot more in the way of EXISTS clauses. The
product we're moving from usually optimized an IN test the same as the
logically equivalent EXISTS test, and where a difference existed, it
almost always did better with the EXISTS -- so we encouraged application
programmers to use that form. Also, EXISTS works in situations where
you need to compare on multiple columns, so it is useful in many
situations where EXISTS or MIN/MAX techniques just don't work.
</digression>
If fixing this would allow hash or merge techniques to cover this as
well as the index did, and that is true in a more general sense (not
just for this one example), then temporary indexes would clearly not
have any value.
-Kevin
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: <440435BC.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