From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jim C. Nasby <jnasby@pervasive.com>
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: pgsql-hackers@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] temporary indexes
Date: Tue, 28 Feb 2006 12:05:48 -0500
Message-ID: <16076.1141146348@sss.pgh.pa.us> (raw)
In-Reply-To: <20060228164515.GS82012@pervasive.com>
References: <44041B68.EE98.0025.0@wicourts.gov>
<20060228164515.GS82012@pervasive.com>
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> FWIW, Sybase supported something similar a long time ago. It had the
> ability to build a temporary 'clustered table' (think index organized
> table) when there was enough benefit to do so. This is actually
> much easier to make happen inside a transaction for us, because we don't
> need to keep visibility information around. There's probably also some
> index metadata that could be done away with. Perhaps the materialize
> node could be made to allow this.
How does what you describe differ from a merge join? Or a hash join,
if you imagine the temp index as being a hash rather than btree index?
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. 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. The problem only becomes apparent at the next
level up, where those subplans are going to be repeated a huge number of
times ---- but the subplan plan is already chosen and won't be changed.
So even if we invented a temp-index facility, it would fail to be
applied in Kevin's example. 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.
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, jnasby@pervasive.com, Kevin.Grittner@wicourts.gov, pgsql-hackers@postgresql.org
Subject: Re: [PERFORM] temporary indexes
In-Reply-To: <16076.1141146348@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