Re: Better estimates of index correlation

> I don't understand, are they going years between vacuums because their
> data is static? In which case the index correlation won't change. Or
> is it append-only, in which case I suspect the newly appended data is
> likely to have the same correlation as the old data.

Append-only. And yes, one could assume that correlation wouldn't change
frequently. However, it may change more frequently than vacuums occur
-- I'm not exaggerating about "years". I have several clients with
large databases where they have log tables which only get vacuumed for
XID wraparound, once every 2 years or so.

There's also the question of how we get correlation stats for a new
index/table, or one which has just been upgraded. Requiring a full DB
vacuum isn't practical for those using pg_upgrade.

> But is there
> anything stopping us from doing some sort of ANALYZE-style sample of
> the index pages as well?

This would be ideal. Or even a separate command to scan the indexes
only to collect correlation data. Since the indexes are 20X to 100X
smaller than the tables (usually), it may be practical to full-scan them
even if we can't do the same with the table.

--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com