ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL DBA(55) - MVCC#8(对全表扫描的影响)

PostgreSQL DBA(55) - MVCC#8(对全表扫描的影响)

原创 PostgreSQL 作者:husthxd 时间:2019-07-16 10:54:34 0 删除 编辑

本节内容来源于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
Understanding System Columns in PostgreSQL

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6906/viewspace-2650640/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
ITPUB数据库版块资深版主,对Oracle、PostgreSQL有深入研究。现就职于广州云图数据技术有限公司,系统架构师。

注册时间:2007-12-28

  • 博文量
    1347
  • 访问量
    3809670