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 shine when your queries return a small number of columns compared to the table width, and when those queries run frequently (the index cost spreads across many executions). They also help when heap access is expensive due to large rows or slow storage.

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

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

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

-- 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

High Selectivity Example

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

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.

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 adds write overhead. A table with 5 indexes takes longer to UPDATE than a table with 2. Profile your workload before adding indexes.

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.