index only scan的误区续

引言

昨日有一篇关于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并发控制:严格的一致性与极致的性能》,就对查询进行清理有点疑惑,目前来看确实如此,只是具体的触发时机还有待深究。再次感谢 “稻草人”的提问,三人行,必有我师焉。

来源:PostgreSQL学徒,本文观点不代表自营销立场,网址:https://www.zyxiao.com/p/136553

发表评论

登录后才能评论
服务中心
服务中心
联系客服
联系客服
侵权联系 投诉举报
返回顶部
河南,挺住!郑州,挺住!一起为他们加油!!