How to Debug Slow SQL Queries: A Step-by-Step Guide

Step 1: Enable the Slow Query Log

Before you can fix a slow query, you need to find it. The slow query log is built into every major database engine and records any query that exceeds a time threshold you define. In PostgreSQL, the setting is log_min_duration_statement, measured in milliseconds. In MySQL, it is the slow_query_log flag combined with long_query_time.

The log gives you the actual query text, total execution time, and lock wait time: everything you need to reproduce the problem and trace it back to the right part of your codebase. For production systems, start with a threshold of 1,000 milliseconds. On a staging environment, lower it to 100ms to catch emerging problems before they reach production.

Route the slow query log to your centralised logging system so you can search it across time windows. Some teams skip this step because they rely on application monitoring alone, but application traces rarely capture the actual SQL text, which makes diagnosis far harder and slower.

-- PostgreSQL: log queries slower than 1 second
ALTER SYSTEM SET log_min_duration_statement = 1000;
SELECT pg_reload_conf();

-- MySQL: enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Step 2: Find Your Worst Queries with pg_stat_statements

The slow query log shows individual slow executions, but it does not tell you which queries are consuming the most total database time. A query that takes 400ms and runs 200,000 times per day costs far more than a query that takes 10 seconds and runs once a week, yet the slow query log alone would only flag the second one.

The pg_stat_statements extension in PostgreSQL (and performance_schema.events_statements_summary in MySQL) solves this by aggregating execution statistics across all calls, including:

• Total execution time: cumulative time spent running this query fingerprint

• Average execution time: mean time per execution

• Standard deviation: how much execution time varies from call to call

• Call count: how many times the fingerprint has run: the key signal for detecting N+1 patterns

Once the extension is installed, query the view sorted by total_exec_time to see your actual hot spots. The pct_of_total column is especially useful: if one query fingerprint accounts for 40% of total database CPU, that is your first priority regardless of its individual execution time. Use the calls column to detect N+1 patterns: a simple-looking query running 500,000 times per day is a strong signal.

-- Enable the extension (add to postgresql.conf, then restart)
-- shared_preload_libraries = 'pg_stat_statements'

-- Top 10 queries by total execution time
SELECT
  substring(query, 1, 80) AS query_short,
  calls,
  round(total_exec_time::numeric, 2) AS total_ms,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round((total_exec_time / sum(total_exec_time) OVER ()) * 100, 2) AS pct_of_total
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Step 3: Run EXPLAIN ANALYZE to See the Execution Plan

Once you have identified a slow query, EXPLAIN ANALYZE is the most powerful diagnostic tool available. It executes the query and returns the execution plan the database actually used, with real measured timing for each step. The critical distinction is between EXPLAIN, which only estimates the plan without running the query, and EXPLAIN ANALYZE, which runs it and shows actual row counts and actual times.

Always include the BUFFERS option. This reveals how many data blocks were served from the shared memory cache versus read from disk. A high Buffers Read count indicates your working set exceeds available memory and disk I/O is the bottleneck. Include VERBOSE for column-level detail on which tables and indexes each node uses.

For INSERT, UPDATE, or DELETE statements, always wrap EXPLAIN ANALYZE in a transaction and roll back afterwards, otherwise the statement will modify your data while you are only trying to measure it.

-- Full diagnostic output
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT TEXT)
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 100;

-- For data-modifying statements: wrap and rollback
BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
UPDATE orders SET status = 'processed'
WHERE created_at < NOW() - INTERVAL '1 day';
ROLLBACK;

Step 4: Read the Execution Plan Correctly

An execution plan is a tree of operations, read from the innermost (most indented) node outward. Each node represents one database operation. The planner shows estimated costs alongside actual measured values from the ANALYZE run.

The three numbers to focus on in each node are:

• Actual time: milliseconds spent in that operation: the first value is startup time, the second is total time

• Actual rows: how many rows that node actually produced

• Buffers hit vs. buffers read: memory access versus disk access

A Seq Scan on a large table is almost always a sign of a missing index. An Index Scan that removes far more rows than it returns suggests the index predicate is not filtering early enough and a more selective index is needed. The gap between estimated rows and actual rows is critical: a large divergence means the planner made its join and scan decisions based on stale statistics. Running ANALYZE on the affected table refreshes those statistics and often generates a dramatically better plan without any other changes.

-- Refresh statistics on a table after bulk loads or large deletes
ANALYZE orders;

-- Plan node reference:
-- Seq Scan          ? Full table scan (look for missing index)
-- Index Scan        ? Uses index, but still fetches from table heap
-- Index Only Scan   ? Fastest: index contains all needed columns
-- Hash Join         ? Efficient for large row sets on both sides
-- Nested Loop       ? Efficient when inner side is small and indexed
-- Rows Removed by Filter ? Large number = index not filtering early enough

Root Cause 1: Missing Indexes

The single most common cause of slow SQL is a sequential scan on a large table because no suitable index exists for the query's WHERE, JOIN, or ORDER BY clauses. The fix is a targeted composite index that matches the query pattern.

When building composite indexes, column order matters: put equality filter columns first, followed by range filter or sort columns. PostgreSQL can use a composite index on (status, created_at) for a query filtering on status and ordering by created_at, but it cannot efficiently use an index on (created_at, status) for the same query.

After creating an index, re-run EXPLAIN ANALYZE to confirm the planner picks it up: the plan should switch from Seq Scan to Index Scan or Index Only Scan. Always use CREATE INDEX CONCURRENTLY in production so the index builds in the background without blocking writes. Monitor index bloat on high-write tables and use REINDEX CONCURRENTLY periodically to reclaim space from dead index entries.

-- Create a composite index matching the query's filter and sort pattern
CREATE INDEX CONCURRENTLY idx_orders_status_date
ON orders(status, created_at DESC);

-- Confirm the planner uses it
EXPLAIN (ANALYZE)
SELECT id, total FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC
LIMIT 100;
-- Expected: Index Scan using idx_orders_status_date

Related: our database engineering service

Root Cause 2: The N+1 Query Pattern

The N+1 problem is the silent performance killer of application-layer database interactions. It occurs when code fetches a list of N records and then issues one additional database query per record to load related data, producing N+1 round-trips for what should be a single query.

Detection is straightforward with pg_stat_statements: look for a query that appears tens of thousands or hundreds of thousands of times with near-identical structure but a different parameter value each time. A SELECT * FROM customers WHERE id = $1 running 80,000 times in an hour is the textbook signature.

The fix is to batch the lookup using a JOIN or a WHERE IN / ANY clause, so the database handles the relationship in one operation instead of N separate ones. ORMs are the most common source of N+1 patterns because they make lazy relationship traversal easy and invisible: each property access on a related object silently triggers a new database query.

-- N+1 anti-pattern (bad): one query per order row
-- SELECT * FROM customers WHERE id = 1
-- SELECT * FROM customers WHERE id = 2  ... repeated 1000 times

-- Batch JOIN rewrite (good): one query total
SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.id = ANY(:order_ids);

-- Or with WHERE IN for a fixed list
SELECT * FROM customers
WHERE id IN (1, 2, 3, 4, 5);

Related: our deep dive on the N+1 query problem, why your API is slow even when the database looks fine

Root Cause 3: Full Scans on Filtered Subsets, Solved with Partial Indexes

Some queries only ever need a small fraction of a table's rows: only open support tickets, only pending payment records, only failed job queue entries. A standard index on the filter column helps in these cases, but a partial index is significantly better.

A partial index covers only rows that satisfy a WHERE predicate defined at creation time. It is smaller, faster to scan, and fits more readily into the database cache than a full index on the same column. If a table has two million rows but only 30,000 match status = 'open', a partial index covering only those rows is roughly 98% smaller than a full index.

That difference compounds at query time: the planner reads fewer index pages, the OS caches the entire index in memory, and sequential reads from the index are faster. The trade-off is specificity: partial indexes only benefit queries that include the exact predicate they were built on.

-- Full index (covers all rows � large, slower for narrow filters)
CREATE INDEX idx_tickets_status ON support_tickets(status);

-- Partial index (covers only open tickets � much smaller and faster)
CREATE INDEX idx_tickets_open
ON support_tickets(created_at DESC)
WHERE status = 'open';

-- This query uses the partial index directly
SELECT * FROM support_tickets
WHERE status = 'open'
ORDER BY created_at DESC
LIMIT 50;

Root Cause 4: Inefficient JOINs and Stale Statistics

JOIN performance problems fall into two categories: missing indexes on the JOIN columns, or stale table statistics causing the planner to choose the wrong join strategy. Every foreign key column used in a JOIN should have an index: without one, the planner must materialise both tables in memory and hash-join them, which is expensive when either side is large.

Stale statistics are equally dangerous. After a bulk data load or a large DELETE, the planner's row count estimates can be significantly wrong. A table the planner believes contains 10,000 rows but actually holds 10 million will be joined with a nested loop strategy that generates billions of unnecessary iterations.

Run ANALYZE after any significant data change. Schedule autovacuum to run frequently on high-write tables by lowering autovacuum_vacuum_scale_factor. In PostgreSQL 14 and later, CREATE STATISTICS allows you to capture multi-column correlations that the planner cannot infer from single-column statistics alone.

-- Index all foreign key columns used in JOINs
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- Refresh statistics after bulk operations
ANALYZE orders;
ANALYZE order_items;

-- PostgreSQL 14+: improve estimates for correlated columns
CREATE STATISTICS orders_correlation(dependencies)
ON customer_id, status FROM orders;

Step 5: Rewrite Queries to Help the Planner

After resolving indexes and statistics, some queries still underperform because the SQL structure itself forces the planner into a suboptimal strategy. Correlated subqueries are the most common offender: they execute once per outer row, turning a single-pass scan into N passes. Rewrite them as JOINs or lateral joins.

Avoid applying functions to indexed columns in WHERE clauses: WHERE LOWER(email) = 'user@example.com' cannot use a standard B-tree index on the email column. Either create a functional index on LOWER(email), or store the email in lowercase at write time.

Use DISTINCT sparingly: it forces a sort and deduplication pass that prevents index-only scans. If you control the query, EXISTS or GROUP BY often achieves the same result with better plan choices. For complex, multi-step transformations, CTEs can help the planner by materialising intermediate results, but always verify with EXPLAIN ANALYZE, since PostgreSQL 12+ does not automatically materialise CTEs.

-- Correlated subquery (slow): runs once per row in orders
SELECT o.id,
  (SELECT name FROM customers WHERE id = o.customer_id) AS customer_name
FROM orders o;

-- JOIN rewrite (fast): single pass over both tables
SELECT o.id, c.name AS customer_name
FROM orders o
JOIN customers c ON c.id = o.customer_id;

-- Functional index for case-insensitive email lookup
CREATE INDEX idx_users_email_ci ON users(LOWER(email));

-- This WHERE clause now uses the functional index
SELECT * FROM users WHERE LOWER(email) = 'user@example.com';

Useful Tools for Slow Query Debugging

Several tools speed up slow query investigation beyond raw SQL commands:

• pgBadger: parses PostgreSQL log files into HTML reports covering the slowest queries, most frequent queries, lock analysis, and connection statistics, useful for reviewing a bad production incident after the fact

• explain.dalibo.com and explain.depesz.com: turn EXPLAIN ANALYZE output into a colour-coded visual plan tree, making expensive nodes far easier to spot than reading raw text

• pganalyze: continuously collects pg_stat_statements data and alerts on regressions, catching gradual slowdowns caused by table growth

• Datadog APM and New Relic: link slow HTTP traces directly to their underlying SQL queries, making N+1 patterns trivial to detect

• pt-query-digest (Percona): aggregates MySQL slow query log entries by query fingerprint and ranks them by total time consumed

Related: diagnosing PostgreSQL connection exhaustion

A Real Debugging Example: From 4,200ms to 12ms

A client came to us with a reporting dashboard timing out every evening during business hours. Querying pg_stat_statements showed one query consuming 43% of total database CPU: a join between a reports table and a users table, filtered on status and a date range.

EXPLAIN ANALYZE revealed a Seq Scan on the reports table: 800,000 rows scanned, 786,000 removed by the filter predicate, leaving 14,000 matching rows. The scan alone took 4,150ms before the join operation even started. There was no index on the (status, created_at) column combination used in the WHERE clause and ORDER BY.

The fix was a single composite index built concurrently with zero downtime. After the index was available, the planner switched from Seq Scan to Index Scan, fetching the 14,000 matching rows directly without scanning or filtering any other rows. Query time dropped from 4,200ms to 12ms: a 350x improvement. Dashboard timeouts stopped immediately. Total time from identifying the problem in pg_stat_statements to verifying the fix in production was 22 minutes.

-- The original slow query
SELECT r.id, r.title, r.status, u.name AS author
FROM reports r
JOIN users u ON u.id = r.created_by
WHERE r.status = 'approved'
  AND r.created_at > NOW() - INTERVAL '30 days'
ORDER BY r.created_at DESC;

-- The fix: one composite index matching WHERE + ORDER BY
CREATE INDEX CONCURRENTLY idx_reports_status_date
ON reports(status, created_at DESC);

-- Result: Seq Scan ? Index Scan
-- Query time: 4,200ms ? 12ms (350x improvement)

Prevention: Stop Slow Queries Before They Reach Production

Debugging slow queries reactively means users are already affected. Prevention costs a fraction of the effort:

• Review execution plans in code review: check EXPLAIN ANALYZE output for any new query touching tables with more than 100,000 rows: a query that takes 20ms today can take 20 seconds after 18 months of data growth

• Set a statement_timeout: kill runaway queries before they exhaust available connections and cascade into a broader outage

• Monitor pg_stat_statements weekly: a gradual rise in mean execution time almost always means table growth has made a previously acceptable plan unacceptable

• Run ANALYZE after bulk imports: and configure autovacuum to match the write rate on your highest-traffic tables

• Load test new query patterns: against a representative data volume before production: a query that performs well against 10,000 test rows can behave very differently against 50 million production rows

Related: the Database FinOps guide