Re: Tuning to speed select

[Tom Laudeman <twl8n@xxxxxxxxxxxx>]

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@xxxxxxxxxxxx
(434) 924-2456
http://www.people.virginia.edu/~twl8n/
http://laudeman.com/