Re: build multiple indexes in single table pass?

On Tue, 1 Apr 2008, Andrew Dunstan wrote:

> I don't know if this has come up before exactly, but is it possible that we 
> could get a performance gain from building multiple indexes from a single 
> sequential pass over the base table?

It pops up regularly, you might even have walked by a discussion of this 
idea with myself, Jan, and Jignesh over the weekend.  Jignesh pointed out 
that index creation was a major drag on his PostgreSQL benchmarking 
operations and I've run into that myself.  I have a large dataset and 
creating a simple index takes around 70% of the time it takes to load the 
data in the first place, his multiple index tables took multiples of load 
time to index.  Considering that the bulk load isn't exactly speedy either 
this gives you an idea how much room for improvement there is.

The idea we were bouncing around went a step past that and considered 
this:  if you have good statistics on a table, and you have a sample set 
of queries you want to execute against it, how would you use that 
information to plan what indexes should be created?  Needing to be able to 
create multiple indexes at once efficiently was an implementation detail 
to pull that off.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD