Emi Lu writes:
One more thing to consider. If you have a column with lots of repeated
values and a handfull of selective values, you could use a partial index.
http://www.postgresql.org/docs/8.0/interactive/indexes-partial.html
For example imagine you have an accounts table like
Accounts
account_id integer
name varchar
special_cust boolean
Where special_cust are customers that subscribe to some premiun and
expensive service.. but there are very few customers that do... you could do
an index like:
CREATE INDEX accuonts_special_cust_idx ON accounts (special_cust)
WHERE special_cust;
In that case if you wanted to see a list of premiun accounts, that index
should bring those records quickly.
See the link above for examples and links to a couple of papers explaining
why/when you want to use a partial index. In particular the document "The
case for partial indexes" , pages 3 and up.