PostgreSQL的 执行计划保存

开始

首先，要安装 auto_explain:

进入 contrib/auto_explain 目录，运行 gmake ，然后运行  gmake install

然后编辑 postgresql.conf ，加入如下两行：

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration='0'

再在postgresql.conf 中设置log：

# - Where to Log -

log_destination = 'csvlog'              # Valid values are combinations of
                                        # stderr, csvlog, syslog, and eventlog,
                                        # depending on platform.  csvlog
                                        # requires logging_collector to be on.

# This is used when logging to stderr:
logging_collector = on          # Enable capturing of stderr and csvlog
                                        # into log files. Required to be on for
                                        # csvlogs.
                                        # (change requires restart)

验证：

启动 postgresql 以后，运行：

[postgres@localhost bin]$ ./psql
psql (9.2.0)
Type "help" for help.

postgres=# select count(*) from employee;
 count 
-------
  1000
(1 row)

postgres=# \q

查看log：

[作者：技术者高健@博客园  mail: luckyjackgao@gmail.com ]

[postgres@localhost pg_log]$ ll
total 4
-rw------- 1 postgres postgres 768 Nov  8 11:01 postgresql-2012-11-08_110114.csv
-rw------- 1 postgres postgres   0 Nov  8 11:01 postgresql-2012-11-08_110114.log
[postgres@localhost pg_log]$ cat postgresql-2012-11-08_110114.csv |grep "scan"
[postgres@localhost pg_log]$ cat postgresql-2012-11-08_110114.csv
2012-11-08 11:01:14.453 CST,,,7014,,509b207a.1b66,1,,2012-11-08 11:01:14 CST,,0,LOG,00000,"database system was shut down at 2012-11-08 11:00:39 CST",,,,,,,,,""
2012-11-08 11:01:14.459 CST,,,7018,,509b207a.1b6a,1,,2012-11-08 11:01:14 CST,,0,LOG,00000,"autovacuum launcher started",,,,,,,,,""
2012-11-08 11:01:14.460 CST,,,7012,,509b207a.1b64,1,,2012-11-08 11:01:14 CST,,0,LOG,00000,"database system is ready to accept connections",,,,,,,,,""
2012-11-08 11:01:36.950 CST,"postgres","postgres",7022,"[local]",509b2084.1b6e,1,"SELECT",2012-11-08 11:01:24 CST,2/2,0,LOG,00000,"duration: 0.100 ms  plan:
Query Text: select count(*) from employee;
Aggregate  (cost=19.50..19.51 rows=1 width=0)
  ->  Seq Scan on employee  (cost=0.00..17.00 rows=1000 width=0)",,,,,,,,,"psql"

结束