PostgreSQL: Find the Right Index with EXPLAIN

1. Narrow the problem down to one query#

A slow API may be waiting for database connections, serializing too much data, or running hundreds of small queries. Before you create an index, identify the query that is actually consuming time and how many times it runs per request. Capture representative parameters without copying sensitive data into your logs.

Picture a list of jobs filtered by workspace and status, sorted from newest to oldest. The access pattern combines a filter, a sort order, and a limit, and that combination is what you need to optimize. Testing only a SELECT by primary key tells you nothing about the screen that is actually slow. Reproduce the problem in your authorized test environment with a representative data distribution.

2. Tell estimates apart from actual execution#

EXPLAIN shows the estimated plan. EXPLAIN ANALYZE actually runs the statement and adds real measurements; use it carefully, even on a SELECT that calls functions. For writes, wrapping the statement in a transaction and rolling it back does not guarantee that external side effects from functions are undone. Start with controlled reads and a time budget.

-- On authorized staging: $1 and $2 are driver parameters.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, title
FROM jobs
WHERE tenant_id = $1 AND status = $2
ORDER BY created_at DESC, id DESC
LIMIT 50;

Compare estimated and actual rows, the number of loops for each node, and the buffer counts. Plan cost is not expressed in milliseconds; it is an abstract unit used by the planner. The times reported for upper nodes include the work done by their children, so simply adding them up counts the same work twice. A large gap between estimated and actual cardinality can point to insufficient statistics or a skewed data distribution.

Documentation: PostgreSQL: Using EXPLAIN ↗

3. Make the index match the access pattern#

For this pattern, one candidate is a B-tree index that starts with tenant_id and status, followed by created_at and id. The first two columns serve the equality conditions and the remaining two serve the sort order. Treat this as a working hypothesis, not a recipe for every query on the table. Also check the screens that filter the same table in other ways.

-- Run outside a transaction block.
CREATE INDEX CONCURRENTLY jobs_tenant_status_created_id_idx
ON jobs (tenant_id, status, created_at DESC, id DESC);

Every index takes up disk space and adds work to inserts and updates. Do not add every selected column as a covering column out of habit. Compare the query plan and the write behavior before and after the change. A sequential scan can be perfectly reasonable when the query needs a large share of the table.

Documentation: PostgreSQL: Multicolumn indexes ↗ · PostgreSQL: CREATE INDEX and concurrent builds ↗

4. Use a stable sort order when paginating#

created_at values can repeat. Adding a unique identifier as a tiebreaker lets you describe a position in the result set without ambiguity. With a descending sort, the next page asks for rows whose tuple is smaller than the last one received. This example assumes that created_at and id are NOT NULL and that their values do not change while the user is paging.

SELECT id, created_at, title
FROM jobs
WHERE tenant_id = $1 AND status = $2
  AND (created_at, id) < ($3, $4)
ORDER BY created_at DESC, id DESC
LIMIT 50;

Validate the cursor and tie it to the filters and sort order it was issued for. The tenant still comes from the authorized session, never from the cursor. If dates or filters change while the user is paging, the result set can shift: keyset pagination avoids the cost of deep offsets, but it does not give you a transactional snapshot across requests.

5. Verify the benefit under load#

A single run against a warm cache does not represent every request. Compare several runs and the endpoint's latency percentiles, with reasonable concurrency and tenants of different sizes. Watch busy connections, lock wait times, CPU, physical reads, and the volume of data returned to the client.

Record the schema version, the plan, and the test conditions. If you cannot safely reproduce production volume, document that limitation as part of the decision. Do not turn a small measurement into a capacity promise.

6. Ship a verifiable migration#

Delivering the change includes checking that the index is valid, that the relevant plan can use it, and that writes have not regressed. Agree on a rollback path with the team that operates the database. Avoid bundling the change with other migrations that are hard to separate, because you would lose clarity when attributing the result.

Look again when the data grows or the query changes. The right index today may stop being enough if the most common filter shifts. A managed database provider makes operations easier, but it does not know your endpoint's contract: that part of the design remains the application's responsibility.