Make WHERE DATE(created_at) = '2026-01-01' use the index on created_at
A report filters one day of rows, but the query runs a full Seq Scan even though created_at is indexed. The predicate wraps the column in a function.
Constraint: return the same rows (all of 2026-01-01) but let the B-tree index on created_at be used. Assume created_at is a timestamp.
SELECT id, user_id, amount
FROM orders
WHERE DATE(created_at) = '2026-01-01';Rewrite the predicate so it is sargable.
Wrapping the column in DATE() makes the predicate non-sargable — the function runs per row, so the index on created_at is unusable and the planner does a Seq Scan. Rewrite it as a bare-column half-open range on created_at >= '2026-01-01' AND created_at < '2026-01-02'.
- ✗Casting the constant instead of unwrapping the column
- ✗Blaming the SELECT list rather than the non-sargable predicate
- ✗Forcing enable_seqscan off instead of making the predicate sargable
- →When is a functional index on DATE(created_at) the better fix?
- →Why is a half-open range safer than casting to date for a timestamp?
The DATE(created_at) call is evaluated for every row, so the planner cannot reduce the predicate to an index range and falls back to a Seq Scan. Take the function off the column — express it as a half-open range on the bare created_at:
SELECT id, user_id, amount
FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2026-01-02';Now the predicate is sargable: the B-tree on created_at yields an Index/Range Scan over just that day. The half-open range (< next day) correctly covers a timestamp with any time-of-day, unlike the equality = '2026-01-01'. An alternative is an expression index CREATE INDEX ON orders (DATE(created_at)), but the bare-column range is simpler and needs no extra index.
