From: | Manfred Koizar <mkoi-pg(at)aon(dot)at> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Sean Chittenden <sean(at)chittenden(dot)org>, pgsql-performance(at)postgresql(dot)org |
Subject: | Index correlation (was: Moving postgresql.conf tunables into 2003... ) |
Date: | 2003-08-08 16:52:44 |
Message-ID: | 24k7jv4c62amkgq42ouh4csrjuu5c6s1q4@4ax.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Thu, 07 Aug 2003 19:31:52 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
wrote:
>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.
This is true for a column that is the first column of a btree index.
Correlation doesn't help with additional index columns and with
functional indices.
>Note it is possible to measure correlation without regard to whether
>there actually is any index;
But there is no need to, because the correlation is only used for
index access cost estimation.
>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.
I wonder whether it would be better to drop column correlation and
calculate index correlation instead, i.e. correlation of index tuples
to heap tuple positions. This would solve both the multi-column index
and the functional index cost estimation problem.
>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.
Index correlation would be computed on CREATE INDEX and whenever the
heap relation is analysed ...
Servus
Manfred
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2003-08-08 18:53:23 | Re: Perfomance Tuning |
Previous Message | Jonathan Gardner | 2003-08-08 16:40:20 | Re: Perfomance Tuning |