PostgreSQL EXPLAIN: Diagnose Before Adding an Index

PostgreSQL EXPLAIN is most useful when you bring a question to the plan. “Why is this tenant's activity page slow?” is a productive question. “How do I remove every sequential scan?” usually is not.

My starting point would be the exact SQL, representative parameter values, and the user-visible latency. An index can solve a data-access problem. It cannot explain time spent waiting for a connection before the query begins, and it may not address a query blocked behind another transaction.

Establish what you are measuring

Plain EXPLAIN shows the planner's estimated execution strategy. EXPLAIN ANALYZE executes the statement and reports observed behavior. PostgreSQL warns that execution really occurs; this matters especially for statements that modify data. Its Using EXPLAIN guide also describes row estimates, timing, loops, and buffer reporting.

For a read-only investigation in an appropriate environment, an illustrative command is:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, kind
FROM activity
WHERE tenant_id = 42
  AND kind = 'deployment'
ORDER BY created_at DESC
LIMIT 30;

This is a diagnostic example, not a measured result. Even a SELECT can consume substantial resources or call functions with side effects. Use a representative safe query, a suitable timeout, and an environment where the work is acceptable.

Before reading the plan, write a hypothesis: “The database visits many rows for this tenant before finding thirty deployment events.” That statement tells you what evidence would justify changing access paths.

Read mismatches before node names

Imagine a plan whose filter expects a small candidate set but observes thousands of rows. I would investigate the estimate and the data distribution before declaring the chosen join or scan algorithm wrong. Compare the same query for a small tenant and a large one; a single average can conceal the workload that users actually experience.

Also look at repetition. A cheap-looking operation executed many times may dominate the query. Do not add nested timing values together as though every parent and child measured independent work. Focus on where the volume grows and what operation receives that volume.

Keep a small investigation sheet:

The sheet is my proposed workflow. Its purpose is to stop a visually intimidating plan from becoming an excuse to change several things at once.

Design the index around the access pattern

For this exact query, a candidate is (tenant_id, kind, created_at DESC). Equality conditions narrow the first two columns, then the timestamp supports the desired order. PostgreSQL explains how multicolumn B-tree indexes use leading equality constraints and subsequent range constraints in its multicolumn index documentation.

That does not mean this index is automatically the best choice. Suppose the application also needs all activity kinds ordered together. The same index may not provide the desired ordering as directly for that different query. Write down both query shapes before deciding whether two indexes justify their maintenance and storage cost.

I would test one candidate at a time with production-like data distribution. Record the before and after plan, query latency under the same measurement conditions, and any write-cost impact relevant to the application. If the result is inconclusive, call it inconclusive rather than attaching a performance claim to an index definition.

Distinguish a candidate from a production migration

Creating an index during investigation and safely deploying it are separate tasks. PostgreSQL supports concurrent index creation, with operational caveats including extra work, transaction-block restrictions, and possible invalid indexes after failure. Consult CREATE INDEX for the version actually deployed.

A proposed rollout record should include the index name, expected query benefit, allowed build window, abort condition, and a check for the resulting index's validity. If a deployment tool wraps every migration in a transaction, verify compatibility before running a concurrent build.

Do not remove an older index simply because the new one looks similar. Identify which other queries use it, observe a representative workload, and make removal its own reviewed decision. This is especially important when background jobs are absent from the interactive workload used during the investigation.

Keep the explanation with the change

I would put a short explanation beside the migration: which query was slow, which observation supported the hypothesis, and what evidence changed after the candidate index was added. That gives the next engineer a reason to retain or reconsider it.

The cursor pagination example provides a concrete ordered query to analyze. The structured API discussion connects query behavior to the endpoint contract users experience.

A good EXPLAIN investigation ends with a smaller uncertainty: either the index solves the demonstrated access problem, or the evidence points somewhere else. Both outcomes are better than collecting indexes until a dashboard turns green.