Composite & Partial Indexes in PostgreSQL

Most developers learn that indexes make queries faster. What took me longer to understand was that not all indexes are created equal. The order of columns inside a composite index matters, and sometimes indexing only a subset of rows can outperform indexing an entire table.

By the end of this article, you’ll understand why some indexed queries still end up doing sequential scans, how PostgreSQL uses composite indexes, and why partial indexes are one of the most underutilized performance tools available.

Understanding Composite Indexes

A composite index (also called a multi-column index) stores multiple columns together in a single B-Tree structure.

Consider a users table:

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT
);

Suppose most of my queries filter by both first and last name:

SELECT *
FROM users
WHERE first_name = 'John'
  AND last_name = 'Doe';

I can create a composite index:

CREATE INDEX idx_users_first_last
ON users(first_name, last_name);

Now PostgreSQL can efficiently navigate the index using both columns.

Why Column Order Matters

One of the most important things to remember is that PostgreSQL stores composite indexes from left to right.

For an index:

(first_name, last_name)

PostgreSQL can efficiently use it for:

WHERE first_name = 'John';

and:

WHERE first_name = 'John'
  AND last_name = 'Doe';

However, it usually cannot efficiently use the same index for:

WHERE last_name = 'Doe';

because the leading column (first_name) is missing.

Think of it like a phone book. The book is organized by surname first. If you know the surname, finding someone is easy. If you only know the city they live in, the ordering becomes much less useful.

Composite indexes follow the “leftmost prefix rule.” Queries must start with the leading indexed columns to benefit fully.

This is one of the most common indexing mistakes I see. Developers create:

(first_name, last_name)

and assume searches on either column will be equally fast.

They won’t.

A Practical Example

Given:

CREATE INDEX idx_users_first_last
ON users(first_name, last_name);

These queries can use the index efficiently:

WHERE first_name = 'John';

WHERE first_name = 'John'
AND last_name = 'Doe';

This query generally cannot:

WHERE last_name = 'Doe';

If searches by last_name are common, I would either:

• Create another index on last_name
• Create a separate composite index that starts with last_name
• Re-evaluate query patterns before designing indexes

Index design should follow actual query behavior, not table structure.

Composite Indexes and Sorting

Composite indexes are useful not only for filtering but also for sorting.

Suppose I frequently run:

SELECT *
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC;

A composite index can help with both filtering and ordering:

CREATE INDEX idx_orders_customer_created
ON orders(customer_id, created_at DESC);

Now PostgreSQL can:

• Find matching rows
• Return them already sorted
• Avoid an additional sort operation

This becomes increasingly valuable as table size grows.

Index-Only Scans

One of PostgreSQL’s coolest optimizations is the Index-Only Scan.

Normally, PostgreSQL:

1. Uses the index to find row locations
2. Visits the table heap
3. Reads actual row data

That heap lookup can become expensive.

With an Index-Only Scan, PostgreSQL can answer the query directly from the index without touching the table.

For example:

CREATE INDEX idx_users_email
ON users(email);

Query:

SELECT email
FROM users
WHERE email = 'john@example.com';

Since the requested column already exists inside the index, PostgreSQL may perform:

Index Only Scan

instead of:

Index Scan

This reduces disk I/O significantly.

When Index-Only Scans Don’t Happen

Many developers assume Index-Only Scans always occur when columns exist in the index.

That’s not always true.

PostgreSQL must also verify row visibility using its Visibility Map.

If visibility information isn’t available, PostgreSQL may still need to visit heap pages.

As a result:

• Recently modified tables often see fewer Index-Only Scans
• Frequently vacuumed tables benefit more
• Read-heavy workloads gain the most advantage

An Index-Only Scan isn’t just about column coverage. PostgreSQL must also know the row is visible without consulting the heap.

Partial Indexes

Sometimes indexing every row is wasteful.

Imagine an orders table:

CREATE TABLE orders (
    id BIGSERIAL,
    status TEXT,
    total NUMERIC
);

Suppose 95% of rows are completed orders:

completed
completed
completed
completed
pending

But the application mostly queries pending orders:

SELECT *
FROM orders
WHERE status = 'pending';

A normal index would include every row:

CREATE INDEX idx_orders_status
ON orders(status);

Instead, I can create:

CREATE INDEX idx_pending_orders
ON orders(status)
WHERE status = 'pending';

Now PostgreSQL indexes only pending rows.

Why Partial Indexes Can Beat Full Indexes

Because the index contains fewer entries:

• Smaller index size
• Lower storage overhead
• Faster index traversal
• Reduced write amplification
• Better cache efficiency

In some workloads, a partial index can outperform a full index by a significant margin.

This is especially useful when:

• Most rows are inactive
• Soft deletes are used
• Status columns are heavily skewed
• Multi-tenant applications have hot subsets of data

Common Partial Index Patterns

Soft deletes:

CREATE INDEX idx_active_users
ON users(id)
WHERE deleted_at IS NULL;

Pending jobs:

CREATE INDEX idx_pending_jobs
ON jobs(status)
WHERE status = 'pending';

Published articles:

CREATE INDEX idx_published_posts
ON posts(created_at)
WHERE published = true;

These indexes focus only on rows that matter to the application’s critical queries.

Choosing Between Composite and Partial Indexes

I think about the problem using two questions:

Question 1: Which Columns Are Filtered Together?

If queries commonly use multiple columns:

WHERE customer_id = ?
AND status = ?

a composite index is usually the right choice.

Question 2: Do I Query Only a Small Subset of Rows?

If most queries target:

WHERE status = 'active'

while only a small percentage of rows are active, a partial index is often better.

The biggest gains happen when both techniques are combined.

Example:

CREATE INDEX idx_active_customer_orders
ON orders(customer_id, created_at)
WHERE status = 'active';

This creates:

• A composite index
• On a filtered subset of rows
• Optimized for a specific workload

That’s often far more effective than a generic index covering the entire table.

Final Thoughts

Composite indexes taught me that column ordering is just as important as creating the index itself. A perfectly valid index can become nearly useless if the most selective or frequently filtered column isn’t placed correctly.

Partial indexes taught me something equally valuable: sometimes the fastest index is the one that ignores most of the table.

When analyzing slow queries, I no longer ask, “Do I have an index?” Instead, I ask:

• Is the column order aligned with query patterns?
• Can PostgreSQL perform an Index-Only Scan?
• Am I indexing rows that are never queried?
• Would a partial index reduce index size dramatically?

Those questions usually lead to much bigger performance wins than simply adding more indexes.