本节内容来源于PGer的一个问题: Q: 由于多版本的存在，那么全表扫描是不是需要更长的时间了呢？ A: 关于全表扫描,不妨考虑2种极端的情况: 1.insert数据(事务已提交,下同),没有执行update/delete,没有dead tuple,全表扫描效率没有影响; 2.insert数据,执行了大量的update/delete,同时禁用了autovacuum也没有手工执行vacuum,那么存在大量的dead tuple,性能上一是需要更多的IO操作,二是需要执行额外的CPU判断(对于所有的tuple都要执行可见性判断). 其判断逻辑如下: ``` ((Xmin == my-transaction &&　　　　　　　inserted by the current transaction 　Cmin < my-command &&　　　　　　　　　　before this command, and 　(Xmax is null ||　　　　　　　　　　　　the row has not been deleted, or 　　(Xmax == my-transaction &&　　　　　　it was deleted by the current transaction 　　　Cmax >= my-command)))　　　　　　　but not before this command, ||　　　　　　　　　　　　　　　　　　　　　or 　(Xmin is committed &&　　　　　　　　　　the row was inserted by a committed transaction, and 　　(Xmax is null ||　　　　　　　　　　　　the row has not been deleted, or 　　　(Xmax == my-transaction &&　　　　　the row is being deleted by this transaction 　　　　Cmax >= my-command) ||　　　　　　but it’s not deleted "yet", or 　　　　(Xmax != my-transaction &&　　　　the row was deleted by another transaction 　　　　　Xmax is not committed))))　　　　that has not been committed ``` 简单做个实验,创建一张表t_fts, 1.插入数据,大小为s1,执行全表扫描,时间为m秒; 2.update所有行,大小为s2,执行全表扫描,时间为n秒. 理论上来说,n应为m的s2/s1倍左右(相对于IO时间,如果tuple数不多,CPU时间可以忽略不计). 创建数据表,插入数据: ``` testdb=# drop table if exists t_fts; DROP TABLE testdb=# create table t_fts(id int,c1 varchar(200),c2 varchar(200)); CREATE TABLE testdb=# testdb=# insert into t_fts select x,lpad('c1'||x,200,'x'),lpad('c1'||x,200,'x') from generate_series(1,2000000) as x; INSERT 0 2000000 testdb=# select pg_size_pretty(pg_table_size('t_fts')); pg_size_pretty ---------------- 868 MB (1 row) ``` 禁用autovacuum,执行查询: ``` testdb=# alter system set autovacuum=off; ALTER SYSTEM testdb=# show autovacuum; autovacuum ------------ off (1 row) testdb=# explain analyze verbose select * from t_fts; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------- Seq Scan on public.t_fts (cost=0.00..131112.16 rows=2000016 width=412) (actual time=0.048..1086.289 rows=2000000 loops=1) Output: id, c1, c2 Planning Time: 30.762 ms Execution Time: 1181.360 ms (4 rows) ``` 执行update: ``` testdb=# update t_fts set c1 = lpad('c1'||(id+1),200,id+1||''),c2 = lpad('c1'||(id+1),200,id+1||''); UPDATE 2000000 testdb=# select pg_size_pretty(pg_table_size('t_fts')); pg_size_pretty ---------------- 1737 MB (1 row) testdb=# explain analyze verbose select * from t_fts; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- -- Seq Scan on public.t_fts (cost=0.00..262223.14 rows=4000014 width=412) (actual time=3168.414..6117.780 rows=2000000 loops=1 ) Output: id, c1, c2 Planning Time: 5.493 ms Execution Time: 6205.705 ms (4 rows) testdb=# explain analyze verbose select * from t_fts; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- - Seq Scan on public.t_fts (cost=0.00..262223.14 rows=4000014 width=412) (actual time=776.660..2311.270 rows=2000000 loops=1) Output: id, c1, c2 Planning Time: 0.426 ms Execution Time: 2391.895 ms (4 rows) testdb=# explain analyze verbose select * from t_fts; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- - Seq Scan on public.t_fts (cost=0.00..262223.14 rows=4000014 width=412) (actual time=728.758..2293.157 rows=2000000 loops=1) Output: id, c1, c2 Planning Time: 0.481 ms Execution Time: 2373.241 ms (4 rows) ``` **参考资料** [Concurrency Control](http://www.interdb.jp/pg/pgsql05.html) [Understanding System Columns in PostgreSQL](https://severalnines.com/blog/understanding-system-columns-postgresql)