Re: temporary indexes

["Jim C. Nasby" <jnasby(at)pervasive(dot)com>]

On Tue, Feb 28, 2006 at 09:44:08AM -0600, Kevin Grittner wrote:
> It struck me that it would be outstanding if the planner could
> recognize this sort of situation, and build a temporary index based on
> the snapshot of the data visible to the transaction.  It seems to me
> that the obvious downside of this would be the explosion in the number
> of permutations the planner would need to examine -- based not just on
> what indexes ARE there, but which ones it could build.  At a minimum,
> there would need to be a cost threshold below which it would not even
> consider the option.  (In this case, as long as the optimizer spent less
> than 13 hours and 50 minutes considering its options, we would have come
> out ahead.)

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.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461