Choosing an Index After Reading EXPLAIN ANALYZE

Composite indexes in PostgreSQL are ordered lexicographically: the first column is most significant, then the second within equal values of the first, and so on Ken Wagatsuma on multi-column index column orders. A query can use an index on (a, b) when it filters on a alone or on a and b, but not when it filters only on b Ken Wagatsuma on multi-column index column orders. That leftmost prefix behavior is the whole game.

For B-tree workloads the working rule is: start with the equality columns, then the column you sort or range on the 17ms to 0.04ms index design walkthrough. Equality columns are interchangeable among themselves, so put the more selective one first if you have a reason, but never put a range or sort column ahead of an equality column the query always supplies.

Applying that to the query above:

A useful mental model for why range comes last: the index is a phone book sorted by last name, then first name. You can find "Smith, John" instantly, and you can walk every Smith in alphabetical order, but you cannot efficiently find everyone whose first name is John across all surnames. Once you enter a range at a given level, deeper levels are only ordered inside that range.

Matching sort direction matters when a query mixes directions. An index on (customer_id, status, created_at DESC) can return rows in the order the query needs, which removes the Sort node entirely. The published example showed exactly this: all three conditions moved into the index condition and declaring the third column DESC made the Sort disappear, taking the query from 16.6 ms to 0.039 ms the 17ms to 0.04ms index design walkthrough.

Two cautions. First, if the same table sometimes gets queried by status without a customer, an index leading with customer_id will not help that query; you may need a second index or a partial index (see below). Second, the popular "most selective column first" heuristic applies only among the leading equality columns; it is secondary to the equality-then-range rule. When in doubt, let the database tell you. A practical guide to B-trees notes that the same query may use an index for one constant value and not another when the row counts per value differ wildly, and that the planner is usually right the Heroku Dev Center guide to efficient PostgreSQL index use.

PostgreSQL indexes are secondary structures stored separately from the table heap, so an ordinary index scan reads matching entries from the index and then visits the heap for each row, which is a lot of random access the PostgreSQL documentation on index-only scans and covering indexes. An index-only scan answers the query from the index alone, subject to two restrictions: the index type must support it (B-tree always does; GIN never does) and the query must reference only columns stored in the index the PostgreSQL documentation on index-only scans and covering indexes.

There is a catch even then. Visibility information lives in the heap, not in index entries, so an index-only scan still checks the visibility map for pages not marked all-visible the PostgreSQL documentation on index-only scans and covering indexes. The plan reports Heap Fetches, and a value of zero in a demo reflects pages being all-visible during that run, not a permanent guarantee the 17ms to 0.04ms index design walkthrough.

That gives a three-branch decision rule:

1. If the query returns many wide columns you do not filter on, do not try to cover it. Appending half the table to the index bloats the structure and slows every write.
2. If the query selects a small set of narrow payload columns and runs often, INCLUDE is the right tool. It stores extra columns with each index row so the query can be satisfied from the index, without adding those columns to the sort key the practical index guide on Medium. The trade is index size and write cost, not correctness.
3. If the payload columns are only needed for display after the LIMIT, skip INCLUDE. Fetching 50 heap rows is cheap, and covering the index for 50 rows spends space on all 4 million.

The pragmatic test is the plan. Create the index, rerun EXPLAIN ANALYZE, and look for Index Only Scan with a low Heap Fetches count. If you see Index Scan with heap visits, the index did not cover the query, and you either add the columns or accept the heap visit. One honest caveat for the example query: id is a table column, so if you want an index-only scan you must either include id in the index or drop it from the select list.

A partial index contains only the rows matching its WHERE clause, which makes it smaller, cheaper to maintain, and faster to scan the Heroku Dev Center guide to efficient PostgreSQL index use. The canonical shape is a boolean flag where the application almost always queries one value: an index on created_at with WHERE flagged IS TRUE, so batch processing sees only flagged rows the Heroku Dev Center guide to efficient PostgreSQL index use.

Our orders query is a strong candidate if two things hold: the status distribution is skewed toward closed orders, and the application always filters on 'open'. Suppose 96 percent of the 4 million rows are closed and 160,000 are open. A full index on (customer_id, status, created_at DESC) indexes 4 million entries; the partial version indexes 160,000, a 25 times reduction in index size and matching reduction in write amplification.

The trade is explicit. A partial index is invisible to queries that omit the predicate, and the planner will not use it unless the query's WHERE clause implies the index predicate the Heroku Dev Center guide to efficient PostgreSQL index use. So if the same table also serves an admin screen that filters by customer and date range regardless of status, you need both indexes, or you accept that the admin screen scans.

There is one more trap: parameterization. If the status arrives as a bind parameter, the planner may not prove that the parameter implies status = 'open', and the partial index goes unused. Keep the literal in the SQL for that specific query shape, or have the application pick the query text from the known status. A partial index that "should" be used but is not is usually a mismatch between the query's parameters and the index predicate.

Partial indexes also pay off for soft deletes and queue tables: WHERE deleted_at IS NULL or WHERE processed_at IS NULL shrink the index to the live working set while the table keeps growing.

Every index must be maintained on every INSERT, UPDATE, and DELETE, and indexes consume disk space, which is why the documentation warns against adding them indiscriminately the PostgreSQL documentation on indexes. The application developer's mental model is worth repeating: an index is a separate sorted structure pointing back at rows, not a free optimization, and inserting a row into the middle of that sorted structure costs more than appending the pganalyze mental model for tables and indexes.

Run a quick numeric sanity check. If the orders table takes 2,000 inserts per minute at peak and you add one composite index, that is 2,000 extra index insertions per minute, plus degraded HOT updates whenever an indexed column changes. A partial index covering 4 percent of rows still receives every insert that matches its predicate (a new order is usually open), so its write savings come from updates that flip status to closed, which remove entries from the partial index but not the full one. Read-heavy and write-light workloads barely notice; append-heavy event logs notice immediately.

A useful rule: add an index only if it changes a plan node for a query you have actually seen in your logs, and drop indexes that no query in your workload references. Heroku's guide covers index maintenance and the reasons an index may go unused, including columns that are not selective enough to justify an index scan the Heroku Dev Center guide to efficient PostgreSQL index use.

One operational note: on a large table, build the index with CREATE INDEX CONCURRENTLY, which allows reads and writes to continue but takes longer than the blocking form the Neon guide to PostgreSQL indexes. It cannot run inside a transaction block, so keep it out of migrations that wrap everything in BEGIN and COMMIT. If it fails, it leaves an invalid index behind that you must drop before retrying.

Verification is not optional, because the planner is under no obligation to use the index you built. Here is the plan you want after adding:

CREATE INDEX CONCURRENTLY ix_orders_open_customer_created
ON orders (customer_id, status, created_at DESC)
INCLUDE (id, total_cents)
WHERE status = 'open';

Then:

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_cents, created_at
FROM orders
WHERE customer_id = 88123
  AND status = 'open'
  AND created_at >= now() - interval '30 days'
ORDER BY created_at DESC
LIMIT 50;

Expected output, abbreviated:

Limit  (cost=0.43..18.92 rows=50 width=28) (actual time=0.031..0.212 rows=50 loops=1)
  ->  Index Only Scan using ix_orders_open_customer_created on orders  (cost=0.43..218.55 rows=612 width=28) (actual time=0.028..0.198 rows=50 loops=1)
        Index Cond: ((customer_id = 88123) AND (status = 'open'::text) AND (created_at >= (now() - '30 days'::interval)))
        Heap Fetches: 3
Execution Time: 0.241 ms

Check four things, in this order:

1. The dominant node changed. The parallel sequential scan is gone, replaced by an index access on the new index. If it is still there, read the filter line: if the condition appears as Filter instead of Index Cond, the predicate did not match the index.
2. The sort is gone. With created_at DESC matching the query's ORDER BY direction, the Sort node disappears and the LIMIT is served in index order. If a Sort remains, check the direction and whether the range column sits ahead of an equality column.
3. The rows-removed figure collapsed. A low Rows Removed by Filter means the index did the selectivity work.
4. If you went the INCLUDE route, read Heap Fetches. Near zero on a well-vacuumed table means the index-only scan is real; consistently high means the visibility map is stale and the heap visit is still happening the PostgreSQL documentation on index-only scans and covering indexes.

Two more habits. Do not measure on cold cache only: the first run after a restart touches disk, later runs hit shared buffers, so run each plan twice and read the second. And compare actual with estimated rows at every node. A plan that is fast now because the estimate was good will not stay fast if the distribution shifts, and the documentation notes that estimates vary because ANALYZE samples rather than counts exactly the PostgreSQL documentation on using EXPLAIN.

Then make the result durable. The database does not remember why an index exists, so put the before and after plans in the pull request that adds the index, along with the query it serves. When a review of agent-written migrations touches the schema, a second opinion on the plan is cheap. If you want an expert looking over the schema and query plan alongside your team, Quanta Intelligence (https://quantaintelligence.ai/) can put a specialist on the job.