You added the index, ran the query again, and the plan still says Seq Scan. The planner isn't ignoring you. It made a cost estimate, and the estimate said a full scan was cheaper. The job is to find out which number it got wrong.
Start with the real plan
Plain EXPLAIN shows estimates. Add ANALYZE and BUFFERS so you see what actually happened, row counts included.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE lower(email) = 'sam@example.com'
AND created_at > now() - interval '30 days';
Compare rows= in the estimate with actual rows=. When they differ by 100x or more, the statistics are the problem, not the index.
The usual suspects
- The expression doesn't match. An index on
emailcan't servelower(email). Index the expression itself. - The types don't match. Comparing a
bigintcolumn with anumericparameter casts every row. - Stale statistics. A table that grew fast since the last
ANALYZElooks smaller to the planner than it is. - Low selectivity. If the filter keeps 30% of the table, a sequential scan really is cheaper.
Fix the estimate, then the index
Run ANALYZE orders; first and look again. If the estimate is still off on correlated columns, extended statistics help:
CREATE STATISTICS orders_email_created (dependencies)
ON email, created_at FROM orders;
ANALYZE orders;
Only then reach for a new index, and make it match the query exactly, expression and column order both.