One Index That Fixed a 5-Second Query

The query

SELECT date_trunc('day', created_at) as day,
       COUNT(*) as count
FROM events
WHERE project_id = $1
  AND created_at >= $2
GROUP BY 1
ORDER BY 1;

The problem

EXPLAIN ANALYZE revealed a sequential scan on the events table — 2M rows scanned for a single project’s data. The table had an index on created_at but not on (project_id, created_at).

The fix

CREATE INDEX CONCURRENTLY idx_events_project_date
ON events (project_id, created_at);

Before: ~5000ms. After: 8ms. A 625x improvement.

What I learned

Composite indexes are underrated. A single-column index on project_id would have helped, but the composite (project_id, created_at) covered the entire query — PostgreSQL never touches the table, just the index (index-only scan).

Always check EXPLAIN ANALYZE before adding an index. If your query pattern filters by A and sorts/aggregates by B, a composite (A, B) index is likely the answer.