Open a slow query log, sort by duration, and you will find a report that runs for twelve seconds twice a day. Meanwhile a 40-millisecond query running eighty thousand times an hour is quietly consuming far more of your database than that report ever will. Ranking by total time rather than worst case is the single change that makes a slow query log useful.
Published 4 November 2026 · Qcramai Security
MySQL and MariaDB use slow_query_log, with
long_query_time as the threshold in seconds. The default of 10 seconds is far
too high to be useful — almost nothing legitimate takes that long, so the log stays
empty while your real problems run just under the bar. Start at 1, and on a
healthy system drop to 0.2 or lower. Also enable
log_queries_not_using_indexes temporarily, with
log_throttle_queries_not_using_indexes set so it does not flood.
PostgreSQL has no separate slow log; set
log_min_duration_statement in milliseconds and the entries go to the normal
server log. Two extensions are worth more than the log itself:
pg_stat_statements, which aggregates by normalised query and gives you the
total-time ranking directly, and auto_explain, which can attach a plan to each
slow statement so you do not have to reproduce it later.
If you can run pg_stat_statements, start there. It answers the question this
entire article is about without any log parsing.
The arithmetic is the point:
The second one is over a hundred times more expensive, holds connections, competes for buffer pool and locks, and never appears at the top of a list sorted by duration. It is also usually far easier to fix, because a query running eighty thousand times is on a hot path where one index changes everything.
total_time = mean_duration × execution_count, per fingerprint. Sort
descending. That ordering is your work queue, and it rarely resembles the order you get from
sorting the raw log.
Keep the worst-case column too — a query with a huge maximum and a small mean is often a locking or cold-cache problem rather than a plan problem, and that distinction changes the fix.
Aggregating by total time requires grouping executions of the same query, which means normalising the literals away:
SELECT * FROM orders WHERE customer_id = 4821 AND status = 'paid'
SELECT * FROM orders WHERE customer_id = 9134 AND status = 'paid'
-> SELECT * FROM orders WHERE customer_id = ? AND status = ?
Replace literals with placeholders, collapse IN (?, ?, ?) lists to a single
IN (?), normalise whitespace and case. What remains identifies the query shape,
and that is what you count.
One caveat worth knowing: queries sharing a fingerprint can have genuinely different plans when the data is skewed. If one customer has two million orders and the rest have twelve, the same fingerprint will be fast almost always and catastrophic occasionally. A wide gap between mean and maximum within one fingerprint is the signal for that.
MySQL's slow log records Rows_examined and Rows_sent, and the
ratio between them is the most diagnostic number in the file:
Before changing anything, confirm with EXPLAIN. In MySQL, look for
type: ALL (full table scan), key: NULL (no index used), and
Extra: Using filesort or Using temporary on large sets. In
Postgres, EXPLAIN (ANALYZE, BUFFERS) and look for a Seq Scan on a large table,
plus any wide gap between estimated and actual row counts — that gap usually means
statistics are stale and ANALYZE is the fix rather than a new index.
The N+1 pattern — one query to fetch a list, then one more per item — is an ORM habit that hides well because every individual query is fast. In the log it looks like a single fingerprint with an enormous execution count and a tiny mean duration, often arriving in tight bursts.
A useful tell: if the count of one fingerprint is a near-multiple of the count of another that returns a list, you are probably looking at the list query and its per-item follow-up. No index will fix it — the fix is eager loading or a join in the application.
This is also why a threshold of 1 second finds nothing useful on a modern application. The most expensive pattern in many codebases consists entirely of queries that take two milliseconds each.
Indexes are the usual fix, and over-indexing is the usual overcorrection. Each index costs write time on every insert, update and delete, consumes memory and disk, and gives the planner another option to get wrong.
(customer_id, status) serves a lookup on customer_id alone and
on both columns, but not on status alone. Put the column used for equality
first, the ranged or sorted column after.WHERE YEAR(created_at) = 2026
cannot use an index on created_at; a range comparison can. The same applies
to leading wildcards in LIKE '%term'.CREATE INDEX CONCURRENTLY; MySQL does most index additions online, but verify
for your version and engine before running it on a live primary.Then check for indexes nobody uses. Unused indexes are pure write-path cost, and most long-lived schemas carry several.
A long duration in the log does not always mean the query is inefficient. Before optimising, rule out:
Lock_time separately — if it accounts for most of the duration,
your problem is a long transaction somewhere else holding a lock.Three reasons this file is not purely a performance artefact:
SLEEP(5) or pg_sleep that duly took five seconds is a
successful blind injection probe recorded in your own log. Correlate the timestamp with
your access log to find the request that
sent it.long_query_time to?Low enough that the log is useful and high enough that it is readable. One second to begin, then downward as you clear the top offenders. Many teams settle around 100–200 ms on an interactive application.
The logging itself is cheap; writing an enormous volume of it is not. A very low threshold on a busy server can produce significant I/O, so lower it deliberately, watch the file size, and rotate.
pg_stat_statements or the log?Both, for different jobs. pg_stat_statements gives you aggregate totals
cheaply and continuously — the right tool for ranking. The log captures individual
executions with their parameters and timing, which is what you need to investigate one
specific bad run.
Common causes: the planner is not using it (check EXPLAIN rather than
assuming), statistics are stale so the planner is mis-estimating, the column order does not
match the predicate, a function in the WHERE clause prevents its use, or the
real cost was lock waiting all along.
Related reading:
error log triage for the
upstream timed out entries a slow database produces, and
reading access logs for the request side.
If you would rather have the whole stack measured under load,
run a performance test.