引言
昨日有一篇关于index only scan的文章，后面群里有位童鞋提了一个问题，下来琢磨一番，果真发现内容有一些瑕疵。主要是Heap Fetch的分析过程，昨日分析成了HOT和pruning，后面发现不是，HOT的使用限制之一便是不能跨数据块，首先跨数据块的指针会占用更多的字节数，另跨数据块的指针也会产生更多的IO，所以PostgreSQL为了这个原因，没有做跨数据块的多版本行的指针。而且尴尬的是，后面自己还写了HOT的限制，着实脸疼呀。那么到底是什么原因呢？
非HOT更新
前面的内容就不重复了，可以直接跳到：HOT实战一节。先使用非Hot update的方式，也就是不设置 fillfactor
postgres=# create table test(id int,info text);
---此处未指定fillfactor
CREATE TABLE
postgres=# alter table test set (autovacuum_enabled = off);
ALTER TABLE
postgres=# insert into test select n,'test' from generate_series(1,100000) as n;
INSERT 0 100000
postgres=# create index t_idx on test using btree (id);
CREATE INDEX
postgres=# analyze test;
ANALYZE
postgres=# select pg_relation_filepath('test');
pg_relation_filepath
----------------------
base/13578/162742
(1 row)
postgres=# explain analyze select id from test where id = 99;
---此处Heap Fetches为1
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Only Scan using t_idx on test (cost=0.29..8.31 rows=1 width=4) (actual time=0.048..0.050 rows=1 loops=1)
Index Cond: (id = 99)
Heap Fetches: 1
Planning Time: 0.081 ms
Execution Time: 0.096 ms
(5 rows)
postgres=# vacuum test;
VACUUM
postgres=# explain analyze select id from test where id = 99;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Only Scan using t_idx on test (cost=0.29..4.31 rows=1 width=4) (actual time=0.021..0.022 rows=1 loops=1)
Index Cond: (id = 99)
Heap Fetches: 0
Planning Time: 0.145 ms
Execution Time: 0.047 ms
(5 rows)
postgres=# update test set info = 'hello' where id = 99; ---进行更新
UPDATE 1新开一个会话，查询行的标志位infomask，在 v13里面，pageinspect提供了 heap_tuple_infomask_flags()这个函数，可以方便地查看infomask标志位，对于以前的版本，可以参照我之前的文章，有快速获取infomask的信息。
先查询第 0 个block，因为更新之后，ctid 会 redirect 到更新后的元组
postgres=# select lp, t_xmin, t_xmax, t_ctid,
infomask(t_infomask, 1) as infomask,
infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('test', 0)) where lp = 99;
lp | t_xmin | t_xmax | t_ctid | infomask | infomask2
----+---------+---------+-----------+----------------------------+-----------
99 | 7504251 | 7504254 | (540,101) | XMIN_COMMITTED|HASVARWIDTH |
(1 row)
postgres=# select lp, t_xmin, t_xmax, t_ctid,
infomask(t_infomask, 1) as infomask,
infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('test', 0)) where t_ctid = '(540,101)';
lp | t_xmin | t_xmax | t_ctid | infomask | infomask2
----+---------+---------+-----------+----------------------------+-----------
99 | 7504251 | 7504254 | (540,101) | XMIN_COMMITTED|HASVARWIDTH |
(1 row)
再查询第540个block，也就是更新后的元组所在位置，可以看到是UPDATED的标志位，非Hot update
postgres=# select lp, t_xmin, t_xmax, t_ctid,
infomask(t_infomask, 1) as infomask,
infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('test', 540)) where t_ctid = '(540,101)';
lp | t_xmin | t_xmax | t_ctid | infomask | infomask2
-----+---------+--------+-----------+----------------------------------+-----------
101 | 7504254 | 0 | (540,101) | UPDATED|XMAX_INVALID|HASVARWIDTH |
(1 row)
此时在第一个会话进行查询，此处可以看到，Heap Fetches仍为2
postgres=# explain analyze select id from test where id = 99;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Only Scan using t_idx on test (cost=0.29..4.31 rows=1 width=4) (actual time=0.061..0.063 rows=1 loops=1)
Index Cond: (id = 99)
Heap Fetches: 2
Planning Time: 0.083 ms
Execution Time: 0.093 ms
(5 rows)
再次查看标志位，注意观察重点！原来的数据，也就是update的死元组，被select的动作清理掉了！同时给新的元组，增加了XMIN_COMMITTED的标志位，代表事务已经提交，可见了
postgres=# select lp, t_xmin, t_xmax, t_ctid,
infomask(t_infomask, 1) as infomask,
infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('test', 0)) where lp = 99;
lp | t_xmin | t_xmax | t_ctid | infomask | infomask2
----+--------+--------+--------+----------+-----------
99 | | | | |
(1 row)
postgres=# select lp, t_xmin, t_xmax, t_ctid,
infomask(t_infomask, 1) as infomask,
infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('test', 0)) where t_ctid = '(540,101)';
lp | t_xmin | t_xmax | t_ctid | infomask | infomask2
----+--------+--------+--------+----------+-----------
(0 rows)
postgres=# select lp, t_xmin, t_xmax, t_ctid,
infomask(t_infomask, 1) as infomask,
infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('test', 540)) where t_ctid = '(540,101)';
lp | t_xmin | t_xmax | t_ctid | infomask | infomask2
-----+---------+--------+-----------+-------------------------------------------------+-----------
101 | 7504254 | 0 | (540,101) | UPDATED|XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
(1 row)
查看可见性信息
postgres=# select * from pg_visibility_map('test'::regclass,0);
all_visible | all_frozen
-------------+------------
f | f
(1 row)
postgres=# select * from pg_visibility_map('test'::regclass,540);
all_visible | all_frozen
-------------+------------
f | f
(1 row)
再次查看，此处Heap Fetches变成了1，因为老的元组被清理了，满足的CTID就只有最新的Block了，只有1个block了。
postgres=# explain analyze select id from test where id = 99;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Only Scan using t_idx on test (cost=0.29..4.31 rows=1 width=4) (actual time=0.029..0.031 rows=1 loops=1)
Index Cond: (id = 99)
Heap Fetches: 1
Planning Time: 0.102 ms
Execution Time: 0.067 ms
(5 rows)
HOT更新
postgres=# drop table test;
DROP TABLE
postgres=# create table test(id int,info text) with ( fillfactor = 70);
CREATE TABLE
postgres=# alter table test set (autovacuum_enabled = off);
ALTER TABLE
postgres=# insert into test select n,'test' from generate_series(1,100000) as n;
INSERT 0 100000
postgres=# create index t_idx on test using btree (id);
CREATE INDEX
postgres=# analyze test;
ANALYZE
postgres=# select pg_relation_filepath('test');
pg_relation_filepath
----------------------
base/13578/170888
(1 row)
postgres=# explain analyze select id from test where id = 99;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Only Scan using t_idx on test (cost=0.29..8.31 rows=1 width=4) (actual time=0.024..0.026 rows=1 loops=1)
Index Cond: (id = 99)
Heap Fetches: 1
Planning Time: 0.181 ms
Execution Time: 0.070 ms
(5 rows)
postgres=# vacuum test;
VACUUM
postgres=# explain analyze select id from test where id = 99;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Only Scan using t_idx on test (cost=0.29..4.31 rows=1 width=4) (actual time=0.027..0.028 rows=1 loops=1)
Index Cond: (id = 99)
Heap Fetches: 0
Planning Time: 0.151 ms
Execution Time: 0.055 ms
(5 rows)
postgres=# update test set info = 'hello' where id = 99;
UPDATE 1
另开一个会话，进行查询，可以发现，使用了Hot update，新老数据位于一个数据块，这样索引就不需要更新了。在更新tuple1时，postgresql会将tuple1（老元组）的标记位置为heap_hot_update，代表该元组是经过hot更新的行，同时将tuple2（新元组）的标记位置为heap_only_tuple。
postgres=# select lp, t_xmin, t_xmax, t_ctid,
infomask(t_infomask, 1) as infomask,
infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('test', 0)) where t_ctid = '(0,130)';
lp | t_xmin | t_xmax | t_ctid | infomask | infomask2
-----+---------+---------+---------+----------------------------------+-----------------
99 | 7504278 | 7504281 | (0,130) | XMIN_COMMITTED|HASVARWIDTH | HOT_UPDATED
130 | 7504281 | 0 | (0,130) | UPDATED|XMAX_INVALID|HASVARWIDTH | HEAP_ONLY_TUPLE
(2 rows)
查询，可以发现，多次查询，Heap Fetches都始终是1，因为只需要去回表判断第一个数据块的可见性即可。
postgres=# explain analyze select id from test where id = 99;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Only Scan using t_idx on test (cost=0.29..4.31 rows=1 width=4) (actual time=0.055..0.056 rows=1 loops=1)
Index Cond: (id = 99)
Heap Fetches: 1
Planning Time: 0.084 ms
Execution Time: 0.086 ms
(5 rows)
同时也是老样子，删除了死的元组
postgres=# select lp, t_xmin, t_xmax, t_ctid,
infomask(t_infomask, 1) as infomask,
infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('test', 0)) where t_ctid = '(0,130)';
lp | t_xmin | t_xmax | t_ctid | infomask | infomask2
-----+---------+--------+---------+-------------------------------------------------+-----------------
130 | 7504288 | 0 | (0,130) | UPDATED|XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH | HEAP_ONLY_TUPLE
(1 row)
后续就一直是1了
postgres=# explain analyze select id from test where id = 99;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Only Scan using t_idx on test (cost=0.29..4.31 rows=1 width=4) (actual time=0.027..0.029 rows=1 loops=1)
Index Cond: (id = 99)
Heap Fetches: 1
Planning Time: 0.087 ms
Execution Time: 0.058 ms
(5 rows)
postgres=# explain analyze select id from test where id = 99;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Index Only Scan using t_idx on test (cost=0.29..4.31 rows=1 width=4) (actual time=0.026..0.027 rows=1 loops=1)
Index Cond: (id = 99)
Heap Fetches: 1
Planning Time: 0.088 ms
Execution Time: 0.056 ms
(5 rows)
小结
所以，在PostgreSQL里面，清理的动作并不仅仅是vacuum！一共有两个时机
时机1：在查询操作访问到某个页面时，会清理这个页面
时机2：通过vacuum操作来清理
postgres=# delete from test where id = 5;
DELETE 1
第二个会话
postgres=# select lp, t_xmin, t_xmax, t_ctid,
infomask(t_infomask, 1) as infomask,
infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('test', 0));
lp | t_xmin | t_xmax | t_ctid | infomask | infomask2
-----+---------+---------+---------+-----------------------------------------+--------------------
1 | 7504332 | 0 | (0,1) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
2 | 7504332 | 0 | (0,2) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
3 | 7504332 | 0 | (0,3) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
4 | 7504332 | 0 | (0,4) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
5 | 7504332 | 7504335 | (0,5) | XMIN_COMMITTED|HASVARWIDTH | UPDATE_KEY_REVOKED
6 | 7504332 | 0 | (0,6) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
7 | 7504332 | 0 | (0,7) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
8 | 7504332 | 0 | (0,8) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
9 | 7504332 | 0 | (0,9) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
第一个会话
postgres=# delete from test where id = 5;
DELETE 1
postgres=# delete from test where id = 10;
DELETE 1
第二个会话
postgres=# select lp, t_xmin, t_xmax, t_ctid,
infomask(t_infomask, 1) as infomask,
infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('test', 0));
lp | t_xmin | t_xmax | t_ctid | infomask | infomask2
-----+---------+---------+---------+-----------------------------------------+--------------------
1 | 7504332 | 0 | (0,1) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
2 | 7504332 | 0 | (0,2) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
3 | 7504332 | 0 | (0,3) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
4 | 7504332 | 0 | (0,4) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
5 | | | | |
6 | 7504332 | 0 | (0,6) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
7 | 7504332 | 0 | (0,7) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
8 | 7504332 | 0 | (0,8) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
9 | 7504332 | 0 | (0,9) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
10 | 7504332 | 7504336 | (0,10) | XMIN_COMMITTED|HASVARWIDTH | UPDATE_KEY_REVOKED
第一个会话
postgres=# delete from test where id = 5;
DELETE 1
postgres=# delete from test where id = 10;
DELETE 1
postgres=# delete from test where id = 15;
DELETE 1
第二个会话，可以看到，后面的动作会清理前面留下来的坑。
postgres=# select lp, t_xmin, t_xmax, t_ctid,
infomask(t_infomask, 1) as infomask,
infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('test', 0));
lp | t_xmin | t_xmax | t_ctid | infomask | infomask2
-----+---------+---------+---------+-----------------------------------------+--------------------
1 | 7504332 | 0 | (0,1) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
2 | 7504332 | 0 | (0,2) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
3 | 7504332 | 0 | (0,3) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
4 | 7504332 | 0 | (0,4) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
5 | | | | |
6 | 7504332 | 0 | (0,6) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
7 | 7504332 | 0 | (0,7) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
8 | 7504332 | 0 | (0,8) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
9 | 7504332 | 0 | (0,9) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
10 | | | | |
11 | 7504332 | 0 | (0,11) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
12 | 7504332 | 0 | (0,12) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
13 | 7504332 | 0 | (0,13) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
14 | 7504332 | 0 | (0,14) | XMAX_INVALID|XMIN_COMMITTED|HASVARWIDTH |
15 | 7504332 | 7504337 | (0,15) | XMIN_COMMITTED|HASVARWIDTH | UPDATE_KEY_REVOKED
但是自己测了一张新表
postgres=# select * from test2;
id
----
1
2
3
4
5
6
7
8
9
10
(10 rows)
postgres=# delete from test2 where id = 2;
DELETE 1
postgres=# delete from test2 where id = 4;
DELETE 1
postgres=# delete from test2 where id = 6;
DELETE 1
发现又没有清理
postgres=# select lp, t_xmin, t_xmax, t_ctid,
infomask(t_infomask, 1) as infomask,
infomask(t_infomask2, 2) as infomask2
from heap_page_items(get_raw_page('test2', 0));
lp | t_xmin | t_xmax | t_ctid | infomask | infomask2
----+---------+---------+--------+-------------------------------+--------------------
1 | 7504339 | 0 | (0,1) | XMAX_INVALID|XMIN_COMMITTED |
2 | 7504339 | 7504340 | (0,2) | XMAX_COMMITTED|XMIN_COMMITTED | UPDATE_KEY_REVOKED
3 | 7504339 | 0 | (0,3) | XMAX_INVALID|XMIN_COMMITTED |
4 | 7504339 | 7504341 | (0,4) | XMAX_COMMITTED|XMIN_COMMITTED | UPDATE_KEY_REVOKED
5 | 7504339 | 0 | (0,5) | XMAX_INVALID|XMIN_COMMITTED |
6 | 7504339 | 7504342 | (0,6) | XMIN_COMMITTED | UPDATE_KEY_REVOKED
7 | 7504339 | 0 | (0,7) | XMAX_INVALID|XMIN_COMMITTED |
8 | 7504339 | 0 | (0,8) | XMAX_INVALID|XMIN_COMMITTED |
9 | 7504339 | 0 | (0,9) | XMAX_INVALID|XMIN_COMMITTED |
10 | 7504339 | 0 | (0,10) | XMAX_INVALID|XMIN_COMMITTED |
(10 rows)
希望知道的大佬，能够不吝指教！
真是充实的一天，又学到了新知识，之前在看Greenplum文章的时候《Greenplum MVCC并发控制：严格的一致性与极致的性能》，就对查询进行清理有点疑惑，目前来看确实如此，只是具体的触发时机还有待深究。再次感谢 "稻草人"的提问，三人行，必有我师焉。
