A single-column index derives its key from one table column and lets PostgreSQL search a subset of indexed data instead of scanning every row. PostgreSQL uses a B-tree by default, while the USING clause selects another access method. section 4.10 section 4.2
Speeding up a leaderboard query
A game can index scores for range filters and descending leaderboards: CREATE INDEX idx_score ON game.player_stats(score DESC); SELECT username, score, level FROM game.player_stats ORDER BY score DESC LIMIT 5; Before indexing, filtering scores between 5,000 and 6,000 performed a sequential scan, examined 10,000 rows, and took 1.753 ms. Afterward, PostgreSQL used an Index Only Scan and took 0.350 ms; the leaderboard used an Index Scan. section 4.5.1
Choose a single-column index according to the operations the column must support:
- Use a B-tree for equality searches, ordering, and range predicates such as >, <, and BETWEEN. section 4.5.1
- Use a hash index when lookups use equality or IN and range queries are unnecessary. section 4.5.2
- Expect PostgreSQL to prefer a sequential scan when a predicate matches many rows; an index is not automatically faster. section 4.6.1
- Add an index only when workload and table size justify its maintenance cost; replace several insufficient single-column indexes with a composite index when queries combine columns. section 4.6.1 section 11.5.2
Equality lookup with a hash index
A game with only five transferable champion titles can optimize an equality lookup: CREATE INDEX idx_champion_title ON game.player_stats USING hash(champion_title); SELECT username, score, level FROM game.player_stats WHERE champion_title = 'Naval Warlord'; The query changed from a sequential scan that removed 9,999 rows to an Index Scan using idx_champion_title, reducing execution time from 1.463 ms to 0.073 ms. section 4.5.2
CREATE INDEX new_index_name ON tableA(columnA);
Use the compact CREATE INDEX pattern to adapt a single-column index to another table and column. section 4.2
