Finding Slow Queries

The slowest single query is rarely the problem. A query taking 8 ms and running ten thousand times a minute consumes far more of the database than a two-minute report that runs nightly.

Rank by total time, not by worst case.

Per engine

PostgreSQL — pg_stat_statements:

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       rows,
       round(100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0), 1) AS hit_pct,
       left(query, 90) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

MySQL / MariaDB — the sys schema over Performance Schema:

SELECT query, exec_count, total_latency, rows_examined_avg, rows_sent_avg
FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 20;

MongoDB — the profiler:

db.setProfilingLevel(1, { slowms: 100, sampleRate: 0.5 });
db.system.profile.find({ millis: { $gt: 100 } }).sort({ ts: -1 }).limit(10);

ClickHouse — system.query_log:

SELECT normalized_query_hash, any(query) AS sample, count() AS runs,
       sum(query_duration_ms) AS total_ms, formatReadableSize(sum(read_bytes)) AS read
FROM system.query_log WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 DAY
GROUP BY normalized_query_hash ORDER BY total_ms DESC LIMIT 20;

Elasticsearch / OpenSearch — search slow logs, split by query and fetch phase.

The ratio that identifies a missing index

Compare rows examined with rows returned. A query reading 500,000 rows to return 20 is reading 499,980 rows for nothing — that is a missing or mis-ordered index, and no amount of hardware fixes it.

• PostgreSQL: Rows Removed by Filter in EXPLAIN ANALYZE.
• MySQL: rows_examined_avg against rows_sent_avg.
• MongoDB: docsExamined against nreturned.
• ClickHouse: read_rows against the table's total rows.

Slow query logs

# PostgreSQL
log_min_duration_statement = 500ms
log_temp_files = 0          # every spill to disk
log_lock_waits = on

# MySQL
slow_query_log = 1
long_query_time = 0.5

Aggregate them rather than reading line by line — pt-query-digest for MySQL, pgBadger for PostgreSQL — and rank the digest by total time.

Measure from the client too

A query taking 3 ms in the database and 400 ms as the application sees it is a connection pool, network or serialisation problem, and no server-side view will show it. Instrument query duration and connection acquisition time in the application. See Distributed Tracing.