by Aleksey_Tsalolikhin

Postgres 9.2 allows on-the-fly insight into query performance with pg_stat_statements

Postgres 9.2 includes a very handy pg_stat_statements
shared library and extension which allows immediate
insight into query performance.

Installation:
————

1. Add to postgresql.conf:

shared_preload_libraries = ‘pg_stat_statements’

2. Restart PostgreSQL.

3. Run

create extension pg_stat_statements;

to have access to the statistics and the helper functions.

Use
—

— reset statistics
select pg_stat_statements_reset();

— which query was called the most
select * from pg_stat_statements order by calls desc;

— which query used the most CPU time
select * from pg_stat_statements order by total_time desc;

— Example from Postgres reference manual:
— report time, calls, row and hit percentage
SELECT query,
calls,
total_time,
rows,
100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements
ORDER BY total_time
DESC LIMIT 5;

References
———-
1. http://www.postgresql.org/docs/9.2/static/pgstatstatements.html
2. http://pgsnaga.blogspot.com/2011/10/performance-impact-of-pgstatstatements.html
3. http://www.databasesoup.com/2012/09/postgresql-92-out-gate.html