这篇文章主要给大家介绍了关于Postgresql查询效率计算的相关资料,文中通过示例代码介绍的非常详细,对大家学习或者使用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)

结论

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

总结

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

最新资讯
途牛12月1日发布2020年第三季度财报

途牛12月1日发布2020

途牛今日宣布,将于美国东部时间12月1日美国股市开盘前(
华米科技第三季度营收22.351亿元 同比增长20%

华米科技第三季度营收

华米科技今日发布了截至9月30日的2019年第三季度财报,
中国电信柯瑞文:5G好不好用户说了算 三方面推动数字经济发展

中国电信柯瑞文:5G好不

中国电信董事长柯瑞文今日在世界互联网大会主论坛上,围
行业加速出清之际 还有哪些P2P平台在发标?

行业加速出清之际 还

潮起潮落终有时,曾经红极一时的P2P网贷亦是如此,然而谁
同程艺龙第三季度营收19.14亿元,同比下降64.79%

同程艺龙第三季度营收

同程艺龙发布第三季度财报。财报显示,第三季度营收19.1
蛋壳要碎,危及37万租客,多少亿才能拯救它?

蛋壳要碎,危及37万租客

蛋壳公寓累计亏损超60亿元,潜在债务完全是个无底洞,若想
最新文章
PostgreSQL基础知识之SQL操作符实践指南

PostgreSQL基础知识之

这篇文章主要给大家介绍了关于PostgreSQL基础知识之SQ
pgsql查询优化之模糊查询实例详解

pgsql查询优化之模糊

这篇文章主要给大家介绍了关于pgsql查询优化之模糊查
Ubuntu PostgreSQL安装和配置的介绍

Ubuntu PostgreSQL安

今天小编就为大家分享一篇关于Ubuntu PostgreSQL安装
PostgreSQL实现一个通用标签系统

PostgreSQL实现一个通

这篇文章主要给大家介绍了关于利用PostgreSQL实现一个
PostgreSQL中使用数组改进性能实例代码

PostgreSQL中使用数组

这篇文章主要给大家介绍了关于PostgreSQL中使用数组改
Postgresql主从异步流复制方案的深入探究

Postgresql主从异步流

这篇文章主要给大家介绍了关于Postgresql主从异步流复