indexonly

Indexonly refers to a query execution path in which the database can satisfy a query using only the index data, without reading the full table rows from the disk. This is commonly described as an index-only scan or as using a covering index, where the index contains all columns required by the query.

How it works: For an index-only path to be possible, the index must include every column that the query needs in its output or in its predicates. In addition, the database checks whether the heap tuples are visible for the current transaction, often via a visibility map or similar mechanism. If the index can provide all requested data and the visibility information confirms that the corresponding rows are visible, the engine can avoid accessing the table itself, reducing I/O and latency. If some required data is not present in the index or the visibility cannot be established, the planner may still perform heap fetches to obtain the missing information.

Benefits and use cases: The primary advantage is reduced I/O and faster query execution, especially for read-heavy workloads and large datasets. Index-only scans are particularly effective for queries that retrieve a subset of columns and have selective predicates that align with an existing index.

Limitations and trade-offs: Maintaining covering indexes adds storage and write overhead, since the index must stay in sync with table changes. Not all queries can be served index-only; if the index lacks needed columns or if visibility cannot be determined, heap access is required. The effectiveness depends on database implementation details, such as the availability of a visibility map or similar mechanism.

See also: index scan, covering index, visibility map, PostgreSQL index-only scan.