Thread: Measuring degredation of CLUSTER INDEX operation

I've got a table for which "CLUSTER tablename USING index" makes an 
order of magnitude difference.

Are there ways to determine how "unclustered" this table becomes over 
time, so I can schedule downtime to recluster?  I could use the pg_stat 
tables, but this seems awkward.
               -Bryce

NB: For manual optimization work, it would be handy to have a feature in 
"ANALYZE VERBOSE" which gives a measure from 0-100%, right next to the 
"rows examined".  100% would be an optimally clustered result.  0% would 
be every row came from a distinct block on disc.

Related links:
http://www.postgresql.org/docs/8.3/static/sql-cluster.html
http://www.postgresonline.com/journal/index.php?/archives/10-How-does-CLUSTER-ON-improve-index-performance.html

On Mon, Nov 10, 2008 at 12:54 PM, Bryce Nesbitt <bryce2@obviously.com> wrote:
> I've got a table for which "CLUSTER tablename USING index" makes an order of
> magnitude difference.
>
> Are there ways to determine how "unclustered" this table becomes over time,
> so I can schedule downtime to recluster?  I could use the pg_stat tables,
> but this seems awkward.

You should be able to run analyze then select correlation from
pg_stats where schemaname='yourschename' and
tablename='yourtablename';

the closer you are to 1.0 the better the clustering.