Which date filter can use the index?

Question

Accepted Answer

An index on created_at stores raw timestamp values. A range filter (>= / <) lets the database seek directly to the start of the range and scan forward - it uses the index. DATE_TRUNC('month', created_at) = '2024-01-01' applies a function to every row's value before comparing. The database must evaluate DATE_TRUNC for every row, making the index unusable (a full table scan). This is the general rule: applying a function to an indexed column in WHERE defeats the index. Keep the column bare on one side.