Slow SQL is rarely a mystery once you stop guessing and start measuring. This is a hands-on checklist you can run top to bottom against any query, table, or database that is dragging - from reading the execution plan to indexing, rewriting predicates, and knowing when the real fix is the schema, not the query.
What this guide covers
- Rule 0: measure before you tune
- The indexing checklist
- The query-rewriting checklist
- Reduce the work the engine does
- Statistics and the optimizer
- Schema and data types
- Locking and concurrency
- Connection pooling and app-side patterns
- Partitioning and archiving big tables
- Server, config, and caching
- The full checklist as steps
- Where the payoff comes from
- Symptom → cause → fix
Rule 0: measure before you tune
Every hour wasted on performance work traces back to the same mistake: tuning a query nobody profiled. Before you touch an index or rewrite a join, get two numbers - how long the query actually runs, and what the engine plans to do. The execution plan is the ground truth. Everything else in this checklist is downstream of reading it correctly.
Read the plan with EXPLAIN
EXPLAIN shows the optimizer's intended strategy without running the query. EXPLAIN ANALYZE actually executes it and reports real row counts and timings, which is what you want when estimates look suspicious.
-- PostgreSQL: plan + real timings + buffer usage
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT o.id, o.total
FROM orders o
WHERE o.customer_id = 4217
AND o.created_at >= '2026-01-01';-- MySQL 8+: readable tree, then real execution
EXPLAIN FORMAT=TREE
SELECT * FROM orders WHERE customer_id = 4217;
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 4217;Read the plan as a tree that executes from the inside out: the most deeply nested node runs first and feeds its parent, so the leaves are where the cost is born and the root is where it is paid. Two numbers on each node tell most of the story. The cost is the optimizer's own estimate in arbitrary units, useful only for comparing one node against another. The actual rows and loops come from ANALYZE and are real; when a node reports "rows=10" in the estimate but processed two million in reality, you have found why the plan is wrong. Trace the fat part of the tree - the node that touches the most rows or burns the most time - and start your fix there rather than at whatever caught your eye first.
The signals that matter most:
- Seq Scan / Full Table Scan on a large table with a selective filter - usually a missing index.
- Estimated vs actual rows wildly apart (say 10 vs 2,000,000) - stale statistics feeding a bad plan.
- Nested loops over big row counts where a hash join would be cheaper.
- Sort or Hash spilling to disk - not enough work memory, or you are sorting far more rows than you need.
Find the slow queries first
Do not tune the query you happen to be staring at - tune the ones that cost the most in aggregate. Let the engine tell you which those are.
-- PostgreSQL: top time-consuming statements (needs pg_stat_statements)
SELECT substr(query, 1, 80) AS q,
calls, total_exec_time,
round(mean_exec_time::numeric, 2) AS avg_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;On MySQL, enable the slow query log (slow_query_log = ON, long_query_time = 0.5) or query performance_schema.events_statements_summary_by_digest. On SQL Server, use Query Store or sys.dm_exec_query_stats. The lever is always the same: rank by total time (calls × average), not by the single worst outlier.
Baseline first: record the runtime and plan of the query before you change anything. Without a baseline you cannot prove an improvement - and you will not know if a later change made things worse. See our deeper walkthrough on performance tuning.
The indexing checklist
Indexing is the single highest-return lever in most databases. A missing index turns a millisecond lookup into a full scan; the right composite index can replace a scan-and-sort with a single seek. The reason is structural: a table without a useful index forces the engine to examine every row to answer a question, so cost grows linearly with the table - ten times the data, ten times the wait. A B-tree index lets the engine navigate straight to the matching rows in a handful of page reads no matter how large the table gets, which is why the same query can run in one millisecond on a thousand rows or a hundred million. That is the difference between an application that scales and one that quietly falls over as the data grows. Work through this list for any slow read.
Index the columns you actually filter and join on
Put indexes on the columns that appear in WHERE, JOIN ... ON, and ORDER BY clauses - especially foreign keys, which are frequently left unindexed and cause slow joins and lock contention.
-- Single-column index for a common lookup
CREATE INDEX idx_orders_customer ON orders (customer_id);Order composite index columns correctly
In a composite index, column order is not cosmetic. The rule of thumb: equality columns first, then the range/sort column. An index on (customer_id, created_at) serves WHERE customer_id = ? AND created_at >= ? as a single range seek. Reverse the columns and the same query cannot seek efficiently.
-- Equality (customer_id) first, range (created_at) second
CREATE INDEX idx_orders_cust_date
ON orders (customer_id, created_at);Use covering indexes to skip the table
If an index contains every column the query needs, the engine answers from the index alone - an "index-only scan" with no trip back to the heap. Add non-key columns with INCLUDE.
-- Covering index: seek on the key, read total from the leaf
CREATE INDEX idx_orders_cover
ON orders (customer_id, created_at) INCLUDE (total, status);Do not over-index
Every index speeds reads but taxes every INSERT, UPDATE, and DELETE, and consumes storage and cache. Redundant indexes (a lone index on (a) when (a, b) already exists) are pure overhead. Audit for unused ones and drop them.
-- PostgreSQL: indexes that have never been scanned
SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;Maintain indexes over time
Indexes bloat and fragment as data churns. Rebuild or reorganize them on a schedule, and keep statistics fresh (next section). Partial indexes keep hot subsets small.
-- Partial index: only the rows you query most
CREATE INDEX idx_orders_open
ON orders (created_at)
WHERE status = 'open';
-- Rebuild without locking reads/writes (Postgres)
REINDEX INDEX CONCURRENTLY idx_orders_cover;The index types you choose matter as much as the columns. Use this reference to pick the right one.
| Index type | Best for | Notes |
|---|---|---|
| B-tree (default) | Equality and range on ordered data | Serves =, <, BETWEEN, ORDER BY |
| Composite | Multi-column filters and sorts | Leftmost-prefix rule applies |
| Covering / INCLUDE | Index-only scans | Trades write cost for read speed |
| Hash | Pure equality lookups | No range support |
| GIN / full-text | Arrays, JSONB, text search | Larger, slower to update |
| Partial / filtered | Hot subset of a big table | Smaller, cheaper to maintain |
For a full treatment, work through our guide on SQL indexes.
The query-rewriting checklist
Once the indexes exist, the query has to be written so the optimizer can actually use them. This is the step most developers skip, and it is where a surprising amount of slowness hides: the index is present, the plan still shows a scan, and the reason is entirely in how the SQL was phrased. The optimizer is powerful but literal - it will not restructure a predicate you wrote badly, it will simply take you at your word and scan. The most common reason a perfectly good index sits idle is a non-sargable predicate.
Keep predicates sargable
"Sargable" is short for Search ARGument ABLE, and it means the engine can use an index seek to satisfy the condition. The property is fragile: it survives a plain comparison against the raw column, but the moment you wrap that column in a function, cast it, or do arithmetic on it, the engine can no longer reason about the index because it would have to compute your expression for every single row before it could compare. So it does exactly that - a full scan plus a per-row calculation, the worst of both. The fix is almost always the same move: leave the indexed column untouched on one side and push all the transformation onto the constant side, which the engine evaluates once.
-- Function on the column: index on created_at is ignored
SELECT * FROM orders
WHERE YEAR(created_at) = 2026;-- Range on the raw column: index seek
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';The same trap catches WHERE UPPER(email) = '...', WHERE price * 1.2 > 100, and leading-wildcard LIKE '%term'. Rewrite each so the indexed column stands alone on one side of the comparison.
Never select more than you need
SELECT * drags back columns you discard, defeats covering indexes, breaks when the schema changes, and bloats network transfer. Name your columns.
SELECT * FROM orders o
JOIN customers c ON c.id = o.customer_id;SELECT o.id, o.total, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;Prefer EXISTS over IN for correlated checks
When you only need to test for existence, EXISTS can short-circuit on the first match and typically plans better than a large IN (subquery). It also sidesteps the NULL surprises that NOT IN hides.
SELECT c.id, c.name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
AND o.created_at >= '2026-01-01'
);Paginate on a key, not a big OFFSET
LIMIT 20 OFFSET 100000 forces the engine to read and discard 100,000 rows on every page. Keyset (seek) pagination stays constant-time by remembering the last row seen.
SELECT id, created_at FROM orders
ORDER BY created_at
LIMIT 20 OFFSET 100000;-- Remember the last created_at from the previous page
SELECT id, created_at FROM orders
WHERE created_at > '2026-06-30 12:00:00'
ORDER BY created_at
LIMIT 20;More rewrites - window functions, CTEs, join-order tricks - live in our advanced query optimization guide.
Reduce the work the engine does
Beyond indexes and predicates, the fastest query is the one that touches the fewest rows and bytes. Push filters as early as possible so downstream joins, sorts, and aggregates operate on a small set.
- Filter before you join - apply the most selective
WHEREconditions so the join input is already small. - Aggregate on fewer rows - filter, then
GROUP BY, not the reverse. - Avoid implicit type conversions - comparing a
varcharcolumn to an integer literal forces a per-row cast and kills index usage. Match the literal type to the column. - Batch large writes - a single multi-row
INSERTor a chunkedDELETEbeats a million single-row round trips.
-- account_no is VARCHAR; integer literal forces a cast
SELECT * FROM accounts WHERE account_no = 100482;SELECT * FROM accounts WHERE account_no = '100482';Statistics and the optimizer
The optimizer picks plans from statistics about your data - row counts, value distribution, null fraction. When those go stale after a bulk load or heavy churn, it makes bad guesses: nested loops over millions of rows, or a scan where a seek belonged. If the plan's estimated rows are far from the actual rows in EXPLAIN ANALYZE, refresh statistics before you do anything else.
-- Refresh stats (and reclaim space) after big changes
ANALYZE orders; -- PostgreSQL
VACUUM ANALYZE orders; -- Postgres: also cleans dead tuples
ANALYZE TABLE orders; -- MySQL
UPDATE STATISTICS orders; -- SQL ServerKeep autovacuum / auto-update-statistics enabled, and tune its thresholds on high-churn tables rather than turning it off.
Schema and data types
Sometimes the query cannot be saved because the schema is the bottleneck. Right-sized types and sensible structure shrink every read and every index.
- Use the smallest correct type.
INToverBIGINTwhen the range allows;DATEoverDATETIMEwhen there is no time component. Narrower rows mean more rows per page and better cache hit rates. - Store dates as date types, not strings - string dates cannot range-seek and sort wrong.
- Pick a compact primary key. A wide or random key (like a random UUID) bloats every secondary index that references it and fragments inserts.
- Weigh normalization against read cost. Normalize to keep writes clean; selectively denormalize or add a summary table when a hot read joins five tables every time. It is a deliberate trade, not a default.
- Partition genuinely huge tables by date or key so queries prune to one partition. See working with large databases.
Locking and concurrency
A query can be fast in isolation and painfully slow in production for a reason no execution plan will ever show you: it is waiting on a lock. Under concurrency the database serializes access to rows and pages so that two transactions cannot corrupt each other's work, and when a transaction holds a lock longer than it should, everyone queued behind it stalls. This is why a report that runs in 40 milliseconds on your laptop can take 30 seconds at peak hour - the time is not spent computing, it is spent blocked. If a query is fast when run alone but slow under load, suspect locking before you suspect the plan.
The usual culprits are long-running transactions that grab locks early and hold them while doing unrelated work, and transactions that touch rows in inconsistent orders, which produces deadlocks the engine must detect and kill. Keep transactions short and focused: open them as late as possible, do the minimum inside, and commit promptly. Avoid doing slow application work - an HTTP call, a file write, a user prompt - while a transaction is open, because every lock it holds is frozen for the duration.
-- PostgreSQL: who is blocking whom right now
SELECT blocked.pid AS blocked_pid,
blocking.pid AS blocking_pid,
blocked.query AS blocked_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
ON blocking.pid = ANY(pg_blocking_pids(blocked.pid));Beyond keeping transactions short, three levers reduce contention directly:
- Index your foreign keys. An unindexed foreign key forces the child table to be scanned and locked when the parent row changes, turning a small update into a broad lock. This single omission is behind a large share of mysterious blocking.
- Update rows in a consistent order. If every transaction touches rows by ascending primary key, two of them can never form the circular wait that becomes a deadlock.
- Choose the right isolation level. Higher isolation buys stronger guarantees at the cost of more locking. Do not run everything at serializable "to be safe" - use the weakest level that is correct for the operation.
Connection pooling and app-side patterns
Some of the biggest wins are not in the database at all - they are in how the application talks to it. Two anti-patterns dominate: churning connections, and the N+1 query.
Pool your connections
Opening a database connection is expensive. It involves a TCP handshake, authentication, and server-side session setup, and it can easily cost more than the query you are about to run. An application that opens a fresh connection per request, or worse per query, spends most of its database time on setup and teardown, and a burst of traffic can open thousands of connections that overwhelm the server's per-connection memory. A connection pool solves both problems by keeping a small set of warm connections open and lending them out. Size the pool deliberately - a pool far larger than the number of CPU cores usually makes throughput worse, not better, because the extra connections just fight over the same hardware. Tools like PgBouncer for PostgreSQL, or the built-in pool in your application framework, handle this for you.
Kill the N+1 query
The N+1 pattern is the most common performance bug in application code, and ORMs make it easy to write by accident. You fetch a list of N parent rows with one query, then loop over them and lazily load each parent's children - issuing N more queries. What should have been two queries becomes N+1, and at 500 parents that is 501 round trips, each paying network latency on top of its own cost. The plan for every individual query looks perfect; the problem is only visible in the aggregate.
// 1 query for customers, then 1 per customer for their orders
customers = query("SELECT id, name FROM customers");
foreach (customers as c) {
c.orders = query("SELECT * FROM orders WHERE customer_id = ?", c.id);
}-- One query fetches every child set at once
SELECT o.* FROM orders o
WHERE o.customer_id IN (1, 2, 3, /* ... */);
-- or a single JOIN; in an ORM, use eager loading / "include"The fix is to fetch related data in a single round trip - a JOIN, an IN list, or your ORM's eager-loading option (often called "include", "with", or "preload"). Alongside this, batch your writes: a single multi-row INSERT beats a thousand single-row inserts by an order of magnitude, because it pays the round-trip and transaction overhead once instead of a thousand times. Cache read-heavy, rarely-changing results in the application layer so the query never reaches the database at all.
Partitioning and archiving big tables
When a table grows into the hundreds of millions of rows, even a good index eventually strains - the index itself becomes large, maintenance windows lengthen, and queries that legitimately need a wide range slow down. Partitioning is the structural answer: it splits one logical table into many physical pieces along a key, most often a date. Queries that filter on that key touch only the relevant partitions and skip the rest entirely, a plan-level shortcut called partition pruning.
-- PostgreSQL: range-partition orders by month
CREATE TABLE orders (
id bigint,
created_at date NOT NULL,
total numeric
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2026_07 PARTITION OF orders
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');Partitioning pays off in three ways beyond pruning. Maintenance gets cheaper because you rebuild or vacuum one partition at a time rather than the whole table. Dropping old data becomes instant - detaching or dropping a monthly partition is a metadata operation, whereas a DELETE of millions of rows is slow and generates enormous write load. And each partition's indexes stay small enough to keep in cache. The trade-off is real, though: partitioning only helps queries that filter on the partition key, it adds administrative overhead, and choosing the wrong key can make things worse, so reserve it for genuinely large tables.
Even without partitioning, the discipline of archiving keeps hot tables fast. Move rows the application rarely reads - closed orders from three years ago, expired sessions, resolved tickets - into a separate history table or cold storage, so day-to-day queries scan a small, warm working set instead of the entire history of the business. Do the move in chunks to avoid a single giant transaction that locks the table and floods the write-ahead log.
-- Archive in bounded batches, not one giant statement
WITH moved AS (
DELETE FROM orders
WHERE created_at < '2023-01-01'
LIMIT 10000
RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;
-- repeat until zero rows are movedServer, config, and caching
Configuration is the lowest-return lever for a single bad query - but it sets the ceiling for the whole instance. Get the big memory settings right, then stop.
- Buffer pool / shared buffers - the cache that holds hot pages in RAM. Undersized, everything reads from disk. This is the setting that matters most.
- Work memory for sorts and hash joins - too low and operations spill to disk (you will see it in the plan).
- Connection pooling - thousands of raw connections thrash the server; pool them.
- Application and result caching - cache expensive, rarely-changing results in Redis or Memcached so the query never runs. The fastest query is the one you skip.
Watch out: throwing hardware at a missing index just makes a bad plan fail more slowly. Fix the query and the indexes before you resize the box - it is cheaper and usually the actual cause.
The full checklist as steps
Run these in order every time. The sequence is deliberate: measure, then attack the cheapest high-return levers first.
Capture a baseline
Record the current runtime and save the EXPLAIN ANALYZE plan. You cannot improve what you did not measure.
Find the real offender
Rank queries by total time (calls × average) via pg_stat_statements, the slow log, or Query Store. Tune the top of that list.
Read the plan
Look for scans on large filtered tables, estimate-vs-actual gaps, and sorts spilling to disk.
Refresh statistics
If estimates are far off, run ANALYZE before changing anything else.
Fix indexes
Add the missing index, order composite columns equality-then-range, cover where it pays, drop the unused.
Rewrite the query
Make predicates sargable, name columns, prefer EXISTS, switch to keyset pagination, kill implicit casts.
Reduce the work
Filter early and limit rows before you reach for schema or config changes.
Check the app and concurrency
Eliminate N+1 loops, pool connections, keep transactions short, and index foreign keys to cut lock contention.
Re-measure and lock it in
Compare against the baseline plan. Keep the win, revert anything that did not help, add a regression test.
The golden rule: change one thing at a time and re-measure. If you add an index, rewrite the predicate, and bump work memory all at once, you will never know which one helped - or which one quietly hurt.
Where the payoff comes from
Not every lever pays equally. Across typical workloads, correcting indexes and rewriting queries return far more than hardware. Spend your time top-down.
These are rules of thumb, not laws - your baseline decides the truth. But the shape holds almost everywhere: indexing and query work dominate.
Symptom → cause → fix
When a query is slow, match the symptom in the plan to its likely cause and the fix that addresses it.
| Symptom | Likely cause | Fix |
|---|---|---|
| Full/sequential scan on a filtered big table | No usable index on the filter column | Add a B-tree or composite index on the predicate |
| Index exists but is ignored | Non-sargable predicate (function/cast on column) | Rewrite as a range; match literal types |
| Estimated rows far from actual rows | Stale statistics | Run ANALYZE / UPDATE STATISTICS |
| Nested loop over millions of rows | Bad row estimate or missing join index | Index the join key; refresh stats |
| Sort or hash spills to disk | Sorting too many rows / low work memory | Filter earlier; add sort index; raise work_mem |
| Deep pages get slower and slower | Large OFFSET scanning discarded rows | Switch to keyset pagination |
| Fast alone, slow under load | Lock contention or connection thrash | Index FKs; add a connection pool; shorten transactions |
| Many tiny identical queries per request | N+1 in the application / ORM lazy loading | Eager-load with a JOIN or IN list; batch writes |
| Random deadlock errors under concurrency | Transactions locking rows in different orders | Touch rows in a consistent key order; keep transactions short |
| Whole huge table slow despite indexes | Table too large for one physical structure | Partition by date/key; archive cold rows |
Keep sharpening: review the common SQL mistakes that cause most of these, and level up systematically with the SQL advanced course. If you would rather hand it to someone, an experienced SQL developer can profile and fix a slow database in a fraction of the time.