How Indexes Work
An index is a secondary structure that lets PostgreSQL find rows without reading every row in a table.
Without one, answering WHERE email = 'ada@example.com' means scanning the entire table and checking each row, which is fine for a thousand rows and painful for a hundred million.
This page builds the mental model behind indexing before you reach for a specific access method like B-tree, GIN, GiST, or BRIN in the other pages in this section.
Getting this model right matters because indexes are not free, and the real skill is knowing when the read speedup is worth the write cost.
Summary
- Core Idea: An index trades write-time cost and storage for read-time speed by maintaining a separate, searchable structure alongside the table.
- Why It Matters: A missing index turns a millisecond lookup into a full table scan, and a redundant index silently taxes every write forever.
- Key Concepts: access method, selectivity, planner cost estimate, visibility map, write amplification, index bloat.
- When to Use: Add an index when a query filters, joins, or sorts on a column and the table is large enough for a scan to be expensive.
- Limitations / Trade-offs: Every index slows down inserts and updates, consumes disk space, and needs maintenance like
REINDEXor autovacuum attention over time. - Related Topics: B-tree indexes, GIN and GiST, BRIN, index-only scans, partial and covering indexes.
Foundations
The default and most common index type in PostgreSQL is the B-tree, a balanced tree structure that keeps values in sorted order.
A B-tree answers equality lookups, range queries, and sorted retrieval efficiently, which covers the large majority of everyday WHERE and ORDER BY clauses.
PostgreSQL also ships other access methods built for shapes of data a B-tree handles poorly: GIN for values containing many elements like arrays and full-text search, GiST for geometric and range overlap queries, and BRIN for very large tables where physical row order already correlates with a column's value.
Think of an index the way you'd think of the index at the back of a textbook: it does not contain the content itself, it contains sorted pointers back to the pages where that content lives.
That pointer, in PostgreSQL's case, is a tuple identifier (TID), a physical address of a row version on disk.
A simple B-tree index on an email column looks like this in practice.
CREATE INDEX customers_email_idx ON app.customers (email);After this statement, PostgreSQL maintains a sorted structure of email values alongside the table, and a query filtering on email can jump almost directly to the matching rows instead of reading the whole table.
Selectivity is the concept that determines whether that index actually helps: a query that matches one row out of a million is highly selective and benefits enormously from an index, while a query matching half the table gains little because most of the table would be touched either way.
Mechanics & Interactions
When PostgreSQL receives a query, the planner does not simply use an index because one technically applies to the predicate.
It estimates the cost of every viable plan, including a sequential scan, an index scan, and a bitmap index scan, using table and column statistics gathered by ANALYZE.
For a low-selectivity predicate, a sequential scan can actually be cheaper than an index scan, because random-access lookups through an index cost more per row than the index saves by skipping non-matching rows.
This is why forcing index usage is rarely the right instinct, and why stale statistics after a bulk load are a common cause of suddenly bad plans.
A bitmap index scan is the planner's middle ground: it uses the index to build an in-memory bitmap of matching row locations, sorts that bitmap by physical page, then reads the table in page order rather than jumping around randomly.
Composite, multicolumn indexes add another layer of nuance, since column order inside the index determines which query shapes can use it.
An index on (account_id, created_at) serves queries filtering on account_id alone or on both columns together, but it generally cannot serve a query filtering on created_at alone, because the leading column controls the sort order the index actually stores.
Every index also has a write-side cost that is easy to underweight.
INSERT INTO app.customers (email) VALUES ('new@example.com');
-- also updates customers_email_idx, plus the primary key indexEach insert, update, or delete must maintain every index on that table, not just the primary key, so a table with eight indexes pays that cost eight times over on every write.
PostgreSQL's Heap-Only Tuple (HOT) optimization can avoid updating indexes at all when an update changes only columns no index covers and the new row version fits on the same page, which is why keeping frequently updated columns out of unnecessary indexes has a real, measurable benefit.
The visibility map, a compact per-table structure tracking which pages contain only rows visible to all transactions, is what allows an index-only scan to answer a query directly from the index without touching the table at all, as long as every column the query needs is present in the index.
Advanced Considerations & Applications
At scale, index strategy becomes as much about subtraction as addition.
pg_stat_user_indexes exposes idx_scan, a running count of how many times each index has actually been used, and an index sitting at zero scans over weeks of production traffic is a strong candidate for removal.
CREATE INDEX CONCURRENTLY exists specifically because a normal index build takes a lock that blocks writes for its duration, and on a large, busy table that lock can mean real downtime.
The concurrent variant builds the index in the background at the cost of a slower build and the possibility of an INVALID index if the build is interrupted, which then needs to be dropped and retried.
Choosing between access methods is fundamentally a question of what your predicates look like, not a matter of one type being generally better than another.
| Access Method | Strength | Weakness | Best Fit |
|---|---|---|---|
| B-tree | Equality, range, and sorted retrieval; the safe default | No help for containment or overlap predicates | Most OLTP filtering, joins, and ORDER BY clauses |
| GIN | Fast lookups inside composite values like arrays, jsonb, and full-text vectors | Slower, larger writes than B-tree | Full-text search, array containment, jsonb key lookups |
| GiST | Supports nearest-neighbor and overlap predicates via a flexible tree | Lookups are approximate-then-refine, generally slower than B-tree for simple equality | Geometric data, range overlap, PostGIS spatial queries |
| BRIN | Tiny index size relative to table size | Only effective when physical row order correlates with the indexed value | Append-only, time-ordered tables in the tens of millions of rows or more |
Index-only scans deserve special attention as a scaling technique, since adding non-key columns with INCLUDE can let a query be answered entirely from the index when the visibility map is up to date, cutting out a table lookup per row.
Partial indexes push selectivity further by indexing only the subset of rows a workload actually queries, such as indexing only WHERE deleted_at IS NULL, which keeps the index small and fast even as the full table grows with historical rows that queries rarely touch.
Observability around indexes is worth building into routine operations, since tracking index size growth, bloat ratio, and scan counts over time catches both missing indexes, visible as slow queries, and redundant ones, visible as write overhead with no matching read benefit.
Common Misconceptions
- "More indexes always make queries faster." Every additional index adds write-time cost and storage, and an index the planner never chooses to use is pure overhead with no offsetting benefit.
- "The planner will always use an index if one exists on the filtered column." The planner compares estimated costs across all viable plans, and a sequential scan genuinely wins for low-selectivity predicates or small tables.
- "A composite index on (a, b) also serves queries filtering only on b." The leading column defines the index's sort order, so a query that skips the leading column generally cannot use that index efficiently.
- "Index-only scans always avoid touching the table." They only avoid it when the visibility map confirms the relevant pages have no rows invisible to the current transaction, which requires regular vacuuming to stay true.
- "Rebuilding an index always requires downtime."
REINDEX CONCURRENTLYandCREATE INDEX CONCURRENTLYexist precisely to avoid the exclusive lock a plain rebuild would take.
FAQs
Why doesn't PostgreSQL use my index even though it exists on the column I'm filtering?
- The planner estimates that a sequential or bitmap scan is cheaper for this specific query and data distribution.
- Stale statistics after a bulk load can cause the planner to misjudge selectivity.
- Running
ANALYZEon the table often resolves plans that look wrong after large data changes.
What is the actual cost of adding an index?
Every insert, update, or delete that touches an indexed column must also update that index, so each additional index adds incremental write latency and disk usage that scales with table size.
What is a bitmap index scan and why does the planner use it?
A bitmap index scan builds an in-memory map of matching row locations from the index, then reads the table in physical page order, which is often cheaper than a plain index scan when a moderate number of rows match.
Does column order matter in a multicolumn index?
Yes, the leading column determines the index's sort order, so a query filtering only on a non-leading column generally cannot use the index the way a query filtering on the leading column can.
How do I find indexes that are safe to drop?
- Query
pg_stat_user_indexesand look at theidx_scancolumn. - An index sitting at zero or near-zero scans over a representative time window is rarely earning its write-time cost.
- Confirm the observation window covers periodic jobs, like month-end reports, before dropping anything.
What is an index-only scan and why is it faster?
An index-only scan answers a query using only the index, skipping the table lookup entirely, which is possible when every requested column is present in the index and the visibility map confirms the relevant pages are fully visible.
Should I add an index for every column I filter on?
Not automatically, since a column with low selectivity, like a boolean flag, rarely benefits enough from an index to offset its write cost, and multicolumn or partial indexes often outperform a pile of single-column indexes.
Why would I use GIN or GiST instead of a B-tree?
- A B-tree cannot efficiently answer "does this array contain this value" or "do these two ranges overlap."
- GIN is built for containment lookups inside composite values like arrays, jsonb, and full-text search vectors.
- GiST generalizes to nearest-neighbor and overlap queries, which is why PostGIS relies on it for spatial data.
What does BRIN trade away to get such a small index size?
BRIN stores summary ranges per block instead of a full sorted structure, so it is dramatically smaller than a B-tree but only useful when a table's physical row order already correlates with the indexed column, such as an append-only table ordered by insert time.
Why does CREATE INDEX sometimes block writes for a long time?
A plain CREATE INDEX takes a lock that blocks concurrent writes to the table for the duration of the build, which is why CREATE INDEX CONCURRENTLY exists as a slower but non-blocking alternative for production tables.
What happens if a CONCURRENTLY index build fails partway through?
The index is left in an INVALID state, which means it consumes space and write overhead but is never used by the planner, so it needs to be dropped and rebuilt.
Is a unique index the same thing as a unique constraint?
A unique constraint is backed by a unique index under the hood, so functionally they enforce the same rule, though a constraint also appears explicitly in the table's constraint catalog for documentation and tooling purposes.
How does the visibility map relate to indexing at all?
The visibility map tracks which table pages contain only rows visible to every transaction, and it is what allows an index-only scan to skip the table entirely, so a table that is rarely vacuumed loses much of the benefit of index-only scans.
Related
- Indexes Basics - hands-on examples of creating and inspecting indexes
- B-tree Indexes - equality, range, and multicolumn ordering rules in depth
- GIN & GiST - access methods for containment and overlap predicates
- BRIN - block range indexes for very large, naturally ordered tables
- Index-Only Scans - how the visibility map enables table-free reads
- Partial & Covering Indexes - shrinking indexes to the rows that matter
Stack versions: This page was written for PostgreSQL 18.4 (stable major 18, maintenance line 17 also supported).