Re: Better estimates of index correlation

Robert Haas <robertmhaas@gmail.com> writes:
> On Mon, Mar 14, 2011 at 10:38 AM, Tom Lane <tgl@sss.pgh.pa.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