ReviseAlgo Logo

Indexing

Composite, Covering, and Partial Indexes

Fine-tuning indexes to cover specific query combinations.

Last Updated: June 15, 2026 • 16 min read

1. Introduction

Composite indexes span multiple columns, covering indexes include extra columns to enable index-only scans, and partial indexes index only a subset of rows. These advanced techniques let you craft surgical indexes that precisely match your query patterns while minimizing storage and write overhead.

2. Why It Matters

  • Multi-column queries: WHERE category = 'Electronics' AND price < 100 needs a composite index, not two separate indexes.
  • Index-only scans: Covering indexes include all SELECT columns, eliminating heap reads entirely — 2-5x faster.
  • Targeted indexing: Partial indexes on WHERE status = 'active' index only 5% of rows when most are inactive — 20x smaller index.

3. Real-World Analogy

A composite index is like a phone book sorted by (last_name, first_name) — useful for finding "Smith, John" but not "John" alone. A covering index includes the phone number in the listing itself so you don't need to call directory assistance. A partial index is like a "VIP only" contact list — smaller, faster, and focused on the entries you use most.

4. How It Works

-- COMPOSITE INDEX: multi-column, order matters
CREATE INDEX idx_products_cat_price ON products(category, price);
-- Supports: WHERE category = ? AND price < ?
-- Supports: WHERE category = ? (uses first column)
-- Does NOT support: WHERE price < ? (can't skip first column)

-- COVERING INDEX: includes non-key columns for index-only scans
CREATE INDEX idx_orders_user_status ON orders(user_id, status)
    INCLUDE (total, created_at);
-- SELECT total, created_at FROM orders WHERE user_id = ? AND status = ?
-- Uses index-only scan (no heap read needed)


-- PARTIAL INDEX: indexes only matching rows
CREATE INDEX idx_orders_pending ON orders(created_at)
    WHERE status = 'pending';
-- 10x smaller than full index when only 5% of orders are pending


-- UNIQUE partial index (enforce uniqueness on active records)
CREATE UNIQUE INDEX idx_unique_active_email ON users(email)
    WHERE deleted_at IS NULL;

5. Internal Architecture

Composite Index Column Order Rules:
  Index: (A, B, C)
  Supports:  WHERE A=? | A=? AND B=? | A=? AND B=? AND C=?
  Does NOT:  WHERE B=? | C=? | B=? AND C=?
  → Put most selective / most filtered column first

Covering Index (INCLUDE):
  Index stores: (key_cols...) + (included_cols...)
  Included columns are NOT in the B-tree structure
  → They're only in leaf pages (no extra tree depth)
  → Smaller than adding all columns to the index key


Partial Index:
  Only rows matching WHERE clause are indexed
  Index size ∝ matching rows (not total rows)
  → Must match partial index predicate in query WHERE

6. Visual Explanation

7. Practical Example

-- Dashboard query optimized with covering index
CREATE INDEX idx_orders_dashboard ON orders(user_id, status)
    INCLUDE (total, created_at, shipping_address);

-- This query uses index-only scan (fastest possible):
SELECT total, created_at, shipping_address
FROM orders
WHERE user_id = 42 AND status = 'shipped';


-- Partial index for active user lookups
CREATE INDEX idx_active_users_email ON users(email)
    WHERE status = 'active';


-- Much smaller than indexing all users (including deleted/banned)
SELECT * FROM users WHERE email = 'alice@example.com' AND status = 'active';


-- Composite index with sort optimization
CREATE INDEX idx_products_search ON products(category, price DESC);
-- Supports: WHERE category = ? ORDER BY price DESC LIMIT 20
-- No sort step needed (index is already sorted)

8. Common Mistakes

Wrong column order in composite index

Index on (price, category) doesn't help WHERE category = ? AND price < ?. The equality column should come first: (category, price).

Partial index WHERE clause mismatch

A partial index on WHERE status = 'active' is only used when the query also includes WHERE status = 'active'. If your query omits this filter, the partial index won't be used.

9. Quick Quiz

Q1: What's the advantage of INCLUDE over adding columns to the index key?

Answer: INCLUDEd columns are stored only in leaf pages, not in internal B-tree nodes. This keeps the tree smaller and shallower. Key columns affect tree structure; INCLUDEd columns don't.

10. Scenario-Based Challenge

Challenge: API Endpoint Index Optimization

Your API serves: GET /orders?user_id=X&status=pending (most common), GET /orders?user_id=X&status=shipped&page=2 (paginated). Design indexes using covering and partial techniques to optimize both endpoints with minimal storage overhead.

11. Debugging Exercise

This composite index isn't used for this query. Why?

CREATE INDEX idx_orders_date_status ON orders(created_at, status);
-- Query: SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at;

Issue: The equality filter (status) should be the first column, and the range/sort column (created_at) second. Fix: CREATE INDEX idx_orders_status_date ON orders(status, created_at).

12. Interview Questions

Q1: How do you decide column order in a composite index?

A: Equality columns first, then range/sort columns. Among equality columns, put the most selective (highest cardinality) first. Example: (status, category, price) for WHERE status=? AND category=? AND price < ?.

Q2: What is the leftmost prefix rule?

A: A composite index on (A, B, C) can be used for queries on (A), (A, B), or (A, B, C) — but NOT (B), (C), or (B, C). The query must use the leftmost columns of the index. This is why column order matters critically.

13. Production Considerations

  • Audit query patterns first: Before creating composite/covering indexes, analyze actual queries with pg_stat_statements to identify the most common filter combinations.
  • Partial index maintenance: When the partial index WHERE clause becomes stale (e.g., status values change), the index may become less effective. Review periodically.
  • Covering index size trade-off: Adding many INCLUDE columns increases index size. Only include columns that are frequently selected together with the index key.
  • Test with EXPLAIN ANALYZE: Verify that your advanced indexes are actually used. The planner may prefer a different strategy based on table statistics.