postgresql 使用 auto_explain 进行针对性的优化
os: centos 7.4
postgresql: 10.4
auto_explain:
auto_explain 是 postgresql 8.4后添加的一个功能，提供了一种方式来自动记录慢速语句的执行计划，而不需要手工运行EXPLAIN。
这在实际环境中有着非常重要的意义。
安装
使用 make world && make install-world 是自动安装了 auto_explain,
如果没有安装，可以通过postgresql 源码单独安装
$ cd /tmp/postgresql-10.4/contrib/auto_explain
$ make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O2 -fPIC -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o auto_explain.o auto_explain.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O2 -fPIC -shared -o auto_explain.so auto_explain.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-10/lib',--enable-new-dtags
$ make install
/bin/mkdir -p '/usr/pgsql-10/lib'
/bin/install -c -m 755 auto_explain.so '/usr/pgsql-10/lib/auto_explain.so'
$ ls -l
total 180
-rw-r--r-- 1 postgres postgres 9363 May 8 04:51 auto_explain.c
-rw-rw-r-- 1 postgres postgres 101064 Jul 31 08:55 auto_explain.o
-rwxrwxr-x 1 postgres postgres 64600 Jul 31 08:55 auto_explain.so
-rw-r--r-- 1 postgres postgres 405 May 8 04:51 Makefile
$ ls -l $PGHOME/lib |grep -i auto_explain
-rwxr-xr-x 1 postgres postgres 64600 Jul 26 09:53 auto_explain.so
使用
auto_explain 并没有 .sql 、.control 文件，所以是无法通过 create extension 来完成。
需要通过设置 shared_preload_libraries 或 custom_variable_classes 或 session_preload_libraries 参数预先加载 auto_explain 到某些或者所有会话中。
注意，postgresql 9.2 开始，移除了custom_variable_classes参数
$ vi postgresql.conf
shared_preload_libraries = 'auto_explain'
再或者通过load命令加载
postgres=# LOAD 'auto_explain';auto_explain 的一些参数
在 postgresql.conf 最后面追加如下参数
$ vi postgresql.conf
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
# Add settings for extensions here
# auto_explain
auto_explain.log_min_duration = '1s'
auto_explain.log_timing = on
auto_explain.log_verbose = on
或者
postgres=# LOAD 'auto_explain';
postgres=# SET auto_explain.log_min_duration = 0;
postgres=# SET auto_explain.log_analyze = true;
postgres=# SELECT count(*)
FROM pg_class, pg_index
WHERE oid = indrelid AND indisunique;查看日志时，会有如下输出，挺高科技吧。
2018-07-31 09:17:13.065 CST,"postgres","postgres",12228,"[local]",5b5fb79b.2fc4,16,"idle",2018-07-31 09:12:59 CST,3/9,0,LOG,00000,"statement: SELECT count(*)
FROM pg_class, pg_index
WHERE oid = indrelid AND indisunique;",,,,,,,,,"psql"
2018-07-31 09:17:13.065 CST,"postgres","postgres",12228,"[local]",5b5fb79b.2fc4,17,"SELECT",2018-07-31 09:12:59 CST,3/9,0,LOG,00000,"duration: 0.227 ms plan:
Query Text: SELECT count(*)
FROM pg_class, pg_index
WHERE oid = indrelid AND indisunique;
Aggregate (cost=18.36..18.37 rows=1 width=8) (actual time=0.218..0.218 rows=1 loops=1)
Output: count(*)
-> Hash Join (cost=4.87..18.05 rows=122 width=0) (actual time=0.092..0.202 rows=122 loops=1)
Hash Cond: (pg_class.oid = pg_index.indrelid)
-> Seq Scan on pg_catalog.pg_class (cost=0.00..9.41 rows=341 width=4) (actual time=0.023..0.078 rows=341 loops=1)
Output: pg_class.oid
-> Hash (cost=3.34..3.34 rows=122 width=4) (actual time=0.063..0.063 rows=122 loops=1)
Output: pg_index.indrelid
Buckets: 1024 Batches: 1 Memory Usage: 13kB
-> Seq Scan on pg_catalog.pg_index (cost=0.00..3.34 rows=122 width=4) (actual time=0.004..0.029 rows=122 loops=1)
Output: pg_index.indrelid
Filter: pg_index.indisunique
Rows Removed by Filter: 12",,,,,,,,,"psql"
2018-07-31 09:17:13.065 CST,"postgres","postgres",12228,"[local]",5b5fb79b.2fc4,18,"SELECT",2018-07-31 09:12:59 CST,3/0,0,LOG,00000,"duration: 0.623 ms",,,,,,,,,"psql"
使用 auto_explain 需要注意，如果设置不当，或者目标sql执行过于频繁，会导致 log 文件暴增。
更完整的参数介绍请参考
https://www.postgresql.org/docs/current/static/auto-explain.html
http://postgres.cn/docs/9.6/auto-explain.html
