PostgreSQLPerformance

Top statements by total time

Needs the pg_stat_statements extension. Column names are for PostgreSQL 13 and later; on 12 and earlier use total_time and mean_time.

Not yet verified. How scripts are tested

1SELECT round(total_exec_time::numeric, 0) AS total_ms,2       calls,3       round(mean_exec_time::numeric, 2) AS mean_ms,4       rows,5       round(100.0 * total_exec_time / sum(total_exec_time) OVER (), 2) AS pct_total,6       left(query, 200) AS query7FROM pg_stat_statements8ORDER BY total_exec_time DESC9LIMIT 20;

Paste it into your query tool.

Open in denrepo

More PostgreSQL scripts: Performance

• Buffer cache hit ratio per databaseShare of block reads served from shared buffers. OLTP databases usually sit above 99%; a drop suggests memory pressure or a new large scan.