Index-only scan
An index-only scan is a database access technique that answers a query from index contents without fetching corresponding table rows when visibility permits.
Just Use Postgres!
An index-only scan satisfies a query from index contents without fetching the corresponding table rows when the required data is available in the index.
- How it is used
- Optimizes filtered counts and covered retrievals by completing queries from index records instead of table rows.
- How deep it goes
- Explains execution-plan evidence and covering-index construction, but defers broader edge cases and administration.
- Examples
- Uses score-range counts and a region-filtered player leaderboard to compare sequential, bitmap, and index-only plans.
- What you can do after
- Diagnose/debug with EXPLAIN ANALYZE by recognizing index-only plans and adding INCLUDE columns for covered queries.
PostgreSQL Mistakes and How to Avoid Them
An index-only scan answers a query from index contents without needing to fetch the corresponding table rows when visibility information permits.
- How it is used
- Optimizes selective PostgreSQL queries that can be answered from indexed data without reading corresponding table rows.
- How deep it goes
- Explains plan diagnosis, column-selection effects, partial indexes, and foreign-key indexing, but does not detail visibility-map internals.
- Examples
- Uses open-ticket counts, invoice counts by customer, EXPLAIN output, and Python comparisons between selecting identifiers and all columns.
- What you can do after
- Diagnose with EXPLAIN whether indexing changes a sequential scan into an index-only scan and choose a narrower partial index when appropriate.
Read how PostgreSQL Mistakes and How to Avoid Them explains index-only scan →
Related concepts
- index scancontrasts with · An index scan is a database query operation that traverses an index to locate or retrieve records matching query conditions.
- select starcontrasts with · SELECT * is SQL projection syntax that requests every column from the selected table, relation, or other query data source.
- select statementproduced by · A select statement is a language construct that retrieves selected data or chooses among alternative operations or cases according to the language's rules.
- indexingproduced by · Indexing is the organization or use of positions, keys, or lookup structures to identify and retrieve selected data or elements.
- covering indexuses · A covering index is an index containing all columns needed to evaluate and return a query, avoiding access to the base table.
These guides are generated from the text of Manning books by Manning's knowledge graph and link to the sections they draw on. The full books are available on liveBook and at manning.com.