Database indexes

Index unique scan

It occurs when the db looks up a single unique row and is able to stop searching the very instant that row is found. No sequential reading.

Two strict requirements - A unique constraint exists (PK, Unique), An equality operator is used. 

Index range scan

 Accesses a continuous segment of pre-sorted index entries using leading columns.

The database locates the starting value in an index and reads consecutive leaf blocks sequentially until it hits the upper boundary of the range

Requires the query's WHERE clause to reference the leading (leftmost) column of a composite index

Best used for range operators (BETWEEN, <,>,>=,<=) or equality conditions (=) on primary prefix of the index.  (works well if the first column in the index where clause applied with equality check, and rest with ranges)

Highly efficient and selective when fetching small percentage of total table rows

Index skip scan

The database finds distinct values of the omitted leading column and performs a separate mini-range scan for each distinct prefix value.

Triggers on a composite index even when the leading column is missing from the WHERE clause

Best used for composite indexes where the leading column has low cardinality (very few distinct values like gender, status_code..etc) and subsequent columns are well-filtered.

Can prevent expensive full table scans, but can degrade performance significantly if the skipped leading column has high cardinality because it multiplies the number of internal lookups.

Index full scan

An index full scan is a DB operation where the system reads every single block of a B-tree index from start to finish in sorted key order.

When and shy it is used? Avoiding sorts : (1) The optimizer chooses an index full scan when a query has an ORDER BY or GROUP BY clause that matches the sorted columns in the index. (2) If all the columns requested by the query exist inside the index, the db can process the entire request without touching the main base table