The Best Query Tuning Tool in PostgreSQL
pg_stat_statements is an extension that tracks execution statistics for every distinct SQL statement run against the database.
Not just the queries running right now. Every query — normalised, aggregated, and ranked by total impact. It answers the question that pg_stat_activity cannot: across everything this database has run, which queries are causing the most work?
This is the starting point for any serious query tuning effort. Without it you are guessing. With it you have a ranked list of exactly where to focus.
QueryPerformancepgstatstatements.sql
Loading…
Enabling pg_stat_statements
If you get an error that the view does not exist, the extension needs to be loaded:
Enabling_pg_stat_statements.sql
Loading…
Reading the Results
Top Queries by Total Time
| Column | What It Tells You |
|---|---|
query | Normalised SQL (literals replaced with $1, $2, etc.) |
calls | Total times this statement was executed |
total_exec_time | Cumulative execution time in milliseconds |
mean_exec_time | Average execution time per call |
min_exec_time / max_exec_time | Best and worst case — high variance suggests plan instability |
stddev_exec_time | Standard deviation — high value means inconsistent performance |
rows | Total rows returned or affected |
I/O and Cache Stats
| Column | What It Tells You |
|---|---|
shared_blks_hit | Blocks served from shared buffer cache |
shared_blks_read | Blocks read from disk |
blk_read_time | Total time spent reading blocks from disk |
blk_write_time | Total time spent writing blocks |
temp_blks_read / temp_blks_written | Temp file usage — sign of insufficient work_mem for this query |
What to Watch For
| Signal | What It Means |
|---|---|
High total_exec_time with low calls | A rare but extremely slow query — probably a bad plan or missing index |
High total_exec_time with high calls | A fast query run constantly — optimise it and the savings multiply |
mean_exec_time high relative to min_exec_time | Plan instability — query sometimes gets a good plan, sometimes a bad one |
High shared_blks_read vs shared_blks_hit | Low cache hit rate for this query — it is reading a lot from disk |
temp_blks_written > 0 | Query is spilling to disk — increase work_mem or add an index |
High stddev_exec_time | Execution time varies wildly — look for lock waits or parameter sniffing |
Resetting Statistics
ResettingStatistics.sql
Loading…
Gareth Winterman