Postgresql查询效率计算初探

    摘要

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

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

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

    主要关注的参数和表

    参数:来自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);
    ```

    对比下面的四种情况

    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) 

    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) 

    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) 

    把数据乱序插入

    
    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)

    结论

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

    总结

    以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对lingkb的支持。