Indexes and query plans

Match the index to the access pattern

An index helps find selected rows, but uses storage and adds work to writes. Primary and unique constraints already create supporting indexes; a foreign key does not automatically create an index on its referencing columns. Avoid adding a duplicate index without inspecting the existing definitions. Indexes↗, constraints↗.

EXPLAIN estimates a plan. EXPLAIN (ANALYZE, BUFFERS) actually executes the statement and reports observations. Do not use it on an unreviewed production mutation. Even a read can be expensive. A tiny table using a sequential scan is not automatically a performance problem. Using EXPLAIN↗.

Compare a small, labelled workload

Use the isolated fixture server. This separate table has 20,000 synthetic observations, not measured MicroBank traffic. Execute the blocks in order once:

CREATE TABLE study.query_events (
  event_id bigint PRIMARY KEY,
  account_id bigint NOT NULL,
  occurred_at timestamptz NOT NULL
);
INSERT INTO study.query_events
SELECT n, n % 1000,
       timestamptz '2026-09-01T00:00:00Z' + n * interval '1 second'
FROM generate_series(1, 20000) AS g(n);
ANALYZE study.query_events;

EXPLAIN (ANALYZE, BUFFERS)
SELECT event_id, occurred_at FROM study.query_events
WHERE account_id = 42
ORDER BY occurred_at DESC LIMIT 5;

Now introduce a candidate index and repeat the same read:

CREATE INDEX query_events_account_time_idx
ON study.query_events (account_id, occurred_at DESC);
ANALYZE study.query_events;
EXPLAIN (ANALYZE, BUFFERS)
SELECT event_id, occurred_at FROM study.query_events
WHERE account_id = 42
ORDER BY occurred_at DESC LIMIT 5;

The matching IDs should remain 19042, 18042, 17042, 16042, 15042. Compare scan type, rows filtered, estimated versus actual rows, sort steps, buffers, and execution time. Let the planner choose the plan and explain its choice. Repeated runs warm caches, so a faster second run alone is weak evidence.

The index column order is chosen for equality on account followed by timestamp ordering. Different queries may need different designs. ANALYZE updates planner statistics; it does not change the query's intended answer. Multicolumn indexes↗, planner statistics↗.

Checkpoint

Keep before/after plans and the unchanged result. Explain one cost this index adds to writes. For a real service, first identify a frequent or expensive query using appropriate telemetry; a speedup in this fixture does not predict the improvement for MicroBank or production.

Read the plan in a useful order

First confirm the actual result, then find the scan, any sort, and the limit. Estimated rows describe the planner's expectation, while actual rows and loops come from execution; repeated loops matter when judging total work. Cost numbers are planner units, not milliseconds. Buffer observations describe access through PostgreSQL's buffer accounting, not a complete disk benchmark.

For this fixture, the account-first index groups the rows relevant to account 42, then orders them by timestamp so the limited query can avoid examining unrelated accounts when that plan is chosen. It costs space and additional maintenance on inserts/updates/deletes. A different plan is evidence to explain, not a reason to force an index just to match a screenshot. Preserve both plans and continue to roles and connection budgets.