A covering index stores every column a query needs, so PostgreSQL can return qualifying results without reading table rows. Add non-key columns with INCLUDE; these columns are stored but do not build or sort the index structure. section 4.7
Use covering indexes when a frequently executed query still performs table work:
- A player statistics query filters by region, score, and win count, orders by score and win count, and selects username. section 4.7
- Adding username with INCLUDE lets PostgreSQL replace a Bitmap Heap Scan with an Index Only Scan. section 4.7
- The trade-off is maintenance overhead: updates to included columns also update the index and can increase write latency. section 4.7
Covering the hot query
CREATE INDEX idx_composite_covering_index ON game.player_stats (region, score DESC, win_count DESC) INCLUDE (username); The resulting plan reports an Index Only Scan, Heap Fetches: 0, and execution time of 0.602 ms.
CREATE INDEX index_name ON table_name(columnA) INCLUDE(columnB, columnC [, additional_columns]);
Use the INCLUDE clause to store projected columns without using them as index keys. section 4.7
