When a database index helps and when it hurts

What it is

An index is a separate structure that lets the database find rows matching a condition without scanning the whole table. PostgreSQL offers B-tree indexes for equality and range conditions and ordering, plus GIN, GiST, BRIN and hash indexes for other data shapes such as arrays, JSON documents, full-text vectors and geometric types. Multicolumn indexes serve conditions on their leading columns; partial indexes cover a subset of rows; expression indexes cover computed values.

Why it matters

Every index must be updated on insert, update and delete, and occupies storage and cache. An index that no query uses is pure cost; a missing index on a large table turns a lookup into a full scan.

How to apply

• Start from the queries: for each slow one, run EXPLAIN (ANALYZE, BUFFERS) and look for sequential scans on large tables and for sort steps.
• Create the index that matches the predicate and ordering; check with EXPLAIN that the planner uses it.
• Use partial indexes for hot subsets (for example, only open items) and expression indexes for functions applied in the predicate.
• Periodically review unused indexes with the statistics views and drop them.

Pitfalls

The planner ignores an index when a function is applied to the column in the query but not in the index, or when the table is small enough that a scan is cheaper. Low-selectivity columns (booleans) rarely benefit alone. Indexes do not replace fixing an O(n²) query pattern in the application.