PostgreSQL 12: EXPLAIN 新增 SETTINGS 选项显示非默认优化器参数

PostgreSQL 12 版本 EXPLAIN 命令新增加 SETTINGS 选项,可以显示已设置的非默认优化器相关参数,这个功能对SQL性能分析很有用,当SQL出现性能问题SQL执行计划变化时,可以通过此参数分析。

本文简单演示 EXPLAIN 命令的 SETTINGS 选项的使用。

发行说明

Add EXPLAIN option SETTINGS to output non-default optimizer settings (Tomas Vondra)

This can also be output in auto-explain via auto_explain.log_settings.

发行说明提到两点:

  • EXPLAIN 命令新增加 SETTINGS 选项,可以显示已设置的非默认优化器相关参数。
  • auto-explain模块新增 auto_explain.log_settings 参数,控制在记录执行计划时是否记录非默认的优化器相关参数信息。

补丁说明

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Add SETTINGS option to EXPLAIN, to print modified settings.


Query planning is affected by a number of configuration options, and it
may be crucial to know which of those options were set to non-default
values. With this patch you can say EXPLAIN (SETTINGS ON) to include
that information in the query plan. Only options affecting planning,
with values different from the built-in default are printed.

This patch also adds auto_explain.log_settings option, providing the
same capability in auto_explain module.

Author: Tomas Vondra

Discussion: https://postgr.es/m/e1791b4c-df9c-be02-edc5-7c8874944be0@2ndquadrant.com

EXPLAIN (SETTINGS) 演示

先来看一个执行计划如下:

1
2
3
4
5
6
7
8
9
10
[pg12@pghost2 ~]$ psql mydb pguser
psql (12beta2)
Type "help" for help.

mydb=> EXPLAIN (SETTINGS) SELECT * FROM log_sample WHERE id = 1;
QUERY PLAN
-----------------------------------------------------------------------------------
Index Scan using log_sample_pkey on log_sample (cost=0.42..8.44 rows=1 width=27)
Index Cond: (id = 1)
(2 rows)

设置 random_page_cost 参数,查看执行计划,如下:

1
2
3
4
5
6
7
8
9
10
mydb=> SET random_page_cost = 2.0 ;
SET

mydb=> EXPLAIN (SETTINGS) SELECT * FROM log_sample WHERE id = 1;
QUERY PLAN
-----------------------------------------------------------------------------------
Index Scan using log_sample_pkey on log_sample (cost=0.42..4.44 rows=1 width=27)
Index Cond: (id = 1)
Settings: random_page_cost = '2'
(3 rows)

以上执行计划关键字 Settings 这行显示了非默认的优化器相关参数。

设置另一个参数,如下:

1
2
3
4
5
6
7
8
9
10
mydb=> SET enable_bitmapscan = off;
SET

mydb=> EXPLAIN (SETTINGS) SELECT * FROM log_sample WHERE id = 1;
QUERY PLAN
-----------------------------------------------------------------------------------
Index Scan using log_sample_pkey on log_sample (cost=0.42..4.44 rows=1 width=27)
Index Cond: (id = 1)
Settings: enable_bitmapscan = 'off', random_page_cost = '2'
(3 rows)

可见,显示了多个非默认优化器相关参数。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%