pg_stat_statements is a contributed extension for PostgreSQL that logs all queries and how long they took. If you aggregate on this, you can determine where your PostgreSQL spends the most time and what to optimize.
How it works and how you install it is for another blog post.
Total time
SELECT
(total_time / 1000 / 60) AS total,
(total_time/calls) AS avg, calls,
SUBSTRING(query FROM 0 FOR 250)
FROM pg_stat_statements
WHERE calls > 100
ORDER BY 1 DESC
LIMIT 25;
This one is important because you could have some terribly slow query that uses lots of sequential scans, but perhaps it's only used once a week, so who cares?
Most common
SELECT calls, substring(query from 0 for 250)
FROM pg_stat_statements
ORDER BY calls DESC LIMIT 20;
The total time (above) matters more than just the number of times it's used. However, this can be very helpful to spot things like "N+1 problems" such as this use of an ORM:
for user in User.objects.all():
for profile in UserProfile.objects.filter(user=user):
print(profile.theme_preference)
This is likely to be numerous queries when it could be 1. They might all be pretty fast so their total time might not show in the total-time query above.
Reset
select pg_stat_statements_reset();
I run this manually when I know I've shipped a change that will stop doing certain queries that could be dominating stats. If they're not going to happen any more, it's no use worrying about them.
Caveat about the times
My particular PostgreSQL is old. That means that total_time in pg_stat_statements is the time spent in the execution phase and thus excludes the parse, rewrite and plan phases. In more modern versions of PostgreSQL you use total_plan_time and/or total_exec_time whose names make this clearer.
Comments