Before you tune anything, how do you decide which queries are worth your time?

Rank by total time, not by duration

The instinct is to find the slowest statement and fix it, and it is usually the wrong target. A report that takes eight seconds and runs twice a day consumes sixteen seconds. A lookup that takes four milliseconds and runs twelve thousand times a minute consumes forty-eight seconds every minute. Halving the second one returns far more capacity than eliminating the first, and it also reduces the queueing that is making everything else look slow.

So the quantity to sort on is calls multiplied by mean duration — the share of the server's time that statement shape owns. In PostgreSQL that comes from pg_stat_statements, which normalises each statement by replacing its literals with placeholders so that the same shape with different parameters aggregates into one row.

SELECT calls,
       round(total_exec_time)  AS total_ms,   -- this column is total_time before PostgreSQL 13
       round(mean_exec_time, 2) AS mean_ms,
       round(max_exec_time, 2)  AS max_ms,
       rows / greatest(calls, 1) AS rows_per_call,
       query                                  -- keep the text, not just the queryid
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Always carry the statement text through to whatever you write up. A queryid is stable only within one server and one version, so a report that cites the identifier alone is unreadable to the next person and unverifiable after an upgrade.

The MySQL equivalent is the Performance Schema's digest summary, events_statements_summary_by_digest, which aggregates by digest with a total and a count in the same way; the slow query log plus a digest tool covers the same ground when the summary table is not available.

The counters are cumulative, so define the window

Both of those views accumulate since they were last reset, which for a long-lived server means you are reading a mixture of last night's batch, the migration somebody ran in March, and today's traffic. Ranking on that produces a list dominated by whatever happened to run a lot at some point.

Two ways to fix it. Reset the statistics, wait for a representative interval, and read the delta — cheap, but it discards history. Or snapshot the view periodically and difference consecutive snapshots, which is what monitoring products do and what you want if this is a habit rather than an afternoon. Either way, state the window alongside any number you report.

One more limit: these views track a bounded number of distinct statement shapes and evict entries beyond it. If the application builds SQL by concatenating literals, every call is a new shape, the table churns, and the ranking becomes noise. That is itself a finding, because it usually means the plan cache is being wasted too.

What normalisation hides

Aggregating by shape is what makes the ranking possible and it is also what conceals the two most interesting patterns.

The first is parameter skew. One shape, one mean, but the query is fast for every tenant except the largest, and the mean sits somewhere unhelpful between the two populations. The signal is the gap between the mean and the maximum, which is why you select both. A statement whose maximum is two orders of magnitude above its mean is not a slow query, it is two different queries wearing one shape.

The second is rows returned per call. A statement fetching an average of forty thousand rows is not a database problem to be indexed away; it is an application that is filtering or paginating in the wrong place, and no plan change will fix the volume of data being shipped.

Establish where the time went before rewriting anything

A ranking tells you which statement to look at. It does not tell you whether that statement was slow because of its plan, and that distinction decides whether you should be editing SQL at all.

The same statement can be slow because it reads too many pages, because it waits on a lock another transaction holds, because it waits on I/O for pages that are no longer cached, or because the server is simply saturated and it is queueing. Sampling pg_stat_activity and looking at wait_event_type and wait_event for the sessions running your candidate answers this directly: time spent on Lock is a concurrency design problem, time on IO points at working set and memory, and a session that is mostly running with no wait event is doing genuine CPU work where the plan is worth attacking. In MySQL, the Performance Schema wait and stage tables serve the same purpose.

For the plan itself, prefer capturing production plans over reproducing the query by hand. auto_explain with a duration threshold logs plans only for executions that exceeded it, which is precisely the subset you cannot reproduce interactively, and it avoids the trap of tuning against a plan the server never actually chose.

When the highest total time is the wrong thing to fix

Two adjustments keep this from becoming mechanical. Total time ranks by cost to the server, not cost to a user, and those diverge: an analytics job can dominate the list while the endpoint people complain about sits well below it. Weight the list by whether somebody is waiting on that path.

And some entries belong at the top. A health check firing every second accumulates an enormous call count while being individually trivial, and the action for that row is to exclude it rather than optimise it.

Sort by the share of server time a statement shape owns over a stated window, keep the mean next to the maximum so parameter skew cannot hide, and find out what the query was waiting on before you rewrite a single line of it.