Databaseadvanced

Covering Indexes

Indexes that contain all columns needed by a query, allowing the database to satisfy the query entirely from the index without touching the heap table.

Also known as: index-only scans, INCLUDE indexes, included columns

Description

A covering index includes every column referenced in a query -- in the WHERE clause, SELECT list, JOIN conditions, and ORDER BY -- so the database engine can return results directly from the index pages without performing a heap lookup. In PostgreSQL, this manifests as an 'Index Only Scan' in the EXPLAIN output, which is significantly faster than an 'Index Scan' followed by heap fetches, especially on large tables with high I/O costs.

PostgreSQL 11+ supports the INCLUDE clause for indexes: CREATE INDEX idx ON orders (status, created_at) INCLUDE (total, customer_id). The INCLUDE columns are stored in the index leaf pages but are not part of the search key, meaning they do not affect index ordering or size of internal B-tree nodes. This is preferable to adding non-search columns as regular index columns, which would bloat the entire B-tree structure.

Covering indexes trade increased storage and write amplification for dramatically faster read performance. They are most valuable for frequently executed queries on read-heavy tables where heap access is the bottleneck. Monitor the visibility map via pg_stat_user_tables to ensure vacuum runs frequently enough -- Index Only Scans degrade when the visibility map is stale because the engine must check the heap to verify tuple visibility.

Prompt Snippet

For high-frequency read queries, create covering indexes using PostgreSQL's INCLUDE clause to enable Index Only Scans. Example: CREATE INDEX idx_orders_covering ON orders (tenant_id, status) INCLUDE (total_amount, created_at) for a dashboard query selecting those columns. Verify the plan shows 'Index Only Scan' via EXPLAIN (ANALYZE, BUFFERS) and monitor heap fetches -- if heap fetches are high, increase autovacuum_vacuum_scale_factor on the table to keep the visibility map current.