How deeply does PostgreSQL Mistakes and How to Avoid Them cover index scan?

Explains predicate matching, table reads, and plan inspection, but does not develop broader PostgreSQL planner internals.

Index scan

As explained in PostgreSQL Mistakes and How to Avoid Them

An index scan is a query execution method that retrieves matching rows by traversing a database index.

An index scan retrieves matching rows by traversing a PostgreSQL index. A direct predicate on an indexed column can be fast, while applying a function or incompatible cast to that column can force a sequential scan instead. section 2.7

A timestamp predicate that changes the plan

For indexed payment timestamps, `WHERE tstamp = '2023-10-18 03:40:34.000'` produces an `Index Scan`. Rewriting the predicate as `date_trunc('s', tstamp) = '2023-10-18 03:40:34'` produces a `Seq Scan`, because the expression no longer matches the ordinary index. Move the transformation to the comparison value and use a range instead: `tstamp >= '2023-10-18 03:40:34'::timestamptz AND tstamp < '2023-10-18 03:40:34'::timestamptz + INTERVAL '1 s'`.

Use index scans deliberately:

Select only required columns: selecting indexed columns alone may enable an Index-Only Scan, while additional columns require table reads through an Index Scan. section 2.9

How PostgreSQL Mistakes and How to Avoid Them covers it

Cover of PostgreSQL Mistakes and How to Avoid Them

liveBook manning.com

Read PostgreSQL Mistakes and How to Avoid Them on liveBook Get the book at manning.com

Also explained in

liveBook manning.com

Just Use Postgres!An index scan is a database access strategy that retrieves matching table rows through an index rather than examining the entire table.
liveBook manning.com

MongoDB 8.0 in Action, Third EditionAn index scan is query execution that traverses index entries to locate documents matching query conditions.

Compare how 3 books explain index scan →

Related concepts

sequential scancontrasts with · A sequential scan is a search or query operation that examines items in a table or collection in sequence to identify those matching a condition, rather than locating them through an index.
indexinguses · Indexing is the organization or use of positions, keys, or lookup structures to identify and retrieve selected data or elements.
composite indexused by · A composite index is a database index whose key contains multiple fields or columns, ordered according to the index definition.
index fragmentationcontrasts with · Index fragmentation is the condition in which the logical ordering of index pages differs from their physical storage ordering or pages contain inefficiently distributed free space.
index-only scancontrasts with · An index-only scan is a database access technique that answers a query from index contents without fetching corresponding table rows when visibility permits.

This explainer was generated by Manning's knowledge graph from the text of PostgreSQL Mistakes and How to Avoid Them. The section links point to the parts of the book it draws on.