A composite index leads with a range predicate, crippling the equality filter and forcing a wide index scan.
Codesql
-- Table: orders(id, status, created_at, ...), millions of rows
CREATE INDEX idx_orders_lookup
ON orders (created_at, status);
-- Hot query run thousands of times per minute:
SELECT id, total
FROM orders
WHERE status = 'PENDING'
AND created_at >= '2024-01-01';
-- EXPLAIN shows the index is used but scans
-- far more rows than the result set returns.
The index is 'used' yet the query still reads huge numbers of rows. What is the bug?