PostgreSQL Query Optimization: Beyond EXPLAIN ANALYZE.

Advanced techniques for diagnosing and resolving performance bottlenecks in production Postgres.

EXPLAIN ANALYZE tells you what happened. pg_stat_statements tells you how often it happens. Use both.

sql

1-- Find the top 10 slowest queries by total execution time
2SELECT
3  query,
4  calls,
5  round(total_exec_time::numeric, 2) AS total_ms,
6  round(mean_exec_time::numeric, 2) AS mean_ms,
7  round(stddev_exec_time::numeric, 2) AS stddev_ms
8FROM pg_stat_statements
9ORDER BY total_exec_time DESC
10LIMIT 10;

“An index that covers 80% of your queries is worth more than ten indexes that each cover 8%.”