Query Optimization: EXPLAIN, N+1 & Keyset Pagination

Query Optimization

Slow queries are usually the biggest, most fixable performance bottleneck in an application. A single missing index or an N+1 pattern can turn a millisecond lookup into a multi-second stall — and the fix is often one line once you've found it.

The discipline is the same as all performance work: measure with the query planner first, then change one thing and verify. EXPLAIN ANALYZE tells you exactly what the database is doing, which beats guessing every time.

TL;DR

Use EXPLAIN ANALYZE to see the real execution plan.
Eliminate N+1 queries and **SELECT *.
Index the columns in WHERE, JOIN, and ORDER BY.
Use keyset (cursor) pagination instead of large OFFSETs.

Quick Example

The first tool to reach for — the plan reveals whether the database scans the whole table or uses an index:

Core Concepts

Reading execution plans

EXPLAIN shows the planned strategy; EXPLAIN ANALYZE runs the query and shows actual time and row counts. Watch for:

Seq Scan on large tables → likely a missing index.
High actual time on a step → the bottleneck.
Estimated vs actual rows far apart → stale statistics (run ANALYZE).

The usual culprits

N+1 queries — one query, then one more per row.
**SELECT * — fetching columns you don't need.
Large OFFSET — the database still scans all skipped rows.

Techniques

Fix N+1 with a join or `IN`

Keyset pagination instead of OFFSET

Also: rewrite correlated subqueries as joins, cache expensive results (Caching), and batch bulk writes.

Best Practices

Profile the actual slow query (from logs/pg_stat_statements), not a guess.
Add indexes guided by EXPLAIN, then re-check the plan.
Select only needed columns; LIMIT large result sets.
Keep table statistics fresh so the planner estimates well.

Common Mistakes

Deep offset pagination

Optimizing without EXPLAIN

FAQ

How do I read EXPLAIN output?

Read it bottom-up: each node feeds its parent. Look for scan types (Seq Scan vs Index Scan), the actual time per node, loop counts (nested execution), and whether estimated rows match actual rows. Big mismatches mean stale statistics.

Why is my query slow even with an index?

The index may not match the query (function-wrapped column, wrong composite order), the table may be small enough that a scan is cheaper, statistics may be stale, or the slowness may be elsewhere (a join, a sort spilling to disk). EXPLAIN ANALYZE localizes it.

Offset or keyset pagination?

Keyset (cursor) pagination for anything large or deep — it uses an index to jump straight to the page. Offset pagination is fine for small, shallow lists but degrades badly at high offsets because the database scans and discards every skipped row.

When should I denormalize?

For read-heavy workloads where joins or aggregations are the proven bottleneck and the data changes infrequently. Denormalize deliberately (and keep the source of truth), since it trades write complexity and consistency risk for read speed.