Composite and Covering Indexes
Speed up multi-column queries with composite indexes and eliminate table lookups entirely using covering indexes with INCLUDE.
Composite and Covering Indexes is a free PostgreSQL Performance & Query Optimization lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the PostgreSQL Performance & Query Optimization learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
Beyond Single-Column Indexes
Many queries filter on more than one column. A composite index spans multiple columns and can serve such queries far better than separate single-column indexes.
Creating a Composite Index
List columns in the order you want them indexed.
CREATE INDEX idx_orders_cust_date
ON orders (customer_id, order_date);Column Order Matters
A composite index on (a, b) helps queries filtering on a alone or a AND b, but generally NOT queries filtering only on b.
The Leftmost Prefix Rule
PostgreSQL can use any leftmost prefix of the index. So (a, b, c) serves filters on a, on a+b, and on a+b+c.
Ordering by Selectivity
Put the most selective or most frequently filtered column first, often an equality column before a range column, to maximize how many rows the index eliminates early.
CREATE INDEX idx_orders_status_date
ON orders (status, order_date);The Heap Lookup Cost
A normal index scan finds matching rows then visits the table (the heap) to read the other columns. Those extra fetches cost I/O.
Covering Indexes
A covering index contains every column the query needs, so PostgreSQL answers from the index alone, an Index Only Scan, with no heap visit.
Using INCLUDE
The INCLUDE clause adds non-key columns to the index leaf pages so they are available without being part of the searchable key.
CREATE INDEX idx_orders_cust_incl
ON orders (customer_id)
INCLUDE (order_date, total);Confirming Index Only Scan
Check the plan; you want to see Index Only Scan rather than Index Scan plus heap fetches.
EXPLAIN ANALYZE
SELECT order_date, total
FROM orders WHERE customer_id = 42;The Visibility Map Caveat
Index Only Scans still need the heap when pages are not marked all-visible. Keeping tables well-vacuumed maintains the visibility map so scans stay index-only.
VACUUM ANALYZE orders;Costs of Wide Indexes
More columns mean larger indexes and slower writes. Add covering columns only for hot queries, not everywhere.
Quick Check
Test what you have learned.
Recap
You learned how composite indexes serve multi-column filters under the leftmost prefix rule, how column order and selectivity matter, and how covering indexes with INCLUDE enable Index Only Scans, balanced against larger size and write cost.
Frequently asked questions
Is the “Composite and Covering Indexes” lesson free?
Yes — the full text of “Composite and Covering Indexes” is free to read here on the web, and the PostgreSQL Performance & Query Optimization course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the PostgreSQL Performance & Query Optimization course, upgrade to CoddyKit PRO.
What will I learn in “Composite and Covering Indexes”?
Speed up multi-column queries with composite indexes and eliminate table lookups entirely using covering indexes with INCLUDE. You practise PostgreSQL Performance & Query Optimization with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start PostgreSQL Performance & Query Optimization?
No prior experience is required. PostgreSQL Performance & Query Optimization on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Composite and Covering Indexes” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this PostgreSQL Performance & Query Optimization lesson?
Yes. Every PostgreSQL Performance & Query Optimization lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- B-Tree Indexes Fundamentals
- Creating and Using Indexes
- When and How to Index
- Composite and Covering Indexes