Index Design Clinic: Composite Indexes, Covering Indexes, and Partial Indexes

Indexes are a double-edged sword. The right index makes queries fly. The wrong one wastes storage, slows down writes, and gives you nothing.

Introduction

Most queries need indexes to perform well, but adding indexes without understanding column order or selectivity is like navigating with a broken map. Composite indexes are the most misunderstood part of database performance — get the column order wrong and your index becomes useless; get it right and one index serves multiple query patterns.

This clinic is about composite index design, covering indexes for index-only scans, partial indexes for selective conditions, and expression indexes for filtered columns. The goal: build an indexing strategy that matches your actual query patterns rather than guessing blindly.

How Composite Index Ordering Affects Queries

flowchart LR
    subgraph Index["Index: (customer_id, status, created_at)"]
    C1["customer_id=1"]
    C2["customer_id=2"]
    C3["customer_id=3"]
    end
    C1 -->|= 1| S1["status=shipped"]
    C1 -->|= 1| S2["status=pending"]
    C2 -->|= 2| S3["status=shipped"]
    C2 -->|= 2| S4["status=cancelled"]
    C3 -->|= 3| S5["status=pending"]
    S1 -->|Range >| R1["created_at > Jan"]
    S3 -->|Range >| R2["created_at > Jan"]

This shows how a composite index on (customer_id, status, created_at) narrows down. Equality on customer_id first, then equality on status, then range on created_at. Skip the leftmost column and the index cannot help at all.

Composite Index Column Ordering

A composite index covers multiple columns. The order matters more than most people realize.

Consider a query:

SELECT * FROM orders
WHERE customer_id = 42
  AND status = 'shipped'
ORDER BY created_at;

The intuitive approach is to index status first if it seems more selective. Do not do this.

The Leftmost Prefix Rule

PostgreSQL can only use a composite index for a range scan or equality check if you’re querying from the leftmost column. Given this index:

CREATE INDEX idx_orders_composite ON orders(customer_id, status, created_at);

The index supports these query patterns:

• WHERE customer_id = 42
• WHERE customer_id = 42 AND status = 'shipped'
• WHERE customer_id = 42 AND status = 'shipped' AND created_at > '2026-01-01'

But it does NOT support:

• WHERE status = 'shipped' (missing leftmost column)
• WHERE status = 'shipped' AND created_at > '2026-01-01' (missing leftmost column)

Equality First, Range Last

When you have both equality and range conditions, put equality columns first:

-- Good: equality columns (customer_id, status) before range (created_at)
CREATE INDEX idx_orders_good ON orders(customer_id, status, created_at);

-- Bad: range column in the middle breaks index usage for later columns
CREATE INDEX idx_orders_bad ON orders(customer_id, created_at, status);

Which Column Goes First?

When multiple equality columns exist, put the more selective column first. You want the index to narrow down results as quickly as possible at the first level.

-- If customers have many orders but status filters significantly
-- More selective: customer_id (1 customer vs many statuses)
CREATE INDEX idx_orders ON orders(customer_id, status, created_at);

-- If statuses are roughly equally distributed
-- Consider which column has better statistics in your version of PostgreSQL

Covering Indexes for Query Optimization

A covering index includes all the columns a query needs, so PostgreSQL never has to touch the heap.

-- Without covering index, query requires heap fetch
CREATE INDEX idx_orders_customer ON orders(customer_id);

SELECT id, created_at FROM orders WHERE customer_id = 42;
-- Index scan + heap fetch for id, created_at

-- With covering index, query is index-only
CREATE INDEX idx_orders_customer_covering ON orders(customer_id) INCLUDE (id, created_at);

SELECT id, created_at FROM orders WHERE customer_id = 42;
-- Index Only Scan - no heap access needed

When Covering Indexes Help

Covering indexes deliver the most value when three conditions align: the SELECT list is narrow relative to the table width, the query runs frequently enough to amortize index maintenance costs, and heap access is expensive due to large row sizes or storage latency. A table with 50 columns where most queries only need 3 or 4 is a strong candidate. In contrast, a narrow table with 8 columns where queries already select 6 of them gains little from a covering index.

The classic indicator is a query that already uses an index efficiently but still triggers heap fetches. If EXPLAIN shows an Index Scan rather than Index Only Scan, the covering index can close that gap. The INCLUDE approach is preferable to adding columns as full index keys when the included columns do not participate in equality or range filtering, since included columns avoid the sorting and B-tree maintenance overhead of key columns.

Here is a practical decision table:

Run the query through EXPLAIN before and after adding a covering index to confirm the plan switches from Index Scan to Index Only Scan. If the plan does not change, the covering index is not helping.

When Covering Indexes Hurt

They can backfire on wide tables where including columns duplicates significant data. Heavy UPDATE workloads suffer because every index must be updated. And if your queries are already selective enough to use the index efficiently, the covering index adds maintenance cost for little gain.

The INCLUDE clause is PostgreSQL’s way to add columns without making them part of the index key. This avoids the sorting and B-tree maintenance overhead of full index columns.

Partial Indexes for Partitioned Data

A partial index only covers rows that match a condition. This makes them smaller and faster to maintain.

Common Use Cases

Partial indexes are not a general-purpose tool — they earn their place when your data has a natural skew that you can exploit. The three patterns below cover most production scenarios: recent-time data that dominates queries, stable subsets like active records, and exceptional cases worth tracking separately.

Recent Data

-- Only index recent orders, assuming old ones are rarely queried
CREATE INDEX idx_orders_recent ON orders(created_at, customer_id)
WHERE created_at > '2025-01-01';

Active Status

-- Only index active users
CREATE INDEX idx_users_active ON users(email) WHERE status = 'active';

Specific Values

-- Index only failed transactions for debugging
CREATE INDEX idx_transactions_failed
ON transactions(transaction_id, created_at)
WHERE status = 'failed';

Partial Index Trade-offs

The downside: the optimizer might not choose a partial index when the WHERE clause doesn’t match your filter. PostgreSQL has to prove the partial index is relevant.

-- This query uses idx_orders_recent (good)
SELECT * FROM orders WHERE created_at > '2026-02-01';

-- This query probably won't use idx_orders_recent
SELECT * FROM orders WHERE created_at > '2025-06-01';  -- too old

Expression Indexes

Sometimes you need to index a computed value. Expression indexes let you index functions and expressions.

Common Examples

Expression indexes handle query patterns that regular column indexes cannot cover. The three patterns here — case-insensitive search, date truncation, and JSON field extraction — represent the most common production uses. Each trades index maintenance overhead for the ability to query computed values directly.

Case-Insensitive Search

-- Without expression index, LIKE lower() can't use a standard index
SELECT * FROM users WHERE LOWER(email) = LOWER('test@example.com');

-- With expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = LOWER('test@example.com');
-- Now uses the index

Date Truncation

-- If you frequently query by day
CREATE INDEX idx_orders_day ON orders(DATE_TRUNC('day', created_at));

SELECT * FROM orders
WHERE DATE_TRUNC('day', created_at) = '2026-03-26';

JSON Extraction

-- Index a specific JSON field
CREATE INDEX idx_orders_metadata ON orders((metadata->>'customer_type'));

SELECT * FROM orders WHERE metadata->>'customer_type' = 'premium';

Expression Index Considerations

• The expression is evaluated for every row on INSERT and UPDATE. Complex expressions add overhead.
• The optimizer has to match the exact expression. Small differences prevent usage.
• Expression indexes cannot be used for ORDER BY unless the expression is used in the ORDER BY clause.

Index Selectivity

Selectivity is the ratio of distinct index values to total rows. High selectivity (close to 1.0) means each value is nearly unique. Low selectivity means many rows share values.

Low Selectivity Example

Low selectivity means many rows share the same value. With selectivity close to 0, almost every row has the same indexed value — the index will not narrow down results much.

-- If 95% of orders are 'pending', indexing status has poor selectivity
CREATE INDEX idx_orders_status ON orders(status);
-- Most queries still have to scan most of the index

The status column with 95% pending orders is a common trap. An index on status returns roughly 95% of the table for a query like WHERE status = 'pending'. PostgreSQL correctly decides a sequential scan is faster than bouncing around an index for that many rows. The index only helps for the 5% where status is not pending.

Low selectivity wastes index space too. An index on a boolean column has at best 50% selectivity per value. A million-row table stores a million index entries and still returns hundreds of thousands of rows per value.

High Selectivity Example

High selectivity means each value is nearly unique. A primary key or UUID column typically has selectivity close to 1.0 — each lookup hits very few rows.

-- customer_id might be unique or near-unique
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- Each lookup returns very few rows

PostgreSQL leans on the index heavily when selectivity is high. A lookup by customer_id does an O(log n) B-tree traversal then one heap fetch. For one row, that is faster than scanning any significant fraction of the table. Index scans on high-selectivity columns are almost always the right call.

When Low Selectivity Can Still Help

Low-selectivity indexes sometimes pay off in unexpected ways. If your pending orders are always recent and you mostly query recent pending orders, the index gives you locality even though many rows share the status value. Composite indexes can also rescue poor selectivity: (status, created_at) might be highly selective even if status alone is not. And for sorting, an index on status pre-sorts your results regardless of how many rows share each status value.

The first scenario where low selectivity earns its place is locality in range scans. Picture a status column where 85% of rows are 'completed' and 15% are 'pending'. A query like WHERE status = 'pending' AND created_at > '2026-01-01' uses the index on (status, created_at) to jump directly to pending rows, then narrows further by date. The index is not selective in absolute terms, but it eliminates the heap scan for completed rows entirely. Without it, PostgreSQL scans the entire table and filters every row. Even though 15% of 10 million rows is 1.5 million rows, those rows are contiguous on disk because the index keeps them in order — the I/O pattern is nothing like a random heap scan.

Composite indexes rescue low-selectivity leading columns by adding a second column that improves selectivity at the next level. A single-column index on status with 85% completed rows is poor. But (status, created_at) with a date predicate becomes highly selective because the date range cuts the result set dramatically. The index still cannot help a query that filters only on status — you would scan all pending rows — but the moment you add any second filter that correlates with physical order, the composite index pays for itself. You do not replace the column, you extend it.

For sorting workloads, low-selectivity indexes provide value that selectivity calculations completely miss. An index on status pre-sorts all rows by status value. A query with ORDER BY status never triggers a sort step — the index returns rows in the correct order already. On a table with millions of rows where most queries filter by status and sort by created_at, the composite index (status, created_at) eliminates two expensive operations: the heap scan for status filtering and the sort for the ORDER BY clause. The index is not selective in the traditional sense, but it reduces query cost in ways that do not show up in selectivity statistics. Run the query before and after to see the actual difference — the theoretical gain only matters if the numbers bear it out.

Index Selectivity vs. Query Selectivity

The key distinction: index selectivity is about the index itself, but query selectivity is about your specific query. A low-selectivity index on status is useless for SELECT * FROM orders, but if your app always queries WHERE status = 'pending' plus a date range, the index becomes more useful because the date range reduces the result set.

Index Design: When to Use Each Type

Common Indexing Mistakes

-- Don't do this
CREATE INDEX idx_orders_everything ON orders(id, customer_id, status, created_at, updated_at, total, ...);

This bloats your index size and slows down inserts. Only index columns you actually filter or sort by.

Ignoring the query pattern

-- Query: WHERE status = 'shipped' AND customer_id = 42
CREATE INDEX idx_wrong ON orders(status, customer_id);

This order is wrong even though it technically works due to leftmost prefix. Put the more selective column first.

Not considering write overhead

Every index you add translates directly into additional write amplification. For an INSERT, PostgreSQL writes the new row and then updates every index that covers the table. That means one INSERT becomes N index writes, one per index. For an UPDATE, if any indexed column changes, every index containing that column gets an entry written. This is not theoretical: on a table ingesting 50,000 rows per minute with 4 indexes, the database performs 200,000 index entries per minute just to keep indexes current.

Measure the actual write cost before deploying. The pg_stat_user_indexes view shows idx_scan, idx_tup_read, and idx_tup_fetch for each index. An index with zeros across all three counters is pure write overhead with zero read benefit. Drop it. An index with moderate scan counts but heavy write volume might still be net negative if the queries it serves are infrequent.

There is a rough formula for write-heavy workloads: if you are doing more than 10,000 writes per minute and an index does not appear in the top 5 most-scanned indexes, it is a candidate for removal or consolidation into a composite index. On read-heavy workloads, you have more freedom to add indexes since writes are not the bottleneck.

Be especially cautious with expression indexes on high-write tables. The expression must be recomputed on every INSERT and UPDATE, even if the underlying column value has not meaningfully changed. A function like LOWER(email) or DATE_TRUNC('day', created_at) applied to millions of rows per hour adds measurable CPU overhead before you even consider the storage cost of the index entries themselves.

Assuming the planner knows best

PostgreSQL’s planner doesn’t always pick the optimal index. Check with EXPLAIN and test queries directly.

Common Production Failures

Composite index with wrong column order: You create (status, customer_id) when queries filter by customer_id first. The index is useless for those queries. Always index based on your actual query filter order, not some theoretical selectivity ranking.

Expression index desynchronized from queries: You create an expression index on LOWER(email) but queries sometimes use lower(email) with different capitalization, or the application sends email = 'User@Example.com'. Expression indexes must match the query expression exactly. Audit all code paths that query the column.

Partial index not matching the query filter: You create a partial index for WHERE status = 'active' but queries use WHERE status = 'ACTIVE' (uppercase) or WHERE status != 'inactive'. The partial index is ignored. Be consistent with the values your application actually uses.

Covering index missing frequently selected columns: You include id and created_at in the covering index but the query also selects status. The heap is still touched. Verify the covering index covers the entire SELECT list before assuming it eliminates heap access.

Expression index bloating on heavy UPDATE: The expression LOWER(email) must be recomputed on every INSERT and UPDATE to every row. On a table with 100M rows and heavy write volume, the maintenance cost outweighs the query benefit. Measure write overhead before deploying expression indexes at scale.

Capacity Estimation: Composite Index Sizing

Composite indexes grow faster than single-column indexes. Here is how to estimate the cost.

A composite index on (customer_id, status, created_at) where customer_id is INT (4 bytes), status is VARCHAR(20) in the index (up to 20 bytes), and created_at is TIMESTAMP (8 bytes) totals roughly 32 bytes per index entry. For 100 million rows, that is 3.2 GB for the leaf level alone. Add the internal B-tree node overhead of roughly 5–10% and you are near 3.5 GB. A single-column index on just customer_id would be roughly 1.2 GB for the same rows.

Every INSERT to the orders table must now update all three columns in the composite index. If you also have individual indexes on each column, a single INSERT could trigger four separate index updates. On a write-heavy workload inserting 50,000 rows per minute, the index maintenance becomes measurable CPU overhead. Use pg_stat_user_indexes to verify the index is actually used before accepting that write cost.

PostgreSQL estimates plan costs using statistics on the leading column. If the leading column has high cardinality (many distinct values), the planner estimates narrow result sets and prefers index scans. If statistics are stale on the leading column, the planner may assume a composite index is more selective than it actually is and choose it when a simpler index would be faster.

If a composite index is not reducing the result set significantly over the leading column alone, the composite is not earning its storage and write cost. Use EXPLAIN to compare plans with and without the composite index.

Quick Recap Checklist

• Put equality columns before range columns in composite indexes
• Leftmost prefix rule: skipping the leading column breaks index usage
• Covering indexes with INCLUDE eliminate heap fetches but add write overhead
• Partial indexes only help when the WHERE clause matches the index predicate
• Expression indexes must match the query expression exactly
• Low selectivity indexes can still help for sorting and locality
• Run ANALYZE after bulk loads to keep statistics current
• VACUUM keeps visibility maps fresh for index-only scan efficiency
• Compare estimated rows vs actual rows to detect stale statistics
• Index-only scan requires all columns in SELECT plus current visibility map

Interview Questions

Further Reading

• PostgreSQL Index documentation — official reference on index types and usage patterns
• PostgreSQL Composite Index documentation — column ordering and leftmost prefix rule
• PostgreSQL Indexes on Expressions — expression index creation and usage
• Partial Indexes in PostgreSQL — when partial indexes help and when they backfire
• brin index for time-series data — Block Range Indexes for naturally ordered data like timestamps
• pg_stat_user_indexes — checking which indexes are actually used

Conclusion

Good index design comes down to understanding your query patterns. Composite index order follows the leftmost prefix rule: equality columns first, range columns last. Covering indexes eliminate heap fetches for frequently-run queries. Partial indexes keep index size manageable when you only need a subset of data. Expression indexes handle computed values but come with write overhead.