Indexes, Query Plans and Optimisation
Indexes speed up reads only when the query predicate matches what the index stores and the planner estimates that using it is cheaper than a sequential scan. Read plans with EXPLAIN (ANALYZE, BUFFERS) and compare estimated rows with actual rows.
Key points
- 1
A B-tree serves equality, ranges, ORDER BY and prefix LIKE (with text_pattern_ops or the C collation) on its leading columns. Put equality columns first and range columns last in composite indexes.
- 2
Keep indexed columns bare in WHERE clauses: functions, casts, arithmetic and leading wildcards defeat the index unless you create a matching expression or trigram index.
- 3
The planner chooses by cost: for low-selectivity filters a Seq Scan beats an index, and stale statistics (estimated vs actual rows) produce bad plans. Fix with ANALYZE or extended statistics.
- 4
Covering indexes (INCLUDE) plus a well-vacuumed visibility map enable Index Only Scans. Partial indexes stay small and work when the query repeats their predicate.
- 5
Choose the index type for the workload: B-tree by default, GIN for JSONB, arrays and full-text, GiST for spatial and range types, BRIN for huge naturally ordered tables, hash for equality only.
- 6
Use keyset pagination instead of deep OFFSET, batch N+1 lookups, rewrite correlated subqueries as joins or aggregates, and remember MATERIALIZED CTEs block predicate pushdown.
- 7
Every index costs writes and storage. Build with CREATE INDEX CONCURRENTLY outside a transaction, check it is valid, and drop indexes pg_stat_user_indexes shows are unused or redundant.
Common traps
EXPLAIN ANALYZE executes the statement: profile INSERT, UPDATE and DELETE inside BEGIN ... ROLLBACK.
Foreign-key columns are not indexed automatically in PostgreSQL, so joins and cascading deletes scan the child table.
A failed CREATE INDEX CONCURRENTLY leaves an INVALID index that still slows writes until you drop it.
Test yourself on Indexes, Query Plans and Optimisation
Ten questions, with the answer and explanation after each one.