Re: Planner making poor choices?

On Fri, 2003-01-10 at 16:48, Tom Lane wrote:
> Mike Benoit <mikeb(at)netnation(dot)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.

select * from pg_stats where tablename = 'mail_aliases' and
attname='account_id';

avg_width=4
n_distinct=1833
most_common_vals={13275,21845,11402,5535,27252,16878,54262,4027,55189,38627}
most_common_freqs={0.0156667,0.01,0.009,0.00766667,0.00733333,0.00666667,0.00666667,0.00633333,0.00633333,0.006}
histogram_bounds={302,9225,13797,19183,29119,41098,48174,54048,59123,65082,73292}
correlation=-0.0468637

>
> 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

I'll give that a try and see how things change.

Thanks Tom.