How Covering Indexes Can Eliminate Table Reads in YugabyteDB

In the YugabyteDB Tip, How ORDER BY LIMIT Can Change the Best Index in YugabyteDB, the fitth tip in our index tuning series, we dramatically improved our query by creating an index that matches the query’s ordering:

				
					CREATE INDEX activity_log_tenant_created_cover_idx
ON app.activity_log (tenant_id, created_at)
INCLUDE (status, category);

This allowed YugabyteDB to scan rows in the correct order and stop as soon as it found the first 100 matches.

The result was a major improvement:

Plan	Rows Scanned	Execution Time
`(tenant_id, status, category)`	~59,689 table rows ~59,689 index rows	~524 ms
`(tenant_id, created_at) INCLUDE (status, category)`	100 table rows 306 index rows	~3.2 ms

However, the plan still shows something interesting:

				
					Storage Table Rows Scanned: 100

This means YugabyteDB still needs to fetch rows from the base table.

Why?

⚡ Key Idea

A query can avoid table reads entirely if every column required by the query is stored in the index.

🧭 The Index Tuning Journey

This series shows how index tuning usually happens in stages. Each tip removes one layer of wasted work so YugabyteDB can answer the query more efficiently.

Tip	Focus	What Improves
1	Choose the right column order in an index	The index matches the query shape better
2	Why your index scan still reads thousands of rows	Shows why index usage alone is not enough
3	How to eliminate “Rows Removed by Filter”	More filtering happens in the index before table reads
4	How IN() predicates create multiple index ranges	Multiple predicate combinations create distinct index ranges
5	How ORDER BY LIMIT Can Change the Best Index in YugabyteDB	Sort work can be reduced or eliminated
6	How covering indexes eliminate table reads	The query may avoid table access entirely
7	Designing the best index for a query	All tuning ideas come together into one practical design

Why Table Reads Still Happen

Our query selects:

				
					SELECT *

But the index only contains:

				
					tenant_id
created_at
status
category

The table still contains additional columns:

				
					id
updated_at
user_id
source_id

Because those columns are not present in the index, YugabyteDB must still fetch the base table rows.

That is why the plan shows:

				
					Storage Table Rows Scanned: 100

Creating a Fully Covering Index

To eliminate those table reads, we extend the index so that it includes every column referenced by the query:

				
					CREATE INDEX activity_log_full_cover_idx
ON app.activity_log (tenant_id, created_at)
INCLUDE (status, category, id, updated_at, user_id, source_id);

Now the index contains all columns referenced by the query.

In this situation, YugabyteDB can perform what is commonly called an index-only scan.

📌 What a Covering Index Does

A covering index stores all columns required by a query so the database can answer the query directly from the index without reading the table.

The New Query Plan

Remember that the query we’ve been using is:

				
					SELECT *
FROM app.activity_log
WHERE tenant_id =
'11111111-1111-1111-1111-111111111111'
AND category IN ('LOGIN','EXPORT','SYNC')
AND status IN ('PENDING','RUNNING')
ORDER BY created_at
LIMIT 100;

With the fully covering index in place, YugabyteDB now produces an Index Only Scan:

				
					Limit  (actual time=1.240..1.350 rows=100 loops=1)
  ->  Index Only Scan using activity_log_full_cover_idx on activity_log
        Index Cond: (tenant_id = '11111111-1111-1111-1111-111111111111')
        Storage Filter: ((status = ANY ('{PENDING,RUNNING}'))
                      AND (category = ANY ('{LOGIN,EXPORT,SYNC}')))
        Heap Fetches: 0
        Storage Index Rows Scanned: 306
Execution Time: 1.403 ms

Several important things happened:

● The plan now shows Index Only Scan
● Heap Fetches: 0 confirms the table is no longer accessed
● The engine still stops after finding 100 matching rows
● Only 306 index rows are scanned

🔍 Index-Only Scan

An index-only scan occurs when the database can answer a query entirely from the index without reading rows from the base table.

Why This Matters

Table reads are often the most expensive part of a query.

Even in our optimized Tip #5 plan, YugabyteDB still performed:

				
					Storage Table Rows Scanned: 100

By eliminating those reads entirely, the query now runs faster while scanning the same 306 index rows.

Execution time drops again:

~3.2 ms → ~1.4 ms

Practical Design Tradeoff

Although covering indexes can be powerful, they also have costs.

Adding many columns to an index increases:

• index size
• write amplification
• maintenance overhead

Because of this, covering indexes are usually designed for specific high-value queries, not for every query.

⚠️ Design Reminder

Covering indexes can dramatically improve read performance, but they increase index size and write overhead. Use them carefully for queries that run frequently or are latency-sensitive.

Conclusion

In Tip #5, we aligned the index with the query’s ordering so YugabyteDB could stop scanning early.

In Tip #6, we completed the optimization by making the index fully covering.

The new plan shows an actual Index Only Scan with:

				
					Heap Fetches: 0

That means the query is now answered entirely from the index, without reading the base table at all.

Execution time drops agai… from ~3.2 ms to ~1.4 ms … while scanning only 306 index rows.

Have Fun!