我对PostgreSQL 中执行计划的处理范围的理解

开始

我有一个表，大约有一千万条记录，当我运行一个insert 命令向另一个表插入数据时，由于约束和索引的存在，导致插入数据很慢。

但是这也给了我一个机会，来观察 explain plan 是如何处理数据的。

postgres=# create table ptest(id integer, name varchar(20));
CREATE TABLE
postgres=# create table ctest01(CHECK(id<5000000)) inherits (ptest);
CREATE TABLE
postgres=# create table ctest02(CHECK(id>=5000000)) inherits (ptest);
CREATE TABLE
postgres=# 
postgres=# create index on ctest01(id);
CREATE INDEX
postgres=# create index on ctest02(id);
CREATE INDEX
postgres=# 
postgres=# 

postgres=# CREATE OR REPLACE FUNCTION ptest_insert_trigger() RETURNS TRIGGER AS $$ 
postgres$# 
postgres$# BEGIN 
postgres$# 
postgres$#    IF ( NEW.id <5000000 ) THEN 
postgres$#        INSERT INTO ctest01 VALUES (NEW.*);
postgres$#    ELSIF ( NEW.id >= 5000000 ) THEN 
postgres$#        INSERT INTO ctest02 VALUES (NEW.*); 
postgres$#    ELSE 
postgres$#        RAISE EXCEPTION 'Error while inserting data';
postgres$#    END IF; 
postgres$#   
postgres$#   RETURN NULL;
postgres$# END; $$ LANGUAGE plpgsql;
CREATE FUNCTION
postgres=# 
postgres=# CREATE TRIGGER insert_ptest_trigger BEFORE INSERT ON ptest FOR EACH ROW 
postgres-#   EXECUTE PROCEDURE ptest_insert_trigger();
CREATE TRIGGER
postgres=#

我的test02 表，有一千万条数据，下面我来拷贝数据：

insert into ptest select * from test02;

由于数据量大，约束也在起作用，所以运行了很久（事实上两天后崩溃了（我用的是虚拟机））。

在此期间，我开了另外的session，来执行查询：

postgres=# explain select count(*) from ptest;
                                    QUERY PLAN  
-----------------------------------------------------------------------------------
 Aggregate  (cost=55406.40..55406.41 rows=1 width=0)
   ->  Append  (cost=0.00..49601.92 rows=2321793 width=0)
         ->  Seq Scan on ptest  (cost=0.00..0.00 rows=1 width=0)
         ->  Seq Scan on ctest01 ptest  (cost=0.00..24776.52 rows=1159752 width=0)
         ->  Seq Scan on ctest02 ptest  (cost=0.00..24825.40 rows=1162040 width=0)
(5 rows)postgres=#

其实如果执行 select count(*) from ptest, 由于前面的session 的插入动作耗时很久，尚未结束故尚未提交。所以返回的结果是零。

那么为何执行计划分别在两个子表中看到了各一百多万条的记录呢？

PostgreSQL 的物理存储结构中，没有像oracle 那样的rollback或undo segment，所有的数据映像，都存放在数据块中。如果不进行vacuum操作，旧的已经提交的数据、刚刚刷入磁盘未提交的数据和已经提交的数据就都会在数据块中。

如果我作一个程序，每1分钟修改某表格数据的特定记录一次但是不提交，只要我的事务足够长，那么这将会不断地刷入数据到数据块中。这是PostgreSQL 的一个弱点，也是被人诟病之处。

可以说，在一个并发性很高的系统里，每一次的sql 执行，其成本的一个重要方面，就是磁盘访问物理I/O，而由于PostgreSQL 把数据的前映像（提交的和未提交的）、当前映像(提交的和未提交的)，都存放在一起，使得每个sql访问互相影响（因为有未提交的数据掺和），无疑地降低了性能。

在PostgreSQL 社区询问的结果是，如果只是想看近似的结果，不想用太大代价，可以从 pg_class 入手：

SELECT sum(reltuples) FROM pg_class WHERE relname IN ('ptest', 'ctest01', 'ctest02');

结束

我对PostgreSQL 中 执行计划的处理范围的理解

我对PostgreSQL 中执行计划的处理范围的理解