Re: Tuning to speed select

Reece,
We have some multi-column indexes. Speed of insert, update, and delete
are not an issue since this data is essentially static: write-once,
read-many.

As far as I can tell (from running actual tests) Postgres will not use a
multi-column index when the SELECT is constraining on only one of the
columns in the index. If I need a single column index, I create one. If
I need three columns in an index, I create a specific 3 column index.
Granted, my memory is fuzzy, and some of my testing was done on version
7.x and there may be improvements on version 8.x

-Tom

> ...
>
> I found multi-column indexes and clustering to be extremely beneficial
> in 7.4. I still use them in 8.1, but I haven't compared them
> extensively with equivalent queries that use bitmap index scans. The
> obvious downside of having more indexes is the additional time and
> space overhead during insert, update, or delete.
>
> ...
>
> -Reece
>
>--
>Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
>
>

--
Tom Laudeman
twl8n(at)virginia(dot)edu
(434) 924-2456
http://www.people.virginia.edu/~twl8n/
http://laudeman.com/