Re: Better estimates of index correlation

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Mon, Mar 14, 2011 at 10:38 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Note that we could remove the correlation
>> calculations from ANALYZE altogether.

> Only if you don't mind having them only get updated when somebody
> vacuums. If a table is mostly getting inserted into, it may not get
> vacuumed very often (or possibly even - never).

No, I don't mind that. Index correlation is a pretty second-order stat,
and most of the time it'll be just fine if the estimate stays at the
default zero. The situation that's problematic is where you have a
very-far-from-zero number for one index and no estimate for another,
because that can incorrectly bias the planner to prefer the first index;
which is what I think is happening in Surcombe's case. The approach
I'm describing would guarantee that all indexes of a table get their
correlation estimates updated at the same time. (Memo to self: we'd
also want btbuild to compute this stat, so that a newly created index
doesn't start out at a disadvantage compared to others.)

[ thinks for a bit... ] Although there *is* a small practical problem
with having VACUUM update pg_statistic: a plain VACUUM never takes out
an XID. I guess we could have it do the update nontransactionally, the
same way it updates relpages/reltuples.

regards, tom lane