Re: temporary indexes?

On 10/22/15 12:36 PM, Tom Lane wrote:
> Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> writes:
>> On 10/21/15 3:28 PM, Jonathan Vanasco wrote:
>>> Transactions and table-locking issues are probably why temporary indexes don't exist.
>
>> I think it's more that no one has proposed it until now. It probably
>> wouldn't be terribly hard to add them... the biggest issue would
>> probably be changing the buffer management code so it didn't assume that
>> a temporary relation went into temporary buffers.
>
> Uh, why would you do that? You'd be throwing away one of the principal
> performance advantages of temp tables.

This would be for temporary *indexes* on permanent 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.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com