Re: About when we should setup index?

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.