Mike Benoit <mikeb@netnation.com> writes:
> Postgres (v7.3.1) doesn't seem to making use of indexes when it clearly
> is the proper choice to make. I've ran in to this problem several times,
> but "alter table statistics" has always solved the problem. However it
> didn't seem to help in this case.
It wouldn't, since the planner seems to be doing a fine job at
estimating the row count already. I think it may be dropping the ball
on correlation: is this table pretty well clustered by account_id?
It's hard to see how the indexscan could be so cheap if there's not
any clustering, because it would probably have to hit most of the 789
pages in the table in order to retrieve 624 randomly-scattered rows.
It would be useful to look at the number of blocks actually read
(you could investigate that by turning on the statistics collector),
and to see what the correlation value is for account_id in pg_stats.
Another factor is that with such a small table (only about six Mb),
the whole table is probably sitting in kernel disk cache. I'm not
sure if you really want to optimize the behavior for that case,
but if you do, try lowering random_page_cost. For an all-in-RAM
scenario, random_page_cost = 1 is the most accurate setting.
regards, tom lane