Writing Queries the Planner Can Optimize

The query planner is smart about shapes, not semantics — it can't see through WHERE f(col) = const unless you gave it a matching expression index. Most "Postgres is slow" complaints are queries written in a shape the planner can't help.

SARGable predicates

A predicate is search-argument-able when it can use an index — roughly: column on one side, constant on the other, no function wrapping the column.

-- not sargable: the index on created_at is useless
WHERE date_trunc('day', created_at) = '2024-06-01'

-- sargable: a range hits the index directly
WHERE created_at >= '2024-06-01' AND created_at < '2024-06-02'

Same result, different plan. lower(email), substring(col), col + 1 — same trap. Either rewrite as a range/comparison or index the expression: CREATE INDEX ON users (lower(email)).

Offset pagination doesn't scale

OFFSET 50000 still scans and discards 50,000 rows — the deeper the page, the slower the query. Keyset pagination filters where the last page ended:

SELECT * FROM events
WHERE (created_at, id) < ($1, $2)      -- cursor from last row of previous page
ORDER BY created_at DESC, id DESC
LIMIT 50;

Constant time regardless of depth. The cursor needs a unique tiebreaker column (id) and a matching composite index.

The N+1 your ORM hides

SELECT post then SELECT comments WHERE post_id = ? per post is a hundred round trips for what one join does. ORMs make it invisible — log queries (log_min_duration_statement or statement logging in dev) and look for identical shapes in a loop.

Field notes

• JOIN with the right indexes beats correlated subqueries almost every time; a LATERAL join is the sharp tool for "top N per group" (CROSS JOIN LATERAL (SELECT ... ORDER BY ... LIMIT 1)).
• EXISTS beats IN (SELECT ...) for correlated existence checks — the planner can stop at the first match.
• NOT IN with NULLs silently returns nothing: x NOT IN (1, 2, NULL) is always false. Use NOT EXISTS — it's NULL-safe and usually faster.