Re: Moving postgresql.conf tunables into 2003...

Sean Chittenden <sean@chittenden.org> writes:
>> If you CLUSTER on an index and then ANALYSE, you get a correlation of
>> 1.0 (== optimum) for the first column of the index.

> Correlating of what to what?  Of data to nearby data?  Of data to
> related data (ie, multi-column index?)? Of related data to pages on
> disk?  Not 100% sure in what context you're using the word
> correlation...

The correlation is between index order and heap order --- that is, are
the tuples in the table physically in the same order as the index?
The better the correlation, the fewer heap-page reads it will take to do
an index scan.

Note it is possible to measure correlation without regard to whether
there actually is any index; ANALYZE is simply looking to see whether
the values appear in increasing order according to the datatype's
default sort operator.

One problem we have is extrapolating from the single-column correlation
stats computed by ANALYZE to appropriate info for multi-column indexes.
It might be that the only reasonable fix for this is for ANALYZE to
compute multi-column stats too when multi-column indexes are present.
People are used to the assumption that you don't need to re-ANALYZE
after creating a new index, but maybe we'll have to give that up.

> But that value will degrade after time and at what rate?  Does ANALYZE
> maintain that value so that it's kept acurrate?

You keep it up to date by ANALYZE-ing at suitable intervals.  It's no
different from any other statistic.

            regards, tom lane