SQL Query Optimization: From Slow Scans to Index-Only Executions

1. The Anatomy of an Unintended Table Scan

When relational tables grow past hundreds of thousands of rows, queries that performed well in local development suddenly trigger full table scans. The most common culprit is filtering on multiple columns where only a single-column index exists, or applying SQL functions directly to indexed columns, which prevents the query planner from using the index tree efficiently.

-- Problematic Query: Triggers Full Table Scan due to DATE() function
SELECT id, status, total_amount 
FROM orders 
WHERE DATE(created_at) = '2026-04-12' AND status = 'completed';

-- Optimized Query: Range scan utilizing composite index (status, created_at)
SELECT id, status, total_amount 
FROM orders 
WHERE status = 'completed'
  AND created_at >= '2026-04-12 00:00:00' 
  AND created_at <= '2026-04-12 23:59:59';

2. Understanding Index Left-Prefix Matching

A composite B-Tree index on (tenant_id, status, created_at) can be visualized as an index sorted primarily by tenant_id, then by status, then by created_at. Queries filtering by tenant_id alone or (tenant_id, status) utilize the index effectively. However, querying by status alone cannot jump into the index tree and falls back to a scan.

3. Covering Indexes: Eliminating Heap Table Access

When all columns requested in the SELECT clause and WHERE clauses are present within the index leaf pages, the database engine executes an "Index-Only Scan". The engine never visits the actual heap table on disk, resulting in pure in-memory index traversal and sub-millisecond execution times.