The Data Structure That Runs the World
Rudolf Bayer and Edward McCreight invented the B-tree in 1972 at Boeing's research labs. By 1979 it had become the default index structure for every relational database, and it has remained so for fifty years. Understanding why the B-tree won tells you everything about how database indexes work and how to use them effectively.
A B-tree organizes keys in a balanced tree where each node contains multiple keys and pointers, sized to match exactly one disk page, typically 4-8KB. To find a value among a billion rows, you traverse 3-4 levels of the tree, reading 3-4 disk pages. Without the index, the database reads every page in the table, potentially millions. The B-tree reduces a million-page scan to a 4-page traversal.
Every PRIMARY KEY, every UNIQUE constraint, and every CREATE INDEX statement creates a B-tree index. When you define a primary key, you are not just declaring a uniqueness rule. You are telling the database to build a sorted, disk-optimized data structure that maps key values to row locations.
How the Query Optimizer Uses Indexes
SQL is declarative. You describe what you want. The query optimizer decides how to get it. Patricia Selinger's 1979 paper on access path selection in IBM's System R described the first systematic optimizer, and modern query planners are direct descendants of her work.
The optimizer generates multiple execution plans for each query: index scan on column A, index scan on column B, sequential table scan, nested loop join, hash join, merge join. It estimates the cost of each plan using table statistics: row counts, value distributions, index cardinality. It picks the cheapest plan. The statistics are crucial. If they are stale, the optimizer makes wrong decisions. ANALYZE (PostgreSQL) or UPDATE STATISTICS (SQL Server) refresh these statistics.
EXPLAIN ANALYZE is the tool that shows you what the optimizer actually chose. It reveals which indexes were used, how many rows were scanned at each step, and where query time was spent. A developer who can read an EXPLAIN plan can optimize any query. A developer who cannot is guessing. Learning to read EXPLAIN plans is not optional for anyone writing production SQL.
Composite Indexes and the Leftmost Prefix Rule
A composite index on (last_name, first_name) is a B-tree sorted first by last name, then by first name within each last name. Queries filtering on last_name can use this index efficiently. Queries filtering on first_name alone cannot, because first_name is not the outer sort key. This is the leftmost prefix rule: a composite index can be used for any prefix of its columns from left to right.
Index design requires knowing your query patterns before choosing column order. If the dominant query is 'get all posts by user in reverse chronological order', the composite index should be (user_id, created_at DESC). This serves both conditions and eliminates a sort. Getting column order wrong means an index that exists but cannot be used for your actual queries.
Covering Indexes: Eliminating Table Access
A covering index includes all columns that a specific query needs. The database can answer the query entirely from the index without touching the actual table rows. An index on (user_id, created_at, title) covers a query that selects title for a user's posts ordered by date. No table access needed. This can be 10-100x faster for read-heavy queries.
The trade-off is write overhead. Every index slows down INSERT and UPDATE operations because the index must be updated alongside the table. A table with 20 indexes is 20x slower to write than an unindexed table. The practical rule: index your most common and most expensive read queries, accept the write overhead as the price of read performance.
- Always index foreign key columns. Missing FK indexes cause full table scans on every JOIN, which is the top cause of slow JOIN performance.
- Use EXPLAIN ANALYZE before and after adding indexes. Trust the execution plan, not your intuition.
- Composite index column order follows your query patterns. The most selective filter usually goes first.
- Covering indexes eliminate table access for specific query patterns. Worth the write overhead for hot read paths.
- Do not create indexes speculatively. Add them when you have a specific slow query to fix, then measure the improvement.
How to Read an EXPLAIN ANALYZE Output
EXPLAIN ANALYZE executes the query and shows the actual execution plan. Every node in the output represents one step in the plan. Each node shows: the operation type (Seq Scan, Index Scan, Hash Join, Sort), the estimated startup cost and total cost (optimizer estimates), the actual time in milliseconds, the estimated row count, and the actual row count. The gap between estimated and actual row counts reveals stale statistics. A Seq Scan on a large table is not always wrong: for small tables or very high-selectivity conditions, the optimizer correctly chooses a sequential scan over an index.
Key patterns to recognize: an Index Scan is better than a Seq Scan for low-selectivity queries (WHERE user_id = 12345 hits 0.001% of rows). A Seq Scan is correct for high-selectivity queries (WHERE status != 'active' hits 99% of rows, indexing is not helpful). A Hash Join is efficient for joining large tables with no useful index on the join key. A Nested Loop Join with an Index Scan on the inner side is efficient for joining a small table to a large indexed table. If you see a Sort node with high cost, a composite index covering both the filter and the ORDER BY column could eliminate it.
Index Maintenance and the Write Trade-Off
Every index is updated on every INSERT, UPDATE to an indexed column, and DELETE. A table with 10 indexes requires 10 B-tree updates per write operation in addition to the table write itself. For write-heavy workloads like event logging, sensor data ingestion, or financial transaction recording, excessive indexes can make write throughput the bottleneck rather than read performance. The practical approach: start with the minimum indexes required for correctness (primary key, foreign keys), add indexes based on measured slow queries, and review index usage periodically.
PostgreSQL provides pg_stat_user_indexes to show index usage: how many sequential scans used the index, how many index-only scans, and the size. Indexes with zero scans in production are candidates for removal. Indexes with very few scans relative to their size are candidates for scrutiny. Unused indexes impose write overhead with zero read benefit. Regular index audits are part of responsible schema maintenance.
Index Types Beyond B-Tree
- GIN (Generalized Inverted Index): for full-text search (tsvector columns), array containment queries, and JSONB attribute existence queries.
- GiST (Generalized Search Tree): for geometric data (PostGIS), nearest-neighbor queries, and range types.
- BRIN (Block Range Index): for very large tables with natural physical correlation between values and storage order. Minimal size, fast for range scans on naturally ordered data.
- Hash index: O(1) equality lookups. Useful only for equality conditions, cannot support range queries or ordering.
- Partial index: indexes a subset of rows. Reduces index size and maintenance overhead for queries targeting a subset of the table.
How to Read an EXPLAIN ANALYZE Output
EXPLAIN ANALYZE executes the query and shows the actual execution plan. Every node in the output represents one step in the plan. Each node shows: the operation type (Seq Scan, Index Scan, Hash Join, Sort), the estimated startup cost and total cost (optimizer estimates), the actual time in milliseconds, the estimated row count, and the actual row count. The gap between estimated and actual row counts reveals stale statistics. A Seq Scan on a large table is not always wrong: for small tables or very high-selectivity conditions, the optimizer correctly chooses a sequential scan over an index.
Key patterns to recognize: an Index Scan is better than a Seq Scan for low-selectivity queries (WHERE user_id = 12345 hits 0.001% of rows). A Seq Scan is correct for high-selectivity queries (WHERE status != 'active' hits 99% of rows, indexing is not helpful). A Hash Join is efficient for joining large tables with no useful index on the join key. A Nested Loop Join with an Index Scan on the inner side is efficient for joining a small table to a large indexed table. If you see a Sort node with high cost, a composite index covering both the filter and the ORDER BY column could eliminate it.
Index Maintenance and the Write Trade-Off
Every index is updated on every INSERT, UPDATE to an indexed column, and DELETE. A table with 10 indexes requires 10 B-tree updates per write operation in addition to the table write itself. For write-heavy workloads like event logging, sensor data ingestion, or financial transaction recording, excessive indexes can make write throughput the bottleneck rather than read performance. The practical approach: start with the minimum indexes required for correctness (primary key, foreign keys), add indexes based on measured slow queries, and review index usage periodically.
PostgreSQL provides pg_stat_user_indexes to show index usage: how many sequential scans used the index, how many index-only scans, and the size. Indexes with zero scans in production are candidates for removal. Indexes with very few scans relative to their size are candidates for scrutiny. Unused indexes impose write overhead with zero read benefit. Regular index audits are part of responsible schema maintenance.
Index Types Beyond B-Tree
- GIN (Generalized Inverted Index): for full-text search (tsvector columns), array containment queries, and JSONB attribute existence queries.
- GiST (Generalized Search Tree): for geometric data (PostGIS), nearest-neighbor queries, and range types.
- BRIN (Block Range Index): for very large tables with natural physical correlation between values and storage order. Minimal size, fast for range scans on naturally ordered data.
- Hash index: O(1) equality lookups. Useful only for equality conditions, cannot support range queries or ordering.
- Partial index: indexes a subset of rows. Reduces index size and maintenance overhead for queries targeting a subset of the table.
How to Read an EXPLAIN ANALYZE Output
EXPLAIN ANALYZE executes the query and shows the actual execution plan. Every node in the output represents one step in the plan. Each node shows: the operation type (Seq Scan, Index Scan, Hash Join, Sort), the estimated startup cost and total cost (optimizer estimates), the actual time in milliseconds, the estimated row count, and the actual row count. The gap between estimated and actual row counts reveals stale statistics. A Seq Scan on a large table is not always wrong: for small tables or very high-selectivity conditions, the optimizer correctly chooses a sequential scan over an index.
Key patterns to recognize: an Index Scan is better than a Seq Scan for low-selectivity queries (WHERE user_id = 12345 hits 0.001% of rows). A Seq Scan is correct for high-selectivity queries (WHERE status != 'active' hits 99% of rows, indexing is not helpful). A Hash Join is efficient for joining large tables with no useful index on the join key. A Nested Loop Join with an Index Scan on the inner side is efficient for joining a small table to a large indexed table. If you see a Sort node with high cost, a composite index covering both the filter and the ORDER BY column could eliminate it.
Index Maintenance and the Write Trade-Off
Every index is updated on every INSERT, UPDATE to an indexed column, and DELETE. A table with 10 indexes requires 10 B-tree updates per write operation in addition to the table write itself. For write-heavy workloads like event logging, sensor data ingestion, or financial transaction recording, excessive indexes can make write throughput the bottleneck rather than read performance. The practical approach: start with the minimum indexes required for correctness (primary key, foreign keys), add indexes based on measured slow queries, and review index usage periodically.
PostgreSQL provides pg_stat_user_indexes to show index usage: how many sequential scans used the index, how many index-only scans, and the size. Indexes with zero scans in production are candidates for removal. Indexes with very few scans relative to their size are candidates for scrutiny. Unused indexes impose write overhead with zero read benefit. Regular index audits are part of responsible schema maintenance.
Index Types Beyond B-Tree
- GIN (Generalized Inverted Index): for full-text search (tsvector columns), array containment queries, and JSONB attribute existence queries.
- GiST (Generalized Search Tree): for geometric data (PostGIS), nearest-neighbor queries, and range types.
- BRIN (Block Range Index): for very large tables with natural physical correlation between values and storage order. Minimal size, fast for range scans on naturally ordered data.
- Hash index: O(1) equality lookups. Useful only for equality conditions, cannot support range queries or ordering.
- Partial index: indexes a subset of rows. Reduces index size and maintenance overhead for queries targeting a subset of the table.