postgresql cluster和correlation
--今天查看pg_statsinfo报告时发现有如下警告,说明字段物理行序和逻辑行序相关不大
correlation of the clustered table fell below threshold in snapshot '2015-11-30 08:30:00' --- 't.order_num', 44.01 % (threshold = 70 %)
correlation的意义
correlation,统计与字段值的物理行序和逻辑行序有关，统计值范围从-1到1, 趋向于-1表示逆向相关, 趋向于1表示正向相关, 趋向于0表示不相关，
相关性越高, 走索引扫描的离散块扫描更少, 也就是说, 相关性越高, 走索引扫描的离散块扫描代价越低，
对于uuid列其相关性可能越近于0，如果相关性很低，可以考虑修改表字段值
--对于此种情景，可以使用cluster优化
cluster的好处 :
1. 因为PostgreSQL 统计了表的物理存储顺序和每一列值的顺态值, 在执行计划选择时, 可以用到这个顺态值用作计算走索引的成本.
这个值越接近0, 说明表的物理分布上这个列的值比较离散, 走索引的成本越高;
反之这个值越接近1或者-1, 说明表的物理分布上这个列的值比较有序, 走索引的成本越低;
2. cluster 后, 表的物理分布就和索引一致了, 观察上面ctid的变化就可以得知. cluster完后查看pg_stats.correlation会等于1.
3. 注意cluster是一次性的, 在这个表做了dml 后, 物理分布又会被打乱.
4. 结合块设备的read ahead, cluster后, 如果执行计划走这个cluster了的索引取数据(如几百条到几万条[取数在全表来说是比较少的时候]), 可以减少大量的物理磁盘读请求.
--优化前的数据如下
postgres=# select * from pg_stats where tablename='t' and attname='order_num';
schemaname | public
tablename | t
attname | order_num
n_distinct | -0.6737
correlation | 0.42853
--聚集化数据的存储与其索引
postgres=# cluster verbose t using order_num_index;
INFO: clustering "public.t" using index scan on "order_num_index"
INFO: "t": found 0 removable, 198250 nonremovable row versions in 6960 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.68s/0.08u sec elapsed 3.76 sec.
CLUSTER
postgres=# vacuum analyze t ;
VACUUM
--再次查看相关性如下所示
postgres=# select * from pg_stats where tablename='t' and attname='order_num';
schemaname | public
tablename | t
attname | order_num
correlation | 1
--注意在cluster时，盘簇化是一次性操作：当表将来被更新之后，更改的内容不会被盘簇化排序
--在对一个表进行盘簇化排序的时候，会在其上请求一个 ACCESS EXCLUSIVE 锁，其它客户端即不能读也不能写
--磁盘空间会需要至少约 2 倍的表大小和索引大小
--综上对于大表或系统的热表，请慎重选择