PostgreSQL partitioned index tip

Did you know PostgreSQL can make use of a partitioned index? That is an index with a WHERE clause. For example:
CREATE INDEX bigidx1 ON TABLE bigtable1 WHERE bigfield1 < 10000

That effectively creates a better index than a whole column index. The planner can check the WHERE clause of the SELECT and choose a smaller index. Smaller means faster :)

Use with caution and ALWAYS benchmark the new indexes. This type of index is truly useful when you have a good part of the column data (say 70%) with a unique value and the rest with a very disperse value set. And the table is very big, obviously.

Hope it helps.