PostgreSQL查询成本模型

标签: tuicool | 发表时间:2017-12-22 00:00 | 作者:
出处:http://itindex.net/relian

原文: The PostgreSQL Query Cost Model

作者: igor_sarcevic

翻译:无阻我飞扬

摘要:PostgreSQL是一个自由的对象-关系数据库服务器(数据库管理系统),被业界誉为“最先进的开源数据库”,本文作者以代码实例说明了如何计算和评估PostgreSQL查询成本,以下是译文。

数据库查询速度如果太慢会从很多方面损害机构,比如可能会损害一些优秀应用程序的声誉,因为数据库查询速度过慢,造成后台处理速度慢得令人痛苦,并大幅增加基础设施的成本。作为一名经验丰富的Web开发人员,了解数据层的优化策略是绝对必要的。

在本文中,我们将探讨PostgreSQL的成本模型,如何了解 explain命令(关于explain命令可参见这篇 文章)的输出,最重要的是如何利用这些模型数据来提高应用程序的吞吐量。

使用PostgreSQL Explain 命令

在应用程序中部署一个新的查询语句之前,最好通过PostgreSQL中的 explain中的命令来运行它,以评估新查询对应用系统性能的影响。

以一个示例数据库表开始,来说明 explain命令的用法。这个表存有一百万条数据记录。

db # CREATE TABLE users (id serial, name varchar);

db # INSERT INTO users (name) SELECT 'John'
 FROM generate_series(1, 1000000);

db # SELECT count(*) FROM users;
      count

 1000000
(1 row)

db # SELECT id, name FROM users LIMIT 10;
 id | name
----+------
  1 | John
  2 | John
  3 | John
  4 | John
  5 | John
  6 | John
  7 | John
  8 | John
  9 | John
 10 | John
(10 rows)

假设需要用一个给定的id来查找一个用户名,但是在部署新的查询代码之前,要评估这个查询操作的成本。运行一个explain语句来做相关查询:

db # EXPLAIN SELECT * FROM users WHERE id = 870123;

        QUERY PLAN                  
--------------------------------------------------
 Gather  (cost=1000.00..11614.43 rows=1 width=9)
   Workers Planned: 2
           ->  Parallel Seq Scan on users  (cost=0.00..10614.33 rows=1 width=9)
     Filter: (id = 870123)

(4 rows)

在上面的例子中有很多的输出,但是可以得到它的要点。为了运行这个查询,PostgreSQL计划启动两个并行的工作进程(workers)。每个工作进程将在表上进行顺序扫描,最后,收集器合并来自两个工作进程的结果。

在本文中,重点介绍上面输出的 cost以及PostgreSQL如何计算它。

为了简化成本探索,运行上面的查询,但限制可并行的工作进程数量为0。

db # SET max_parallel_workers_per_gather = 0;

db # EXPLAIN SELECT * FROM users WHERE id = 870123;

                   QUERY PLAN
--------------------------------------------------
 Seq Scan on users  (cost=0.00..17906.00 rows=1 width=9)
   Filter: (id = 870123)

(2 rows)

这有点简单。在只有单CPU内核的情况下,评估成本是 17906

成本值背后的数学

在PostgreSQL中,成本或惩罚点大多是一个抽象的概念。PostgreSQL可以执行查询的方式很多,而PostgreSQL总是选择最低成本值的执行规划。

计算成本,PostgreSQL首先查看表的字节数大小。接下来看看用户表的大小。

db # select pg_relation_size('users');

 pg_relation_size
--------------------------
     44285952
(1 row)

PostgreSQL会为每个要依次读取的块添加成本点。如果知道每个块都包含了 8kb,那么就可以计算从表中读取的顺序块的成本值。

block_size = 8192 # block size in bytes
relation_size = 44285952

blocks = relation_size / block_size # => 5406

现在,已经知道块的数量,找出PostgreSQL为每个块读取分配多少个成本点。

db # SHOW seq_page_cost;
 seq_page_cost
 ----------
 1
(1 row)

换句话说,PostgreSQL为每个块分配一个成本点。这就需要 5406个成本点从表中读取数据。

从磁盘读取值并不是PostgreSQL需要做的。它必须将这些值发送给CPU并应用一个 WHERE子句过滤。对于这个计算来说,如下的两个值非常有趣。

db # SHOW cpu_tuple_cost;

 cpu_tuple_cost
--------------------
  0.01
db # SHOW cpu_operator_cost;
 cpu_operator_cost
 ----------------
  0.0025

现在,用所有的值来计算在 explain语句中得到的值。

number_of_records = 1000000

block_size    = 8192     # block size in bytes
relation_size = 44285952

blocks = relation_size / block_size # => 5406

seq_page_cost   = 1
cpu_tuple_cost  = 0.01
cpu_filter_cost = 0.0025;

cost = blocks * seq_page_cost +
   number_of_records * cpu_tuple_cost +
   number_of_records * cpu_filter_cost

cost # => 17546

索引和PostgreSQL成本模型

索引在数据库工程师的生活中很可能仍然是最重要的话题。添加索引是否可以降低SELECT语句的成本呢?通过下面的例子来找出答案。

首先,在users表中添加一个索引:

db # CREATE INDEX idx_users_id ON users (id);

观察一下新索引的查询规划。

db # EXPLAIN SELECT * FROM users WHERE id = 870123;

           QUERY PLAN
----------  ----------  ----------
     Index Scan using idx_users_id on users  (cost=0.42..8.44 rows=1 width=9)
   Index Cond: (id = 870123)
(2 rows)

成本函数显著下降。索引扫描的计算比顺序扫描的计算要复杂一些。它由两个阶段组成。

PostgreSQL会考虑 random_page_costcpu_index_tuple_cost变量,并返回一个基于索引树的高度的值。

db # SHOW random_page_cost;

 random_page_cost
 ----------------
  4

db # SHOW cpu_index_tuple_cost;
 cpu_index_tuple_cost
----------
  0.005

对于实际的计算,请考虑阅读 成本指数计算器的源代码。

工作进程的成本

PostgreSQL可以启动并行的工作进程(worker)来执行查询。但是,开启一个新的工作进程,性能会受到影响。

为了计算使用并行工作进程的成本,PostgreSQL使用 parallel_tuple_cost这个命令,它定义了从一个工作进程传送元组到另一个工作进程的成本, parallel_setup_cost命令意味着启动一个新的工作进程(worker)的成本,以下是查询示例。

db # SHOW parallel_tuple_cost;

 parallel_tuple_cost
---------------------
  0.1

db # SHOW parallel_setup_cost;

 parallel_setup_cost
---------------------
 1000

PS:在本周六的数据库线上峰会上,我们邀请了来自阿里巴巴的周正中老师给大家带来《PostgreSQL流计算案例》分享,欢迎大家报名参加: http://edu.csdn.net/huiyiCourse/series_detail/74

相关 [postgresql 成本 模型] 推荐:

PostgreSQL查询成本模型

- - IT瘾-tuicool
原文: The PostgreSQL Query Cost Model. 作者: igor_sarcevic. 摘要:PostgreSQL是一个自由的对象-关系数据库服务器(数据库管理系统),被业界誉为“最先进的开源数据库”,本文作者以代码实例说明了如何计算和评估PostgreSQL查询成本,以下是译文.

PostgreSQL 9.1发布

- Kai Chen - Solidot
开源数据库项目PostgreSQL发布了v9.1版. 新版本主要的特性包括:同步复制,序列化快照隔离,支持基于列的排序,近邻邻近索引,外来数据封装,支持SELinux许可控制,等等.

PostgreSQL 9.2发布

- - Solidot
时隔一年之后,开源数据库PostgreSQL发布了v9.2版. 主要新特性包括:原生JSON支持,覆盖索引(covering indexes),改进复制和性能等. PostgreSQL显著改善了可伸缩性:线性可扩展性支持最高64核,仅扫描索引,减少CPU消耗;改进垂直可伸缩性:有效利用大服务器硬件资源,锁管理,仅访问索引等底层操作允许数据库引擎处理更大的工作负荷——每秒最高35万只读查询,每秒写入1.4万条数据.

Mysql 和 Postgresql 对比

- - 膘叔
vampire告诉我服务器上安装好了postgreSQL,他也一直在推荐这个玩意,所以了解了一下. Mysql 使用太广泛了,以至于我不得不将一些应用从mysql 迁移到postgresql, 很多开源软件都是以Mysql 作为数据库标准,并且以Mysql 作为抽象基础的,但是具体使用过程中,发现Mysql 有很多问题,所以都迁移到postgresql上了,转一个Mysql 和Postgresql 对比的文章:.

PostgreSQL与MySQL比较

- - 数据库 - ITeye博客
通过执行 MySQL 命令(mysqld)启动实例. 一个实例可以管理一个或多个数据库. 一台服务器可以运行多个 mysqld 实例. 一个实例管理器可以监视 mysqld 的各个实例. 通过执行 Postmaster 进程(pg_ctl)启动实例. 一个实例可以管理一个或多个数据库,这些数据库组成一个集群.

PostgreSQL新手入门

- - 阮一峰的网络日志
自从MySQL被Oracle收购以后, PostgreSQL逐渐成为开源关系型数据库的首选. 本文介绍PostgreSQL的安装和基本用法,供初次使用者上手. 以下内容基于Debian操作系统,其他操作系统实在没有精力兼顾,但是大部分内容应该普遍适用. 首先,安装PostgreSQL客户端. 然后,安装PostgreSQL服务器.

PostgreSQL配置优化

- - CSDN博客推荐文章
转载请注明原文出处: http://blog.csdn.net/roddick621. PostgreSQL配置优化. 200W(整个数据库大小约为300M). 准备命令:pgbench -i -s 20 pgbenchdb. 测试命令:pgbench -r -j4 -c4 -T60 testdb.

PostgreSQL Maestro 14.5 发布

- - 开源中国社区最新新闻
SQL Maestro Group 发布了 PostgreSQL Maestro 14.5 ,这是一个强大的 Windows 下图形化的 PostgreSQL 数据库服务器管理和开发的解决方案. 新版本可通过这里 下载..

PostgreSQL 9.1正式版发布

- Tairan Wang - ITeye资讯频道
PostgreSQL 9.1发布. PostgreSQL(发音为Post-gress-cue-ell)是一个自由的对象-关系数据库服务器(数据库管理系统),基于灵活的 BSD风格许可证. PostgreSQL支持大部分 SQL 标准并且提供了许多其他现代特性:复杂查询、外键、触发器、视图、事务完整性、多版本并发控制等.

ubuntu源码安装postgresql

- - ITeye博客
整个安装过程google无数遍,最后终于搭建完了. 具体步骤,以及遇到error解决方案. 已有 0 人发表留言,猛击->> 这里<<-参与讨论. —软件人才免语言低担保 赴美带薪读研.