KNOWLEDGE / PostgreSQL / Indexing
A Mental Model for PostgreSQL Indexes
A visual model for B-tree indexes, scans, selectivity, and why a planner may ignore an index.
PostgreSQLIndexingQuery Planning
- DOMAIN
- Databases
- LEVEL
- Foundational
- READ
- 8 min
- UPDATED
- Aug 24, 2026
MENTAL MODEL / KEY IDEAS
Keep these in mind
- 01An index is an ordered shortcut
- 02Selectivity changes the cost
- 03The planner compares alternatives
What an index stores
A B-tree stores ordered key values and pointers to table rows. The order lets PostgreSQL skip most values for equality and range predicates.
- Root and branch pages
- Leaf entries
- Heap visibility
Why it may not be used
If a query returns much of a table, random row access can cost more than reading the table sequentially.
- Low selectivity
- Stale statistics
- Expression mismatch
A practical workflow
Start from the actual query, inspect EXPLAIN ANALYZE, and create the smallest index that supports a meaningful access pattern.