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查询成本计算初探》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

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

图形程序开发人员指南

图形程序开发人员指南

Michael Abrash / 前导工作室 / 机械工业出版社 / 1998 / 128

Michael Abrash's classic Graphics Programming Black Book is a compilation of Michael's previous writings on assembly language and graphics programming (including from his "Graphics Programming" column......一起来看看 《图形程序开发人员指南》 这本书的介绍吧!

JSON 在线解析
JSON 在线解析

在线 JSON 格式化工具

SHA 加密
SHA 加密

SHA 加密工具

html转js在线工具
html转js在线工具

html转js在线工具