An Index Must Serve a Real Query

A production query needed a small page of recent records requiring operational attention. The table already had an index which looked relevant. The measured scan still discarded 84,003 historical index entries and recorded 122,936 shared-buffer reads before finding 83 eligible rows.

The missing index was not “an index on the table.” It was an index for this query:

one tenant
active records with zero price
created during the last three months
newest creation time first
stable identifier as the tie-breaker
first page only

Indexing starts with the access path, not with a list of columns which sound important.

Read the measured plan before naming the fix

The retained investigation used a read-only EXPLAIN (ANALYZE, BUFFERS) script with statement and lock timeouts. It captured the exact page query instead of a simplified approximation.

The numbers matter because they narrow the waste:

historical index entries rejected: 84,003
shared-buffer reads:                122,936
eligible rows at the measured node: 83

“The query is slow” leaves many possible causes. The scan evidence says the database can enter an index but must walk through a large historical region before the remaining predicates and limit yield the useful rows.

PostgreSQL documents that EXPLAIN shows the plan tree, rows removed by filters, loops, and buffer activity. Buffer counts are non-distinct operations at a node and its children, not a count of unique pages. EXPLAIN ANALYZE executes the query and adds measurement overhead, so the diagnostic needs the same safety care as other production reads.¹

Do not jump from one large number to “add more indexes.” Locate the node doing the work, compare estimated and actual cardinality, inspect loops, and identify which query condition becomes a filter instead of an index condition.

The index should read like the access path

The corrected PostgreSQL index has this conceptual shape:

CREATE INDEX CONCURRENTLY recent_attention_items
ON records (
    tenant_id,
    created_at DESC,
    id DESC
)
WHERE
    amount = 0
    AND cancelled_at IS NULL
    AND refunded_at IS NULL;

Each part has a job.

tenant_id is the equality scope. created_at is the bounded range and primary display order. id makes that order total for stable pagination. The partial predicate excludes records which can never appear in this operational list.

The matching query is:

WHERE tenant_id = :tenant
  AND created_at >= :three_month_cutoff
  AND created_at < :tomorrow
  AND amount = 0
  AND cancelled_at IS NULL
  AND refunded_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT :page_size

The index is useful because equality, range, order, and limit cooperate. The database can enter one tenant’s recent index range and produce rows in the requested order rather than walking backward through unrelated history.

PostgreSQL notes that a B-tree matching ORDER BY is especially useful with a limit because it may return the first rows without sorting the full candidate set.²

Column order follows comparison shape

“Put the most selective column first” is not a complete index rule.

For a B-tree access path, ask:

Which columns use equality?
Where does the range begin?
Which order must the scan produce?
Which columns make that order unique?

A common shape is:

equality scope -> range and primary order -> tie-breaker

That is why tenant precedes creation time in this case. The tenant is fixed, then the query enters a bounded time range and reads newest first.

Reverse those columns and the index may serve a global recent-time scan while doing more work to isolate one tenant. Omit creation time and the query can enter the active zero-price subset but still walk through old entries. Omit the identifier and the visible order can contain ties.

The right order depends on the real predicate and distribution. Another query may need a different index even when it touches the same columns.

A partial predicate should describe durable exclusion

The operational list cares only about zero-price records which are neither cancelled nor refunded. A partial index keeps only that subset.

PostgreSQL can use a partial index only when it can recognize that the query’s conditions imply the index predicate. Equivalent business meaning hidden behind different SQL, some parameterized predicates, or an omitted condition can make the index ineligible.³

This gives partial indexes two costs beyond ordinary index maintenance:

• the application query and index predicate must remain compatible; and
• the excluded population must remain genuinely irrelevant to that access path.

If operators later need cancelled records in the same list, the query contract has changed. The partial index should be reconsidered rather than forced onto the new path.

Partial indexes are useful when the interesting subset is small and stable enough to justify special treatment. They are not a substitute for partitioning, and a collection of partial indexes for every possible value can be worse than one well-chosen general index.

Representative data decides whether the design is useful

A query which returns one row from a table of one hundred test records may prefer a sequential scan because the whole table fits in very few pages. That does not predict the plan for a large production distribution.

PostgreSQL’s index-usage guidance is blunt: run ANALYZE, experiment with real data, and do not treat tiny synthetic data as representative.⁴

Useful evidence includes:

• table and index sizes;
• value distribution for tenant and state predicates;
• fraction of rows inside the time window;
• actual versus estimated rows at important nodes;
• rows removed by filters;
• loops for correlated subqueries or joins;
• buffer hits and reads;
• sort method and memory; and
• cold, warm, and repeated execution behavior when those distinctions matter.

The retained production measurements justify the access-path diagnosis. The repository does not retain the actual before-and-after plan outputs, so this article does not claim a measured post-change speedup.

Query and index changes should land as one contract

The original page ordered by identifier. The new index needed creation time to turn the three-month condition into a bounded ordered scan.

Changing only the index would not make the query request that order. Changing only the query could force a sort without a matching index. The correction therefore changed both:

query order:
    created_at DESC, id DESC

index order:
    tenant_id, created_at DESC, id DESC

The visible meaning remains “newest first,” but it becomes explicit that newest means creation time. The identifier remains the deterministic tie-breaker.

A focused regression verifies the primary query sort. It does not prove the production planner chooses the index. That requires a representative plan after deployment.

Index rollout is part of the cost

Every additional index consumes storage and write work. Inserts create another entry. Updates to indexed columns or predicate membership may add, remove, or rewrite entries. Vacuum and backup scope grow.

The retained migration builds the index concurrently and outside the normal migration transaction. That reduces write blocking during construction, but it does not make the operation free. Construction still reads the table, consumes I/O and CPU, and can fail.

A rollout plan should cover:

build method
lock and statement timeouts
capacity during construction
invalid-index detection
post-build ANALYZE
representative EXPLAIN verification
rollback or drop procedure
usage review after deployment

An unused index is not harmless documentation. It is permanent write and operational cost until someone removes it.

The fixture models fit, not a PostgreSQL plan

The [DATA-10 fixture][reproduction] creates synthetic rows across tenants, dates, and lifecycle states. It compares the candidates retained by a broad tenant index, an active zero-price partial index, and the full recent ordered access-path model. All three paths produce the same final result; the narrower models examine fewer irrelevant candidates.

It also counts which writes would affect the partial index. The fixture does not run PostgreSQL, generate an execution plan, reproduce the retained measurements, or prove the planner will choose an index.

An index is a hypothesis with a maintenance bill

Before adding one, record:

exact query shape
representative parameters
measured plan and cardinalities
predicate and ordering contract
expected candidate reduction
write and storage cost
safe rollout
post-deployment verification
removal condition

Then measure again against the same query and representative data.

The retained case needed no generic call to “index the slow table.” It needed one tenant, one active subset, one recent range, one ordered page, and one stable tie-breaker to become the same access path. An index earns its place only when the query can make that sentence true.