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

前端之家收集整理的这篇文章主要介绍了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 原文链接:https://www.f2er.com/postgresql/195395.html

猜你在找的Postgre SQL相关文章