Preparing an access path
Without a suitable index, finding rows by a column may require inspecting the whole table. An index organizes searchable keys and row-location information so that execution can first narrow the candidates.
Indexes can assist filtering, joins, and some ordering operations. Supported conditions depend on the access method, keys, and operators, so indexing one column does not accelerate every query involving the table.
Column order affects a multicolumn B-tree’s scan volume. In an (a, b) index, equality on a can restrict a contiguous region; a b-only condition may use the index without pruning equally well. PostgreSQL 18 skip scan can exploit few distinct a values through repeated internal equality searches. Neither “missing the first column forbids index use” nor “index use reads only matches” is a universal rule; the actual plan matters.
The planner may still choose a scan
When a query retrieves much of a table, locating rows through an index and then fetching them can cost more than a sequential scan. A planner compares paths using estimated row counts and access costs.
PostgreSQL examples switch among sequential, bitmap, and ordinary index scans as selectivity changes. EXPLAIN reveals the chosen plan. An index’s existence and its actual use are separate facts that should be checked.
Read benefits have maintenance costs
Changes to indexed data require maintenance, adding storage and work to inserts, updates, and deletes. An unused index can impose overhead without supplying a compensating read benefit.
Planning also depends on statistics. Changed distributions can make older estimates misleading; PostgreSQL uses ANALYZE to refresh this information. Useful design follows real queries and data rather than treating the number of indexes as a performance score.