Indexing is one of the most misunderstood areas of PostgreSQL performance tuning. Developers often assume that “more indexes = faster queries”, which leads to tables with dozens of single-column indexes that slow down writes and still don’t support the queries the business actually runs. In this article we’ll break down when multiple single-column indexes make sense, when a single multi-column index is the right choice, and how PostgreSQL decides which one to use.

What Is an Index?

At a high level, an index is a separate data structure that stores the values of one or more columns in a sorted, searchable format (typically a B-Tree). Instead of scanning the entire table, PostgreSQL can use the index to quickly locate matching rows. The tradeoff is that indexes must be maintained during INSERT, UPDATE, and DELETE operations, which adds overhead. Choosing the right type and shape of index is essential for balancing read performance, write performance, and storage use.

Single-Column Indexes

A single-column index is the simplest and most common type of index. It accelerates queries that filter or sort on that one column. These are useful when different queries filter on different columns independently. However, relying solely on separate single-column indexes can force PostgreSQL to combine them using bitmap index scans, which is slower than using a purpose-built multi-column index.

Multi-Column Indexes

A multi-column (composite) index stores multiple columns together in a defined order, such as (status, created_at). These indexes are extremely powerful when your workload frequently filters on the same set of columns in the same sequence. Because the values are stored in order, PostgreSQL can perform efficient range scans and avoid merging multiple separate indexes.

How PostgreSQL Uses Multi-Column Indexes

Column order matters! In an index defined as (A, B, C), PostgreSQL can fully utilize:

• A by itself
• A and B together
• A, B, and C together

But the index is far less effective if a query filters only on B or only on C. If your workload frequently queries by the same combination of columns, a multi-column index is usually a substantial win.

When Multiple Single-Column Indexes Are Better

If your application issues many different types of queries—sometimes filtering by A, sometimes by B, sometimes by C—then one composite index won’t help much. In these cases, separate single-column indexes may provide better coverage and flexibility. PostgreSQL can merge them using bitmap scans when necessary, although this has limitations compared to a dedicated multi-column index.

When a Multi-Column Index Is the Right Choice

If your workload consistently filters by multiple columns in a predictable pattern, you should strongly consider a composite index. For example:

SELECT *
FROM orders
WHERE status = 'ACTIVE'
AND created_at >= NOW() - INTERVAL '7 days';

A multi-column index on (status, created_at) dramatically reduces the search space. While PostgreSQL could combine two single-column indexes, the composite index will almost always be faster—especially on large tables.

The True Cost of “Index Everything”

Over-indexing is one of the most common mistakes. Each index increases:

• Write amplification (INSERT/UPDATE/DELETE)
• Storage usage
• Vacuum and autovacuum work
• Backup size and restore time
• Replication lag

Indexes are not free. They must match your actual workload—not what you think it might be one day.

Bitmap Index Scans

When the planner needs to use multiple single-column indexes to satisfy a query, it performs a bitmap AND/OR operation. This can be efficient for moderate datasets but is still slower than a well-crafted composite index. Bitmap scans read more pages into memory and must merge bitmap sets, adding CPU and I/O overhead.

Real-World Examples

Login Table

SELECT * FROM users
WHERE email = 'john.doe@example.com'
AND password_hash = '...';

A composite index on (email, password_hash) is ideal.

E-Commerce Filters

Sometimes filtering by category, sometimes by price → single-column indexes make more sense.

Time-Series Data

Queries almost always filter by (device_id, timestamp) → multi-column index.

User Table

Queries filter by email alone → single-column index on email.

Choosing the Right Index: Practical Rules

• Use multi-column indexes when queries consistently filter by the same columns.
• Use single-column indexes when query patterns vary widely.
• For multi-column indexes, put the most selective or most frequently filtered column first.
• Do not index every column “just in case”.
• Use EXPLAIN and real query logs to guide your decisions.

Conclusion

Indexes are powerful, but only when they match the way your application queries data. Choosing between multiple single-column indexes and a single multi-column index has a major impact on performance, write overhead, and storage usage. When you understand how PostgreSQL uses each type of index and how your workload actually behaves, you can create an indexing strategy that stays fast, efficient, and easy to maintain!