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=#
```

PostgreSQL DBA(182) - HOT

相關文章