Database Indexing – Part 2: Understanding Covering Indexes in PostgreSQL

Recently, while working on a performance optimization task, I came across an interesting problem.

I had a query where I needed to fetch document versions for around 6,500 document IDs.

Something like this:

SELECT document_id, document_version
FROM documents
WHERE document_id IN (101, 102, 103, ..., 6500);

This looks like a simple query, right?

But the entire operation was taking around 4 seconds. When I checked where the time was being spent, I noticed that this single database query was taking almost 85% of the total time.

That's around 3.4 seconds for just one database query!

So, I started looking for ways to reduce the query execution time.

My first thought was to create an index on both document_id and document_version.

CREATE INDEX idx_document_version
ON documents (document_id, document_version);

But I'm filtering only using document_id. I just need document_version in the result.

Also, document_version changes frequently, so I was concerned about the additional index maintenance during updates.

I wanted to know whether both columns really needed to be part of the index key.

PostgreSQL also has an INCLUDE option, where we can store additional columns in an index without making them key columns.

CREATE INDEX idx_document_version
ON documents (document_id) INCLUDE (document_version);

Both indexes contain the columns required by my query. But how are they different internally? Does using INCLUDE make any difference to query performance, index size, or updates?

Let's look at how both indexes work.

1. Understanding Covering Indexes

A covering index is an index that contains all the columns required by a query, so PostgreSQL may be able to return the result without fetching the actual row from the table.

For example, consider this query:

SELECT document_id, document_version
FROM documents
WHERE document_id = 500;

Here, we're using document_id to search and document_version to get the required value.

If we have an index only on document_id, PostgreSQL can use it to find the matching record. But it still needs to visit the heap to fetch document_version.

What if both values were already available in the index?

We could create an index like this:

CREATE INDEX idx_document_version
ON documents (document_id) INCLUDE (document_version);

Here, we have two types of columns.

1. Key Column — document_id

• Used to search and navigate through the B-tree.
• Determines the ordering of index entries.
• Can be used for filtering and sorting.

2. Non-Key Column — document_version

• Stored along with the key in the leaf pages.
• Doesn't participate in B-tree searching or ordering.
• Can be returned from the index without fetching that value from the heap.

How Are These Columns Stored?

PostgreSQL's B-tree has three types of pages: root, internal, and leaf pages.

In an index created using INCLUDE:

• Root and internal pages contain navigation keys based on document_id and pointers to lower-level pages.
• Leaf pages contain document_id, the included document_version, and a reference to the corresponding heap tuple.

So, PostgreSQL searches the B-tree using document_id and finds document_version in the matching leaf entry.

Since both required columns are available in the index, PostgreSQL may be able to return the result without fetching the actual row from the heap. This is called an Index Only Scan.

2. Testing Covering Indexes in PostgreSQL

Let's create a documents table with 10 million records and compare how PostgreSQL performs with a primary key index and an index using INCLUDE.

Step 1: Create the Table

CREATE TABLE documents (
    document_id BIGINT PRIMARY KEY,
    document_name TEXT,
    document_version INTEGER
);

Since document_id is the primary key, PostgreSQL automatically creates a B-tree index named documents_pkey.

Step 2: Insert 10 Million Records

INSERT INTO documents (
    document_id,
    document_name,
    document_version
)
SELECT
    g,
    'Document_' || g,
    (random() * 10)::INTEGER + 1
FROM generate_series(1, 10000000) AS g;

Now, our table contains 10 million documents.

Let's fetch document versions for 6,500 document IDs and compare both approaches.

Query 1: Using the Primary Key Index

EXPLAIN (ANALYZE, BUFFERS)
SELECT document_id, document_version
FROM documents
WHERE document_id = ANY(
    ARRAY(
        SELECT generate_series(1, 6500)::BIGINT
    )
);

Output:

PostgreSQL used an Index Scan.

The primary key index contains document_id, but not document_version.

So PostgreSQL:

1. Searches the index for the matching document IDs.
2. Gets the corresponding heap tuple references.
3. Visits the heap to fetch document_version.

The query returned 6,500 records and took 11.028 ms.

Query 2: Creating an Index with INCLUDE

Now, let's create an index that also contains document_version.

CREATE INDEX idx_documents_covering
ON documents (document_id) INCLUDE (document_version);

Here, document_id is the key column, while document_version is a non-key column stored in the leaf pages.

Let's execute the same query again.

EXPLAIN (ANALYZE, BUFFERS)
SELECT document_id, document_version
FROM documents
WHERE document_id = ANY(
    ARRAY(
        SELECT generate_series(1, 6500)::BIGINT
    )
);

Output:

This time, PostgreSQL used an Index Only Scan.

Since both document_id and document_version are available in the index, PostgreSQL was able to return the required values without visiting the heap in this execution.

Notice:

Heap Fetches: 0

The query took 9.144 ms.

Comparing the Execution Times

Metric	Primary Key Index	Index with INCLUDE
Scan type	Index Scan	Index Only Scan
Rows returned	6,500	6,500
Execution time	11.028 ms	9.144 ms

In this experiment, the execution time decreased from 11.028 ms to 9.144 ms, approximately a 17% improvement.

With the primary key index, PostgreSQL had to visit the heap to fetch document_version.

But with INCLUDE, both required columns were available in the index, and PostgreSQL avoided those heap fetches.

A Note on Caching

I tested both approaches on my local Ubuntu machine. Before each test, I restarted PostgreSQL and cleared the Linux filesystem cache using the following commands:

sudo systemctl stop postgresql
sync
echo 3 | sudo tee /proc/sys/vm/drop_caches
sudo systemctl start postgresql

These commands are only intended for a dedicated local test environment, not a production or shared server.

The covering index improved query performance in our experiment by avoiding heap fetches.

However, since we can't completely eliminate caching effects, it's difficult to say exactly how much improvement we can expect.

These results are from my local system, and the actual performance improvement may vary depending on the environment and workload.

3. Why Can an Index Only Scan Still Have Heap Fetches?

In our previous query, PostgreSQL used an Index Only Scan with Heap Fetches: 0.

But an Index Only Scan doesn't always mean PostgreSQL will completely avoid accessing the heap.

Let's update some records and check.

UPDATE documents
SET document_version = document_version + 1
WHERE document_id BETWEEN 1 AND 6500;

Now, let's execute the same query again.

EXPLAIN (ANALYZE, BUFFERS)
SELECT document_id, document_version
FROM documents
WHERE document_id = ANY(
    ARRAY(
        SELECT generate_series(1, 6500)::BIGINT
    )
);

Output:

PostgreSQL still used an Index Only Scan, but this time:

Heap Fetches: 13000

We updated 6,500 records, but PostgreSQL performed 13,000 heap fetches.

Why?

How Does PostgreSQL Check Row Visibility?

PostgreSQL uses MVCC (Multi-Version Concurrency Control) to manage updates and transactions.

When we update a row, PostgreSQL creates a new row version instead of overwriting the existing one.

The index contains the values we need, but it doesn't have enough information to determine whether a row version is visible to the current transaction.

For this, PostgreSQL maintains a Visibility Map.

The visibility map keeps track of which heap pages contain only row versions that are visible to all transactions.

• If a heap page is marked all-visible, PostgreSQL can return the required values from the index without checking the heap.
• If a heap page isn't marked all-visible, PostgreSQL needs to visit the heap to check row visibility.

What Happened After the UPDATE?

When we updated those 6,500 records, PostgreSQL created new row versions and cleared the all-visible flags for the affected heap pages.

Since document_version is included in our index, the update also required new index entries.

During the next Index Only Scan, PostgreSQL had to check the heap for row visibility.

That's why we got:

Heap Fetches: 13000

These are heap tuple fetch attempts, not 13,000 different heap pages.

What About VACUUM?

PostgreSQL provides VACUUM to clean up dead tuples so their space can be reused. It also updates the visibility map when pages qualify as all-visible.

VACUUM (ANALYZE) documents;

This can help reduce heap fetches during Index Only Scans, although it doesn't guarantee that they will become zero.

We'll cover how VACUUM works internally in a separate article.

Conclusion

Covering indexes can help reduce heap access when all the required columns are available in the index.

But indexes also come with a cost.

Even though INCLUDE columns are not part of the index key, PostgreSQL still needs to store and maintain their values. If an included column is updated frequently, it can increase write overhead.

So, we need to consider both read and write performance before creating additional indexes.

In the next article, we'll look at composite indexes, how column order affects index usage, and why the order of columns matters when filtering and sorting records.

That's it for now!

Thanks for reading! 😊