Covering Index

Concept

In the previous section on Clustered vs Non-Clustered Indexes, we discussed the Bookmark Lookup problem.

If you have an index on email, and you run:
SELECT first_name, last_name FROM users WHERE email = 'alice@mail.com';

1. The database traverses the B-Tree index to find the email.
2. The index does NOT contain the first_name or last_name. It only contains the email and a pointer to the hard drive.
3. The database must execute an expensive physical disk jump (a Bookmark Lookup / Heap Fetch) to grab the actual row and extract the names.

A Covering Index is a highly specialized index designed to completely eliminate that physical disk jump.
If an index contains every single column requested in the SELECT, WHERE, and ORDER BY clauses, the query is “covered”. The database retrieves the answer purely from the index in RAM, completely ignoring the primary table.

How to Create a Covering Index

There are two ways to do this.

Approach 1: The Composite Index Hack

You simply create a Composite Index containing all the columns you need.

-- Create an index specifically built for our query
CREATE INDEX idx_email_names ON users(email, first_name, last_name);

Now, when you run SELECT first_name, last_name FROM users WHERE email = 'alice@mail.com', the database traverses the B-Tree looking for the email. When it finds the Leaf Node, the names are physically sitting right next to the email inside the index! It returns the data instantly. This is called an Index-Only Scan.

Approach 2: The INCLUDE Clause (Modern Standard)

The Composite Index hack is mathematically inefficient. The B-Tree forces itself to actively sort the first_name and last_name columns, which is unnecessary CPU work since we only filter by email.

Modern databases (PostgreSQL, SQL Server) introduced the INCLUDE clause. It allows you to attach “dumb” payload data to the Leaf Nodes of an index without sorting them.

-- This builds a B-Tree sorted ONLY by email.
-- It simply staples the names onto the leaf nodes as a free payload.
CREATE INDEX idx_email_payload ON users(email) 
INCLUDE (first_name, last_name);

Trade-Offs

• Pros: Unmatched, blistering read performance. It is the absolute fastest way a relational database can execute a query, frequently returning results in sub-milliseconds.
• Cons: Index Bloat. You are duplicating massive amounts of data. If you INCLUDE 5 massive VARCHAR columns in your index, you are effectively creating a second physical copy of the entire table on your hard drive. Furthermore, any time first_name is updated, the database must not only update the main table, but it must also update the INCLUDE payload in the index, slowing down UPDATE queries.

Interview Questions

Q: A developer adds SELECT * to a query that was previously using a Covering Index. The query execution time jumps from 2 milliseconds to 500 milliseconds. Why?
A: By changing the query to SELECT *, the query is no longer “covered”. The index only contains email, first_name, and last_name. Because the SELECT * demands the created_at timestamp, the password_hash, and 10 other columns, the database cannot fulfill the request purely from the index. It is forced to abandon the Index-Only Scan and revert to performing a painfully slow Bookmark Lookup/Heap Fetch to the physical hard drive to grab the missing columns. This is why SELECT * is an anti-pattern.

Q: When looking at an Execution Plan in PostgreSQL (using EXPLAIN), how do you definitively know if your query achieved a Covering Index optimization?
A: You will see the phrase Index Only Scan.
If you see Index Scan, it means the database used the index to find the row, but was forced to perform a Heap Fetch to grab additional missing columns. If you see Index Only Scan, it means the database never touched the physical table and served the entire query directly from the index’s B-Tree in RAM.