Query Plan Surprises in PostgreSQL

I had a reporting query that took eleven seconds on three million rows. After a few weeks of poking at it, it runs in under 200 milliseconds. Here’s what actually changed — and more importantly, what I misunderstood about the query planner before I started.

The Problem

The query joined four tables, filtered by date range, grouped, and sorted. Nothing unusual. But it had grown over two years: someone added a subquery here, a LEFT JOIN there, nobody ever looked at the execution plan while the data was small enough to not care.

EXPLAIN ANALYZE
SELECT
  u.name,
  p.category,
  COUNT(e.id) AS event_count,
  MIN(e.created_at) AS first_event
FROM users u
JOIN profiles p ON p.user_id = u.id
LEFT JOIN events e ON e.user_id = u.id
WHERE e.created_at BETWEEN '2025-01-01' AND '2025-09-30'
GROUP BY u.name, p.category
ORDER BY event_count DESC;

The plan showed a sequential scan on events — all 2.9 million rows, date filter applied after the join. The planner had no useful index and was doing exactly what you’d expect with no guidance.

The Progression

Each change moved the needle differently. The table below is the actual timing from EXPLAIN ANALYZE output, not estimated rows:

The partial index helped, but the real gain came from understanding that PostgreSQL was inlining the CTE and losing the pre-filtered row count. Forcing materialization — WITH events_filtered AS MATERIALIZED (...) — gave the planner an accurate row estimate going into the join, which changed the join strategy from hash to index nested loop.

What I Got Wrong First

My first instinct was to add a GIN index. Wrong tool. GIN is for full-text search and array operations, not range scans on timestamps.

My second instinct was to blame the LEFT JOIN. Also wrong. The join itself wasn’t the problem — the sequential scan feeding it was.

The actual fix was boring: read the plan carefully, find where the row count estimates diverged from reality, and give the planner better information at that point. In this case, a partial index on the filtered column and a materialized CTE.

The Partial Index

CREATE INDEX CONCURRENTLY idx_events_user_recent
ON events (user_id, created_at)
WHERE created_at >= '2025-01-01';

CONCURRENTLY is non-negotiable on a production table — it doesn’t lock writes. The WHERE clause makes the index smaller and faster than a full index on those columns. The planner picks it up automatically once the query filter overlaps with the index predicate.

On Reading EXPLAIN ANALYZE

The thing that costs the most time is ignoring the estimated vs. actual row counts. PostgreSQL shows both — rows=X is the estimate, actual rows=Y is what happened. When those numbers differ by an order of magnitude, you’ve found your problem. Everything downstream of a bad estimate is suspect.

EXPLAIN (ANALYZE, BUFFERS) is more useful than plain EXPLAIN ANALYZE — the BUFFERS output shows cache hits vs. disk reads, which often explains why the same query runs fast on the dev machine and slow in production.

The Part Nobody Writes About

Running these experiments in production is nerve-wracking in a way that’s hard to explain. You add an index, watch for locking issues, check replication lag. You test the rewritten query on a read replica first. You remind yourself that CONCURRENTLY index builds can fail silently and leave invalid indexes that the planner still tries to use.

None of that is hard, exactly. It’s just detail work that takes longer than the actual query optimization. The query took three weeks not because the SQL was hard but because I had to do it carefully around a production workload.

The result is worth it. 183 milliseconds opens up options — pagination, real-time dashboards, scheduled exports — that eleven seconds closes off completely.