pg_stat_statements
pg_stat_statements is an extension that records how often and how long each kind of query runs.
In this page:
Syntax
CREATE EXTENSION pg_stat_statements;
SELECT query, calls, total_exec_time FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT n;pg_stat_statements
Add it to shared_preload_libraries, restart, and run CREATE EXTENSION pg_stat_statements. The pg_stat_statements view then shows normalised queries with calls, total_exec_time, mean_exec_time and rows.
Sort by total time to find the queries that cost the most overall, and reset with pg_stat_statements_reset().
Note:
The most expensive query overall is often a fast query called millions of times.
Example: pg_stat_statements
shop=# CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
shop=# SELECT calls, round(total_exec_time::numeric, 1) AS total_ms, round(mean_exec_time::numeric, 2) AS mean_ms, left(query, 50) AS query
shop-# FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 3;
calls | total_ms | mean_ms | query
---------+----------+---------+----------------------------------------------------
1204331 | 98213.4 | 0.08 | SELECT * FROM sessions WHERE token = $1
15200 | 44310.9 | 2.91 | SELECT o.id, c.name FROM orders o JOIN customers c
310 | 22001.2 | 70.97 | SELECT date_trunc($1, created_at), count(*) FROM ev⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Forgetting to preload the library
- Looking only at the slowest single query
- Not resetting statistics after changes
Chapter Summary
- Extension that tracks query statistics
- Needs shared_preload_libraries
- Sort by total_exec_time
- Reset after tuning
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: