Making a PostgreSQL Query 10x Faster with One Fix

The Day "This Is Timing Out" Showed Up in Slack

It was around 3 PM on a Friday. A message popped up in the frontend team's Slack channel. The dashboard API was throwing timeouts. At first I assumed it was a server issue, but nope — the query was clocking in at 3.2 seconds.

This API fetched user activity data. It was fine in the early days of the service. When the data was 100K rows. But a year later, it had grown to 8 million rows, and the gradual slowdown had gone completely unnoticed.

The Moment I Ran EXPLAIN ANALYZE

I copied the offending query and ran EXPLAIN ANALYZE. A classic analytics query — joining the users table with the activity log, filtering by date range, and aggregating with GROUP BY.

The results showed Seq Scans firing in two places. Full table scans on an 8-million-row table. Estimated cost exceeding 150,000. No wonder it was slow.

There Was an Index, But It Wasn't Being Used

I had a B-tree index on created_at. But looking closer at the query, the comparison was wrapped in DATE(created_at). The moment you wrap a column in a function, the index doesn't get used. I knew this in theory, but it's so easy to miss in actual code.

Changed it to WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01' and it immediately switched to an Index Scan. That alone shaved off 1.2 seconds.

(Why I didn't catch this from the start, I honestly don't know.)

Still 1.8 Seconds

Looking at the JOIN side, it was filtering on both user_id and action_type, but each only had a single-column index. I added a composite index.

CREATE INDEX idx_activity_user_action ON activity_log (user_id, action_type, created_at)

The query planner chose an Index Only Scan, and at that moment it dropped to 800ms. I learned — in my bones, not just my head — that column order in composite indexes matters. Equality conditions first, range conditions last in the WHERE clause.

Restructuring the Subquery

The original query had nested subqueries. I refactored them into CTEs with the MATERIALIZED hint. The new design first narrowed 8 million rows to 200K with the date filter, then ran aggregations on that result set.

800ms down to 400ms.

Running ANALYZE Manually

One more thing. Running ANALYZE manually shaved off another 50ms. PostgreSQL's query planner builds execution plans based on table statistics, and stale statistics lead to suboptimal plans.

The production autovacuum settings were still at defaults. I lowered autovacuum_analyze_scale_factor to 0.01 for the 8-million-row table to increase the statistics refresh frequency.

(This is something even the official docs don't cover well.)

From 3 Seconds to 307ms

Replaced function-based conditions with range conditions, added a composite index, restructured the query, and refreshed statistics. All basic stuff. Nothing fancy whatsoever.

The truth is, 80% of query tuning is reading EXPLAIN ANALYZE properly, and the rest is getting your indexes right.

Honestly, If I'd Written It Right from the Start

When data is small, every query is fast. Problems only surface as the service grows. After this experience, I started thinking about expected data volume first whenever designing a new table. Will it hold up at 1 million rows? What about 10 million? Will this query still work a year from now?