PostgreSQL: query is slow even though the column is indexed

This query takes ~4 s on a 30M-row table even though created_at has a B-tree index:

SELECT * FROM events
WHERE date(created_at) = '2026-10-01';

EXPLAIN shows a sequential scan. Why is the index ignored?

Wrapping the column in a function (date(created_at)) means the index on created_at cannot be used — the planner would have to compute date() for every row. Rewrite it as a range on the raw column:

SELECT * FROM events
WHERE created_at >= '2026-10-01'
  AND created_at <  '2026-10-02';

Or, if you really query by date often, create an expression index:

CREATE INDEX ON events ((created_at::date));

Range rewrite: 4 s → 3 ms. Thank you.

Also watch the time zone: created_at::date on a timestamptz depends on the session TimeZone, so the expression index is not immutable and Postgres will refuse it.

Good point — for timestamptz use (created_at AT TIME ZONE 'UTC')::date.