Postgresql查询成本计算初探

栏目: 数据库 · PostgreSQL · 发布时间: 5年前

内容简介:关系数据库很重要的一个方面是查询速度。查询速度的好坏,直接影响一个系统的好坏。查询速度一般需要通过查询规划来窥视执行的过程。查询路径会选择查询代价最低的路径执行。而这个代价是怎么算出来的呢。

摘要

关系数据库很重要的一个方面是查询速度。查询速度的好坏,直接影响一个系统的好坏。

查询速度一般需要通过查询规划来窥视执行的过程。

查询路径会选择查询代价最低的路径执行。而这个代价是怎么算出来的呢。

主要关注的参数和表

参数:来自postgresql.conf文件,可以通过show 来查看

seq_page_cost = 1.0                    # measured on an arbitrary scale
random_page_cost = 4.0                 # same scale as above
cpu_tuple_cost = 0.01                  # same scale as above
cpu_index_tuple_cost = 0.005           # same scale as above
cpu_operator_cost = 0.0025             # same scale as above
parallel_tuple_cost = 0.1              # same scale as above
parallel_setup_cost = 1000.0           # same scale as above

表(视图): pg_class(主要关注relpages, reltuples), pg_stats

分析简单的查询的成本计算过程

建立模拟数据,插入100000条数据进入一个表

create table test(id int, info text);
insert into test(id, info) select i, md5(i::text) from generate_series(1, 100000) t(i);

没有索引的情况

分析全表查询的成本计算过程

postgres=# analyze test;        #防止没有分析
postgres=# explain select * from test;
                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on test  (cost=0.00..1834.00 rows=100000 width=37)
  1. 查询pg_class表,查看test表的page数量和行数
    postgres=# select t.relpages, t.reltuples from pg_class t where t.relname = 'test';
     relpages | reltuples 
    ----------+-----------
          834 |    100000
    成本为1834.00是怎么算出来的?
  2. 这个过程,实际上是顺序扫描了834个page,节点发射了100000行
  3. 查看配置参数
    seq_page_cost = 1.0 
    cpu_tuple_cost = 0.01
  4. 得出的结果就是
    postgres=# select 834 * 1.0 + 100000 * 0.01;
     ?column? 
    ----------
      1834.00
  5. 得出来的查询成本就是 1834.00。和上面的查询计划算出来的一致。

全表加入条件的成本计算过程

postgres=# explain select * from test where id = 100;
                       QUERY PLAN                       
--------------------------------------------------------
 Seq Scan on test  (cost=0.00..2084.00 rows=1 width=37)
   Filter: (id = 100)

成本 2084.00是怎么算出来的?

  1. 查询pg_class表, pages,tuples和上面的例子一样
  2. 这个过程就是顺序test表,发射100000行,然后通过云存过滤了100000行
  3. 查看过滤运算一行的代价
    cpu_operator_cost = 0.0025
  4. 得出的结果是
    postgres=# select 834 * 1.0 + 100000 * 0.01 + 100000 * 0.0025;
     ?column?  
    -----------
     2084.0000

加入索引的情况

``
create index on test(id);
```

对比下面的四种情况

  1. Index Only Scan
postgres=# explain select id from test where id = 100;
                                 QUERY PLAN                                  
-----------------------------------------------------------------------------
 Index Only Scan using test_id_idx on test  (cost=0.29..8.31 rows=1 width=4)
   Index Cond: (id = 100)
  1. Index Scan
postgres=# explain select * from test where id = 100;
                               QUERY PLAN                                
-------------------------------------------------------------------------
 Index Scan using test_id_idx on test  (cost=0.29..8.31 rows=1 width=37)
   Index Cond: (id = 100)
  1. Index Scan
postgres=# explain select * from test where id < 100;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Index Scan using test_id_idx on test  (cost=0.29..10.11 rows=104 width=37)
   Index Cond: (id < 100)
  1. 把数据乱序插入
truncate table test;
insert into test(id, info) select i, md5(i::text) from generate_series(1, 1000000) t(i) order by random();
postgres=# explain select * from test where id < 100;
                                 QUERY PLAN                                 
----------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=5.22..380.64 rows=102 width=37)
   Recheck Cond: (id < 100)
   ->  Bitmap Index Scan on test_id_idx  (cost=0.00..5.19 rows=102 width=0)
         Index Cond: (id < 100)

结论

  1. 有索引的时候,成本会大大减少。
  2. 执行计划跟数据的分布有很大的关系。
  3. 有索引的分析相对复杂一点,可以先参考 官方源码实现 。后面再补充上来

以上所述就是小编给大家介绍的《Postgresql查询成本计算初探》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

重新定义团队:谷歌如何工作

重新定义团队:谷歌如何工作

拉兹洛·博克 / 宋伟 / 中信出版集团 / 2015-12-1 / CNY 56.00

谷歌首席人才官拉斯洛•博克权威力作,谷歌公开认可的谷歌高层作品,首度揭秘谷歌颠覆工业时代模式的人才和团队管理的核心法则,《纽约时报》畅销榜第一名,Business Insider 2015最佳商业书籍,谷歌的创造力就在于此! 编辑推荐! 1、 谷歌人才官首次公开谷歌人才和团队管理的核心秘籍 在谷歌执掌人事多年的拉斯洛•博克是人才和团队管理的顶级专家。他加入谷歌后,谷歌的员工数从六......一起来看看 《重新定义团队:谷歌如何工作》 这本书的介绍吧!

JS 压缩/解压工具
JS 压缩/解压工具

在线压缩/解压 JS 代码

MD5 加密
MD5 加密

MD5 加密工具

SHA 加密
SHA 加密

SHA 加密工具