开始
首先，要安装 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"
结束
本文转自健哥的数据花园博客园博客，原文链接：http://www.cnblogs.com/gaojian/archive/2012/11/08/2760156.html，如需转载请自行联系原作者
