Analyzing Query Statistics (pg_stat_statements)

The Problem

You can't optimize every query. You need to find the *worst offenders* first.

PostgreSQL: pg_stat_statements

This extension tracks statistics for every query executed.

SELECT query, calls, total_time, mean_time

FROM pg_stat_statements

ORDER BY total_time DESC

LIMIT 10;

What to Look For

Pro Tip

Focus on queries that are both slow AND frequently called. A query that takes 10 seconds but runs once a month is less urgent than one that takes 500ms but runs 1000 times per hour.

*Day 78: Table Partitioning.*