本文共 33957 字，大约阅读时间需要 113 分钟。
PostgreSQL , Heap Only Tuple , HOT
PostgreSQL目前默认的存储引擎在更新记录时，会在堆内产生一条新版本，旧版本在不需要使用后VACUUM回收，回收旧版本前，需要先回收所有关联这个版本的所有索引POINT。
PG的索引的KEY为索引字段或表达式的值，VALUE为行号。
8.3以前，每个TUPLE版本（行号）都有对应的索引POINT，因此更新的放大比较大。
8.3开始，引入了HOT的概念，当更新记录时，如果能满足两个条件时，通过HEAP PAGE内部LINK来串起所有TUPLE版本，因此索引不变。
HOT必须满足如下两个条件：
Necessary Condition A: UPDATE does not change any of the index keys Necessary Condition B: The new version should fit in the same old block – HOT chains can not cross block boundary. 1、索引字段的值不变。(其中任意一个索引字段的值发生了变化，则所有索引都需要新增版本) 2、新的版本与旧的版本在同一个HEAP PAGE中。
10以后，可以使用二级索引来解决更新引入的索引放大问题：
1、创建测试表，写入10条测试数据
postgres=# create table a(id int, c1 int, c2 int, c3 int); CREATE TABLE postgres=# insert into a select generate_series(1,10), random()*100, random()*100, random()*100; INSERT 0 10
2、创建索引
postgres=# create index idx_a_1 on a (id); CREATE INDEX postgres=# create index idx_a_2 on a (c1); CREATE INDEX postgres=# create index idx_a_3 on a (c2); CREATE INDEX
3、通过pageinspect插件观察索引页内容
postgres=# SELECT * FROM bt_metap('idx_a_1'); magic | version | root | level | fastroot | fastlevel --------+---------+------+-------+----------+----------- 340322 | 2 | 1 | 0 | 1 | 0 (1 row) postgres=# SELECT * FROM bt_metap('idx_a_2'); magic | version | root | level | fastroot | fastlevel --------+---------+------+-------+----------+----------- 340322 | 2 | 1 | 0 | 1 | 0 (1 row) postgres=# SELECT * FROM bt_metap('idx_a_3'); magic | version | root | level | fastroot | fastlevel --------+---------+------+-------+----------+----------- 340322 | 2 | 1 | 0 | 1 | 0 (1 row) postgres=# SELECT * FROM bt_page_items('idx_a_1',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00 4 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00 5 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 6 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00 7 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00 8 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00 9 | (0,9) | 16 | f | f | 09 00 00 00 00 00 00 00 10 | (0,10) | 16 | f | f | 0a 00 00 00 00 00 00 00 (10 rows) postgres=# SELECT * FROM bt_page_items('idx_a_2',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 2 | (0,7) | 16 | f | f | 06 00 00 00 00 00 00 00 3 | (0,1) | 16 | f | f | 0d 00 00 00 00 00 00 00 4 | (0,9) | 16 | f | f | 0e 00 00 00 00 00 00 00 5 | (0,4) | 16 | f | f | 20 00 00 00 00 00 00 00 6 | (0,3) | 16 | f | f | 24 00 00 00 00 00 00 00 7 | (0,6) | 16 | f | f | 28 00 00 00 00 00 00 00 8 | (0,10) | 16 | f | f | 2a 00 00 00 00 00 00 00 9 | (0,2) | 16 | f | f | 3f 00 00 00 00 00 00 00 10 | (0,8) | 16 | f | f | 55 00 00 00 00 00 00 00 (10 rows) postgres=# SELECT * FROM bt_page_items('idx_a_3',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,6) | 16 | f | f | 09 00 00 00 00 00 00 00 2 | (0,5) | 16 | f | f | 12 00 00 00 00 00 00 00 3 | (0,8) | 16 | f | f | 13 00 00 00 00 00 00 00 4 | (0,1) | 16 | f | f | 14 00 00 00 00 00 00 00 5 | (0,4) | 16 | f | f | 22 00 00 00 00 00 00 00 6 | (0,10) | 16 | f | f | 2b 00 00 00 00 00 00 00 7 | (0,3) | 16 | f | f | 30 00 00 00 00 00 00 00 8 | (0,9) | 16 | f | f | 33 00 00 00 00 00 00 00 9 | (0,7) | 16 | f | f | 46 00 00 00 00 00 00 00 10 | (0,2) | 16 | f | f | 4b 00 00 00 00 00 00 00 (10 rows) 4、HOT更新，（更新的字段上没有索引，并且新的版本记录在同一个HEAP PAGE上）
postgres=# update a set c3=c3+1 where id=1; UPDATE 1 postgres=# SELECT * FROM bt_page_items('idx_a_1',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00 4 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00 5 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 6 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00 7 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00 8 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00 9 | (0,9) | 16 | f | f | 09 00 00 00 00 00 00 00 10 | (0,10) | 16 | f | f | 0a 00 00 00 00 00 00 00 (10 rows) postgres=# SELECT * FROM bt_page_items('idx_a_2',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 2 | (0,7) | 16 | f | f | 06 00 00 00 00 00 00 00 3 | (0,1) | 16 | f | f | 0d 00 00 00 00 00 00 00 4 | (0,9) | 16 | f | f | 0e 00 00 00 00 00 00 00 5 | (0,4) | 16 | f | f | 20 00 00 00 00 00 00 00 6 | (0,3) | 16 | f | f | 24 00 00 00 00 00 00 00 7 | (0,6) | 16 | f | f | 28 00 00 00 00 00 00 00 8 | (0,10) | 16 | f | f | 2a 00 00 00 00 00 00 00 9 | (0,2) | 16 | f | f | 3f 00 00 00 00 00 00 00 10 | (0,8) | 16 | f | f | 55 00 00 00 00 00 00 00 (10 rows) postgres=# SELECT * FROM bt_page_items('idx_a_3',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,6) | 16 | f | f | 09 00 00 00 00 00 00 00 2 | (0,5) | 16 | f | f | 12 00 00 00 00 00 00 00 3 | (0,8) | 16 | f | f | 13 00 00 00 00 00 00 00 4 | (0,1) | 16 | f | f | 14 00 00 00 00 00 00 00 5 | (0,4) | 16 | f | f | 22 00 00 00 00 00 00 00 6 | (0,10) | 16 | f | f | 2b 00 00 00 00 00 00 00 7 | (0,3) | 16 | f | f | 30 00 00 00 00 00 00 00 8 | (0,9) | 16 | f | f | 33 00 00 00 00 00 00 00 9 | (0,7) | 16 | f | f | 46 00 00 00 00 00 00 00 10 | (0,2) | 16 | f | f | 4b 00 00 00 00 00 00 00 (10 rows) postgres=# update a set c3=c3 where id=1 returning ctid,*; ctid | id | c1 | c2 | c3 --------+----+----+----+---- (0,13) | 1 | 13 | 20 | 15 (1 row) UPDATE 1 postgres=# SELECT * FROM bt_page_items('idx_a_1',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00 4 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00 5 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 6 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00 7 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00 8 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00 9 | (0,9) | 16 | f | f | 09 00 00 00 00 00 00 00 10 | (0,10) | 16 | f | f | 0a 00 00 00 00 00 00 00 (10 rows) postgres=# SELECT * FROM bt_page_items('idx_a_2',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 2 | (0,7) | 16 | f | f | 06 00 00 00 00 00 00 00 3 | (0,1) | 16 | f | f | 0d 00 00 00 00 00 00 00 4 | (0,9) | 16 | f | f | 0e 00 00 00 00 00 00 00 5 | (0,4) | 16 | f | f | 20 00 00 00 00 00 00 00 6 | (0,3) | 16 | f | f | 24 00 00 00 00 00 00 00 7 | (0,6) | 16 | f | f | 28 00 00 00 00 00 00 00 8 | (0,10) | 16 | f | f | 2a 00 00 00 00 00 00 00 9 | (0,2) | 16 | f | f | 3f 00 00 00 00 00 00 00 10 | (0,8) | 16 | f | f | 55 00 00 00 00 00 00 00 (10 rows) postgres=# SELECT * FROM bt_page_items('idx_a_3',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,6) | 16 | f | f | 09 00 00 00 00 00 00 00 2 | (0,5) | 16 | f | f | 12 00 00 00 00 00 00 00 3 | (0,8) | 16 | f | f | 13 00 00 00 00 00 00 00 4 | (0,1) | 16 | f | f | 14 00 00 00 00 00 00 00 5 | (0,4) | 16 | f | f | 22 00 00 00 00 00 00 00 6 | (0,10) | 16 | f | f | 2b 00 00 00 00 00 00 00 7 | (0,3) | 16 | f | f | 30 00 00 00 00 00 00 00 8 | (0,9) | 16 | f | f | 33 00 00 00 00 00 00 00 9 | (0,7) | 16 | f | f | 46 00 00 00 00 00 00 00 10 | (0,2) | 16 | f | f | 4b 00 00 00 00 00 00 00 (10 rows) postgres=# update a set c3=c3 where id=1; UPDATE 1 postgres=# SELECT * FROM bt_page_items('idx_a_1',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00 4 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00 5 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 6 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00 7 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00 8 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00 9 | (0,9) | 16 | f | f | 09 00 00 00 00 00 00 00 10 | (0,10) | 16 | f | f | 0a 00 00 00 00 00 00 00 (10 rows) postgres=# SELECT * FROM bt_page_items('idx_a_2',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 2 | (0,7) | 16 | f | f | 06 00 00 00 00 00 00 00 3 | (0,1) | 16 | f | f | 0d 00 00 00 00 00 00 00 4 | (0,9) | 16 | f | f | 0e 00 00 00 00 00 00 00 5 | (0,4) | 16 | f | f | 20 00 00 00 00 00 00 00 6 | (0,3) | 16 | f | f | 24 00 00 00 00 00 00 00 7 | (0,6) | 16 | f | f | 28 00 00 00 00 00 00 00 8 | (0,10) | 16 | f | f | 2a 00 00 00 00 00 00 00 9 | (0,2) | 16 | f | f | 3f 00 00 00 00 00 00 00 10 | (0,8) | 16 | f | f | 55 00 00 00 00 00 00 00 (10 rows) postgres=# SELECT * FROM bt_page_items('idx_a_3',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,6) | 16 | f | f | 09 00 00 00 00 00 00 00 2 | (0,5) | 16 | f | f | 12 00 00 00 00 00 00 00 3 | (0,8) | 16 | f | f | 13 00 00 00 00 00 00 00 4 | (0,1) | 16 | f | f | 14 00 00 00 00 00 00 00 5 | (0,4) | 16 | f | f | 22 00 00 00 00 00 00 00 6 | (0,10) | 16 | f | f | 2b 00 00 00 00 00 00 00 7 | (0,3) | 16 | f | f | 30 00 00 00 00 00 00 00 8 | (0,9) | 16 | f | f | 33 00 00 00 00 00 00 00 9 | (0,7) | 16 | f | f | 46 00 00 00 00 00 00 00 10 | (0,2) | 16 | f | f | 4b 00 00 00 00 00 00 00 (10 rows) 5、HOT更新，有索引的字段被更新，但是值不变。并且新版本在同一个PAGE里。
postgres=# update a set c2=c2 where id=1 returning ctid,*; ctid | id | c1 | c2 | c3 --------+----+----+----+---- (0,14) | 1 | 13 | 20 | 15 (1 row) UPDATE 1 postgres=# SELECT * FROM bt_page_items('idx_a_1',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00 4 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00 5 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 6 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00 7 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00 8 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00 9 | (0,9) | 16 | f | f | 09 00 00 00 00 00 00 00 10 | (0,10) | 16 | f | f | 0a 00 00 00 00 00 00 00 (10 rows) postgres=# SELECT * FROM bt_page_items('idx_a_2',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 2 | (0,7) | 16 | f | f | 06 00 00 00 00 00 00 00 3 | (0,1) | 16 | f | f | 0d 00 00 00 00 00 00 00 4 | (0,9) | 16 | f | f | 0e 00 00 00 00 00 00 00 5 | (0,4) | 16 | f | f | 20 00 00 00 00 00 00 00 6 | (0,3) | 16 | f | f | 24 00 00 00 00 00 00 00 7 | (0,6) | 16 | f | f | 28 00 00 00 00 00 00 00 8 | (0,10) | 16 | f | f | 2a 00 00 00 00 00 00 00 9 | (0,2) | 16 | f | f | 3f 00 00 00 00 00 00 00 10 | (0,8) | 16 | f | f | 55 00 00 00 00 00 00 00 (10 rows) postgres=# SELECT * FROM bt_page_items('idx_a_3',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,6) | 16 | f | f | 09 00 00 00 00 00 00 00 2 | (0,5) | 16 | f | f | 12 00 00 00 00 00 00 00 3 | (0,8) | 16 | f | f | 13 00 00 00 00 00 00 00 4 | (0,1) | 16 | f | f | 14 00 00 00 00 00 00 00 5 | (0,4) | 16 | f | f | 22 00 00 00 00 00 00 00 6 | (0,10) | 16 | f | f | 2b 00 00 00 00 00 00 00 7 | (0,3) | 16 | f | f | 30 00 00 00 00 00 00 00 8 | (0,9) | 16 | f | f | 33 00 00 00 00 00 00 00 9 | (0,7) | 16 | f | f | 46 00 00 00 00 00 00 00 10 | (0,2) | 16 | f | f | 4b 00 00 00 00 00 00 00 (10 rows) 以上HOT更新，所有索引都没有发生变化。
6、NON-HOT更新，更新了索引字段的值，所有索引都发生了变化，至少发生了3个索引IO。
postgres=# update a set c2=c2+1 where id=1 returning ctid,*; ctid | id | c1 | c2 | c3 --------+----+----+----+---- (0,15) | 1 | 13 | 21 | 15 (1 row) UPDATE 1 postgres=# SELECT * FROM bt_page_items('idx_a_1',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,15) | 16 | f | f | 01 00 00 00 00 00 00 00 2 | (0,1) | 16 | f | f | 01 00 00 00 00 00 00 00 3 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 4 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00 5 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00 6 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 7 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00 8 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00 9 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00 10 | (0,9) | 16 | f | f | 09 00 00 00 00 00 00 00 11 | (0,10) | 16 | f | f | 0a 00 00 00 00 00 00 00 (11 rows) postgres=# SELECT * FROM bt_page_items('idx_a_2',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 2 | (0,7) | 16 | f | f | 06 00 00 00 00 00 00 00 3 | (0,15) | 16 | f | f | 0d 00 00 00 00 00 00 00 4 | (0,1) | 16 | f | f | 0d 00 00 00 00 00 00 00 5 | (0,9) | 16 | f | f | 0e 00 00 00 00 00 00 00 6 | (0,4) | 16 | f | f | 20 00 00 00 00 00 00 00 7 | (0,3) | 16 | f | f | 24 00 00 00 00 00 00 00 8 | (0,6) | 16 | f | f | 28 00 00 00 00 00 00 00 9 | (0,10) | 16 | f | f | 2a 00 00 00 00 00 00 00 10 | (0,2) | 16 | f | f | 3f 00 00 00 00 00 00 00 11 | (0,8) | 16 | f | f | 55 00 00 00 00 00 00 00 (11 rows) postgres=# SELECT * FROM bt_page_items('idx_a_3',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,6) | 16 | f | f | 09 00 00 00 00 00 00 00 2 | (0,5) | 16 | f | f | 12 00 00 00 00 00 00 00 3 | (0,8) | 16 | f | f | 13 00 00 00 00 00 00 00 4 | (0,1) | 16 | f | f | 14 00 00 00 00 00 00 00 5 | (0,15) | 16 | f | f | 15 00 00 00 00 00 00 00 6 | (0,4) | 16 | f | f | 22 00 00 00 00 00 00 00 7 | (0,10) | 16 | f | f | 2b 00 00 00 00 00 00 00 8 | (0,3) | 16 | f | f | 30 00 00 00 00 00 00 00 9 | (0,9) | 16 | f | f | 33 00 00 00 00 00 00 00 10 | (0,7) | 16 | f | f | 46 00 00 00 00 00 00 00 11 | (0,2) | 16 | f | f | 4b 00 00 00 00 00 00 00 (11 rows) 7、垃圾回收，首先回收索引垃圾版本，最后回收表的垃圾版本。
postgres=# vacuum verbose a; INFO: vacuuming "public.a" INFO: scanned index "idx_a_1" to remove 1 row versions DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: scanned index "idx_a_2" to remove 1 row versions DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: scanned index "idx_a_3" to remove 1 row versions DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: "a": removed 1 row versions in 1 pages DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: index "idx_a_1" now contains 10 row versions in 2 pages DETAIL: 1 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: index "idx_a_2" now contains 10 row versions in 2 pages DETAIL: 1 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: index "idx_a_3" now contains 10 row versions in 2 pages DETAIL: 1 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "a": found 5 removable, 10 nonremovable row versions in 1 out of 1 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 716311280 There were 4 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM
8、垃圾回收后，索引的垃圾版本被清除。
postgres=# SELECT * FROM bt_page_items('idx_a_1',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,15) | 16 | f | f | 01 00 00 00 00 00 00 00 2 | (0,2) | 16 | f | f | 02 00 00 00 00 00 00 00 3 | (0,3) | 16 | f | f | 03 00 00 00 00 00 00 00 4 | (0,4) | 16 | f | f | 04 00 00 00 00 00 00 00 5 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 6 | (0,6) | 16 | f | f | 06 00 00 00 00 00 00 00 7 | (0,7) | 16 | f | f | 07 00 00 00 00 00 00 00 8 | (0,8) | 16 | f | f | 08 00 00 00 00 00 00 00 9 | (0,9) | 16 | f | f | 09 00 00 00 00 00 00 00 10 | (0,10) | 16 | f | f | 0a 00 00 00 00 00 00 00 (10 rows) postgres=# SELECT * FROM bt_page_items('idx_a_2',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,5) | 16 | f | f | 05 00 00 00 00 00 00 00 2 | (0,7) | 16 | f | f | 06 00 00 00 00 00 00 00 3 | (0,15) | 16 | f | f | 0d 00 00 00 00 00 00 00 4 | (0,9) | 16 | f | f | 0e 00 00 00 00 00 00 00 5 | (0,4) | 16 | f | f | 20 00 00 00 00 00 00 00 6 | (0,3) | 16 | f | f | 24 00 00 00 00 00 00 00 7 | (0,6) | 16 | f | f | 28 00 00 00 00 00 00 00 8 | (0,10) | 16 | f | f | 2a 00 00 00 00 00 00 00 9 | (0,2) | 16 | f | f | 3f 00 00 00 00 00 00 00 10 | (0,8) | 16 | f | f | 55 00 00 00 00 00 00 00 (10 rows) postgres=# SELECT * FROM bt_page_items('idx_a_3',1); itemoffset | ctid | itemlen | nulls | vars | data ------------+--------+---------+-------+------+------------------------- 1 | (0,6) | 16 | f | f | 09 00 00 00 00 00 00 00 2 | (0,5) | 16 | f | f | 12 00 00 00 00 00 00 00 3 | (0,8) | 16 | f | f | 13 00 00 00 00 00 00 00 4 | (0,15) | 16 | f | f | 15 00 00 00 00 00 00 00 5 | (0,4) | 16 | f | f | 22 00 00 00 00 00 00 00 6 | (0,10) | 16 | f | f | 2b 00 00 00 00 00 00 00 7 | (0,3) | 16 | f | f | 30 00 00 00 00 00 00 00 8 | (0,9) | 16 | f | f | 33 00 00 00 00 00 00 00 9 | (0,7) | 16 | f | f | 46 00 00 00 00 00 00 00 10 | (0,2) | 16 | f | f | 4b 00 00 00 00 00 00 00 (10 rows) 1、创建测试表32个索引。
do language plpgsql $$ declare sql text; begin sql := 'create table a (id int primary key,'; for i in 1..32 loop sql := sql||'c'||i||' int default random()*1000,'; end loop; sql := rtrim(sql,','); sql := sql||') with (fillfactor=80)'; execute sql; for i in 2..32 loop execute 'create index idx_a_c'||i||' on a (c'||i||')'; end loop; end; $$;
2、写入1000万记录
insert into a (id) select generate_series(1,10000000);
3、non-hot更新
vi test_non_hot.sql \set id random(1,10000000) update a set c2=c2+random()*100-100 where id=:id;
4、HOT更新
vi test_hot1.sql \set id random(1,10000000) update a set c1=c1+random()*100-100 where id=:id; vi test_hot2.sql \set id random(1,10000000) update a set c2=c2 where id=:id;
5、性能对比
5.1、HOT
pgbench -M prepared -n -r -P 1 -f ./test_hot1.sql -c 28 -j 28 -T 120 transaction type: ./test_hot1.sql scaling factor: 1 query mode: prepared number of clients: 28 number of threads: 28 duration: 120 s number of transactions actually processed: 9139010 latency average = 0.368 ms latency stddev = 0.187 ms tps = 76157.798606 (including connections establishing) tps = 76174.469712 (excluding connections establishing) script statistics: - statement latencies in milliseconds: 0.001 \set id random(1,10000000) 0.366 update a set c1=c1+random()*100-100 where id=:id; Total DISK READ : 0.00 B/s | Total DISK WRITE : 13.14 M/s Actual DISK READ: 0.00 B/s | Actual DISK WRITE: 13.82 M/s TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND 45828 be/4 postgres 0.00 B/s 23.39 K/s 0.00 % 1.06 % postgres: postgres postgres 127.0.0.1(41326) UPDATE 45810 be/4 postgres 0.00 B/s 23.39 K/s 0.00 % 0.99 % postgres: postgres postgres 127.0.0.1(41290) UPDATE 45821 be/4 postgres 0.00 B/s 23.39 K/s 0.00 % 0.98 % postgres: postgres postgres 127.0.0.1(41312) idle 45820 be/4 postgres 0.00 B/s 7.80 K/s 0.00 % 0.96 % postgres: postgres postgres 127.0.0.1(41310) UPDATE 45822 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.96 % postgres: postgres postgres 127.0.0.1(41314) UPDATE 45819 be/4 postgres 0.00 B/s 7.80 K/s 0.00 % 0.94 % postgres: postgres postgres 127.0.0.1(41308) UPDATE 45806 be/4 postgres 0.00 B/s 7.80 K/s 0.00 % 0.92 % postgres: postgres postgres 127.0.0.1(41282) idle 45824 be/4 postgres 0.00 B/s 7.80 K/s 0.00 % 0.90 % postgres: postgres postgres 127.0.0.1(41318) UPDATE 45827 be/4 postgres 0.00 B/s 54.58 K/s 0.00 % 0.89 % postgres: postgres postgres 127.0.0.1(41324) UPDATE 45814 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.89 % postgres: postgres postgres 127.0.0.1(41298) UPDATE 45818 be/4 postgres 0.00 B/s 7.80 K/s 0.00 % 0.88 % postgres: postgres postgres 127.0.0.1(41306) idle 45823 be/4 postgres 0.00 B/s 15.59 K/s 0.00 % 0.87 % postgres: postgres postgres 127.0.0.1(41316) UPDATE 45805 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.87 % postgres: postgres postgres 127.0.0.1(41280) UPDATE 45826 be/4 postgres 0.00 B/s 7.80 K/s 0.00 % 0.86 % postgres: postgres postgres 127.0.0.1(41322) UPDATE 45809 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.86 % postgres: postgres postgres 127.0.0.1(41288) UPDATE 45808 be/4 postgres 0.00 B/s 23.39 K/s 0.00 % 0.85 % postgres: postgres postgres 127.0.0.1(41286) UPDATE 45825 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.85 % postgres: postgres postgres 127.0.0.1(41320) UPDATE 45804 be/4 postgres 0.00 B/s 23.39 K/s 0.00 % 0.84 % postgres: postgres postgres 127.0.0.1(41278) UPDATE 49040 be/4 postgres 0.00 B/s 12.85 M/s 0.00 % 0.84 % postgres: wal writer process 45816 be/4 postgres 0.00 B/s 7.80 K/s 0.00 % 0.66 % postgres: postgres postgres 127.0.0.1(41302) idle 45829 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.64 % postgres: postgres postgres 127.0.0.1(41328) UPDATE 45803 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.62 % postgres: postgres postgres 127.0.0.1(41276) BIND 45795 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.59 % postgres: postgres postgres 127.0.0.1(41274) UPDATE 45807 be/4 postgres 0.00 B/s 7.80 K/s 0.00 % 0.58 % postgres: postgres postgres 127.0.0.1(41284) UPDATE 45812 be/4 postgres 0.00 B/s 15.59 K/s 0.00 % 0.56 % postgres: postgres postgres 127.0.0.1(41294) UPDATE 45811 be/4 postgres 0.00 B/s 15.59 K/s 0.00 % 0.55 % postgres: postgres postgres 127.0.0.1(41292) UPDATE 45817 be/4 postgres 0.00 B/s 7.80 K/s 0.00 % 0.55 % postgres: postgres postgres 127.0.0.1(41304) UPDATE 45815 be/4 postgres 0.00 B/s 7.80 K/s 0.00 % 0.55 % postgres: postgres postgres 127.0.0.1(41300) UPDATE 45813 be/4 postgres 0.00 B/s 0.00 B/s 0.00 % 0.51 % postgres: postgres postgres 127.0.0.1(41296) UPDATE
5.2、NON-HOT
pgbench -M prepared -n -r -P 1 -f ./test_non_hot.sql -c 28 -j 28 -T 120 transaction type: ./test_non_hot.sql scaling factor: 1 query mode: prepared number of clients: 28 number of threads: 28 duration: 120 s number of transactions actually processed: 6472445 latency average = 0.519 ms latency stddev = 0.707 ms tps = 53922.273197 (including connections establishing) tps = 53933.908671 (excluding connections establishing) script statistics: - statement latencies in milliseconds: 0.002 \set id random(1,10000000) 0.517 update a set c2=c2+random()*100-100 where id=:id; Total DISK READ : 0.00 B/s | Total DISK WRITE : 191.66 M/s Actual DISK READ: 0.00 B/s | Actual DISK WRITE: 142.11 M/s TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND 49040 be/4 postgres 0.00 B/s 136.56 M/s 0.00 % 7.17 % postgres: wal writer process 45997 be/4 postgres 0.00 B/s 1918.73 K/s 0.00 % 1.60 % postgres: postgres postgres 127.0.0.1(41384) BIND 45983 be/4 postgres 0.00 B/s 1903.25 K/s 0.00 % 1.56 % postgres: postgres postgres 127.0.0.1(41356) UPDATE 45977 be/4 postgres 0.00 B/s 1829.75 K/s 0.00 % 1.54 % postgres: postgres postgres 127.0.0.1(41344) UPDATE 45984 be/4 postgres 0.00 B/s 1918.73 K/s 0.00 % 1.53 % postgres: postgres postgres 127.0.0.1(41358) UPDATE 45985 be/4 postgres 0.00 B/s 1616.99 K/s 0.00 % 1.50 % postgres: postgres postgres 127.0.0.1(41360) UPDATE 45986 be/4 postgres 0.00 B/s 1748.52 K/s 0.00 % 1.49 % postgres: postgres postgres 127.0.0.1(41362) UPDATE 45995 be/4 postgres 0.00 B/s 1616.99 K/s 0.00 % 1.47 % postgres: postgres postgres 127.0.0.1(41380) UPDATE 45988 be/4 postgres 0.00 B/s 1910.99 K/s 0.00 % 1.46 % postgres: postgres postgres 127.0.0.1(41366) UPDATE 45979 be/4 postgres 0.00 B/s 1763.99 K/s 0.00 % 1.46 % postgres: postgres postgres 127.0.0.1(41348) UPDATE 45976 be/4 postgres 0.00 B/s 1918.73 K/s 0.00 % 1.45 % postgres: postgres postgres 127.0.0.1(41342) UPDATE 45982 be/4 postgres 0.00 B/s 1887.78 K/s 0.00 % 1.43 % postgres: postgres postgres 127.0.0.1(41354) UPDATE 45987 be/4 postgres 0.00 B/s 2019.31 K/s 0.00 % 1.42 % postgres: postgres postgres 127.0.0.1(41364) UPDATE 45992 be/4 postgres 0.00 B/s 1616.99 K/s 0.00 % 1.40 % postgres: postgres postgres 127.0.0.1(41374) UPDATE 45994 be/4 postgres 0.00 B/s 1941.94 K/s 0.00 % 1.38 % postgres: postgres postgres 127.0.0.1(41378) UPDATE 45990 be/4 postgres 0.00 B/s 1794.94 K/s 0.00 % 1.36 % postgres: postgres postgres 127.0.0.1(41370) UPDATE 45975 be/4 postgres 0.00 B/s 1934.20 K/s 0.00 % 1.35 % postgres: postgres postgres 127.0.0.1(41340) UPDATE 45974 be/4 postgres 0.00 B/s 1910.99 K/s 0.00 % 1.35 % postgres: postgres postgres 127.0.0.1(41338) UPDATE 45980 be/4 postgres 0.00 B/s 2003.83 K/s 0.00 % 1.28 % postgres: postgres postgres 127.0.0.1(41350) UPDATE 45991 be/4 postgres 0.00 B/s 1748.52 K/s 0.00 % 1.13 % postgres: postgres postgres 127.0.0.1(41372) UPDATE 45996 be/4 postgres 0.00 B/s 2003.83 K/s 0.00 % 1.04 % postgres: postgres postgres 127.0.0.1(41382) UPDATE 45993 be/4 postgres 0.00 B/s 1918.73 K/s 0.00 % 1.02 % postgres: postgres postgres 127.0.0.1(41376) UPDATE 45972 be/4 postgres 0.00 B/s 1903.25 K/s 0.00 % 1.02 % postgres: postgres postgres 127.0.0.1(41334) UPDATE 45978 be/4 postgres 0.00 B/s 1740.78 K/s 0.00 % 1.02 % postgres: postgres postgres 127.0.0.1(41346) UPDATE 45998 be/4 postgres 0.00 B/s 1841.36 K/s 0.00 % 1.02 % postgres: postgres postgres 127.0.0.1(41386) UPDATE 45981 be/4 postgres 0.00 B/s 1818.15 K/s 0.00 % 1.01 % postgres: postgres postgres 127.0.0.1(41352) UPDATE 45989 be/4 postgres 0.00 B/s 1895.52 K/s 0.00 % 1.01 % postgres: postgres postgres 127.0.0.1(41368) UPDATE 45973 be/4 postgres 0.00 B/s 1941.94 K/s 0.00 % 0.99 % postgres: postgres postgres 127.0.0.1(41336) idle 45961 be/4 postgres 0.00 B/s 1872.31 K/s 0.00 % 0.96 % postgres: postgres postgres 127.0.0.1(41332) UPDATE 49039 be/4 postgres 0.00 B/s 4.37 M/s 0.00 % 0.00 % postgres: writer process 49036 be/4 postgres 0.00 B/s 3.87 K/s 0.00 % 0.00 % postgres: logger process
使用HOT技术，使得TPS从 53922 提升到了 76157 。IO资源消耗从 192MB/s 降低到了 14MB/s 。
HOT、二级索引、zheap存储引擎，都可以解决更新引入的索引放大问题。
本文介绍了HOT，HOT必须满足如下两个条件：
Necessary Condition A: UPDATE does not change any of the index keys Necessary Condition B: The new version should fit in the same old block – HOT chains can not cross block boundary. 1、索引字段的值不变。(其中任意一个索引字段的值发生了变化，则所有索引都需要新增版本) 2、新的版本与旧的版本在同一个HEAP PAGE中。
src/backend/access/heap/README.HOT
转载地址：http://dyell.baihongyu.com/