Postgres: why is my index not being used for this query?

Dev Overflow

Home

Community

Collections

Find Jobs

asked 2 months ago

527

I have an index on created_at but EXPLAIN still shows a sequential scan:

1CREATE INDEX idx_events_created ON events (created_at);
2
3EXPLAIN ANALYZE
4SELECT * FROM events WHERE DATE(created_at) = '2026-01-15';

1CREATE INDEX idx_events_created ON events (created_at);
2
3EXPLAIN ANALYZE
4SELECT * FROM events WHERE DATE(created_at) = '2026-01-15';

The table has about 4 million rows.

Priya Raut

• answered 2 months ago

Wrapping the column in DATE() makes the predicate non-sargable — the planner cannot match DATE(created_at) against an index on created_at.

Query a range instead:

1SELECT * FROM events
2WHERE created_at >= '2026-01-15'
3  AND created_at <  '2026-01-16';

1SELECT * FROM events
2WHERE created_at >= '2026-01-15'
3  AND created_at <  '2026-01-16';

If you genuinely need the function form, index the expression itself:

1CREATE INDEX idx_events_created_date ON events ((DATE(created_at)));

1CREATE INDEX idx_events_created_date ON events ((DATE(created_at)));

The range version is still preferable — it also works for timestamps with time zones without surprises.

of 1

Global search