PostgreSQL Index

A PostgreSQL index is a promise to the planner: “if you come to me with this predicate, I will hand back the matching rows faster than a heap scan would.” The planner does not take that promise on trust. Before it uses an index it checks six things, in a fixed order, and any one of them failing sends the query back to a sequential scan or a worse index. Most of the “PostgreSQL ignored my index” tickets we handle at MinervaDB are one of those six checks failing quietly.

This page is organised as that checklist. Each section is one check, the catalog query or EXPLAIN detail that shows whether it passed, and the archive posts that go deeper. It is written for the engineer who already knows what a B-tree is and wants to know why the planner did what it did.

The archive it introduces covers PostgreSQL index bloat and REINDEX CONCURRENTLY, partial and spatial indexes, plan stability, outdated statistics, LIKE and ILIKE, index selection inside the planner, the PostgreSQL 17 and 18 changes, and the myths that survive every release.

Check 1: does a PostgreSQL index exist that matches the predicate’s operator class?

The first check is the dullest and the one most often failed by schema drift. The planner looks for an index whose leading column, operator class and collation match the predicate. A B-tree on lower(email) is invisible to WHERE email = $1; a B-tree on a text column with the default collation is useless for ILIKE; a GiST index answers && but not =.

The catalog answers this before EXPLAIN does:

SELECT
    i.indexrelid::regclass                          AS index_name,
    am.amname                                       AS access_method,
    pg_get_indexdef(i.indexrelid)                   AS definition,
    i.indisvalid,
    i.indisunique,
    pg_size_pretty(pg_relation_size(i.indexrelid))  AS size
FROM pg_index i
JOIN pg_class c   ON c.oid = i.indexrelid
JOIN pg_am am     ON am.oid = c.relam
WHERE i.indrelid = 'public.orders'::regclass
ORDER BY pg_relation_size(i.indexrelid) DESC;

Two columns matter more than they look. indisvalid = false means a CREATE INDEX CONCURRENTLY failed part-way and left a shell the planner will never use but that every write still maintains. The access method column is where the LIKE question is settled: the optimizing PostgreSQL LIKE and ILIKE performance post works through when a B-tree with text_pattern_ops is enough, when a trigram GIN index is needed, and why ILIKE defeats both without an expression index.

The same check decides the spatial case. The faster PostgreSQL spatial indexes post covers GiST versus SP-GiST for PostGIS geometry and the operator classes each one serves, and the btree_gist improvements in PostgreSQL 18 post covers the extension that lets a single GiST index serve both a range predicate and an equality on an ordinary column. For the question “does PostgreSQL have a row-store index at all”, the PostgreSQL row store index post answers it plainly: the heap is the row store, and every index is secondary.

Check 2: are the statistics fresh enough for the planner to trust the PostgreSQL index?

The planner uses an index only if its estimate of the rows returned is small relative to the table. That estimate comes from pg_statistic, which is only as good as the last ANALYZE. A table that received a million rows since its last analyze has a planner that still believes it is small, and a small table is cheaper to scan than to probe.

SELECT
    relname,
    n_live_tup,
    n_mod_since_analyze,
    round(100.0 * n_mod_since_analyze / greatest(n_live_tup, 1), 1) AS pct_changed,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
WHERE n_mod_since_analyze > 0
ORDER BY pct_changed DESC
LIMIT 20;

A pct_changed above the autovacuum analyze threshold (10 percent by default, plus 50 rows) with a stale last_autoanalyze means autovacuum is behind, and the index decisions on that table are being made on old numbers. The resolving outdated statistics in PostgreSQL post is the diagnostic walkthrough, including the per-table autovacuum_analyze_scale_factor override that large tables almost always need.

Row estimates also depend on how many distinct values a column has, which is where correlated columns mislead the planner into multiplying selectivities that are not independent. Extended statistics (CREATE STATISTICS ... (dependencies, ndistinct)) fix that, and the index selection in PostgreSQL post shows the before-and-after on a real plan. The PostgreSQL indexing myths post retires the belief that adding an index changes the plan on its own; it changes nothing until the statistics say the index is worth using.

Check 3: is the PostgreSQL index cheaper than the alternatives on this plan?

Given a usable index and trustworthy statistics, the planner still prices it against a sequential scan, a bitmap scan and every other index on the table, using random_page_cost, seq_page_cost and effective_cache_size. On SSD-backed instances left at the default random_page_cost = 4, the planner overprices index probes and drifts toward sequential scans on mid-size tables. That is a configuration fault dressed up as an indexing fault.

EXPLAIN is the only honest witness here. The PostgreSQL query performance with EXPLAIN ANALYZE post is the reading guide, and the troubleshooting slow PostgreSQL queries with EXPLAIN ANALYZE and pg_stat_statements post pairs it with the workload view, so the query being tuned is one that matters rather than one that was noticed.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS, FORMAT TEXT)
SELECT o.order_id, o.total
FROM   orders o
WHERE  o.customer_id = 48213
  AND  o.created_at >= now() - interval '30 days';

-- what to read, in order:
--   1. the node type on the orders scan: Index Scan, Index Only Scan, Bitmap Heap Scan or Seq Scan
--   2. rows=  estimated versus actual on that node: a 10x gap is a statistics problem, not an index problem
--   3. Buffers: shared hit / read on the index versus the heap: a large heap read after a small index read
--      means the index found the rows but they were scattered: a covering index or CLUSTER is the fix
--   4. SETTINGS: random_page_cost and work_mem as they were when the plan was chosen

A plan that flips between an index and a sequential scan from one day to the next is the plan-stability problem, and the PostgreSQL plan stability with outlines post explains the options: pinned statistics targets, pg_hint_plan, and the extension-based outline approach, with the operational cost of each. The execution plan cache post covers the prepared-statement side, where a generic plan chosen on the sixth execution can quietly stop using the index that the first five custom plans used.

Check 4: can the PostgreSQL index answer the query without visiting the heap?

An index-only scan skips the heap entirely when every column the query needs is in the index and the visibility map says the page is all-visible. The second condition is the one that surprises people: a table with heavy updates has few all-visible pages until vacuum runs, so an index-only scan degrades into an index scan with heap fetches, and EXPLAIN shows it as Heap Fetches: N.

-- a covering index for the query in check 3: the WHERE columns lead, the SELECT columns ride along
CREATE INDEX CONCURRENTLY orders_customer_created_covering_idx
    ON orders (customer_id, created_at DESC)
    INCLUDE (order_id, total);

-- how much of the table is all-visible: low percentages mean heap fetches on every index-only scan
SELECT
    c.relname,
    c.relpages,
    c.relallvisible,
    round(100.0 * c.relallvisible / greatest(c.relpages, 1), 1) AS pct_all_visible
FROM pg_class c
WHERE c.relname = 'orders';

Vacuum is therefore part of index performance, not a separate topic. The PostgreSQL VACUUM and VACUUM FULL post explains why they are one process with two modes and what each does to the visibility map, and the performance bottlenecks in UPDATE- and DELETE-intensive applications post covers HOT updates, fillfactor and the index-write amplification that heavy updates cause. The PostgreSQL DELETE vs TRUNCATE post is the companion for the bulk-removal case, where the index cost of a million deletes is the reason to reach for partition drop or truncate instead.

Check 5: is the PostgreSQL index itself in good physical shape?

An index that passes the first four checks can still be slow because it is bloated: pages half-empty after churn, or a B-tree that has grown to several times its rebuilt size. The planner does not know this. It prices the index by its page count, so a bloated index is both slower to scan and, perversely, less likely to be chosen.

-- requires the pgstattuple extension (contrib); read-only, safe on production, scans the index
CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT
    i.indexrelid::regclass  AS index_name,
    s.avg_leaf_density,
    s.leaf_fragmentation,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS size
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid AND c.relam = (SELECT oid FROM pg_am WHERE amname = 'btree')
CROSS JOIN LATERAL pgstatindex(i.indexrelid) s
WHERE i.indrelid = 'public.orders'::regclass
ORDER BY s.avg_leaf_density;

-- rebuild without blocking writes; verify indisvalid afterwards, and drop the _ccnew leftover if it failed
REINDEX INDEX CONCURRENTLY orders_customer_created_covering_idx;

A leaf density below roughly 60 percent on a B-tree that is not freshly built is the working threshold we use before a rebuild is worth scheduling. The detecting and fixing PostgreSQL index bloat with REINDEX CONCURRENTLY post is the procedure, including the lock it does take (a brief ShareUpdateExclusiveLock) and the disk it needs (a full second copy).

The should I rebuild my PostgreSQL index post is the decision guide for when a rebuild helps and when it is theatre, and the rogue index troubleshooting in PostgreSQL 17 post covers the index that is large, unused, and still being written on every insert.

Unused indexes are the other physical-shape problem. They cost nothing to read and everything to write:

SELECT
    s.indexrelid::regclass                          AS index_name,
    s.relid::regclass                               AS table_name,
    s.idx_scan,
    pg_size_pretty(pg_relation_size(s.indexrelid))  AS size,
    i.indisunique
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique          -- unique indexes enforce constraints; leave them alone
  AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

Read idx_scan over a full business cycle, not a week; a month-end index scanned twelve times a year is not unused. The PostgreSQL index optimization and management post sets out the review cadence we run for managed customers, and why the drop is always DROP INDEX CONCURRENTLY behind a confirmation gate with the definition saved first.

Check 6: does the PostgreSQL index match the shape of the workload, not just one query?

The last check is the one the planner cannot make, because it sees one query at a time. An index that is perfect for one predicate can be the wrong index for the table: a partial index that covers the hot 2 percent of rows and lets the other 98 percent go to a sequential scan they were going to get anyway; a bulk load that would be five times faster with the indexes dropped and rebuilt afterwards; a LATERAL join whose inner side needs a different leading column from the outer.

The PostgreSQL partial index step-by-step guide post is the shape most often missing on OLTP tables with a status column: WHERE status = 'pending' indexes a few thousand rows instead of a few hundred million. The high-throughput bulk loading in PostgreSQL post measures the index-maintenance cost during COPY and the drop-load-rebuild pattern. The PostgreSQL LATERAL joins, aggregate functions and unnest posts each end with the index shape the construct wants, and the UPDATE … LIMIT in PostgreSQL post shows the ctid-based batching pattern whose index needs are different from a single-statement update.

Workload shape also includes what is not local. The foreign data wrappers post covers the fact that a remote index is used only when the predicate is pushed down, which EXPLAIN VERBOSE shows as the Remote SQL line; and the active-active PostgreSQL cluster and failover in AWS with Elastic IPs and DNS posts are reminders that an index strategy is per-node: a replica with different random_page_cost or a cold cache after failover will choose differently from the primary.

The six checks on one page

The table is the version of this page that fits on a runbook card. The “shows as” column is what the failure looks like from the outside; the “confirm with” column is the query above that proves it.

PostgreSQL index behaviour by version

The checks are stable across releases; what changes is how often each one fails. Version notes, pinned so they can be verified against the release notes:

Since PostgreSQL 13, B-tree deduplication stores repeated key values once per leaf page, which shrinks indexes on low-cardinality columns and makes check 5 fail less often on them. Since PostgreSQL 14, btree bottom-up deletion removes dead index tuples during inserts on tables with many non-HOT updates, reducing the bloat that heavy update workloads used to accumulate between vacuums. Since PostgreSQL 16, pg_stat_io exposes index reads and writes per backend type, so the “how much of this is index I/O” question in check 3 has a system view instead of a guess.

Since PostgreSQL 17, B-tree scans handle IN (...) lists and array predicates with a single index scan instead of one per value, which changes the plan for the common “look up these fifty ids” query; the PostgreSQL 17 rogue index post covers the diagnostics that release added.

Since PostgreSQL 18, B-tree skip scan lets the planner use a multicolumn index when the leading column is not in the predicate, provided the leading column has few distinct values, which removes a whole class of check-1 failures on (tenant_id, created_at) style indexes.

The PostgreSQL 18 performance internals post covers the optimizer and indexing changes in depth, optimizing SQLs in PostgreSQL 18.4 applies them to twelve query patterns, and PostgreSQL 18 performance tuning and the PostgreSQL 18 performance configuration matrix set the parameters around them.

Before any of these are relied on in production, the extension side needs the same care: the safely upgrading PostgreSQL extensions post covers the index-bearing extensions (PostGIS, pg_trgm, btree_gist) whose operator classes must match after a major upgrade, or check 1 fails on every query that used them.

PostgreSQL index work in an operated estate

On a single database the six checks are a tuning exercise. Across an estate they are an operating discipline, and three of the archive posts are about that layer. The pgBadger for continuous PostgreSQL monitoring post turns the slow-query log into a weekly report where new sequential scans on large tables stand out.

The PostgreSQL tracing and monitoring with minimal impact post sets the sampling and logging thresholds so that the observability itself does not become the write load that bloats the indexes. The CloudNativePG on Kubernetes post covers the operator-managed cluster where a rebuild has to be scheduled around pod restarts and volume headroom.

Security sits in the same archive for a reason: the key management in PostgreSQL encryption and PostgreSQL threat modelling for FinTech posts both touch the fact that an index on an encrypted column indexes ciphertext, so range and prefix predicates stop working the day column-level encryption is switched on, and the design has to decide in advance which lookups remain indexable.

Two standing rules apply to everything above. Test every index change on a copy with production statistics before applying it to production, because the planner’s decision on the copy is the only preview there is. And keep a robust DR posture around rebuilds: a REINDEX CONCURRENTLY that fails leaves an invalid index, a DROP INDEX that removed the wrong one needs the saved definition to restore it, and both are far cheaper to recover from with a tested backup than without.

Where this PostgreSQL index archive sits

This is one of the PostgreSQL archives on minervadb.com. The PostgreSQL archive is the broad collection, the PostgreSQL performance archive holds the tuning work that indexing is one part of, the PostgreSQL internals archive goes underneath to page layout, MVCC and the buffer manager, and the PostgreSQL statistics archive is check 2 on its own. The authoritative reference for access methods and operator classes is the PostgreSQL documentation chapter on indexes.

For an index review across an estate, a plan-stability incident, or a rebuild programme on tables too large to take a maintenance window, the MinervaDB PostgreSQL consulting practice runs this checklist against real workloads, with the catalog evidence for every recommendation and the rollback path written down before the first change.