PostgreSQL 查询计划时间的计算详解

1. 准备，新建一个测试表

postgres=# create table dbsys_groups(id serial,sid character varying,name character varying);
CREATE TABLE

2. 为该表插入测试数据

postgres=# insert into dbsys_groups values(generate_series(1,1000),md5(random()::text),md5(random()::text));
INSERT 0 1000

3. 查看 select * from dbsys_groups 的查询计划

postgres=# explain analyze select * from dbsys_groups;
                                                  QUERY PLAN                                               
--------------------------------------------------------------------------------
 Seq Scan on dbsys_groups  (cost=0.00..23.00 rows=1000 width=70) (actual time=0.010..0.102 rows=1000 loops=1)
 Total runtime: 0.199 ms
(2 rows)

可以看到总时间花费了23。

注意： "actual time"数值是以真实时间的毫秒来计算的，而"cost"预估值是以磁盘页面读取数量来计算的，所以它们很可能是不一致的。然而我们需要关注的只是两组数据的比值是否一致。

参考： http://www.cnblogs.com/stephen-liu74/archive/2011/12/25/2301064.html

现在来解释下这个时间 PostgreSQL的优化器是怎么计算出来的。

首先解释下PostgreSQL 的cost 的系统常量：

   ① seq_page_cost：连续块扫描操作的单个块的cost. 例如全表扫描
   ② random_page_cost： 随机块扫描操作的单个块的cost. 例如索引扫描
   ③ cpu_tuple_cost：处理每条记录的CPU开销（tuple：关系中的一行记录）
   ④ cpu_index_tuple_cost：扫描每个索引条目带来的CPU开销
   ⑤ cpu_operator_cost：操作符或函数带来的CPU开销.(需要注意函数以及操作符对应的函数的三态, 执行计划会根据三态做优化, 关系到多条记录时三态对应的调用次数是需要关心的)

具体查看这些常量的具体值可以通过 show xxx 查看，比如：

postgres=# show seq_page_cost ;
 seq_page_cost 
---------------
 1
(1 row)

表示顺序扫描一个page页的单位成本是1。

所以对于上面的查询计划使用的顺序扫描，我们知道总成本是23，现在我们只需要知道dbsys_groups所占的是几个page和总共几条记录，然后和通过公式

     总成本=seq_page_cost*page数+cpu_tuple_cost*tuple数

就可以得到。

通过系统表pg_class来查看：

postgres=# select relpages,reltuples from pg_class where relname='dbsys_groups'; 
 relpages | reltuples 
----------+-----------
       13 |      1000
(1 row)

可以看到dbsys_groups表所占的page数是13，总记录数是1000.

然后看看的cpu处理每一行的cost值是多少

postgres=# show cpu_tuple_cost ;
 cpu_tuple_cost 
----------------
 0.01
(1 row)

所以总的cost是 1(seq_page_cost)*13(磁盘总页数)+0.01(cpu_tuple_cost)*1000(表的总记录数)=23。

5. 看看下一个查询计划：

postgres=# explain analyze select * from dbsys_groups where id<300;
                                                 QUERY PLAN                                               
--------------------------------------------------------------------------------
 Seq Scan on dbsys_groups  (cost=0.00..25.50 rows=300 width=70) (actual time=4.715..6.064 rows=299 loops=1)
   Filter: (id < 300)
   Rows Removed by Filter: 701
 Total runtime: 6.164 ms
(4 rows)

先解释下为什么这个查询计划比上一个查询计划返回的行数少，总的cost的时间反而增加呢？原因如下：

1. 该查询计划的扫描数据块的方式和上一个查询计划的扫描方式一样，都是使用了顺序扫描，即两个查询计划的扫描的数据块文件个数是一样的，反而该查询计划在过滤每条记录的时候还需要检查where条件id<300，增加了cpu 的时间开销；

2. 该cost只反映规划器关心的东西。尤其是开销没有把结果行传递给客户端的时间考虑进去，这个时间可能在真正的总时间里面占据相当重要的分量；但是被规划器忽略了，因为它无法通过修改规划来改变之

接下来开始计算cost=25.5是怎么来的：

postgres=# show cpu_operator_cost ;
 cpu_operator_cost 
-------------------
 0.0025
(1 row)

cost = 1(seq_page_cost)*13(磁盘总页数)+0.01(cpu_tuple_cost)*1000(表的总记录数)+0.0025(cpu_operation_cost)*1000(表的总记录数)=25.5