indexscan

An indexscan, in database terminology, refers to the process of executing a query by traversing an index to locate the rows that satisfy a condition, rather than scanning all rows in a table. An index is a data structure that organizes keys and pointers to their corresponding rows, enabling faster lookup for specific values or ranges.

In a typical index-based scan, the database traverses the relevant index to find matching index entries and then follows pointers (row identifiers) to retrieve the corresponding table rows. Some databases can produce results without touching the table data if all required columns are present in the index, a scenario known as an index-only scan. Conversely, if the needed data is not in the index, the system will perform lookups to the table (heap or heap-like storage) to fetch the remaining columns.

There are several variants associated with index scans. A standard index scan uses a single index to locate matching rows. A bitmap index scan constructs bitmaps representing matches from one or more indexes and then applies bitwise operations to identify the final set of rows. Different index types (B-trees, GiST, hash, etc.) support different kinds of predicates and ordering, influencing efficiency.

The database query planner decides whether to use an indexscan based on statistics about data distribution, the selectivity of predicates, index availability, and estimated I/O costs. Index scans are generally advantageous for selective predicates, range queries, and when the index can provide the data in the requested order. They may be less beneficial for queries that touch a large portion of the table or require many non-indexed columns, where a full table scan could be cheaper.