Why is this query slow?

The cause: the predicate is not sargable. Wrapping the indexed column in DATE(...) means the planner is no longer comparing created_at to a constant — it is comparing a function of created_at. A B-tree index stores created_at values, not DATE(created_at) values, so it cannot be used to satisfy that predicate. The planner falls back to a sequential scan, and is correct to.

The fix: rewrite as a range over the raw column.

WHERE created_at >= '2026-09-01' AND created_at < '2026-09-02'

Now the comparison is against the indexed values directly and the index range scan applies. The half-open interval also avoids the BETWEEN bug of including exactly midnight on the second day.

The alternative is an expression index on DATE(created_at), which works but costs storage and write throughput and only serves queries written that exact way. Prefer the rewrite.

Other reasons an index goes unused, worth naming to show the general skill:

• Low selectivity. If the predicate matches a large fraction of rows, a sequential scan genuinely is cheaper than an index scan plus that many heap fetches. The planner is right and the index is the wrong fix.
• Type mismatch. Comparing a bigint column to a string constant can force a cast on the column, with the same sargability failure.
• Stale statistics. The planner estimates from ANALYZE data; badly out-of-date statistics produce badly wrong plans. ANALYZE first before concluding anything.
• Leading-column rule. An index on (a, b) does not serve a predicate on b alone.

How to confirm. EXPLAIN (ANALYZE, BUFFERS) — which gives estimated and actual rows, so a large divergence between them points straight at a statistics problem, and buffer counts show whether the work was cache or disk. EXPLAIN alone shows only the plan and estimates, and reasoning from estimates is exactly the mistake this question is testing for. Compare plans before and after, on the real data volume.