Is clustering factor stored in pg_stats.correlation?

Started by AlexK987over 11 years ago3 messagesgeneral
Jump to latest
#1AlexK987
alex.cue.987@gmail.com

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: AlexK987 (#1)
Re: Is clustering factor stored in pg_stats.correlation?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

#3AlexK987
alex.cue.987@gmail.com
In reply to: Tom Lane (#2)
Re: Is clustering factor stored in pg_stats.correlation?

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general