Re: Problem with index not always being used

"Rob Tester" <robtester(at)gmail(dot)com> writes:
> SELECT * FROM STUFF WHERE state=12; --causes a seq scan of the table
> where
> SELECT * FROM STUFF WHERE state=16 --Uses the index.

This behavior is intended and appropriate, if there are lots of rows
with state=12 and not many with state=16. As an example, if nearly the
whole table had state=12 you would certainly not wish it to use an
indexscan for that. The correct way to think about your gripe is that
the planner's cutting over at the wrong row density. There are a couple
of places to look for a solution:

First, are the planner's estimated row counts for both cases reasonably
close to reality, according to EXPLAIN ANALYZE? If not, you may need to
increase the statistics target (either globally with
default_statistics_target or for the state column with ALTER TABLE).
Don't forget to re-ANALYZE the table after changing the target.

If the statistics are good then you need to fool with the planner's cost
parameters to get it to make decisions that reflect your environment.
Decreasing random_page_cost is usually the thing to do if it's choosing
seqscans too readily. But be wary of choosing a new value on the basis
of just one test case.

You can find a lot about this in the pgsql-performance list archives,
and there are several relevant articles at techdocs:
http://www.postgresql.org/docs/techdocs

regards, tom lane