Composite and Covering Index

A composite index uses more than one key column. An index on (customer_id, ordered_at) orders customers first and dates within each customer. Customer equality plus a date range identifies a bounded section. A date condition alone spans many customer groups; a scan, another index, or an engine-specific skip scan may be preferable.

A covering index contains all values a particular query needs. Coverage is a relationship between the index and the query, not a property that makes the index cover every query. Adding amount can cover an amount query but increases index size and maintenance. PostgreSQL INCLUDE stores payload columns without making them search keys.

Having the requested values is not always enough to avoid table access. PostgreSQL must also establish whether a row version is visible. Its visibility map can let an index-only scan skip all-visible heap pages; otherwise heap fetches remain. Check the actual plan and Heap Fetches rather than interpreting the node name as a guarantee of zero table visits.

Reference: Official documentation. See the worked examples in Relational Database Internals.