PostgreSQL Query Optimization Techniques

Once you can read a query plan, the next skill is knowing what to do about a bad one. Optimization isn't a bag of tricks so much as a short list of patterns that fool the planner or make it do unnecessary work, plus the fixes for each. Most slow queries in a real application come from a handful of these, and recognizing them turns a 30-second query into a 30-millisecond one.

This is part 14 of the PostgreSQL Masterclass, following the article on reading EXPLAIN.

Stop hiding indexed columns behind functions

This is the single most common self-inflicted slow query. The moment you wrap an indexed column in a function or arithmetic, a plain index on it stops applying:

-- can't use an index on created_at
WHERE date_trunc('day', created_at) = '2024-03-01'
WHERE created_at::date = '2024-03-01'

-- can use an index on created_at
WHERE created_at >= '2024-03-01' AND created_at < '2024-03-02'

Rewriting the filter as a range on the raw column lets the index do its job. The same applies to WHERE amount * 1.1 > 100 (rearrange to amount > 90.9) and WHERE lower(email) = ... (use an expression index, as covered earlier). The rule: keep the indexed column bare on one side of the comparison.

Avoid SELECT * on wide or hot queries

SELECT * pulls every column, which blocks index-only scans (the index rarely covers all columns) and ships data you don't use. On a hot query, selecting only the columns you need can turn an index scan plus heap fetch into a pure index-only scan:

-- forces a heap fetch for the extra columns
SELECT * FROM orders WHERE customer_id = 5;

-- can be an index-only scan with the right covering index
SELECT id, total FROM orders WHERE customer_id = 5;

It's a small habit with an outsized effect on your most frequent queries.

Fix N+1 before you fix SQL

The slowest "database problem" often isn't a slow query, it's a fast query run 500 times. An ORM that loads a list, then fires one query per item to load a relation, produces the N+1 pattern. No index tuning fixes it, because each query is already fast; the cost is the round trips.

-- 1 query for orders, then N queries for each order's customer
SELECT * FROM orders LIMIT 100;
SELECT * FROM customers WHERE id = ?;  -- x100

The fix is to fetch the related data in one query with a join, or a single WHERE id = ANY($1) over the collected ids. If you use an ORM, this is what its eager-loading or include feature is for. When a page feels slow and the individual queries look fine, count how many queries it runs.

Keyset pagination for deep pages

OFFSET looks convenient and gets catastrophically slow on deep pages, because Postgres has to generate and discard every row up to the offset. OFFSET 100000 reads 100,000 rows to throw them away.

-- slow on page 1000: reads and discards 20,000 rows
SELECT * FROM orders ORDER BY created_at DESC OFFSET 20000 LIMIT 20;

-- keyset: jumps straight to the right spot using the index
SELECT * FROM orders
WHERE created_at < '2024-03-01 10:00:00'
ORDER BY created_at DESC
LIMIT 20;

Keyset pagination (also called cursor pagination) remembers the last row's sort value and filters past it, so every page costs the same regardless of depth. It's the right default for infinite-scroll and large lists.

Batch your writes

Inserting rows one at a time pays the per-statement overhead once per row. A multi-row insert or COPY is dramatically faster:

-- one statement, many rows
INSERT INTO events (type, payload) VALUES
  ('signup', '{}'), ('login', '{}'), ('logout', '{}');

For bulk loads, COPY beats even multi-row inserts. The same idea applies to updates: collect the changes and apply them in one statement with a CASE or a join against a VALUES list, rather than a loop of single-row updates.

Let EXISTS short-circuit

When you only need to know whether a related row exists, EXISTS stops at the first match, while a join or COUNT may do more work:

-- stops at the first matching order
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Prefer EXISTS over IN (subquery) for existence checks on large sets, and over COUNT(*) > 0 when you don't need the actual count.

Help the planner with statistics

Sometimes the query is fine and the planner just has bad information. Two fixes come up repeatedly. ANALYZE refreshes column statistics after a large data change. For columns that are correlated (a city and state that move together), the planner assumes independence and underestimates, which extended statistics fix:

CREATE STATISTICS orders_city_state (dependencies)
  ON city, state FROM orders;
ANALYZE orders;

This is a targeted tool, not an everyday one, but when a plan's row estimates are wildly off on correlated columns, it's the fix.

The order to work in

When a query is slow, work down this list:

1. Read the plan. Find the expensive node.
2. Is an indexed column hidden behind a function? Rewrite the filter.
3. Is it a missing index? Add one that makes the query selective.
4. Is it actually N+1 from the application? Fix the fetching, not the SQL.
5. Deep OFFSET? Switch to keyset pagination.
6. Estimates way off? ANALYZE, and consider extended statistics.

None of these are exotic. They're the same handful of issues behind most slow queries, and once you recognize them, optimization becomes pattern-matching instead of mystery.

Next, we zoom out from single queries to the whole database: finding which queries are slow across your entire workload using pg_stat_statements.