How the PostgreSQL Planner Selects Indexes

When PostgreSQL runs a query, its planner builds an execution plan by comparing indexes that support the query’s conditions. It uses data statistics and estimated costs to compare plans, including the operations each index supports. An index is usable when it supports the query’s search condition. The planner compares using that index with other choices, including a sequential scan, which reads the table.

The choice affects which rows PostgreSQL visits and how it returns them. Understanding the decision helps you see why a plan uses one index, combines several indexes, or scans the table. The examples and behavior below follow the PostgreSQL 18 documentation; check the documentation for the version you run when details matter.

First, PostgreSQL must be able to use the index

An index must support the operation in the query. A B-tree index, PostgreSQL’s default index type, supports equality and range comparisons and can provide sorted output. Other index types support different searches. An operator class tells PostgreSQL which operators an index method supports for a particular data type. For example, having an index on a column does not make every expression or comparison involving that column indexable.

A plain index on email does not directly match a condition such as lower(email) = 'person@example.com', but an index created on lower(email) does.

A partial index stores entries only for rows that satisfy a condition in the index definition. PostgreSQL can use it when it can determine that the query’s WHERE condition implies the index condition. That determination has limits: a parameterized condition might not establish the implication when PostgreSQL plans the query.

For a B-tree index on several columns, PostgreSQL scans a narrower part of the index when conditions match the leading columns to exact values and apply a span condition to the next column. Conditions on later columns can still help check rows, but they do not necessarily narrow the scan as much.

Statistics and costs shape the choice

Once PostgreSQL finds usable ways to run the query, it estimates how many rows each way will return and how much work it will require. These estimates use approximate statistics about table size and how values are distributed. ANALYZE or VACUUM ANALYZE gathers those statistics. Run ANALYZE before investigating a surprising plan, especially after data changes.

The estimates can be inaccurate. PostgreSQL generally estimates conditions on separate columns as independent, so correlated values can lead it to misjudge how many rows a query will return. Extended statistics record selected relationships between columns and can improve estimates for such queries.

PostgreSQL compares estimated costs, which are relative units rather than milliseconds. The estimates account for factors such as page access and CPU work. A regular index scan finds matching entries in the index, then fetches the corresponding table rows. When a query needs many rows, fetching them this way can cost more than reading the table in order. A bitmap scan collects row locations from an index, then fetches the table rows in their physical order. For some queries, this takes less work.

An index can also help satisfy ORDER BY. A B-tree index with a matching order can avoid a separate sort. With ORDER BY ... LIMIT, PostgreSQL can sometimes return the requested first rows without processing all matches. If a query needs a large portion of the table, scanning and sorting may cost less.

LIMIT can change which index wins

Consider the PostgreSQL 18 documentation’s example:

SELECT *
FROM tenk1
WHERE unique1  9000
LIMIT 2;

The documented plan scans the tenk1_unique2 index, then applies unique1 < 100 as a filter. The planner estimates that this scan can find two rows that meet the conditions and stop. A bitmap plan would first have to collect matching row locations in a bitmap. That initial work would delay the first rows.

For a query that needs more matching rows, PostgreSQL can instead combine indexes with a BitmapAnd, then fetch the matching table rows. PostgreSQL must scan each index, and the combined results do not keep the indexes’ ordering. The query might therefore also need a sort. The planner compares the full plans rather than choosing an index on its own.

The example demonstrates why adding LIMIT can change the plan: PostgreSQL accounts for the chance to stop early. It does not establish a universal rule about which index wins. The result depends on the query, data, statistics, and cost estimates.

Read the scan conditions in EXPLAIN

To understand an index choice, find the plan step that scans the table and check which conditions PostgreSQL applies there:

• Index Scan using ... names the index used for a regular index scan. Bitmap Index Scan builds row locations, which a parent Bitmap Heap Scan uses to fetch table rows.
• Index Cond shows a condition used by the index scan to find candidate rows. Filter shows a condition checked after the node fetches rows. A filter can reduce the output without reducing the rows the index path must visit.
• Index Only Scan using ... means the plan can return requested values from the index. Whether PostgreSQL avoids visiting the table also depends on visibility information for the table’s pages.
• Recheck Cond appears with bitmap scans when PostgreSQL must check a condition again against fetched rows.

In the example above, unique2 > 9000 appears as the index condition, while unique1 < 100 appears as a filter. That distinction shows which predicate narrowed the index search and which PostgreSQL checked afterward.

In plain EXPLAIN, rows and cost are estimates. The estimated row count describes how many rows the plan step returns, not necessarily how many it examines. EXPLAIN ANALYZE runs the query and reports actual row counts and timing alongside the estimates. Compare the estimated and actual counts to find gaps. Because EXPLAIN ANALYZE executes the statement, take care with queries that modify data.

Investigate an unexpected plan

First, check whether PostgreSQL can use the index. Confirm that the index supports the query’s operator and expression. For a multicolumn index, check that the query conditions match its column order. For a partial index, check that the query conditions imply its condition. Then check whether the statistics are current and whether estimated row counts differ from actual counts.

Finally, compare the full plan. Check its filters and table fetches. Inspect its bitmap steps and sorts, and see whether LIMIT lets PostgreSQL stop early. Test with data and a workload that represent production, because a plan that works well on a small or differently sized table might not suit production data.

You can use planner settings such as enable_indexscan to help diagnose plan choices; these settings influence which plan types PostgreSQL considers. Forcing an alternative does not prove it will perform better under normal settings. Adjust cost parameters only when they better reflect the storage and caching behavior of the environment; changing them just to make PostgreSQL choose a particular index can hide the underlying issue.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com