How well clustered is a table?
I was wondering if there is some indication of how well clustered a table
is.
In other words, when a Cluster command is performed then a table would be
100% clustered.
As updates etc are made the table clowly loses its clustering.
Is there any indication as to how "bad" it is at any one point?
In response to Jonathan Blitz :
I was wondering if there is some indication of how well clustered a table is.
In other words, when a Cluster command is performed then a table would be 100%
clustered.
As updates etc are made the table clowly loses its clustering.
Is there any indication as to how "bad" it is at any one point?
Sure, because PG needs this information. See system_view pg_stats, column
correlation. This ranges from -1 to +1. When the value is near -1 or +1,
an index scan on the column will be estimated to be cheaper than when it
is near zero, due to reduction of random access to the disk.
For instance:
test=# select correlation from pg_stats where tablename='foo' and attname='key';
correlation
-------------
0.112908
(1 Zeile)
Zeit: 1,409 ms
test=*# cluster foo using idx_key;
CLUSTER
Zeit: 728,885 ms
test=*# analyse foo;
ANALYZE
Zeit: 59,970 ms
test=*# select correlation from pg_stats where tablename='foo' and attname='key';
correlation
-------------
1
(1 Zeile)
Zeit: 1,505 ms
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99