PostgreSQL cluster table using index
Postgre
sql CLUSTER意在将表按照索引的顺序排布.
可以通过ctid来观察这个排布,或者通过pg_stats.correlation来观察这个排布.
下面来举个例子 :
创建测试表 :
digoal=> create table test (id int, val numeric);
CREATE TABLE
插入测试数据 :
insert into test select generate_series(1100000),0)">random();
INSERT 0 100000
表分析 :
vacuum analyze test;
VACUUM
可以看出id列的顺序和表的物理分布一致 :
correlation from pg_stats where schemaname='digoal'and tablename'test' attname'id';
correlation
-------------
1
1 row)
'val'0.00625629
)
ctididval val=( minval) test ctid | id val
-----------+-------+------------------------
380154704200.00000077439472079277
)
因为测试数据是generate_series(1,100000)生成的,所以ID列的值与物理分布相同 :
-------+----+-------------------
00.645392156671733
)
创建索引 :
create index idx_test_1 on testCREATE INDEX
digoal create index idx_test_2 on testCREATE INDEX
使用cluster重新对表进行物理分布 :
cluster test using idx_test_2CLUSTER
再次查看
val的最小值的物理存储位置 :
说明表的物理分布已经和idx_test_2的排序相同了.
-------+-------+------------------------
)
再次查看id
的最小值的物理存储位置 :
----------+----+-------------------
34937)
分析表 :
可以看出现在表的物理分布和val列的值顺序一致.
-0.00118176
现在使用idx_test_1这个索引来重分布test表 :
结果不再解释.
cluster verbose test idx_test_1;
INFO clustering "digoal.test" index scan on "idx_test_1"
"test" found removable100000 nonremovable row versions in542 pages
DETAIL dead row versions cannot be removed yet.
cpu 0.00s/0.15u sec elapsed 0.19 sec.
CLUSTER
);
val
-----------+-------+------------------------
0.00000077439472079277
)
val
-------+----+-------------------
0.645392156671733
)
;
VACUUM
;
correlation
-------------
1
0.00780408
)
cluster的好处 :
1. 因为Postgre
sql 统计了表的物理存储顺序和每一列值的顺态值,在执行计划选择时,可以用到这个顺态值用作计算走索引的成本.
这个值越接近0,说明表的物理分布上这个列的值比较离散,走索引的成本越高;
反之这个值越接近1或者-1,说明表的物理分布上这个列的值比较有序,走索引的成本越低;
2. cluster 后,表的物理分布就和索引一致了,观察上面ctid的变化就可以得知. cluster完后查看pg_stats.correlation会等于1.
3. 注意cluster是一次性的,在这个表做了dml 后,物理分布又会被打乱.
4. 结合块设备的read ahead,cluster后,如果执行计划走这个cluster了的索引取数据(如几百条到几万条[取数在全表来说是比较少的时候]),可以减少大量的物理磁盘读请求.
cluster加哪些锁?
SESSION 1 :
begin;
BEGIN
CLUSTER
pg_backend_pid();
pg_backend_pid
----------------
10222
SESSION 2 :
pidlocktypedatabaserelationgrantedmodeb.relname pg_locks apg_class b a=oid =10222 pid locktype database relation granted mode relname
-------+----------+----------+----------+---------+---------------------+------------
10222 1638689731 t AccessShareLock idx_test_2
AccessExclusiveLock89729 idx_test_1
89726ShareLock test
test
6 rows
对照
src/include/storage/lock.h的定义,可以看出这里总共涉及了以下锁 :
test表的ShareLock 是CREATE INDEX (WITHOUT CONCURRENTLY)申请的.
test表的
AccessExclusiveLock 是重建表申请的,
索引的AccessShareLock和
AccessExclusiveLock
是重建索引申请的.
也就是说cluster不但要重建表还要重建索引.
AccessExclusiveLock和所有锁冲突,因此select也被阻断 :
BEGIN
CLUSTER
* test limit waiting...
SESSION 3 :
oid relname
-------+----------+----------+----------+---------+---------------------+----------------------------
12044 2663 pg_class_relname_nsp_index
2662 pg_class_oid_index
1259 pg_class
11069 pg_locks
1075989762 f 56858976889765 pg_toast_89762
89769 idx_test_2
12
pid=10759的会话正在等待test表的AccessShareLock.
因此filenode也是发生变化的,如下 :
\d+ test
Table"digoal.test"
Column Type ModifiersStorageStats target Description
----------+-----------------------------+-----------+---------+--------------+-------------
id integer plain
crt_time timestamp without time zone
Indexes:
"idx_test_1" btree "idx_test_2"crt_time DESCHasOIDs no
pg_relation_filepath('test'::regclass pg_relation_filepath
----------------------------------------------
pg_tblspc16385/PG_9.2_2012043011638689752
'idx_test_1'89755
'idx_test_2'89756
执行cluster后,表,索引的文件名都发生了变化 :
CLUSTER
);
pg_relation_filepath
----------------------------------------------
89757
89760
89761
【参考】
1.src/backend/commands/cluster.c
2.
pg_stats.correlation
Statistical correlation between physical row ordering logical ordering of the column valuesThis ranges to +1.When the value is near or an index scan on the column will be estimated to be cheaper than when it near zero due to reduction of random access to the disk( column nullif the column data type does not have a <operator.)
\d pg_stats
View"pg_catalog.pg_stats"
------------------------+----------+-----------
schemaname name tablename attname inherited booleannull_frac real avg_width integer n_distinct most_common_vals anyarray most_common_freqs real[]histogram_bounds correlation most_common_elems most_common_elem_freqs elem_count_histogram
3.src/include/storage/lock.h
#defineNoLock 0
/* SELECT */
RowShareLock 2/* SELECT FOR UPDATE/FOR SHARE */
RowExclusiveLock 3/* INSERT,UPDATE,DELETE */
ShareUpdateExclusiveLock4 /* VACUUM (non-FULL),ANALYZE,CREATE
* INDEX CONCURRENTLY */
5/* CREATE INDEX (WITHOUT CONCURRENTLY) */
ShareRowExclusiveLock/* like EXCLUSIVE MODE,but allows ROW
* SHARE */
ExclusiveLock7/* blocks ROW SHARE/SELECT...FOR
* UPDATE */
8/* ALTER TABLE,DROP TABLE,VACUUM
* FULL,and unqualified LOCK TABLE */
5.
http://www.postgresql.org/docs/9.2/static/view-pg-stats.html
原文链接：https://www.f2er.com/postgresql/194916.html
