SQL Performance Tuning: Indexes, Plans & Joins

First pass checklist

Quick Reference: What to do first

The highest-frequency triage questions, in the order that usually saves time.

Signal	What it usually means	First move	Verify with	Concrete example
One node dominates runtime	That node is the real bottleneck, not the query text as a whole.	Fix the node type first: scan, join, sort, aggregate, or lock wait.	`EXPLAIN (ANALYZE, BUFFERS)` or `EXPLAIN ANALYZE`	A 7 ms index probe feeding a 3.4 s sort means the sort is the target.
Actual rows are far above estimate	Statistics are stale, skewed, or the predicate shape is misleading the planner.	Refresh stats, inspect skew, and simplify predicates before forcing hints.	`ANALYZE`, `ANALYZE TABLE`, histogram/stat views	Planner expects 12 rows; execution returns 240,000 rows after a status filter.
Seq scan / table scan on a selective filter	The predicate is not indexable, the index order is wrong, or the table is tiny enough that the scan is cheaper.	Check sargability and whether the leading index column matches the filter.	Predicate text + index definition	`WHERE lower(email) = 'a@b.com'` without an expression index.
Sort / filesort dominates	The result set is being ordered after too many rows are produced.	Reduce rows earlier or add an index that matches the filter plus order.	Plan node labels and top-N rows	`ORDER BY created_at DESC LIMIT 50` over millions of rows without a supporting index.
Lock wait or blocked session	The query is waiting on another transaction, not spending time on its own plan.	Find the blocker and shorten or reorder the transaction scope.	`pg_locks`, Performance Schema lock tables	An update pauses behind a long reporting transaction that left a read lock open.
Fast in dev, slow in prod	Row count, data distribution, cache warmth, or lock pressure is different.	Benchmark with production-like cardinality and real parameter values.	Same SQL, same bind params, same snapshot mode	Dev table has 20k rows; prod has 48M, so the planner takes a different path.
Adding another index makes writes hurt	Index overhead is now larger than the read benefit.	Collapse overlapping indexes, prefer composite shape, or use a partial index.	Write path latency + index usage stats	Three separate single-column indexes on the same lookup path become one composite index.
Query changed, plan changed a lot	The optimizer is sensitive to join order, selectivity, or a CTE/materialization fence.	Check whether the rewrite changed cardinality or blocked a transformation.	Before/after plans on the same dataset	Wrapping a filter in a CTE prevents the engine from pushing it down early.

Reading the plan

Execution plan literacy

A plan is not a verdict. It is a model, and the useful question is which node burns time, rows, or I/O.

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT customer_id, order_id, total_cents
FROM orders
WHERE account_id = 42
  AND created_at >= TIMESTAMP '2026-07-01'
ORDER BY created_at DESC
LIMIT 50;

Node	Definition	Good sign	Bad sign	Example / gotcha
Sequential scan / table scan	Read the table linearly and filter rows after reading them.	The table is small, or the predicate returns a large fraction of rows.	Selective lookup on a large table that should have been indexable.	Example 8,000-row lookup on a 2,000-row table can still be cheaper than an index. Gotcha Do not assume every scan is bad.
Index scan	Use an index to find qualifying rows, then fetch the table rows if needed.	Equality or range predicates line up with the index’s leading columns.	The filter is wrapped in a function or starts on the wrong column.	Example `WHERE account_id = 42` on `(account_id, created_at)`. Gotcha Reversed column order often breaks the win.
Index-only scan / covering index	Return rows from the index alone without heap or table fetches when the engine can prove visibility and all required columns are present.	The query projects only indexed columns, and the visibility map or engine metadata makes the lookup cheap.	Frequent updates keep pages non-visible, so the engine still has to visit the heap.	Example PostgreSQL can use an index-only scan; MySQL shows `Using index`. Gotcha A covering index is not automatically a small index.
Bitmap scan / batched lookup	Combine several index hits and visit the table in batches.	Multiple predicates each narrow the set enough to justify bitmap fusion.	You only need one narrow lookup and the batching overhead adds work.	Example `status = 'open' AND region = 'us'` may combine two indexes. Gotcha Bitmap wins are workload-dependent, not universal.
Nested loop join	For each row from the outer input, probe the inner input.	Outer input is tiny and the inner probe is indexed and selective.	The outer input explodes and the inner probe repeats millions of times.	Example 12 parent rows joining to 12 indexed child lookups. Gotcha A nested loop can be excellent or catastrophic depending on outer-row count.
Hash join	Build an in-memory hash table on one side, then probe it from the other side.	Equality join with a large enough build side to justify hashing.	Hash table spills because memory is too small or the build side is bloated.	Example Fact table joining a medium dimension by primary key. Gotcha Spills create a hidden sort-ish penalty.
Merge join	Walk two ordered inputs in lockstep.	Both inputs are already sorted by the join key or can be read from matching indexes.	A forced sort is more expensive than a different join algorithm.	Example Two B-tree paths on the same key. Gotcha Merge join often needs supporting order on both sides.
Sort / filesort	Order rows after they have been produced by earlier nodes.	Sorting a small result set, especially top-N after a selective predicate.	Sorting millions of rows because the filter came too late.	Example MySQL shows `filesort` when no index can satisfy `ORDER BY`. Gotcha Sorting before limiting is the classic waste.
Hash aggregate / temp aggregate	Group rows using an in-memory hash table or temp structure.	Grouping a reduced set after a good filter.	Aggregating too early on a fan-out join or spilling to disk.	Example Aggregate after pre-filtering orders by date. Gotcha Counting everything on a hot path is still expensive.
Materialize / CTE fence	Store an intermediate result before the optimizer can fully rearrange the statement.	Reusing the same expensive subresult more than once.	The materialization blocks pushdown and turns a selective query into a broad one.	Example A CTE used once can still be fine. Gotcha Do not assume a CTE is free or automatically inlined.

Rule of thumb: a plan estimate that is off by one order of magnitude or more is already a tuning clue, not a rounding error.

Index design

Pick the index that matches the access pattern

Indexing is about matching shape, not collecting features. Overlapping indexes cost writes, memory, and maintenance.

Pattern	Definition	Example	Use when	Watch out for
B-tree on one column	The default balanced-tree index for equality and ordered range lookups.	`CREATE INDEX ON orders (account_id);`	Equality filters, joins, and ordered retrieval on a single key.	Do not expect it to help if the predicate starts with a function or if the table is tiny.
Composite B-tree	One index over multiple columns, ordered by the leading column sequence.	`CREATE INDEX ON orders (account_id, created_at DESC);`	`WHERE account_id = 42 AND created_at >= ... ORDER BY created_at DESC`.	The leftmost columns must line up with the filter order; the wrong order wastes the index.
Covering / include columns	Add non-key columns so the query can be satisfied without hitting the base table.	PostgreSQL: `CREATE INDEX ... INCLUDE (status, total_cents);`	Read-mostly list pages that project a few extra fields.	Bigger indexes are slower to maintain; on hot write paths, keep the payload tight.
Partial index	An index over only the subset of rows that matches a fixed predicate.	`CREATE INDEX ON orders (created_at) WHERE status = 'open';`	A stable subset like open orders, active sessions, or pending tasks.	If the predicate does not appear verbatim in the query, the planner may not use it.
Expression index	An index on a computed expression instead of a raw column.	`CREATE INDEX ON users ((lower(email)));`	Case-folded lookups, normalized keys, or repeated computed filters.	Expressions must match the query shape and collation rules; otherwise the index is ignored.
BRIN	A compact block-range index that summarizes values by page range.	`CREATE INDEX ON events USING brin (created_at);`	Very large append-only or mostly-ordered tables with time-range queries.	Poor fit for random-update tables or highly shuffled data where locality is weak.
GIN / GiST	Specialized access methods for arrays, text search, ranges, and other non-scalar search types.	`USING gin (tags)` or `USING gist (price_range)`	Full-text search, containment, overlaps, and similar operators.	Do not swap these in for plain equality lookups when a B-tree is the simpler answer.
Prefix index, MySQL	Index only the leading portion of a long string column.	`INDEX title_prefix (title(64))`	Long text columns where the prefix is enough to narrow the search.	Prefix indexes can hurt ordering and uniqueness assumptions because the tail is not indexed.
Unique index	Enforces no duplicate keys and speeds exact lookups on the same key.	`CREATE UNIQUE INDEX ON users (email);`	Natural keys, lookup tables, and foreign-key targets.	In PostgreSQL, nullable unique columns allow multiple NULLs by default unless you opt into `NULLS NOT DISTINCT`.
Skip-scan / index combination	Planner techniques that salvage a partially matching multicolumn index.	`(country, city)` helping a `city = ...` search in a narrow cardinality regime.	When the leading column has few distinct values and the engine can make up the gap.	These are optimizations, not design goals; build the index for the common query first.

Decision guidance: use a single composite B-tree when the same query filters and orders by the same prefix. Use a partial index when the hot path is a small stable subset. Use a covering index when table lookups are the bottleneck, not when the filter is already cheap.

Anti-pattern: stacking separate single-column indexes on the same query path and hoping the engine will magically turn them into one perfect access path. That usually just adds write cost.

Query rewrites

Make the query easier to optimize

Most slow queries become fast once the planner can see the real filter, join, and ordering shape.

Rewrite	Definition	Example	Why it helps	When not to use it
Make predicates sargable	Write the condition so the engine can use an index directly.	`WHERE created_at >= '2026-07-01'` instead of `DATE(created_at) = ...`	Functions on indexed columns often hide the search key from the planner.	If you truly need the transformed form, use an expression index or stored normalized column instead.
Use keyset pagination	Paginate by the last seen sort key rather than by offset.	`WHERE (created_at, id) < (:created_at, :id) ORDER BY created_at DESC, id DESC LIMIT 50`	Deep offsets make the database skip more and more rows.	Do not use it when random page-number access is the product requirement.
Prefer EXISTS / NOT EXISTS	Express semi-joins and anti-joins directly.	`WHERE EXISTS (SELECT 1 FROM payments p WHERE p.order_id = o.id)`	Lets the optimizer stop at the first match instead of materializing a larger join result.	If you need columns from the child rows, use a join; EXISTS only answers presence.
Pre-aggregate before joining	Reduce fan-out by grouping the large side first.	Aggregate order totals by customer before joining to the customer table.	Smaller intermediate sets reduce join, sort, and memory cost.	Do not pre-aggregate if you need row-level detail from the raw fact table.
Remove accidental DISTINCT	Stop deduplicating rows that should not have duplicated in the first place.	Fix the join key instead of slapping `DISTINCT` on the SELECT list.	Distinct often hides a join bug while adding a sort or hash step.	Use it only when duplicate elimination is actually part of the business rule.
Fetch narrow, then wide	Limit the first pass to keys, then join back for the expensive payload.	Find 50 ids first, then fetch the wide JSON body in a second step.	Reduces sort width and memory pressure.	Extra round trips can hurt when the second pass is not selective.
Avoid unnecessary CTE fences	Do not force the engine to materialize an intermediate result unless reuse or isolation is the point.	Inline a single-use CTE if it blocks predicate pushdown.	The optimizer can often rearrange an inline subquery more aggressively.	Use a CTE when you actually want to reuse the result or to make the query readable and the engine still performs well.
Move wide columns late	Keep the early plan working on ids and filters, not giant text blobs.	Join a filtered key set to the table containing large JSON payloads.	Less data to sort, hash, and move between nodes.	Not useful when the payload is already needed for every row in the result.
Constrain update batches	Process rows in small chunks to shorten locks and reduce contention.	`UPDATE ... WHERE id > :cursor ORDER BY id LIMIT 500` in a loop.	Shorter transactions are easier for the engine to schedule.	Batching is not a substitute for a missing index on the update predicate.

-- Better than OFFSET for deep pages
SELECT id, created_at, total_cents
FROM orders
WHERE account_id = 42
  AND (created_at, id) < (TIMESTAMP '2026-07-05 12:00:00', 981277)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Data shape

Schema and storage choices that change performance

Sometimes the right answer is not a query rewrite at all. The data is shaped wrong for the hot path.

Choice	Definition	Example	Benefit	Tradeoff
Normalize hot lookups	Store repeated facts once so joins and updates stay cheap.	Keep a customer table instead of repeating the same customer fields on every order row.	Less duplication, smaller updates, cleaner indexes.	Too much normalization can create join-heavy read paths that need good indexes.
Denormalize read-heavy reports	Store precomputed or repeated fields when repeated joins are the bottleneck.	A reporting table with daily customer totals and segment labels.	Fewer joins and fewer lookups on read-mostly endpoints.	Write amplification and consistency maintenance go up.
Partition large time-series tables	Split a table into smaller child tables so old data can be skipped early.	Monthly partitions for event logs or invoices.	Partition pruning can cut both scan time and maintenance work.	Partitioning is not free; bad partition keys create more complexity than speed.
Materialized view or summary table	Precompute a stable aggregate or join result.	Daily revenue by region refreshed every hour.	Turns repeated expensive queries into cheap reads.	Refresh lag and refresh cost must be acceptable for the business use case.
Refresh statistics after drift	Update planner statistics so row estimates reflect current data distribution.	PostgreSQL `ANALYZE`; MySQL `ANALYZE TABLE`; SQLite `ANALYZE` or `PRAGMA optimize`.	Better selectivity estimates and better join choice.	Stats are only as good as the sample and the freshness of the data.
Vacuum / visibility maintenance	Reclaim dead tuples and update visibility metadata in PostgreSQL.	`VACUUM (ANALYZE)` on tables with heavy updates.	Helps index-only scans and reduces bloat-related I/O.	Frequent updates can still keep pages non-all-visible, so index-only scans are not guaranteed.

Decision guidance: choose partitioning only when a stable key like time or tenant genuinely lets the engine prune most data. If the hot path is a single lookup, an index is usually simpler and faster to operate.

Gotcha: in PostgreSQL, vacuum does more than reclaim space. It also updates planner statistics and the visibility map, which affects both plan quality and index-only scan eligibility.

Commands by engine

What to run in PostgreSQL, MySQL, and SQLite

Use the engine’s own optimizer tools first. Hints are a last resort, not the opening move.

Engine	Capture the plan	Refresh stats	Watch slow queries	Inspect locks / waits
PostgreSQL	`EXPLAIN (ANALYZE, BUFFERS, VERBOSE)`	`ANALYZE` or `VACUUM (ANALYZE)`	`pg_stat_statements` for server-wide timing and query fingerprints; `auto_explain` for logging slow plans automatically.	`pg_locks` for active locks and waits; `pg_stat_io` / `pg_statio_*` to understand cache behavior.
MySQL 8.0	`EXPLAIN` and `EXPLAIN ANALYZE`	`ANALYZE TABLE` to refresh table statistics	Slow query log with `long_query_time` and `min_examined_row_limit`	Performance Schema `data_locks` and `data_lock_waits` for blocking analysis
SQLite	`EXPLAIN QUERY PLAN`	`ANALYZE` or `PRAGMA optimize`	Use the query planner overview and shell output; runtime telemetry is much thinner than PostgreSQL/MySQL.	SQLite is usually lock-light at the query level, so focus more on plan shape and database access patterns.

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
ROLLBACK;

EXPLAIN ANALYZE
SELECT ...;

ANALYZE TABLE orders;

MySQL 8.0.18 introduced EXPLAIN ANALYZE. In PostgreSQL, EXPLAIN ANALYZE actually runs the statement, so wrap write-side probes in a transaction and roll them back when you only want measurement.

Common mistakes

Anti-patterns that waste time

These are the moves that make tuning feel random. They are usually avoidable.

Indexing before reading the plan

Without the plan, you do not know whether the bottleneck is scan, join, sort, lock, or cache behavior. Build the mental model first, then the index.

Tuning on toy data

Small tables hide bad selectivity and make scans look cheap. Use production-like row counts and distributions before drawing conclusions.

Hiding predicates in functions

WHERE DATE(created_at) = ... or WHERE lower(email) = ... often blocks index use unless you created a matching expression index.

Relying on OFFSET for deep paging

OFFSET makes the database walk and discard rows you never needed. Keyset pagination keeps the search anchored to the last seen key.

Using DISTINCT as a join patch

Distinct may hide a fan-out bug while adding a sort or hash step. Fix the join multiplicity or pre-aggregate first.

Ignoring stale statistics

When estimates are wrong, the index may be fine and the statistics may not be. Refresh the stats before you blame the access path.

Chasing join hints too early

Hints are a last resort. If the planner is wrong, first fix cardinality, join input size, or index shape so the plan becomes obvious.

Forgetting lock time

A slow query that is blocked is a concurrency problem, not a SQL-shape problem. Use the lock views before you rewrite the statement.

Primary sources

What this page was checked against

The links below are the verification targets for the versioned behavior summarized on the page.

PostgreSQL plan and stats

`EXPLAIN`, `ANALYZE`, planner statistics, index usage, and buffer/visibility behavior.

PostgreSQL maintenance and locks

Locks, lock waits, vacuum, auto-explain, and query-level statistics collection.

MySQL and SQLite optimization

EXPLAIN, EXPLAIN ANALYZE, optimizer statistics, slow query logging, and SQLite’s query planner docs.

Related pages