Wrapped In A Function

events holds 41 million rows. created_at is timestamp (no time zone) and carries a B-tree index. This query takes nine seconds:

SELECT count(*) FROM events
WHERE date_trunc('day', created_at) = DATE '2026-03-01';

 Aggregate  (cost=1017413.90..1017413.91 rows=1 width=8)
            (actual time=9021.334..9021.335 rows=1 loops=1)
   ->  Seq Scan on events  (cost=0.00..1016900.00 rows=205560 width=0)
                           (actual time=3.118..9009.772 rows=118442 loops=1)
         Filter: (date_trunc('day'::text, created_at) = '2026-03-01'::date)
         Rows Removed by Filter: 40881558
 Planning Time: 0.121 ms
 Execution Time: 9021.372 ms

Why did the planner ignore the index?

count(*) can never use an index, because visibility is recorded in the heap and every candidate row has to be checked there anyway.

The indexed column appears only inside date_trunc(...), so the B-tree has no condition it can be searched on. It stores created_at values, not truncated ones.

The estimate of 205,560 rows is nearly double the actual 118,442, and that stale-statistics error pushed the cost model past the point where an index scan wins.

118,442 rows is too large a share of the table for an index scan to pay off, so the planner correctly chose to read the heap sequentially.