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(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD