Diagnosing and Fixing Slow Queries

This page is a vendor-neutral overview of how to detect, diagnose, and fix slow queries across SQL and NoSQL stores. Unlike the SQL, MongoDB and Couchbase reference sections on this site, no single reference book or specification underpins it: the content was generated with the assistance of AI from general knowledge, and every concrete claim should be verified against each engine’s own current documentation before being relied on in production.

This section’s sources lists the reference material consulted while preparing these pages.

A query that used to be fast can become slow for many different reasons — a missing index, a query planner working off stale statistics, a large offset, an N+1 loop from an ORM, or plain lock contention — and each of those has a different fix. This page walks through a single, engine-agnostic workflow for getting from "this feels slow" to a confirmed root cause: how to tell whether a query is actually slow, how to detect it from each engine’s own log or profiler, how to read the resulting execution plan, and how to match what the plan shows to one of the typical root causes, each linked to the page on this site that documents its fix.

What Counts as "Slow"

There is no universal millisecond threshold that makes a query "slow" — 50 ms is unremarkable for a nightly batch report and unacceptable for a request in the hot path of an interactive API. Two things actually decide it:

  • A baseline. The same query, or the same shape of query, has a historical latency distribution; "slow" means a meaningful regression against that baseline (e.g. p95 latency doubling), not an absolute number picked in isolation.

  • The caller’s latency budget. A query embedded in a page that must respond in 200 ms has a much tighter budget than a query feeding an overnight export. The same 300 ms query is fine in one context and a production incident in the other.

Because of this, the first step is always to measure — capture the query’s actual latency (and, ideally, its resource cost: rows examined, I/O, lock waits) from a real log or profiler — before reaching for any optimization. Optimizing a query that was never actually a bottleneck wastes effort and risks making the code harder to maintain for no benefit.

Detecting Slow Queries

SQL

MySQL — enable the slow query log and set the threshold, in seconds, above which a query is logged:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1; -- log queries taking longer than 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

The raw log is rarely read line by line in production; summarize it with mysqldumpslow (bundled with MySQL) or the more detailed pt-query-digest (Percona Toolkit), which ranks queries by total time across all executions rather than by any single slow occurrence:

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log   # top 10 by total time
pt-query-digest /var/log/mysql/mysql-slow.log             # per-query-fingerprint breakdown

PostgreSQL — the equivalent knob is log_min_duration_statement (milliseconds), set in postgresql.conf or per session:

ALTER SYSTEM SET log_min_duration_statement = 500; -- log statements slower than 500 ms
SELECT pg_reload_conf();

For aggregated, cumulative statistics across all executions of a query (rather than one log line per slow execution), enable the pg_stat_statements extension and query its view directly:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT query, calls, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

auto_explain goes one step further and attaches the execution plan itself to the log entry for any statement that crosses a duration threshold, which removes the need to reproduce a slow query manually to see its plan:

LOAD 'auto_explain';
SET auto_explain.log_min_duration = '500ms';
SET auto_explain.log_analyze = true;

SQL Server — Query Store, once enabled on a database, continuously records every query’s compiled plan and its runtime statistics (duration, CPU, logical reads) without needing a log threshold set in advance:

ALTER DATABASE CurrentDb SET QUERY_STORE = ON;
ALTER DATABASE CurrentDb SET QUERY_STORE (OPERATION_MODE = READ_WRITE);

-- Top queries by average duration over the last day.
SELECT TOP 10 qt.query_sql_text, rs.avg_duration
FROM sys.query_store_query_text qt
JOIN sys.query_store_query q ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval rsi ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time > DATEADD(day, -1, GETUTCDATE())
ORDER BY rs.avg_duration DESC;

Query Store also underpins the "Regressed Queries" report in SQL Server Management Studio and Azure Data Studio, which is often the fastest way to spot a query whose plan changed for the worse after a data or statistics change.

MongoDB, Elasticsearch, Solr & Hibernate

The other stores and the ORM layer on this site already document their own slow-query detection in depth — this section only points to it rather than repeating it:

  • MongoDB’s database profiler records every operation slower than a configurable slowms threshold to the system.profile collection; see The database profiler.

  • Elasticsearch’s per-index slow logs record slow search and indexing requests at configurable warn/info/debug thresholds; see What to watch.

  • Solr’s slowQueryThresholdMillis request handler setting logs any request that exceeds it; see Request logging and the slow-query log.

  • Hibernate’s own slow-query log (LOG_QUERIES_SLOWER_THAN_MS) and its Statistics API expose per-query and per-session-factory timing independent of which database sits underneath; see The slow-query log and The Statistics API.

Redis

Redis has no query planner to profile — instead it exposes command-level diagnostics: SLOWLOG records commands that exceed a configurable execution-time threshold, LATENCY (LATENCY HISTORY, LATENCY DOCTOR) tracks latency spikes by event source, big-key detection (redis-cli --bigkeys, MEMORY USAGE) finds oversized keys that dominate memory and command cost, and redis-cli --hotkeys samples access frequency to surface hot keys. See Administration, Monitoring & Troubleshooting for the full reference.

Reading an Execution Plan

Every engine’s execution plan answers the same handful of questions, in its own vocabulary:

  • Scan or seek? Did the engine walk every row/document in a collection (a full scan — Seq Scan in PostgreSQL, ALL/type: ALL in MySQL’s EXPLAIN, COLLSCAN in MongoDB), or did it use an index to jump straight to the matching rows (an index seek/lookup — Index Scan/Index Only Scan, ref/range/const, IXSCAN)? A full scan is not automatically wrong for a small table, but it is the single most common cause of a slow query against a large one.

  • Join strategy (SQL only) — nested-loop, hash, or merge join, and in what order the tables were joined. A nested-loop join driven by the wrong (larger) table, or a join order that ignores which table has the more selective filter, multiplies the scan cost.

  • Sort — whether an ORDER BY/sort() was satisfied by an index in the required order, or required a separate, sometimes disk-spilling, sort step over the whole matched set.

  • Examined vs. returned — regardless of engine, the single most telling number in a plan is the ratio between how many rows/documents the engine had to examine (rows examined in MySQL, docsExamined in MongoDB’s explain(), the actual row count of a Seq Scan node in PostgreSQL) and how many it actually returned. A query that examines a million rows to return ten is doing a full scan in disguise, however its plan happens to be labeled.

flowchart LR subgraph Scan ["Full scan -- examine every row"] direction LR T1[row 1] --> T2[row 2] --> T3["row 3
(match)"] --> T4[...] --> T5[row N] end subgraph Seek ["Index seek -- jump straight to the match"] direction LR Idx[(Index)] --> M["row 3
(match)"] end

Typical Root Causes

  • Missing or unused indexes — full scans. A predicate on a column with no supporting index (or one the planner chooses not to use, e.g. because the column has low selectivity) forces a full scan. Example: WHERE email = 'a@example.com' on an email column with no index scans the entire table. The fix is workload- and engine-specific index design, covered on each store’s own indexing page (e.g. Indexing & query performance for MongoDB).

  • Non-sargable predicates. Wrapping an indexed column in a function or applying a leading wildcard prevents the planner from using an otherwise-available index, e.g. WHERE YEAR(created_at) = 2024 or WHERE name LIKE '%smith' — both force a full scan even with an index on created_at/name. Rewriting as a sargable range (WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01') restores the index seek.

  • Large OFFSET/SKIP values. Paging deep into a result set with OFFSET/skip() forces the engine to walk and discard every row before the offset; see Pagination: Offset vs. Keyset for why, and for the keyset/seek-method alternative.

  • N+1 queries from an ORM. Lazy-loading an association inside a loop issues one query per parent row instead of one query total, which shows up as many fast individual queries rather than one slow one; see Fetching and the N+1 Problem for the fetch strategies that fix it.

  • Poor join order or stale statistics (SQL) / poor compound-index key order (MongoDB). A SQL planner that joins tables in a suboptimal order, or that is working from statistics that no longer reflect the table’s actual data distribution (after a bulk load, for instance), can pick a plan that looks fine on paper and scans far more than necessary in practice — re-running ANALYZE/UPDATE STATISTICS is often enough to fix it. MongoDB’s equivalent is a compound index whose field order doesn’t follow the Equality, Sort, Range (ESR) rule; see Indexing & query performance.

  • Over-fetching. SELECT * in SQL, or returning whole documents from MongoDB/Elasticsearch/Solr without a projection, forces the engine and the network to move far more data than the caller actually needs, even when the underlying scan itself is efficient. Projecting only the columns/fields actually used is usually a one-line fix.

  • Lock or contention waits masquerading as a slow query. A query that is itself well-planned can still appear slow in a log or profiler because it spent most of its wall-clock time waiting on a lock held by another transaction, not doing scan or computation work. The plan’s own cost estimate looking cheap while the observed duration is high is the tell; the fix is shortening or reordering the competing transaction, not touching the query’s plan at all.

Tools

  • EXPLAIN/EXPLAIN ANALYZE (MySQL, PostgreSQL) or SQL Server’s graphical execution plan, plus each vendor’s visual plan viewer — pgAdmin, MySQL Workbench, and Azure Data Studio all render a plan as a diagram rather than raw text, which makes the scan/seek and join-order questions above much faster to answer by eye.

  • MongoDB’s cursor.explain() for the raw plan, and MongoDB Compass’s Explain Plan tab for the same information as a visual execution tree alongside a summary-stats banner (documents returned/examined, keys examined, index used, and total execution time at a glance).

  • mongostat/mongotop for a live, terminal-based view of overall server throughput and per-collection read/write time, useful for confirming a query is actually the bottleneck before diagnosing it further.

  • pt-query-digest (Percona Toolkit) for turning a raw MySQL slow query log into a ranked-by-total-time breakdown per query fingerprint.

Workflow Summary

Put together, the workflow is the same six steps regardless of which engine is involved: measure the query’s actual latency against its baseline and the caller’s budget; if it is genuinely slow, detect it from the engine’s own log or profiler rather than guessing; read the resulting plan, focusing on scan vs. seek, join strategy, sort, and the examined-vs-returned ratio; match what the plan shows to one of the typical root causes above; apply the linked fix for that root cause; and re-measure the same query to confirm the fix actually closed the gap against the baseline, since a plan that looks better is not proof the caller-facing latency actually improved.

flowchart TD A[Measure against baseline
and latency budget] --> B{Actually slow?} B -- no --> A B -- yes --> C[Detect via log / profiler] C --> D[Read the execution plan] D --> E[Match symptom to root cause] E --> F[Apply the linked fix] F --> G[Re-measure] G --> B

Sources

  • MongoDB Manual — Database Profiler, cursor.explain().

  • MongoDB Compass documentation — the Explain Plan tab.

  • MySQL Reference Manual — Slow Query Log, mysqldumpslow.

  • PostgreSQL documentation — EXPLAIN, log_min_duration_statement, pg_stat_statements, auto_explain.

  • Microsoft SQL Server documentation — Query Store, Execution Plans.

  • Percona Toolkit documentation — pt-query-digest.