164: SQL - Indexes and Query Plans

Learning outcomes

design indexes from real access patterns;
read an EXPLAIN plan without cargo-culting indexes;
compare offset and keyset pagination.

Practice

Measure the owner's task-list query before and after (owner_id, created_at, id). Use EXPLAIN (ANALYZE, BUFFERS) on disposable data. Document selectivity, write cost, and the query shape served by the index.

Reading a plan

Look first at actual rows versus estimated rows, scan type, join strategy, and total loops. A sequential scan is not automatically a defect for a small table. An index can be ignored when most rows match, statistics are stale, the expression differs, or the planner estimates incorrectly.

For keyset pagination, carry the last (created_at, id) pair and query the next page with a matching lexicographic predicate. Offset pagination is simpler but gets slower and can shift under concurrent inserts.

Evidence

Use the same dataset and warm/cold conditions for comparison. Record query latency, rows examined, index size, and write impact instead of claiming an index is faster from one local run.

Checkpoint

Explain why an index is not automatically used, what a sequential scan can mean, and why keyset pagination needs a stable cursor.

References

Plans for analytical queries

Window queries and CTEs still need indexes and measurement. For top-N-per-group, compare the window query from 117 with a correlated LATERAL query when an index can serve each owner's newest rows:

sql
SELECT owners.id, recent.id, recent.title
FROM owners
CROSS JOIN LATERAL (
  SELECT t.id, t.title
  FROM tasks AS t
  WHERE t.owner_id = owners.id AND t.status = 'open'
  ORDER BY t.created_at DESC NULLS LAST, t.id DESC
  LIMIT 3
) AS recent;

An index such as (owner_id, status, created_at DESC, id DESC) may help this shape, but its benefit depends on owner count, status selectivity, table visibility, and write cost. Use EXPLAIN (ANALYZE, BUFFERS) and check that the plan's actual row counts and loops fit the workload.

For duplicate cleanup, first identify duplicates and retain a deterministic winner, then delete by primary key in a transaction. Add a unique constraint only after cleaning existing data; otherwise the migration fails halfway or blocks writers unexpectedly. Test ties, NULL ordering, an owner with no tasks, a huge group, and a duplicate inserted concurrently. Interview question: why can DISTINCT ON be fast but PostgreSQL-specific, and what portability or index tradeoff justifies the window-function alternative?