Re: Tuning to speed select

On Wed, 2006-08-09 at 16:54 -0400, Tom Laudeman wrote:

> Great suggestion. I've read about CLUSTER, but never had a chance to
> use it. The only problem is that this table with 9 million records has
> 5 or 6 indexes. It is hard to pick the most used, but I'll bet CLUSTER
> will make at least one of the queries run very fast, especially for an
> index with a small number of distinct values.

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.

The approach I took to design multi-column indexes was to run explain on
representative queries and look for seq scans. The seq scans indicate
which columns /might/ be well-served by indexes. In 7.4, the order of
indexed columns was important. (I saw something in the 8.1 release notes
that made me think that this was no longer true, but I haven't verified
that.)

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0