PostgreSQL 的查询执行计划

2016/05/05 PostgreSQL

查询执行计划一般用来找出 SQL 的性能瓶颈。通常情况下我会抓出一批执行比较慢的 SQL,然后逐条分析,找出影响性能的因素,然后逐一解决。当然最容易解决的一种情况属于索引缺失的情况。

关系数据库一般使用 explain 来显示 SQL 的查询执行计划,只不过具体的参数不同而已。

PostgreSQL 中的 explain 命令的格式如下:

EXPLAIN [option] statement.

命令行中的选项 option 可以是:

ANALYZE [boolean]
VERBOSE [boolean]
COSTS [boolean]
BUFFERS [boolean]
FORMAT {TEXT | XML | JSON | YAML}

ANALYZE 会真正去执行 SQL 来获得,实际的执行计划。因为 ANALYZE 会真正被执行,所以我们可以看到每一步具体的时间消耗,以及实际返回的数据的行数。加上 ANALYZE 回去真正执行 SQL,如果是一些更新、删除或者插入的语句,以及一些 DDL 语句,是会对数据库有改变的。所以如果不想对数据库有所改变,可以把这些语句放到一个事务里面,执行完成可以立即回滚,具体做法如下:

BEGIN;
EXPLAIN ANALYZE;
ROLLBACK;
  • VERBOSE 选项用于显示执行计划的附加信息。这些附加信息包括:计划树中每个节点输出的各个列,如果触发器被触发,还会输出触发器的名称。该选项的默认值为 FALSE。
  • COSTS 选项显示每个计划节点的启动成本和总成本,以及估计的行数和每行宽度。该选项的默认值为 TRUE。
  • BUFFERS 选项显示关于缓冲区的使用信息。该参数只能与 ANALYZE 参数一起使用。显示的共享信息包括共享块、本地块和临时块分别包含表和索引、临时表和临时索引,以及在排序和物化计划中使用的磁盘块。上层节点显示的块数包括其所有子节点使用的块数。该选项的默认值为 FALSE。

FORMAT 选项指定输出格式,其实处格式可以是 TEXT,XML,JSON,YAML。该参数默认为 TEXT。

对 EXPLAIN 输出结果的解释:

kjds_20160308=# explain select * from address;
QUERY PLAN
------------------------------------------------------------
Seq Scan on address (cost=0.00..10.20 rows=20 width=4044)
(1 row)

结果中 “Seq Scan on address” 表示顺序扫描表 “address”,顺序扫描也就是全表扫描。后面的内容 (cost=0.00..10.20 rows=20 width=4044) 分三部分:

  • cost=0.00..10.20: “cost=” 后面有连个数字,中间是由 “..” 分隔,第一个数字 “0.00” 表示启动的成本,也就是说返回一行需要多少 cost 值;第二个数字表示返回所有数据的成本。
  • row=20 : 表示会返回 20 行
  • width=4044: 表示每行的平均宽度为 4044 字节

成本 “cost” 描述一个 SQL 执行的代价是多少,默认情况下不同的操作 cost 值如下:

  • 顺序扫描一个数据块,cost 值为 1
  • 随机扫描一个数据库,cost 值为 4
  • 处理一个数据行的CPU,cost 值为 0.01
  • 处理一个索引行的CPU,cost 值为 0.005
  • 每个操作符的 CPU 代价为 0.0025

Search

    Table of Contents