Partial Indexes in PostgreSQL

Source: Dev.to

Overview

Partial indexes are refined indexes that target specific access patterns. Instead of indexing every row in a table, they only index the rows that match a condition — making them smaller, faster, and more efficient for the right use cases.

How Partial Indexes Work

Partial indexes work best on queries that:

• Filter first then scan multiple rows
• Target meaningful subsets of data
• Might otherwise hit index thresholds that cause the planner to ignore the index entirely

Demonstration with a Sample Database

The example uses the MySQL sample database converted to PostgreSQL. The salaries table contains about 3 million rows.

CREATE TABLE "employees"."salaries" (
    "id" int4 GENERATED ALWAYS AS IDENTITY,
    "emp_no" int4,
    "salary" int4,
    "from_date" date,
    "to_date" date,
    PRIMARY KEY ("id")
);

Standard Index Query

SELECT COUNT(*)
FROM salaries
WHERE salary > 100000
  AND to_date = '9999-01-01';

In this dataset, 9999-01-01 marks an active salary. The query took roughly 140 ms (cold) and 40 ms (hot).

Adding a Partial Index

Before adding an index, consider how many rows will be filtered out. In this case, only 247 000 of the 3 million rows are current (active), a solid reduction.

CREATE INDEX idx_salaries_salary_todate_partial
ON salaries (salary)
WHERE to_date = '9999-01-01';

With the partial index, the same query runs in ≈16 ms (both cold and hot).

When Partial Indexes Shine

Large Result Sets and the “30 % Rule”

Range queries can be ignored by the planner when the result set exceeds roughly 30 % of total rows. For example:

SELECT COUNT(*)
FROM salaries
WHERE salary > 50000
  AND to_date = '9999-01-01';

• salary > 100000 returns 17 000 rows → index used.
• salary > 50000 returns 215 000 rows (≈87 % of active rows) → standard index ignored, table scan used, hot time ≈120 ms.

The partial index remains useful because it reduces the starting row count, keeping the query time around 40 ms even with the broader filter.

Repeated Queries on a Subset

A typical scenario is a queue with an is_processed boolean:

CREATE INDEX idx_unprocessed_queue
ON queue (created_at)
WHERE is_processed = false;

If unprocessed rows are a small fraction of the total, the index stays small and fast.

Index Size Benefits

• Full salary index: 58 MB
• Partial salary index: 7 MB

Smaller indexes fit better into the buffer cache, increasing the likelihood that they stay in memory.

Situations Where Benefits Are Minimal

If a column is already uniquely indexed, adding a partial index on an additional condition may not improve performance:

SELECT id
FROM users
WHERE email = 'test@test.com' AND is_active = true;

Since email is unique, the planner will use the unique index regardless of is_active. However, on very large tables a partial index can still reduce index bloat.

Summary

Partial indexes are a powerful tool for reducing query times on specific access patterns. They are most effective when:

• Queries consistently filter on the same condition(s)
• The condition excludes a substantial portion of rows
• The resulting index remains significantly smaller than a full index

Start by analyzing your most common queries and estimating how much data would be filtered out. If the numbers look promising, a partial index may be a worthwhile addition.