Covering, Partial and Expression Indexes

Build covering indexes with INCLUDE to eliminate heap fetches, partial indexes to shrink index size for sparse queries, and expression indexes for case-insensitive search — each shown with a real query plan.

Most index advice stops at "add a B-tree and move on." This course goes one layer deeper: we look at three index shapes that each solve a specific problem B-tree alone cannot. We start with covering indexes — using INCLUDE to carry extra columns into the index leaves so a query never has to fetch the heap at all, landing an Index Only Scan. Then we build a partial index, pruning the index down to only the rows that actually matter for a query, and watch the size shrink from tens of megabytes to under three. We finish with expression indexes, where the index stores the result of a function — lower(email) — so a case-insensitive lookup hits the index instead of scanning every row. Every claim is backed by a real EXPLAIN ANALYZE, run on a live Postgres with a million orders.

What you'll build

• Build a covering index with INCLUDE and verify Index Only Scan with Heap Fetches: 0
• Explain the difference between a key column and an INCLUDE column
• Create a partial index with a WHERE predicate and measure the size reduction
• Predict when the planner will and will not use a partial index
• Build an expression index on lower(email) and get an Index Scan for case-insensitive lookup
• Measure index sizes with pg_relation_size
• Describe the read/write/storage trade-off for each index type

Contents

1. A database to experiment on
2. Wait for it to be ready
3. One command to bring it up
4. Start the server
5. A short way into the database
6. The customers table
7. The orders table
8. The tickets table
9. Create the tables
10. Seed the customers
11. Seed the orders
12. Seed the tickets
13. Load the data
14. Fresh statistics and a single-threaded planner
15. Confirm what we are sitting on
16. The query a covering index is built for
17. No index, no shortcut
18. A composite index on customer and date
19. The index eliminates the scan, but not all heap work
20. The query for Index Only Scan
21. INCLUDE — carry the column without making it a key
22. Refresh the visibility map
23. Heap Fetches: 0
24. Measure what INCLUDE costs
25. The covering index is 39 MB
26. The query a partial index is built for
27. The plan before any index
28. A full index on status for comparison
29. Better, but the index carries dead weight
30. A partial index with a WHERE predicate
31. The planner picks the partial index
32. Measure the size reduction
33. 2 MB versus 38 MB
34. A query that does not match the predicate
35. The partial index is invisible
36. The query an expression index is built for
37. Seq Scan on customers
38. An index on the expression, not the column
39. Same query, new index
40. Index Scan using customers_lower_email_idx
41. When the expression does not match
42. Seq Scan again — the expression did not match
43. Three questions before creating an index
44. Your own plan-reading tool