Database Indexing: What It Changes and How to Measure It
Understanding B-tree index mechanics in PostgreSQL, analyzing execution plans with EXPLAIN ANALYZE, and avoiding premature over-indexing traps.
The Cost of Sequential Scans
When a table grows past several hundred thousand rows, queries without appropriate index support degrade rapidly. Let us analyze what happens behind the scenes during query execution.
Analyzing Query Plans
PostgreSQL provides EXPLAIN (ANALYZE, BUFFERS) to inspect query planning and execution statistics:
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM posts
WHERE status = 'published'
AND published_at <= NOW()
ORDER BY published_at DESC
LIMIT 10;
Without a composite index on (status, published_at), the database engine must execute a Seq Scan across the entire table, loading blocks from disk into buffer memory and filtering row by row.
Constructing Composite Indexes
Compound indexes must follow query access patterns. For filtering by status and sorting by publication date:
CREATE INDEX idx_posts_status_published_at
ON posts (status, published_at DESC);
After adding this index, the query planner transitions to an Index Scan:
Limit (cost=0.28..12.45 rows=10 width=850) (actual time=0.035..0.048 rows=10 loops=1)
-> Index Scan using idx_posts_status_published_at on posts
Execution time drops from milliseconds to fractions of a millisecond.
When NOT to Index
Every index introduces overhead on INSERT, UPDATE, and DELETE operations. Keep indexes focused on high-cardinality filters and common sorting patterns.