PostgreSQL的HOT(Heap Only Tuple)技术在更新数据时通过修改原行指针的方式指向新Tuple的行指针,从而减少了维护索引开销,
值得注意的是,HOT需满足以下两个条件:
1、索引字段的值不变
2、新的版本与旧的版本在同一个HEAP PAGE中(表级参数:fillfactor)

没有索引的情况

[pgmaster@dev beta1]$ psql
Timing is on.
Expanded display is used automatically.
psql (14beta1)
Type "help" for help.
[local]:5014 pgmaster@testdb=# create table t(id int,c1 varchar);
CREATE TABLE
Time: 8.733 ms
[local]:5014 pgmaster@testdb=# insert into t select x,'c1'||x from generate_series(1,1000000) as x;
INSERT 0 1000000
Time: 3659.622 ms (00:03.660)
[local]:5014 pgmaster@testdb=# update t set c1 = 'TEST';
UPDATE 1000000
Time: 4958.278 ms (00:04.958)
[local]:5014 pgmaster@testdb=#
[local]:5014 pgmaster@testdb=# create table t(id int,c1 varchar) with (fillfactor = 50);
CREATE TABLE
Time: 6.214 ms
[local]:5014 pgmaster@testdb=# insert into t select x,'c1'||x from generate_series(1,1000000) as x;
INSERT 0 1000000
Time: 3437.055 ms (00:03.437)
[local]:5014 pgmaster@testdb=# update t set c1 = 'TEST';
UPDATE 1000000
Time: 3634.432 ms (00:03.634)
[local]:5014 pgmaster@testdb=#

没有索引,fillfactor为100和50的情况下,时间分别是4958ms和3634ms

有索引,更新非索引字段

[local]:5014 pgmaster@testdb=# drop table t;
DROP TABLE
Time: 17.293 ms
[local]:5014 pgmaster@testdb=# create table t(id int,c1 varchar);
CREATE TABLE
Time: 7.536 ms
[local]:5014 pgmaster@testdb=# insert into t select x,'c1'||x from generate_series(1,1000000) as x;
INSERT 0 1000000
Time: 3126.884 ms (00:03.127)
[local]:5014 pgmaster@testdb=# create index idx_t_id on t(id);
CREATE INDEX
Time: 977.749 ms
[local]:5014 pgmaster@testdb=# update t set c1 = 'TEST';
UPDATE 1000000
Time: 12563.625 ms (00:12.564)
[local]:5014 pgmaster@testdb=#
[local]:5014 pgmaster@testdb=# drop table t;
DROP TABLE
Time: 37.979 ms
[local]:5014 pgmaster@testdb=# create table t(id int,c1 varchar) with (fillfactor = 50);
CREATE TABLE
Time: 6.363 ms
[local]:5014 pgmaster@testdb=# insert into t select x,'c1'||x from generate_series(1,1000000) as x;
INSERT 0 1000000
Time: 3365.423 ms (00:03.365)
[local]:5014 pgmaster@testdb=# create index idx_t_id on t(id);
CREATE INDEX
Time: 1108.723 ms (00:01.109)
[local]:5014 pgmaster@testdb=# update t set c1 = 'TEST';
UPDATE 1000000
Time: 3680.839 ms (00:03.681)
[local]:5014 pgmaster@testdb=#
[local]:5014 pgmaster@testdb=#

存在索引,fillfactor为100和50的情况下,时间分别是12563ms和3680ms.
可以看出,为了维护索引,在fillfactor=100的情况下,更新的时间是没有索引的两倍不止,而fillfactor=50的情况下,时间与没有索引相当.

更新索引字段

[local]:5014 pgmaster@testdb=# drop table t;
DROP TABLE
Time: 18.273 ms
[local]:5014 pgmaster@testdb=# create table t(id int,c1 varchar) with (fillfactor = 100);
CREATE TABLE
Time: 7.149 ms
[local]:5014 pgmaster@testdb=# insert into t select x,'c1'||x from generate_series(1,1000000) as x;
INSERT 0 1000000
Time: 2811.478 ms (00:02.811)
[local]:5014 pgmaster@testdb=# create index idx_t_id on t(id);
CREATE INDEX
Time: 857.703 ms
[local]:5014 pgmaster@testdb=# update t set id = 100;
UPDATE 1000000
Time: 9961.625 ms (00:09.962)
[local]:5014 pgmaster@testdb=#
[local]:5014 pgmaster@testdb=# drop table t;
DROP TABLE
Time: 16.775 ms
[local]:5014 pgmaster@testdb=# create table t(id int,c1 varchar) with (fillfactor = 50);
CREATE TABLE
Time: 6.526 ms
[local]:5014 pgmaster@testdb=# insert into t select x,'c1'||x from generate_series(1,1000000) as x;
INSERT 0 1000000
Time: 2906.845 ms (00:02.907)
[local]:5014 pgmaster@testdb=# create index idx_t_id on t(id);
CREATE INDEX
Time: 879.316 ms
[local]:5014 pgmaster@testdb=# update t set id = 100;
UPDATE 1000000
Time: 9126.076 ms (00:09.126)

更新索引字段,不管fillfactor是多少,两者时间接近,但均差于没有索引的情况,但差距不大.

小结
PG提供HOT技术,但要用上HOT,但如果使用默认的参数(fillfactor=100)并没有效果,原因是更新之后的tuple并不能落在同一个page上面,
因此需与fillfactor参数配合使用,而且在多次更新的情况下不会出现剧烈波动

[local]:5014 pgmaster@testdb=# drop table t;
DROP TABLE
Time: 18.715 ms
[local]:5014 pgmaster@testdb=# create table t(id int,c1 varchar) with (fillfactor = 50);
CREATE TABLE
Time: 7.896 ms
[local]:5014 pgmaster@testdb=# insert into t select x,'c1'||x from generate_series(1,1000000) as x;
INSERT 0 1000000
Time: 3081.390 ms (00:03.081)
[local]:5014 pgmaster@testdb=# create index idx_t_id on t(id);
CREATE INDEX
Time: 1116.643 ms (00:01.117)
[local]:5014 pgmaster@testdb=# update t set c1 = 'TEST';
UPDATE 1000000
Time: 3162.766 ms (00:03.163)
[local]:5014 pgmaster@testdb=# update t set c1 = 'TEST';
UPDATE 1000000
Time: 3697.414 ms (00:03.697)
[local]:5014 pgmaster@testdb=# update t set c1 = 'TEST';
UPDATE 1000000
Time: 4136.395 ms (00:04.136)
[local]:5014 pgmaster@testdb=# update t set c1 = 'TEST';
UPDATE 1000000
Time: 4922.762 ms (00:04.923)
[local]:5014 pgmaster@testdb=# update t set c1 = 'TEST';
UPDATE 1000000
Time: 4154.564 ms (00:04.155)
[local]:5014 pgmaster@testdb=#