When is full data set scanning more efficient than index access?

When the percentage of records that need to be processed is sufficiently high (for example, more than 10-20%) for the costs of index searching and reading individual data blocks to outweigh the benefits.

Other cases:

• Lack of a suitable index: If there is no index for the query that covers the filtering fields or provides faster access to data than a full scan.
• Uneven data distribution (skew): If the indexed field has low cardinality and most data is concentrated on a small number of values, scanning may be faster than traversing many index leaves with the same values.
• Small data set: For small tables, the costs of maintaining and using an index may outweigh the benefits of its use.
• Sequential reading: A full scan usually involves sequential disk reading, which can be faster than random access when using an index, especially on HDDs.
• Large amount of data in a row: If each row contains a large amount of data, reading the entire data set at once may be more efficient than multiple disk accesses to retrieve individual rows via an index.

Example where a full scan might be more efficient:

// Simple SELECT * on a table with a small number of rows.
SELECT *
FROM small_table;

// Query with a filter that affects most rows
SELECT *
FROM large_table
WHERE status = 'processed'; // If most rows have the status 'processed'