explain命令

在sql语句前加上explain关键字来显式SQL命令的执行计划。
pgsql中的explain语法格式如下：

1
EXPLAIN [(option[, ...])] statement;

其中option有以下几个可选项：

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

ANALYZE关键字会通过实际执行SQL语句来获得实际执行计划，因此可以获取到执行计划的每一步耗费了多少时间，以及它实际返回的行数；
VERBOSE关键字会显示额外的附加信息，比如计划树中的每个节点输出的各个列，如果有触发器被触发，还会输出触发器的名称，默认值是false；
COSTS关键字会显示每个计划节点的启动成本和总成本，及估计的行数和每行的宽度，默认是true；
BUFFERS关键字会显示缓冲区的使用情况，BUFFERS只能和ANALYZE关键字一起使用，显示的缓冲区信息包括共享块的读写块数，本地块的读写块数，以及临时块读写块数。共享块、本地块和临时块分别包含表和索引、临时表和临时索引，以及在排序和物化计划中使用的磁盘块。上层节点显示的块数包含了所有子节点使用的块数。默认值是false。
FORMAT关键字可以设置计划输出的格式，默认是TEXT文本格式。

示例

1
2
3
4
5
6
7
8
9
ubuntu=> explain(analyse true, verbose true, buffers true) select * from explain_table;
                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Seq Scan on pg_temp.explain_table  (cost=0.00..18.10 rows=810 width=72) (actual time=0.012..0.014 rows=2 loops=1)
   Output: id, name, salary
   Buffers: local hit=1
 Planning Time: 0.070 ms
 Execution Time: 0.031 ms
(5 rows)

表示在表pg_temp.explain_table上执行顺序扫描，启动成本和最终成本通过“…”隔开，启动成本是返回第一行的cost值，最终成本是返回全部数据的cost值。紧接着显示了估计会返回的行数和每行的宽度，示例中估计会返回810行，每行需要72字节的宽度。由于加上了ANALYZE关键字，所以会实际执行一遍，并返回实际执行的耗时和返回的行数。下一行打印了加上VERBOSE关键字会输出的附加信息，会输出三个属性id，name和salary。然后Buffers表示本次查询只访问只访问了本地的一个数据块。

cost值是数据库中定义的每个命令执行的成本，便于进行后续的优化工作，默认情况下，不用操作的cost设置如下：
顺序扫描一个数据块，cost值 = 1；
随机扫描一个数据块，cost值 = 4；
处理一个数据行的cpu代价，cost值 = 0.01；
处理一个索引行的cpu代价，cost值 = 0.005；
每个操作符的cpu代价，cost值 = 0.0025。

解释

代价可以通过查询代价模型的参数查看，比如顺序扫描一个块的代价对应的参数是seq_page_cost：

1
2
3
4
5
ubuntu=> SHOW seq_page_cost;
 seq_page_cost
---------------
 1
(1 row)

过滤是由CPU来执行，涉及到两个参数cpu_tuple_cost和cpu_operator_cost：

1
2
3
4
5
6
7
8
9
10
11
ubuntu=> SHOW cpu_tuple_cost;
 cpu_tuple_cost
----------------
 0.01
(1 row)

ubuntu=> SHOW cpu_operator_cost;
 cpu_operator_cost
-------------------
 0.0025
(1 row)

简单的做个测试，在已知有4百万条数据的表中查询一条，关闭并行查询。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
ubuntu=> SELECT pg_relation_size('test') / 8192;
 ?column?
----------
    21622
(1 row)

ubuntu=> SET max_parallel_workers_per_gather TO 0;
SET

ubuntu=> EXPLAIN SELECT * FROM test where id = 23333;
                       QUERY PLAN
--------------------------------------------------------
 Seq Scan on test  (cost=0.00..71622.00 rows=1 width=9)
   Filter: (id = 23333)
(2 rows)

得到的执行代价是71622，在默认8k一数据块的情况下，4百万数据占了21622个数据块，216221+40000000.01+4000000*0.0025=71622，和执行计划给出的代价一致。

pg中还有其他的相关参数，比如
索引操作的代价random_page_cost(默认是4，主要是因为机械硬盘上的随机访问要比顺序代价高，而在固态上两者基本没差，可以在postgresql.conf中将索引的代价调低，比如设置为1)；
索引的CPU代价cpu_index_tuple_cost = 0.005；
并行工作进程向另外的进程传递数据的代价parallel_tuple_cost = 0.1；
开启一个并行工作进程的代价parallel_setup_cost = 1000.0；
等等。

统计信息收集

pgsql的信息主要是AutoVacuum进程进行收集，用于查询优化时的代价估计。表和索引的行数、块数等统计信息存放在“pg_class”系统表中；其他统计信息主要收集在“pg_statistic”系统表中；其他性能数据可以通过查询“pg_stat_*”对应的视图获得。
一般不需要去修改统计信息收集相关的参数，如果查询优化器执行的不是最优的计划，往往是因为没有收集到足够的统计信息导致的，可以通过更频繁的统计信息收集或者手动执行统计来支持查询优化器的选择判断。
手动执行统计的命令是

1
ANALYZE [VERBOSE] [tablename [(column [, ...])]];

比较常用的策略是在数据库比较空闲的时候运行一次VACUUM和ANALYZE命令。