Why indexes matter#
Without an index, table scans are O(n) — queries degrade quickly as data grows. A good index brings lookups down to O(log n).
Read the plan first#
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;The plan tells you whether it’s a sequential scan or an index scan, guiding your index decisions.
Common index types#
- B-tree: the default; great for equality and range queries
- GIN: full-text search and array/JSON containment
- BRIN: large tables with physical ordering
Common pitfalls#
- Over-indexing: write amplification and storage cost
- Indexing low-selectivity columns: little payoff
- Ignoring composite index column order: the leftmost-prefix rule
Summary#
There is no silver bullet. Start from the execution plan and watch pg_stat_user_indexes to make sound decisions.