Обсуждение: Is clustering factor stored in pg_stats.correlation?
I believe that tables' clustering factors are stored in pg_stats.correlation. Here is how I came to this conclusion: create table narrow_table as with numbers as( select generate_series as n from generate_series(0,1048575)) select n as seq_number, (n/1024) + ((n%1024)<<10) as rand_number from numbers; alter table narrow_table add constraint pk_narrow_table primary key(seq_number); alter table narrow_table add constraint unq_narrow_table unique(rand_number); cluster narrow_table using pk_narrow_table; vacuum full analyze narrow_table; Now my table is physically ordered by its primary key. Apparently the following query indicates that: SELECT attname, correlation FROM pg_stats WHERE tablename LIKE '%narrow%'; "seq_number";1 "rand_number";0.00665399 Please correct me if I am wrong, or confirm my conclusion. -- View this message in context: http://postgresql.nabble.com/Is-clustering-factor-stored-in-pg-stats-correlation-tp5831610.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
AlexK987 <alex.cue.987@gmail.com> writes: > I believe that tables' clustering factors are stored in pg_stats.correlation. Dunno what you mean by "clustering factor", but pg_stats.correlation stores the correlation coefficient between the sequence of the column's data values and the sequence of their physical order. Correlation coefficient is defined in the usual way: http://en.wikipedia.org/wiki/Pearson_product-moment_correlation_coefficient so it ranges between +1 in your example (perfect ordering) and -1 (perfect descending order). regards, tom lane
Thank you Tom, that's exactly what I meant. I believe the term "clustering factor" is well-known: http://use-the-index-luke.com/glossary/index-clustering-factor What is the right word to use instead in Postgres universe? -- View this message in context: http://postgresql.nabble.com/Is-clustering-factor-stored-in-pg-stats-correlation-tp5831610p5831615.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.