To: "Jim C. Nasby" <jnasby@pervasive.com>
cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>,
 pgsql-hackers@postgresql.org,
	pgsql-performance@postgresql.org
Subject: Re: [PERFORM] temporary indexes 
In-reply-to: <20060228164515.GS82012@pervasive.com> 
References: <44041B68.EE98.0025.0@wicourts.gov>
	<20060228164515.GS82012@pervasive.com>
Comments: In-reply-to "Jim C. Nasby" <jnasby@pervasive.com>
	message dated "Tue, 28 Feb 2006 10:45:15 -0600"
Date: Tue, 28 Feb 2006 12:05:48 -0500
Message-ID: <16076.1141146348@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"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