Finding Slow Queries Systematically: SQL Debugging in Practice

1. Why slow queries rarely stand out on their own

A slow query rarely stands out immediately. It usually begins unnoticed: a table grows slowly, an index is missing for a rarely used filter, or an application generates a query under certain conditions that nobody tested during development. As long as the data volume is small, the query runs in a few milliseconds. Only once the table grows to hundreds of thousands or millions of rows does an innocent query turn into a slow query that blocks the entire request.

The tricky part: without systematic monitoring, nobody notices the gradual transition. An application's average response time can stay unremarkable for weeks while individual queries are already running in the seconds range in the background, just infrequently enough not to skew the mean. This is exactly where the need arises to find slow queries systematically, instead of waiting for user complaints or random observations in a log.

A systematic approach combines three building blocks: a continuous capture source directly inside the database, clearly defined thresholds for what counts as slow, and a repeatable process that turns raw log data into prioritized action items. The following sections show exactly what this process looks like for MySQL and PostgreSQL, and which tools are used along the way.

2. Slow query log as the first diagnostic source

The slow query log is the most obvious source in MySQL and MariaDB to find slow queries. It logs every query whose execution time exceeds a configured threshold, together with a timestamp, the number of rows examined and the full query text. It is enabled via the system variable slow_query_log, with the threshold set through long_query_time. Important: by default the slow query log writes to a file, but it can also log directly into a table, which makes evaluation with plain SQL much easier.

PostgreSQL has no native slow query log in the same sense, but offers a functionally equivalent setting with log_min_duration_statement: any statement that takes longer than the configured value in milliseconds ends up in the server log. The key difference from MySQL: PostgreSQL logs into the regular log file, not a separate table, which is why tools like pgBadger are almost mandatory for evaluation if you want to find slow queries systematically without combing through log files by hand.

-- MySQL / MariaDB: enable slow query log and write to a table
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1.0;          -- threshold in seconds
SET GLOBAL log_output = 'TABLE';           -- instead of FILE: lands in mysql.slow_log
SET GLOBAL log_queries_not_using_indexes = 'ON';

-- Evaluate directly via SQL, no external toolchain required
SELECT
    sql_text,
    query_time,
    lock_time,
    rows_examined,
    rows_sent,
    start_time
FROM mysql.slow_log
ORDER BY query_time DESC
LIMIT 20;

The equivalent configuration for PostgreSQL in postgresql.conf logs a slow query once it exceeds a certain duration, without logging every single statement in the system, which keeps log overhead reasonable:

-- postgresql.conf: log slow queries above 500ms
log_min_duration_statement = 500
log_line_prefix = '%m [%p] user=%u,db=%d '
log_checkpoints = on
log_lock_waits = on

-- Evaluation via pgBadger (command line, not SQL):
-- pgbadger /var/log/postgresql/postgresql-*.log -o report.html

3. pg_stat_statements: sampling instead of logging

The pg_stat_statements extension solves a problem that pure logging cannot: it aggregates execution statistics per normalized query pattern instead of logging every single execution. Two queries that only differ in the concrete value of a WHERE condition get merged into a single entry, including total time, call count and average time. This makes pg_stat_statements the preferred tool to find slow queries systematically without having to search through gigabytes of log files.

The decisive advantage over pure logging: pg_stat_statements also captures queries that are individually fast but generate substantial load in aggregate through massive repetition, a pattern that stays completely invisible with threshold-only logging. These exact cases are often what tips a database over under load, even though no single log entry looked critical.

-- Enable the extension once (superuser or pg_monitor role)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- The ten most expensive query patterns by total time
SELECT
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows,
    total_exec_time / NULLIF(calls, 0) AS avg_ms_per_call
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Reset statistics after a deployment
-- to cleanly isolate before/after comparisons
SELECT pg_stat_statements_reset();

4. Setting thresholds correctly

A threshold that is too low generates log flooding and overhead, a threshold that is too high leaves relevant slow queries undetected. Practice shows: a good starting value is oriented around the 95th or 99th percentile of normal response times, not an arbitrary fixed value such as one second. An API that takes 20 milliseconds per database call on median already has a serious problem at 500 milliseconds, while a batch reporting system with typically 2 seconds per query needs an entirely different yardstick.

A two-tier approach has proven effective: a low threshold for permanent aggregation via pg_stat_statements or the table variant of the slow query log, and a higher threshold for detailed logging with the full query text and explain output. This keeps permanent overhead low while genuinely critical slow queries are still captured with full context. It is also important to re-evaluate the threshold regularly, because the application's normal baseline shifts as data volumes grow.

5. Aggregation: finding the ten most expensive query patterns

Raw log entries are of little help for prioritization unless they are aggregated. The decisive question is not "which single query was slowest", but "which query pattern causes the most load in aggregate". A query that runs 5000 times per minute at 10 milliseconds costs more total database time than a query that takes 2 seconds once an hour. Anyone who wants to find slow queries systematically must therefore sort by total_exec_time, not by mean_exec_time alone.

For MySQL, this aggregation is traditionally handled by pt-query-digest from the Percona Toolkit, which reads slow log files, normalizes queries (replacing literals with placeholders) and groups them by total time. The result is a report that shows exactly the query patterns above that make up the largest share of total time, regardless of whether any single execution stood out as "slow".

-- pt-query-digest (command line, Percona Toolkit):
-- pt-query-digest /var/log/mysql/slow.log > digest-report.txt

-- Equivalent aggregation directly in SQL, when the slow log lives in a table
SELECT
    -- Normalization: replace numeric literals with a placeholder
    REGEXP_REPLACE(sql_text, '[0-9]+', 'N') AS query_pattern,
    COUNT(*) AS call_count,
    SUM(query_time) AS total_seconds,
    AVG(query_time) AS avg_seconds,
    MAX(query_time) AS worst_case
FROM mysql.slow_log
WHERE start_time > NOW() - INTERVAL 1 DAY
GROUP BY query_pattern
ORDER BY total_seconds DESC
LIMIT 10;

6. Correlating APM and database metrics

Database internal capture shows which query is slow, but not always why it just became slow. Application performance monitoring, for example with tools like New Relic, Datadog or self hosted solutions based on OpenTelemetry, ties a slow query to a specific HTTP request, a user workflow or a deployment timestamp. This correlation is essential to distinguish between "this query is fundamentally poorly written" and "this query only became slow because of yesterday's deployment".

A proven pattern is to attach trace IDs from the application code as a SQL comment to every query. This allows an entry in the slow query log to be mapped directly to a trace in the APM system, without manually matching timestamps. This pattern is framework agnostic and works with any SQL client that allows raw query strings.

-- Trace context as a SQL comment for later correlation
/* trace_id=7f3a9c21 route=/checkout/submit user_id=48213 */
SELECT o.id, o.total, o.status
FROM orders o
WHERE o.customer_id = 48213
  AND o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 5;

-- The comment appears in the slow log,
-- filtering for a specific trace becomes trivial:
-- WHERE sql_text LIKE '%trace_id=7f3a9c21%'

7. Sampling in production without noticeable overhead

Fully logging every query in a high load system produces noticeable overhead by itself and can paradoxically cause exactly the latency it is meant to uncover. pg_stat_statements elegantly solves this problem, because it only maintains aggregated counters per query pattern instead of writing every single execution. Overhead typically sits in the low single digit percentage range and is therefore acceptable even in high throughput production systems.

For MySQL, probabilistic sampling is recommended in high load environments: instead of logging every query, only a fraction, say every hundredth execution, is logged, but with full context including the explain plan. This drastically reduces log overhead while statistically relevant patterns remain visible. Important here: sampling must not be purely time based, because rare but especially expensive queries could otherwise be systematically overlooked.

8. A repeatable diagnostic process

Without a fixed process, finding slow queries stays a matter of luck, depending on who happens to look at the log. A repeatable process looks like this: first, a daily or weekly automated review of the top query patterns by total time. Second, a defined threshold above which a pattern turns into a ticket in the prioritization backlog. Third, a before and after comparison after every optimization, to verify that the change actually worked and no new slow query emerged elsewhere.

A common mistake is running this process only reactively after incidents, instead of establishing it as a fixed part of the deployment cycle. Teams that run pg_stat_statements_reset() before every major release and then compare the top ten list against the previous week catch regressions within hours instead of weeks. This proactive rhythm is exactly the difference between finding slow queries by chance and finding them systematically.

9. Tools compared

The choice of the right tool depends strongly on the database system and the load situation. The following table compares the most important approaches to find slow queries systematically, by overhead, level of detail and typical use case.

In practice, experienced teams combine several of these tools: pg_stat_statements or the table variant of the slow query log for continuous baseline capture, complemented by APM correlation for root cause analysis during acute incidents. No single tool covers both requirements at once, which is why the combination is the actual key to finding slow queries systematically.

10. Summary

Finding slow queries systematically means not relying on lucky finds or user complaints, but establishing a continuous capture source directly inside the database. Slow query log and pg_stat_statements provide the raw data, sensibly set thresholds separate relevant from irrelevant entries, and aggregation by total time instead of individual case shows the actually most expensive query patterns. This combination turns finding slow queries from a reactive emergency measure into a plannable, repeatable part of operations.

The biggest lever lies in integrating this process into the deployment cycle, instead of only activating it during acute incidents. A team that resets statistics before every release and then compares the top ten list catches regressions early and saves itself expensive incident night shifts. The tools for this are already built into MySQL and PostgreSQL, what is often missing is only the fixed process to use them consistently.

11. FAQ: Finding Slow Queries Systematically