Indexing
Composite, Covering, and Partial Indexes
Fine-tuning indexes to cover specific query combinations.
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
5. Internal Architecture
6. Visual Explanation
7. Practical Example
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?
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.