Optimizing Skewed Queries with PostgreSQL Partial Indexes

The problem: indexing rows nobody queries

Picture an orders table with ten million rows, where 98% have status = 'completed' and your application almost exclusively queries the small slice of pending rows. A standard B-tree index on status indexes all ten million rows anyway — wasting storage, slowing writes, and often being ignored by the planner because the value distribution is so skewed.

The useful takeaway: PostgreSQL lets you put a WHERE clause directly on an index. A partial index only contains rows matching a predicate, so it stays small, cheap to maintain, and highly selective for exactly the queries you care about.

What a partial index actually is

A partial index is a regular index (usually B-tree) with a predicate attached at creation time. Only rows satisfying that predicate get an index entry. Two consequences follow:

• Smaller size: if 2% of rows match, the index is roughly 2% of the size of a full index on the same column.
• Cheaper writes: inserting or updating a row that doesn't match the predicate costs nothing in index maintenance.

The classic use cases are queue-like tables (WHERE is_processed = false), soft deletes (WHERE deleted_at IS NULL), and hot subsets such as active users or open tickets.

A worked example

Assume PostgreSQL 12+ (the syntax is far older, but planner behavior and tooling described here are current for supported versions). Given an orders table:

-- Run in psql or any SQL client as a role with CREATE on the schema
CREATE INDEX idx_orders_pending_created
  ON orders (created_at)
  WHERE status = 'pending';

The index key is created_at, but only pending orders appear in it. A query like this can use it:

SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at
LIMIT 50;

Verify the planner agrees — run this in the same database:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at LIMIT 50;

You should see an Index Scan (or Index Only Scan) using idx_orders_pending_created. As a negative check, run EXPLAIN on WHERE status = 'completed' — the planner must fall back to another plan (often a sequential scan), because the index provably cannot contain those rows.

To quantify the storage win, compare sizes:

SELECT pg_size_pretty(pg_relation_size('idx_orders_pending_created'));

Compare that against a full index on the same column. On a table where pending rows are rare, the difference is typically dramatic.

The planner's strict matching rule

This is where partial indexes bite people. PostgreSQL will only use the index if it can prove the query's WHERE clause implies the index predicate. WHERE status = 'pending' matches. But a parameterized query like WHERE status = $1 may not use the index in a generic plan, because the planner can't prove $1 will be 'pending'. Similarly, WHERE status IN ('pending', 'shipped') is not implied by the predicate and won't qualify.

Practical rule: write the query predicate to textually match (or clearly imply) the index predicate, and test with EXPLAIN using your real query shape — including how your ORM or driver binds parameters.

Trade-offs and limitations

• Narrow utility: a partial index is useless for queries outside its predicate. If access patterns are unpredictable, a full B-tree is the safer default.
• Index sprawl: it's tempting to create one partial index per status value. Each index still costs write overhead for matching rows and adds planning and maintenance burden. Prefer one well-chosen predicate over many.
• Predicate drift: if the application changes what "active" means (say, from deleted_at IS NULL to also excluding archived rows), the index predicate must be updated too — the index won't adapt on its own.

Actionable closing

Find one table in your system where a boolean or status column splits rows into "constantly queried" and "almost never queried." Create a partial index on the hot subset, then confirm with EXPLAIN ANALYZE that your real query uses it and with pg_relation_size that it's meaningfully smaller than a full index. If either check fails, drop it — a partial index that the planner can't prove applicable is pure overhead.