~/hailcpy
all posts
feb 12, 2026·4 min readpostgresindexesperformancerds

Postgres had an index. It still sequential-scanned 1.7 million rows.

The investigations list took 30-40 seconds. We optimized that query first. The real load was a quota check doing a sequential scan on 1.7 million rows because the composite index was in the wrong order.

The investigations list is the page every analyst lives on. One tenant's cluster had it at 30-40 seconds. CPU on RDS was high. I assumed the list query was the problem, because that is the query people wait on.

It was a heavy query. Lots of joins, subqueries, some of them missing indexes. I rewrote the worst parts and ran the new version on production. Still slow. Worse: it was variable. Fast at night. Painful in the morning.

Night is when that tenant stops streaming alerts. No analysts, no LLM calls, cluster quiet. Morning, the box is busy again. So the list query was paying for someone else's work.

Looking at the wrong metric

I checked locks. Something was grabbing the buffer, holding it, doing it again. Then I opened RDS Performance Insights and sorted by queries per second.

Nothing jumped out. The suspicious query was not the hottest by call rate. About 500k calls/day, maybe 5-10 per second. Mean time around a second. What was wrong: rows scanned. Huge.

EXPLAIN ANALYZE on production:

Limit  (cost=284500.64..284500.65 rows=1 width=1203) (actual time=601.474..605.256 rows=0 loops=1)
  Buffers: shared hit=40884 read=232178
  I/O Timings: shared read=736.336
  ->  Sort  (cost=284500.64..284500.65 rows=1 width=1203)
        Sort Key: metric_threshold_breach.breach_detected_at DESC
        ->  Gather
              Workers Launched: 2
              ->  Parallel Seq Scan on pov.metric_threshold_breach
                    Filter: metric_name = 'llm_quota_global'
                            AND state = 'actions_completed'
                    Rows Removed by Filter: 556726
Execution Time: 605.274 ms

Parallel sequential scan. Three workers. Each threw away ~557k rows. Planner estimated one matching row and still decided to read the heap. 232k pages from disk. 605ms to return zero rows.

The table had an index. Postgres was not using it.

Why the index did not count

The Django index was:

models.Index(fields=["metric_name", "breach_detected_at"]),

The query was:

WHERE metric_name = 'llm_quota_global'
  AND state = 'actions_completed'
ORDER BY breach_detected_at DESC
LIMIT 1;
Query predicates versus old and new btree column ordercol 1col 2col 3querymetric_name =state =time DESC LIMIT 1old indexmetric_nametime(no prefix)seek stops. state is not here → seq scannew indexmetric_namestate-timeequalities first, sort last. index scan, 3 pages
btree uses a leftmost prefix. time in slot 2 meant Postgres could not seek on state.

Leftmost prefix is metric_name. That part matches. Second column is time. The equality the planner actually needs next is state. It cannot jump to actions_completed inside an index ordered by timestamp.

metric_threshold_breach had 1.7 million rows. Almost all of them (1.6 million) were metric_name = 'llm_quota_global'. Almost all of those (1.59 million) were state = 'actions_failed'.

So "filter by metric_name" does not narrow anything. You still walk 1.6 million wide rows (tuple width 1203) looking for the rare actions_completed row. Index + heap random I/O loses to a parallel sequential scan. Planner picks the scan. That is reasonable given the stats. The index was just the wrong shape.

The feedback loop

This tenant was out of LLM quota. Every call after the breach still wrote a row: llm_quota_global / actions_failed. 500k LLM calls a day, most of them failed, table only grows.

And every one of those calls also read the table: "is there a completed breach for this metric?" Sequential scan, 600ms, 5-10 times a second. That is several seconds of database work per wall-clock second. The investigations list, sitting on the same instance, waited behind it.

At night the writes stop, the scans stop, the list looks fine. In the morning the quota table is on fire again. That is the pattern I wasted time blaming on the list SQL.

The index that matched the query

models.Index(
    fields=["metric_name", "state", "-breach_detected_at"],
    name="mb_name_state_time_idx",
)

Equality columns first (metric_name, state), sort column last, descending so ORDER BY breach_detected_at DESC LIMIT 1 is the first index tuple. After:

Limit  (cost=0.43..6.48 rows=1 width=1203) (actual time=0.028..0.028 rows=0 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using mb_name_state_time_idx on pov.metric_threshold_breach
        Index Cond: metric_name = 'llm_quota_global'
                    AND state = 'actions_completed'
Planning Time: 0.514 ms
Execution Time: 0.048 ms

Three buffer hits. 0.048ms. Same query identifier, same LIMIT 1, same zero rows.

RDS load dropped at least 20% immediately. The investigations list fell from 30-40 seconds to about 2 seconds just from the box being idle enough to run it. Later I actually did the list-query work (rewrite + indexes) and got it to ~300ms. That part was real. It was not the first 40 seconds.

What I look at now

QPS is a bad sort key when each call reads 1.7 million rows. Sort Performance Insights by rows examined and by total time, then EXPLAIN (ANALYZE, BUFFERS) the weird one on prod, not a snapshot of staging.

If the plan says Seq Scan and you "already have an index," print the index columns next to the WHERE and ORDER BY. If the equalities are not a prefix, Postgres is not being stupid. The index is.