IndexScan uses an index to determine which rows satisfy a search condition. Index scans help reduce the set of qualifying rows before accessing the table. Index scans also return rows in sorted order.

IndexScan appears in the short plan as correlation-name<index-name>, where correlation-name is the correlation name specified in the FROM clause, or the table name if none was specified, and index-name is the name of the index.

Indexes provide an efficient mechanism for reading a few rows from a large table. However, an index scan can be more expensive than a sequential scan when reading many rows from a table. Index scans cause pages to be read from the database in random order, which is more expensive than sequential reads. Index scans may also reference the same table page multiple times if there are several rows on the page that satisfy the search condition. If only a few pages are matched by the index scan, it is likely that the pages will remain in cache, and multiple access does not lead to extra I/O. However, if many pages are matched by the search condition, they may not all fit in cache. This can lead to the index scan reading the same page from disk multiple times.

The optimizer uses an index scan to satisfy a search condition if the search condition is sargable, and if the optimizer's estimate of the selectivity of the search condition is sufficiently low for the index scan to be cheaper than a sequential table scan.

An index scan can also evaluate non-sargable search conditions after rows are fetched from the index. Evaluating conditions in the index scan is slightly more efficient than evaluating them in a filter after the index scan.

Even if there are no search conditions to satisfy, indexes can also be used to satisfy an ordering requirement, either explicitly defined in an ORDER BY clause, or implicitly needed for a GROUP BY or DISTINCT clause. Ordered group-by and ordered distinct methods can return initial rows faster than hash-based grouping and distinct, but they may be slower at returning the entire result set.

The optimizer tends to prefer index scans over sequential table scans if the optimization_goal setting is first-row. This is because indexes tend to return the first few rows of a query faster than table scans.

When writing a query, you can specify index hints to tell the optimizer which indexes to use and how to use them. However, index hints override the query optimizer's decision making logic, and so should be used only by experienced users. Using index hints may lead to suboptimal access plans and poor performance. See FROM clause.