Jim Nasby <Jim.Nasby@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
