Re: Does "correlation" mislead the optimizer on large tables?

Ron Mayer <ron(at)intervideo(dot)com> writes:
> On a large tables, I think the "correlation" pg_stats field as calculated
> by "vacuum analyze" or "analyze" can mislead the optimizer.

If you look in the pghackers archives, you will find some discussion
about changing the equation that cost_index() uses to estimate the
impact of correlation on indexscan cost. The existing equation is
ad-hoc and surely wrong, but so far no one's proposed a replacement
that can be justified any better. If you've got such a replacement
then we're all ears...

> In particular, if I have a large table t with columns 'a','b','c', etc,
> and I cluster the table as follows:
> create table t_ordered as select * from t order by a,b;
> vacuum analyze t_ordered;
> Column "b" will (correctly) get a very low "correlation" in
> the pg_stats table -- but I think the optimizer would do better
> assuming a high correlation because similar 'b' values are still
> grouped closely on the same disk pages.

How would that be? They'll be separated by the stride of 'a'.

It seems likely to me that a one-dimensional correlation statistic may
be inadequate, but I haven't seen any proposals for better stats.

regards, tom lane