Jim Nasby <Jim.Nasby@BlueTreble.com> writes:
> On 10/22/15 12:36 PM, Tom Lane wrote:
>> Uh, why would you do that? You'd be throwing away one of the principal
>> performance advantages of temp tables.
> Actually, it depends on what behavior you'd expect from a temporary
> index. If it was only going to exist for the duration of a REPEATABLE
> READ transaction it wouldn't care about concurrent DML on the table, so
> the index could use temp buffers and the index creation could take
> shortcuts as well, since it'd only need to index tuples that satisfy
> that transaction's snapshot.
> OTOH, if you had anything looser than that the index would need to
> operate the same as a regular index, so all other backends would need to
> update it.
Hmm, good point. Still, that means that such indexes would be
considerably more expensive than true temp indexes, because of the
concurrency and locking requirements, which would be just like regular
indexes. AFAICS it would be better to think of them as unlogged indexes,
because suppressing WAL logging is all you could get out of it.
FWIW, I don't find much attraction in the idea of building an index for
use by a single query. There basically isn't any scenario where that's
going to beat running a plan that doesn't require the index. The value of
an index is generally to avoid a whole-table scan and/or a sort, but
you'll necessarily pay those costs to make the index.
regards, tom lane