PostgreSQL CLUSTER意在将表按照索引的順序排布.
可以通過ctid來觀察這個排布, 或者通過pg_stats.correlation來觀察這個排布.
下面來舉個例子 :
建立測試表 :
digoal=> create table test (id int, val numeric);
CREATE TABLE
插入測試資料 :
digoal=> insert into test select generate_series(1,100000),random();
INSERT 0 100000
表分析 :
digoal=> vacuum analyze test;
VACUUM
查詢表的實體分布和對應列的值離散情況 :
可以看出id列的順序和表的實體分布一緻 :
digoal=> select correlation from pg_stats where schemaname='digoal' and tablename='test' and attname='id';
correlation
-------------
1
(1 row)
digoal=> select correlation from pg_stats where schemaname='digoal' and tablename='test' and attname='val';
0.00625629
查詢val的最小值的實體存儲位置 :
digoal=> select ctid,id,val from test where val=(select min(val) from test);
ctid | id | val
-----------+-------+------------------------
(380,154) | 70420 | 0.00000077439472079277
查詢id的最小值的實體存儲位置 :
因為測試資料是generate_series(1,100000)生成的, 是以ID列的值與實體分布相同 :
digoal=> select ctid,id,val from test where id=(select min(id) from test);
ctid | id | val
-------+----+-------------------
(0,1) | 1 | 0.645392156671733
建立索引 :
digoal=> create index idx_test_1 on test(id);
CREATE INDEX
digoal=> create index idx_test_2 on test(val);
使用cluster重新對表進行實體分布 :
digoal=> cluster test using idx_test_2;
CLUSTER
再次檢視val的最小值的實體存儲位置 :
說明表的實體分布已經和idx_test_2的排序相同了.
ctid | id | val
-------+-------+------------------------
(0,1) | 70420 | 0.00000077439472079277
再次檢視id的最小值的實體存儲位置 :
ctid | id | val
----------+----+-------------------
(349,37) | 1 | 0.645392156671733
分析表 :
可以看出現在表的實體分布和val列的值順序一緻.
-0.00118176
現在使用idx_test_1這個索引來重分布test表 :
結果不再解釋.
digoal=> cluster verbose test using idx_test_1;
INFO: clustering "digoal.test" using index scan on "idx_test_1"
INFO: "test": found 0 removable, 100000 nonremovable row versions in 542 pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU 0.00s/0.15u sec elapsed 0.19 sec.
0.00780408
cluster的好處 :
1. 因為PostgreSQL 統計了表的實體存儲順序和每一列值的順态值, 在執行計劃選擇時, 可以用到這個順态值用作計算走索引的成本.
這個值越接近0, 說明表的實體分布上這個列的值比較離散, 走索引的成本越高;
反之這個值越接近1或者-1, 說明表的實體分布上這個列的值比較有序, 走索引的成本越低;
2. cluster 後, 表的實體分布就和索引一緻了, 觀察上面ctid的變化就可以得知. cluster完後檢視pg_stats.correlation會等于1.
3. 注意cluster是一次性的, 在這個表做了dml 後, 實體分布又會被打亂.
4. 結合塊裝置的read ahead, cluster後, 如果執行計劃走這個cluster了的索引取資料(如幾百條到幾萬條[取數在全表來說是比較少的時候]), 可以減少大量的實體磁盤讀請求.
cluster加哪些鎖?
SESSION 1 :
digoal=> begin;
BEGIN
digoal=> select pg_backend_pid();
pg_backend_pid
----------------
10222
SESSION 2 :
digoal=> select pid,locktype,database,relation,granted,mode,b.relname from pg_locks a,pg_class b where a.relation=b.oid and pid=10222;
pid | locktype | database | relation | granted | mode | relname
-------+----------+----------+----------+---------+---------------------+------------
10222 | relation | 16386 | 89731 | t | AccessShareLock | idx_test_2
10222 | relation | 16386 | 89731 | t | AccessExclusiveLock | idx_test_2
10222 | relation | 16386 | 89729 | t | AccessShareLock | idx_test_1
10222 | relation | 16386 | 89729 | t | AccessExclusiveLock | idx_test_1
10222 | relation | 16386 | 89726 | t | ShareLock | test
10222 | relation | 16386 | 89726 | t | AccessExclusiveLock | test
(6 rows)
對照src/include/storage/lock.h的定義, 可以看出這裡總共涉及了以下鎖 :
test表的ShareLock 是CREATE INDEX (WITHOUT CONCURRENTLY)申請的.
test表的AccessExclusiveLock 是重建表申請的,
索引的AccessShareLock和AccessExclusiveLock 是重建索引申請的.
也就是說cluster不但要重建表還要重建索引.
AccessExclusiveLock和所有鎖沖突, 是以select也被阻斷 :
digoal=> cluster test using idx_test_1;
digoal=> select * from test limit 1;
waiting...
SESSION 3 :
digoal=> select pid,locktype,database,relation,granted,mode,b.relname from pg_locks a,pg_class b where a.relation=b.oid;
pid | locktype | database | relation | granted | mode | relname
-------+----------+----------+----------+---------+---------------------+----------------------------
12044 | relation | 16386 | 2663 | t | AccessShareLock | pg_class_relname_nsp_index
12044 | relation | 16386 | 2662 | t | AccessShareLock | pg_class_oid_index
12044 | relation | 16386 | 1259 | t | AccessShareLock | pg_class
12044 | relation | 16386 | 11069 | t | AccessShareLock | pg_locks
10759 | relation | 16386 | 89762 | f | AccessShareLock | test
5685 | relation | 16386 | 89768 | t | AccessShareLock | idx_test_1
5685 | relation | 16386 | 89768 | t | AccessExclusiveLock | idx_test_1
5685 | relation | 16386 | 89765 | t | AccessExclusiveLock | pg_toast_89762
5685 | relation | 16386 | 89762 | t | ShareLock | test
5685 | relation | 16386 | 89762 | t | AccessExclusiveLock | test
5685 | relation | 16386 | 89769 | t | AccessShareLock | idx_test_2
5685 | relation | 16386 | 89769 | t | AccessExclusiveLock | idx_test_2
(12 rows)
pid=10759的會話正在等待test表的AccessShareLock.
是以filenode也是發生變化的, 如下 :
digoal=> \d+ test
Table "digoal.test"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+---------+--------------+-------------
id | integer | | plain | |
crt_time | timestamp without time zone | | plain | |
Indexes:
"idx_test_1" btree (id)
"idx_test_2" btree (crt_time DESC) CLUSTER
Has OIDs: no
digoal=> select pg_relation_filepath('test'::regclass);
pg_relation_filepath
----------------------------------------------
pg_tblspc/16385/PG_9.2_201204301/16386/89752
digoal=> select pg_relation_filepath('idx_test_1'::regclass);
pg_tblspc/16385/PG_9.2_201204301/16386/89755
digoal=> select pg_relation_filepath('idx_test_2'::regclass);
pg_tblspc/16385/PG_9.2_201204301/16386/89756
執行cluster後, 表, 索引的檔案名都發生了變化 :
pg_tblspc/16385/PG_9.2_201204301/16386/89757
pg_tblspc/16385/PG_9.2_201204301/16386/89760
pg_tblspc/16385/PG_9.2_201204301/16386/89761
【參考】
1. src/backend/commands/cluster.c
2.
pg_stats.correlation
Statistical correlation between physical row ordering and logical ordering of the column values.
This ranges from -1 to +1.
When the value is near -1 or +1, an index scan on the column will be estimated to be cheaper than when it is near zero, due to reduction of random access to the disk.
(This column is null if the column data type does not have a < operator.)
digoal=> \d pg_stats View "pg_catalog.pg_stats"
Column | Type | Modifiers
------------------------+----------+-----------
schemaname | name |
tablename | name |
attname | name |
inherited | boolean |
null_frac | real |
avg_width | integer |
n_distinct | real |
most_common_vals | anyarray |
most_common_freqs | real[] |
histogram_bounds | anyarray |
correlation | real |
most_common_elems | anyarray |
most_common_elem_freqs | real[] |
elem_count_histogram | real[] |
3. src/include/storage/lock.h
#define NoLock 0
#define AccessShareLock 1 /* SELECT */
#define RowShareLock 2 /* SELECT FOR UPDATE/FOR SHARE */
#define RowExclusiveLock 3 /* INSERT, UPDATE, DELETE */
#define ShareUpdateExclusiveLock 4 /* VACUUM (non-FULL),ANALYZE, CREATE
* INDEX CONCURRENTLY */
#define ShareLock 5 /* CREATE INDEX (WITHOUT CONCURRENTLY) */
#define ShareRowExclusiveLock 6 /* like EXCLUSIVE MODE, but allows ROW
* SHARE */
#define ExclusiveLock 7 /* blocks ROW SHARE/SELECT...FOR
* UPDATE */
#define AccessExclusiveLock 8 /* ALTER TABLE, DROP TABLE, VACUUM
* FULL, and unqualified LOCK TABLE */
![龍珠訓練營task04[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)