作者
digoal
日期
2019-04-05
标签
PostgreSQL , greenplum , explain , 开关 , settings , set , options , 优化器相关
背景
Greenplum 很早就支持了这个功能，即EXPLAIN时输出用户设置的（非默认）优化器相关参数。换句话说，对执行计划的选择有影响的参数。
```
Add SETTINGS option to EXPLAIN, to print modified settings.
author Tomas Vondra tomas.vondra@postgresql.org
Thu, 4 Apr 2019 06:04:31 +0800 (00:04 +0200)
committer Tomas Vondra tomas.vondra@postgresql.org
Thu, 4 Apr 2019 06:04:31 +0800 (00:04 +0200)
commit ea569d64ac7174d3fe657e3e682d11053ecf1866
tree fa6b135c64de45765a78c7f72b22481fd9b95bc3 tree | snapshot
parent d1f04b96b99d595e80791cdb0faa9cfdde2a5afa commit | diff
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
Reviewed-by: Rafia Sabih, John Naylor
Discussion: https://postgr.es/m/e1791b4c-df9c-be02-edc5-7c8874944be0@2ndquadrant.com
```
例子如下
影响执行计划的参数被显示出来。
```
postgres=# set client_encoding ='utf8';
SET
postgres=# set max_parallel_workers=128;
SET
postgres=# set work_mem ='1GB';
SET
postgres=# set max_parallel_workers_per_gather =0;
SET
postgres=# explain (verbose,costs,summary,settings) select count(*) from abc;
QUERY PLAN
Aggregate (cost=179057.19..179057.20 rows=1 width=8)
Output: count(*)
-> Seq Scan on public.abc (cost=0.00..154056.75 rows=10000175 width=0)
Output: id, info
Settings: max_parallel_workers = '128', max_parallel_workers_per_gather = '0', work_mem = '1GB'
Planning Time: 0.066 ms
(6 rows)
```
参考
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=ea569d64ac7174d3fe657e3e682d11053ecf1866
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
