44 When to Index

Indexes help when you filter, sort, or join on the indexed column(s); they cost disk space and slow down INSERT/UPDATE/DELETE; and on small tables, the planner may sequential-scan anyway. So the question is not “should this column have an index?” — it is “does this query need an index, and which one would help?” The answer comes from EXPLAIN and pg_stat_user_indexes.

What you’ll learn

• The fundamental trade-off: index = faster reads, slower writes, more disk
• Which queries benefit (equality, range, join, ORDER BY)
• Why low-cardinality columns rarely benefit from a plain B-tree
• Detecting which indexes are unused with pg_stat_user_indexes
• The role of ANALYZE in keeping statistics current

Example

-- Compare plans: without an index vs. with one
DROP INDEX IF EXISTS fruits_name_idx;
EXPLAIN (FORMAT TEXT) SELECT * FROM fruits WHERE name = 'apricot';

CREATE INDEX fruits_name_idx ON fruits (name);
ANALYZE fruits;   -- refresh planner statistics

EXPLAIN (FORMAT TEXT) SELECT * FROM fruits WHERE name = 'apricot';

-- Range queries also benefit
EXPLAIN (FORMAT TEXT) SELECT * FROM fruits WHERE id BETWEEN 2 AND 4;

-- Index usage stats — which indexes is the database actually using?
SELECT relname AS table_name,
       indexrelname AS index_name,
       idx_scan,
       idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan DESC
LIMIT 10;

A B-tree index helps when:

• Equality lookups on the indexed column (WHERE x = ?).
• Range scans (WHERE x BETWEEN ? AND ?, WHERE x < ?).
• Joins on the indexed column (JOIN on parent.id = child.parent_id). Always index foreign-key columns.
• ORDER BY matching the index’s column(s) and direction.
• Anchored prefix LIKE (LIKE 'abc%').

It does not help (or barely helps) when:

• The query scans most of the table anyway (there is no universal percentage threshold; row width, correlation, cache state, and costs matter).
• The column has very few distinct values (is_active boolean — index won’t help the common-case branch).
• The condition wraps the column in a function (WHERE lower(email) = ...) — unless you have an expression index.
• The table is tiny (sequential scan is faster than the index lookup overhead).

On small tables the planner often prefers Seq Scan even when an index exists — because reading the whole table from one or two pages is cheaper than the index lookup. That is a feature, not a bug.

ANALYZE table updates the statistics the planner uses to choose plans. Run it after large data changes; autovacuum runs it automatically based on row-churn thresholds, but a manual run is sometimes needed.

Run the example

To run (requires fruits):

$ psql -f source/when-to-index.sql postgres
DROP INDEX
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on fruits  (cost=0.00..1.10 rows=1 width=36)
   Filter: (name = 'apricot'::text)
(2 rows)

CREATE INDEX
ANALYZE
                      QUERY PLAN
-------------------------------------------------------
 Seq Scan on fruits  (cost=0.00..1.10 rows=1 width=12)
   Filter: (name = 'apricot'::text)
(2 rows)
 ...

Common pitfalls

• Adding indexes for queries you “might” run someday. Each one slows every write. Index based on real workloads.
• Indexes that are subsets of another. (a) is redundant if (a, b) exists for equality lookups on a. The same leading column also supports range scans. Check size, uniqueness, predicates, and workload before treating an index as redundant.
• Functions on indexed columns block index use: WHERE upper(name) = 'X' ignores an index on name — use an expression index or rewrite.
• Bloated indexes that grew over time: routine REINDEX CONCURRENTLY keeps them tight.

Downloads

SQL file: when-to-index.sql · All lesson files · Setup guide