PostgreSQL Partial Indexes Cut My Query Time by 60%

The payments table had crossed 50 million rows. Our dashboard queries for pending transactions were taking 2.3 seconds on average during peak hours. The obvious fix — adding an index on the status column — helped, but only brought it down to 1.1 seconds. The real breakthrough came from a partial index.

The Problem

We were indexing rows we never queried. Completed transactions make up 94% of the table, but our dashboard only ever queries pending, processing, and failed transactions. A full index on status was maintaining entries for 47 million rows we never touched in these queries.

The Partial Index

// sql
CREATE INDEX idx_payments_pending_status
ON payments (status, created_at)
WHERE status IN ('pending', 'processing', 'failed');

This index only covers about 3 million rows — 6% of the table. It's smaller, faster to scan, and stays hot in memory. Query time dropped to 380ms average.

What I Learned About Query Planning

PostgreSQL's query planner is smart enough to use partial indexes when your WHERE clause matches the index condition exactly or is a subset of it. But it won't use a partial index for queries that could potentially need rows outside the index — even if you know they don't. You need to match the predicate precisely.

The lesson: before adding another full-table index, ask whether you're indexing data you'll never query. Partial indexes are one of PostgreSQL's most underutilized features, and they consistently deliver outsized returns for the minimal effort they require.