In this lesson, you will learn how to design queries that avoid touching the actual table storage entirely by leveraging "Index Only Scans."
The Cost of the "Full Table Scan"
When you run a standard query like SELECT * FROM Orders WHERE status = 'shipped', the database must perform a Full Table Scan if there is no index. It reads the entire table file from the disk into memory, row by row, to find matches. Even with a secondary index, if you request columns not included in that index, the database is forced to perform "RID Lookups" or "Bookmarking" to go back to the main table to fetch the remaining data. This is expensive and slow.
The Power of the Index Only Scan
An Index Only Scan occurs when every piece of information requested by your query is physically stored within the B-Tree index structure itself. Because the B-Tree nodes already contain the indexed columns, the database engine realizes it has no reason to visit the "heap" or the main table storage. It simply traverses the B-Tree to the leaf nodes and extracts the values directly from there.
Consider this table:
CREATE INDEX idx_orders_status_total ON Orders (status, total_amount);
If you execute the following query, the database can satisfy the request entirely from the idx_orders_status_total index:
SELECT status, total_amount
FROM Orders
WHERE status = 'shipped';
Because status (the filter) and total_amount (the projection) are both inside the index, the database performs an Index Only Scan. It skips the table file entirely, resulting in significantly lower I/O costs and faster response times.
When does this fail?
If you change your query to SELECT status, total_amount, customer_id FROM Orders WHERE status = 'shipped', the database can no longer perform an Index Only Scan. Since customer_id is not in our index, the engine must "look up" the row in the table to retrieve that extra column. Always ensure your indexes "cover" your most frequent queries to take full advantage of this performance optimization.
Key Takeaway: By including all columns referenced in your SELECT and WHERE clauses within your index, the database can retrieve data directly from the B-Tree leaf nodes, completely bypassing the expensive full table scan.