Indexes & Query Performance
Covering and Index-Only Scans
A covering index contains every value needed by a query, making an index-only path possible. Whether it avoids base storage depends on visibility and implementation: PostgreSQL may still visit heap pages unless its visibility map proves all tuples on a page visible.
- Coverage is query-relative.
- Key and included columns have different roles.
- Index-only is a physical execution property.
- PostgreSQL visibility lives outside index tuples.
- Payload width has a price.
- Frequently changing columns weaken the case.
CREATE INDEX orders_recent_lookup_idx
ON orders (customer_id, status, placed_at DESC)
INCLUDE (order_id);
EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, placed_at, status
FROM orders
WHERE customer_id = 7 AND status = 'PAID'
ORDER BY placed_at DESC
LIMIT 50;| Observation | Interpretation |
|---|---|
| Index Only Scan | Plan can obtain query values from index |
| Heap Fetches: 0 | Visibility checks avoided heap for this run |
| Heap Fetches > 0 | Coverage exists, but some heap visibility checks occurred |
| High buffers / wide index | Coverage may cost cache capacity |
| Frequent updates | Payload maintenance and all-visible churn may offset read gain |
Other engines may store visibility or clustered rows differently, so PostgreSQL’s visibility-map caveat is not vendor-universal. Conversely, the generic term “covering” must not be read as a promise of zero base-table work everywhere.