postgres统计信息
一、 统计信息的收集
1. 主要参数
其中最主要的是track_counts，开启才会收集统计信息。
postgres=# select name,setting,short_desc,context from pg_settings where name like 'track%';
name | setting | short_desc | context
---------------------------+---------+--------------------------------------------------------------+------------
track_activities | on | Collects information about executing commands. | superuser
track_activity_query_size | 2048 | Sets the size reserved for pg_stat_activity.query, in bytes. | postmaster
track_commit_timestamp | on | Collects transaction commit time. | postmaster
track_counts | on | Collects statistics on database activity. | superuser
track_functions | none | Collects function-level statistics on database activity. | superuser
track_io_timing | on | Collects timing statistics for database I/O activity. | superuser
track_wal_io_timing | off | Collects timing statistics for WAL I/O activity. | superuser
二、自动收集
由autovacuum触发。触发条件：
autovacuum_analyze_threshold：表被修改行数阈值，默认50
autovacuum_analyze_scale_factor：表被修改行数比例，默认0.1
autovacuum_naptime：收集统计信息频率，默认60S
计算公式：pg_stat_all_tables.n_mod_since_analyze （自上次analyze以来被修改的行数）> autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × pg_class.reltuples
自上次analyze以来被修改的行数>50+0.1*总数
它也有对应普通表的表级同名参数，可以针对各表调整。toast表无需收集统计信息，因此没有针对它的参数
我们先修改postgresql.conf的autovacuum_naptime = 10s，systemctl restart POSTGRES方便观察
select name,setting,short_desc,context from pg_settings where name like 'autovacuum%';
name | setting | short_desc | context
---------------------------------------+-----------+-------------------------------------------------------------------------------------------+------------
autovacuum | on | Starts the autovacuum subprocess. | sighup
autovacuum_analyze_scale_factor | 0.1 | Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples. | sighup
autovacuum_analyze_threshold | 50 | Minimum number of tuple inserts, updates, or deletes prior to analyze. | sighup
autovacuum_freeze_max_age | 200000000 | Age at which to autovacuum a table to prevent transaction ID wraparound. | postmaster
autovacuum_max_workers | 3 | Sets the maximum number of simultaneously running autovacuum worker processes. | postmaster
autovacuum_multixact_freeze_max_age | 400000000 | Multixact age at which to autovacuum a table to prevent multixact wraparound. | postmaster
autovacuum_naptime | 10 | Time to sleep between autovacuum runs. | sighup
autovacuum_vacuum_cost_delay | 2 | Vacuum cost delay in milliseconds, for autovacuum. | sighup
autovacuum_vacuum_cost_limit | -1 | Vacuum cost amount available before napping, for autovacuum. | sighup
autovacuum_vacuum_insert_scale_factor | 0.2 | Number of tuple inserts prior to vacuum as a fraction of reltuples. | sighup
autovacuum_vacuum_insert_threshold | 1000 | Minimum number of tuple inserts prior to vacuum, or -1 to disable insert vacuums. | sighup
autovacuum_vacuum_scale_factor | 0.2 | Number of tuple updates or deletes prior to vacuum as a fraction of reltuples. | sighup
autovacuum_vacuum_threshold | 50 | Minimum number of tuple updates or deletes prior to vacuum. | sighup
autovacuum_work_mem | -1 | Sets the maximum memory to be used by each autovacuum worker process. | sighup
(14 rows)
test1=# drop table t_231110_1;
DROP TABLE
test1=# create table t_231110_1 as select * from t1 where id <=2000;
SELECT 2000
test1=# SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 't_231110_1';
-[ RECORD 1 ]-+---
reltuples | -1
relpages | 0
relallvisible | 0
test1=# SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 't_231110_1';
-[ RECORD 1 ]-+---
reltuples | -1
relpages | 0
relallvisible | 0
test1=# SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 't_231110_1';
-[ RECORD 1 ]-+-----
reltuples | 2000
relpages | 11
relallvisible | 11
test1=# delete from t_231110_1 where id <251;
DELETE 250
test1=# SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 't_231110_1';
-[ RECORD 1 ]-+-----
reltuples | 2000
relpages | 11
relallvisible | 11
test1=# select *,now() from pg_stat_user_tables where relname = 't_231110_1';
-[ RECORD 1 ]-------+------------------------------
relid | 78641
schemaname | test1
relname | t_231110_1
seq_scan | 1
seq_tup_read | 2000
idx_scan |
idx_tup_fetch |
n_tup_ins | 2000
n_tup_upd | 0
n_tup_del | 250
n_tup_hot_upd | 0
n_live_tup | 1750
n_dead_tup | 250
n_mod_since_analyze | 250
n_ins_since_vacuum | 0
last_vacuum |
last_autovacuum | 2023-11-10 17:47:39.844508+08
last_analyze |
last_autoanalyze | 2023-11-10 17:47:39.848246+08
vacuum_count | 0
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 1
now | 2023-11-10 17:47:59.053272+08
test1=# select *,now() from pg_stat_user_tables where relname = 't_231110_1';
-[ RECORD 1 ]-------+------------------------------
relid | 78641
schemaname | test1
relname | t_231110_1
seq_scan | 1
seq_tup_read | 2000
idx_scan |
idx_tup_fetch |
n_tup_ins | 2000
n_tup_upd | 0
n_tup_del | 250
n_tup_hot_upd | 0
n_live_tup | 1750
n_dead_tup | 250
n_mod_since_analyze | 250
n_ins_since_vacuum | 0
last_vacuum |
last_autovacuum | 2023-11-10 17:47:39.844508+08
last_analyze |
last_autoanalyze | 2023-11-10 17:47:39.848246+08
vacuum_count | 0
autovacuum_count | 1
analyze_count | 0
autoanalyze_count | 1
now | 2023-11-10 17:48:16.530313+08
test1=# delete from t_231110_1 where id <252;
DELETE 1
test1=# SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 't_231110_1';
-[ RECORD 1 ]-+-----
reltuples | 2000
relpages | 11
relallvisible | 11
test1=# SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 't_231110_1';
-[ RECORD 1 ]-+-----
reltuples | 1749
relpages | 11
relallvisible | 9
三、手动收集
analyze [verbose] [table[(column[,..])]]
verbose：显示收集进度
table：要收集的表名，如果不指定，则收集当前数据库中所有表的统计信息
column：要收集的列名，如果不指定，则收集所有字段的统计信息
analyze命令对表加4级锁，不阻塞写
test1=# delete from t_231110_1 where id <300;
DELETE 48
test1=# select *,now() from pg_stat_user_tables where relname = 't_231110_1';
relid | schemaname | relname | seq_scan | seq_tup_read | idx_scan | idx_tup_fetch | n_tup_ins | n_tup_upd | n_tup_del | n_tup_hot_upd | n_live_tup | n_dead_tup | n_mod_since_analyze | n_ins_since_vacuum
| last_vacuum | last_autovacuum | last_analyze | last_autoanalyze | vacuum_count | autovacuum_count | analyze_count | autoanalyze_count | now
-------+------------+------------+----------+--------------+----------+---------------+-----------+-----------+-----------+---------------+------------+------------+---------------------+-------------------
-+-------------+-------------------------------+--------------+-------------------------------+--------------+------------------+---------------+-------------------+-------------------------------
78641 | test1 | t_231110_1 | 3 | 5499 | | | 2000 | 0 | 299 | 0 | 1701 | 299 | 48 | 0
| | 2023-11-10 17:47:39.844508+08 | | 2023-11-10 17:48:29.848021+08 | 0 | 1 | 0 | 2 | 2023-11-15 17:00:49.196063+08
(1 row)
test1=# SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 't_231110_1';
-[ RECORD 1 ]-+-----
reltuples | 1749
relpages | 11
relallvisible | 9
test1=# analyze verbose t_231110_1;
INFO: analyzing "test1.t_231110_1"
INFO: "t_231110_1": scanned 11 of 11 pages, containing 1701 live rows and 299 dead rows; 1701 rows in sample, 1701 estimated total rows
ANALYZE
test1=# SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 't_231110_1';
-[ RECORD 1 ]-+-----
reltuples | 1701
relpages | 11
relallvisible | 9
四、基础统计信息
基础统计信息保存在pg_class中，主要是下面3项：
reltuples：表预估行数，也是执行计划里row=的来源之一，pg 14用-1表示没收集过统计信息，以区分于空表
relpages：表预估页数 relpages
relallvisible ：vm(visibility map)文件中被标记的页数
test1=# SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 't1';
reltuples | relpages | relallvisible
-----------+----------+---------------
1000000 | 5406 | 5406
(1 row)
--数据量太少，不搜集
test1=# SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 't_sn';
reltuples | relpages | relallvisible
-----------+----------+---------------
-1 | 0 | 0
(1 row)
test1=# select count(*) from t_sn;
count
-------
44
(1 row)
test1=# SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 't_sn';
reltuples | relpages | relallvisible
-----------+----------+---------------
51 | 1 | 0
(1 row)
test1=# SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 't_sn';
reltuples | relpages | relallvisible
-----------+----------+---------------
51 | 1 | 0
(1 row)
test1=# create table t_sn_1 as select * from t_sn limit 50;
SELECT 50
test1=# SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 't_sn_1';
reltuples | relpages | relallvisible
-----------+----------+---------------
-1 | 0 | 0
(1 row)
test1=# show autovacuum_analyze_threshold;
autovacuum_analyze_threshold
------------------------------
80
(1 row)
--我们可以看到表最少51条的时候才会收集统计信息。关于这个参数autovacuum_analyze_threshold
五、详细统计信息
这部分内容存在pg_statistic系统表中，但里面的内容很难看懂，因此通常我们会看pg_stats视图。
test1=# select * from pg_statistic limit 1;
starelid | staattnum | stainherit | stanullfrac | stawidth | stadistinct | stakind1 | stakind2 | stakind3 | stakind4 | stakind5 | staop1 | staop2 | staop3 | staop4 | staop5 | stacoll1 | stacoll2 | stacoll3
| stacoll4 | stacoll5 | stanumbers1 | stanumbers2 | stanumbers3 | stanumbers4 | stanumbers5 |
stavalues1
| stavalues2 | stavalues3 | stavalues
4 | stavalues5
----------+-----------+------------+-------------+----------+-------------+----------+----------+----------+----------+----------+--------+--------+--------+--------+--------+----------+----------+---------
-+----------+----------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+------------+----------
--+------------
77287 | 1 | f | 0 | 4 | -1 | 2 | 3 | 0 | 0 | 0 | 97 | 97 | 0 | 0 | 0 | 0 | 0 | 0
| 0 | 0 | | {1} | | | | {17421,961152,1931969,3106330,4049478,5119039,6200899,7266733,8268266,9260787,10391944,11354350,12522380,134295
88,14487268,15559867,16656110,17635031,18557451,19561260,20547848,21460044,22434634,23431919,24512315,25561362,26560343,27554268,28640328,29560086,30469073,31551260,32467149,33337712,34330741,35226473,36114
670,37085759,38089956,38933130,39895636,41010193,41995095,43110324,44110725,45048321,46120158,47139256,47981909,48892920,49959168,51015987,51984496,53148345,54162567,55038427,56044457,57142785,58044143,5916
5724,60130955,61174168,62187183,63332405,64231303,65244345,66210709,67144889,68172017,69137946,70194587,71251123,72312744,73348374,74345119,75286906,76300975,77127001,78197145,79176269,80189973,81205756,821
63881,83084830,84059138,85145298,86136019,87197267,88286805,89140910,90025817,90992023,91975919,92996262,93969304,94901128,95832032,96836258,97828850,98945118,99996910} | | |
|
(1 row)
pg_stats视图内容
test1=# \x
Expanded display is on.
postgres=# select * from pg_stats where tablename='tb13';
-[ RECORD 1 ]----------+-------------------------------------------------------
schemaname | public （表所在的schema）
tablename | tb13 （表名）
attname | id （字段名）
inherited | f （是否是继承而来的字段，t：是；f：否）
null_frac | 0 （null值的百分比，这里为0%）
avg_width | 4 （该字段的平均长度）
n_distinct | -1 （表示该字段的唯一值的个数，-1：表示该字段有唯一约束，大于0的整数，比如m：表示该字段有m个唯一值）
most_common_vals | （高频值，这里没有，因为是主键）
most_common_freqs | （高频值的出现的频率）
histogram_bounds | {1,1010,2020,3030,4040,5050,6060,7070,8080,9090,10100} (该字段除高频值以外值的的柱状图信息)
correlation | 1 （表中记录的逻辑顺序与存储的物理顺序的关系，-1到1之间，1表示逻辑顺序与存储的物理顺序相同，-1表示逻辑顺序与存储的物理顺序相反）
most_common_elems | （该字段是数组元素的统计信息，高频元素）
most_common_elem_freqs | （该字段是数组元素的统计信息，高频元素出现的频率）
elem_count_histogram | （该字段是数组元素的统计信息，该列元素唯一值个数平均分布柱状图）
1. null_frac 空值比率
执行计划预估空值行数时，会用 reltuples * null_frac
create table t_test_a (id int);
insert into t_test_a select generate_series(1,5000);
insert into t_test_a select null from t_test_a limit 1000;
test1=# select * from pg_stats where tablename='t_test_a';
-[ RECORD 1 ]----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | test1
tablename | t_test_a
attname | id
inherited | f
null_frac | 0.16666667
avg_width | 4
n_distinct | -0.8333333
most_common_vals |
most_common_freqs |
histogram_bounds | {1,50,100,150,200,250,300,350,400,450,500,550,600,650,700,750,800,850,900,950,1000,1050,1100,1150,1200,1250,1300,1350,1400,1450,1500,1550,1600,1650,1700,1750,1800,1850,1900,1950,2000,2050,2100,2150,2200,2250,2300,2350,2400,2450,2500,2550,2600,2650,2700,2750,2800,2850,2900,2950,3000,3050,3100,3150,3200,3250,3300,3350,3400,3450,3500,3550,3600,3650,3700,3750,3800,3850,3900,3950,4000,4050,4100,4150,4200,4250,4300,4350,4400,4450,4500,4550,4600,4650,4700,4750,4800,4850,4900,4950,5000}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
SELECT reltuples::numeric, relpages, relallvisible FROM pg_class WHERE relname = 't_test_a';
-[ RECORD 1 ]-+-----
reltuples | 6000
relpages | 26
relallvisible | 26
test1=# select 6000*0.16666667;
-[ RECORD 1 ]-----------
?column? | 1000.00002000
test1=# explain select * from t_test_a where id is null;
-[ RECORD 1 ]----------------------------------------------------------
QUERY PLAN | Seq Scan on t_test_a (cost=0.00..86.00 rows=1000 width=4)
-[ RECORD 2 ]----------------------------------------------------------
QUERY PLAN | Filter: (id IS NULL)
2. n_distinct 非重复值
- 如果值为负数，其绝对值代表非重复值在列中占比（总行数/非重复值）
例如-1表示所有值均不重复（总行数/非重复值=1），-0.8表示非重复值占0.8（非重复值/总行数=0.8）。
- 非重复值占比超过10%时会用比例表示，否则使用具体数字
–这个值具体就是用来判断数据库是否使用索引还是用全表。也是可以告诉我们，如果这个特别小说明我们要考虑好是否要建索引。
3. 最频繁值 Most Common Values
pg_stats视图的most_common_vals 和 most_common_freqs字段。
most_common_vals 最频繁的值，most_common_freqs对应值的比例。
create table t_test_b(id int);
insert into t_test_b select 1 from t_test_a limit 90;
insert into t_test_b select 0 from t_test_a limit 10;
test1=# select * from pg_stats where tablename='t_test_b';
-[ RECORD 1 ]----------+----------
schemaname | test1
tablename | t_test_b
attname | ?column?
inherited | f
null_frac | 0
avg_width | 4
n_distinct | 2
most_common_vals | {1,0}
most_common_freqs | {0.9,0.1}
histogram_bounds |
correlation | 0.459946
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
test1=# explain select * from t_test_b where id=0;
-[ RECORD 1 ]-------------------------------------------------------
QUERY PLAN | Seq Scan on t_test_b (cost=0.00..2.25 rows=10 width=4)
-[ RECORD 2 ]-------------------------------------------------------
QUERY PLAN | Filter: (id = 0)
4.直方图
如果distinct值太多，pg不可能一个个存起来，就会使用直方图保存。直方图的基本原理是将数据排序后分成若干个桶（bucket），并记录每个桶中数据的最大值、最小值、出现频次占比等信息。
--显示部分字段具体值
test1=# select * from pg_stats where tablename='t_test_a';
-[ RECORD 1 ]----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | test1
tablename | t_test_a
attname | id
inherited | f
null_frac | 0.16666667
avg_width | 4
n_distinct | -0.8333333
most_common_vals |
most_common_freqs |
histogram_bounds | {1,50,100,150,200,250,300,350,400,450,500,550,600,650,700,750,800,850,900,950,1000,1050,1100,1150,1200,1250,1300,1350,1400,1450,1500,1550,1600,1650,1700,1750,1800,1850,1900,1950,2000,2050,2100,2150,2200,2250,2300,2350,2400,2450,2500,2550,2600,2650,2700,2750,2800,2850,2900,2950,3000,3050,3100,3150,3200,3250,3300,3350,3400,3450,3500,3550,3600,3650,3700,3750,3800,3850,3900,3950,4000,4050,4100,4150,4200,4250,4300,4350,4400,4450,4500,4550,4600,4650,4700,4750,4800,4850,4900,4950,5000}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
--显示全部字段具体值。
test1=# create table t_test_c as select * from t_test_a limit 100;
SELECT 100
test1=# select * from pg_stats where tablename='t_test_c';
-[ RECORD 1 ]----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | test1
tablename | t_test_c
attname | id
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
test1=#
最常见的直方图分为两类
- 等宽直方图 Equi-width Histogram
将数据按最大、小值区间等分为N份，即所谓"等宽"。
假设某一列各个值的分布如下
划分为4个桶，则等宽直方图为
优点是简洁清晰，缺点则是无法根据各值出现频率进行统计。如果桶中值偏差度过高，预估的返回行数可能差距会很大。
- 等高直方图 Equi-depth Histogram
将数据按总频次等分为N份，每个桶中数值的频次之和为总行数的 1/N，即所谓"等高"。
优点是增加选择率估算的准确性；且数据分散的区间内每个桶中的数值跨度更大，有利于减小储存直方图所消耗的内存。缺点是如果某个值占比极高，会导致它自己占很多个桶，其他大量值挤在一个桶中。
5.非标准类型的统计信息
most_common_elems,most_common_elem_freqs,elem_count_histogram 会显示非标准类型的元素的mcv,mcf,直方图信息，通常适用于数组、向量、范围等数据类型。
6.平均宽度 avg_width
顾名思义，列中存储值的平均宽度，通常对变长的字符串类型比较有意义。
test1=# select * from pg_stats where tablename='t_test_c';
-[ RECORD 1 ]----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | test1
tablename | t_test_c
attname | id
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100}
correlation | 1
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
test1=# drop table t_test_c;
DROP TABLE
test1=# create table t_test_c (id varchar(44));
CREATE TABLE
test1=# insert into t_test_c select *from t_test_a limit 100;
INSERT 0 100
test1=#
test1=# select * from pg_stats where tablename='t_test_c';
-[ RECORD 1 ]----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
schemaname | test1
tablename | t_test_c
attname | id
inherited | f
null_frac | 0
avg_width | 2
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds | {1,10,100,11,12,13,14,15,16,17,18,19,2,20,21,22,23,24,25,26,27,28,29,3,30,31,32,33,34,35,36,37,38,39,4,40,41,42,43,44,45,46,47,48,49,5,50,51,52,53,54,55,56,57,58,59,6,60,61,62,63,64,65,66,67,68,69,7,70,71,72,73,74,75,76,77,78,79,8,80,81,82,83,84,85,86,87,88,89,9,90,91,92,93,94,95,96,97,98,99}
correlation | 0.8082088
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
7. 相关度 Correlation
元组顺序与物理存储顺序。1表示完全一致，-1表示完全相反，越一致通常性能越好。
看上面的例子，用int，相关性是1，用varchar，相关性是0.8082088？为什么？
test1=# SELECT * FROM t_test_c ORDER BY ID;
id
-----
1
10
100
11
12
13
14
15
16
17
18
19..
一致性高的好处，单表聚簇：按照某表里的一个字段来集中存储值相同的行在一个数据库块上，这样按照这个字段来扫描数据时，会显著降低i/0;
\1. 因为PostgreSQL 统计了表的物理存储顺序和每一列值的顺态值, 在执行计划选择时, 可以用到这个顺态值用作计算走索引的成本.
这个值越接近0, 说明表的物理分布上这个列的值比较离散, 走索引的成本越高;
反之这个值越接近1或者-1, 说明表的物理分布上这个列的值比较有序, 走索引的成本越低;
\2. cluster 后, 表的物理分布就和索引一致了, 观察上面ctid的变化就可以得知. cluster完后查看pg_stats.correlation会等于1.
\3. 注意cluster是一次性的, 在这个表做了dml 后, 物理分布又会被打乱.
\4. 结合块设备的read ahead, cluster后, 如果执行计划走这个cluster了的索引取数据(如几百条到几万条[取数在全表来说是比较少的时候]), 可以减少大量的物理磁盘读请求.
postgres=# create table test2(id int);
CREATE TABLE
postgres=# insert into test2 SELECT ceil(random() * 5) AS num FROM generate_series(1,5);
INSERT 0 5
postgres=# select * from test2;
id
----
5
2
1
4
3
(5 rows)
postgres=# analyze test2;
ANALYZE
postgres=# select correlation from pg_stats where tablename = 'test2';
correlation
-------------
-0.2
(1 row)
--使用cluster重新排序元组，cluster命令可以对表进行进行聚簇，不过只对存量数据有效，增量数据无法保证，并且是8级锁，通常不会这样来用。
postgres=# create index on test2(id);
CREATE INDEX
postgres=# cluster test2 USING test2_id_idx ;
CLUSTER
postgres=# select * from test2;
id
----
1
2
3
4
5
(5 rows)
postgres=# analyze test2;
ANALYZE
postgres=# select correlation from pg_stats where tablename = 'test2';
correlation
-------------
1
(1 row)
–注意在cluster时，盘簇化是一次性操作：当表将来被更新之后，更改的内容不会被盘簇化排序
–在对一个表进行盘簇化排序的时候，会在其上请求一个 ACCESS EXCLUSIVE 锁，其它客户端即不能读也不能写
–磁盘空间会需要至少约 2 倍的表大小和索引大小
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
