postgresql-pg_stat_statements

修改postgresql.conf文件，并重启postgreSQL

shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 1000                   //跟踪的最大语句数量，缺省是1000条
pg_stat_statements.track = all                  //控制那些语句会被追踪，可选top（缺省），all和none

创建pg_stat_statements

create extension pg_stat_statements;     //创建pg_stat_statements extension
CREATE EXTENSION
d pg_stat_statements;                   //查看视图结构
          View "public.pg_stat_statements"
       Column        |       Type       | Modifiers
---------------------+------------------+-----------
 userid              | oid |
 dbid                | oid |
 query               | text |
 calls               | bigint |
 total_time          | double precision |
 rows                | bigint |
 shared_blks_hit     | bigint |
 shared_blks_read    | bigint |
 shared_blks_dirtied | bigint |
 shared_blks_written | bigint |
 local_blks_hit      | bigint |
 local_blks_read     | bigint |
 local_blks_dirtied  | bigint |
 local_blks_written  | bigint |
 temp_blks_read      | bigint |
 temp_blks_written   | bigint |
 blk_read_time       | double precision |
 blk_write_time      | double precision |

查找执行最慢的语句（查最慢10条SQL语句）：

select query,calls,total_time,(total_time/calls) as average,rows,100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0) as hit_percent from pg_stat_statements order by average desc limit 10;

为使输出精简，也可执行以下语句（最慢2条语句):

select query,0) as hit_percent from pg_stat_statements order by average desc limit 2;