Case 8: Index Scan vs. Index-Only Scan (The Final Boss of SELECT Optimization)

The Story

• Table: user_profiles, a huge table with 80,000,000 rows of user profile data.
• Columns: id, user_id, last_seen_at, reputation_score, country_code.
• Current State: To build a leaderboard of recently active users, there's an index on last_seen_at.
  • CREATE INDEX idx_profiles_last_seen ON user_profiles(last_seen_at DESC);
• The Problem: We need to get the user_id and reputation_score of the 100 most recently active users for the homepage.

The (Almost) Optimized SQL Query

This query looks simple and already uses the existing index.

EXPLAIN ANALYZE
SELECT user_id, reputation_score
FROM user_profiles
ORDER BY last_seen_at DESC
LIMIT 100;

The Query Plan (BEFORE the final optimization)

The planner will use the last_seen_at index to find the 100 rows.

                                                              QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..25.54 rows=100 width=12) (actual time=0.035..0.455 rows=100 loops=1)
   ->  Index Scan using idx_profiles_last_seen on user_profiles  (cost=0.57..204500.21 rows=80000000 width=12) (actual time=0.033..0.430 rows=100 loops=1)
 Planning Time: 0.120 ms
 Execution Time: 0.485 ms

A time of 0.485ms is incredibly fast! So why can we still optimize this?

The (Hidden) Bottleneck

1. Look at this: Index Scan. This plan means:

   • Step 1 (Read the Index): PostgreSQL scans the idx_profiles_last_seen index to find the 100 newest last_seen_at entries.
   • Step 2 (Heap Fetch / Table Lookup): This index only contains last_seen_at data and a pointer to the full row. It does not contain user_id or reputation_score. So, for each of the 100 entries it finds in the index, PostgreSQL must perform an extra I/O operation called a "heap fetch" to go back to the main table and get the values from the other two columns.

2. The Problem: Even though it's fast, this query is making 100 unnecessary heap fetches. Each fetch is a separate read from a potentially different location on disk, which can cause random I/O. If we can eliminate these 100 extra reads, the performance will be absolute perfection.

The Solution: Use a Covering Index (with the INCLUDE clause)

• The Logic: Create an index that "covers" all the columns the query needs. When that happens, PostgreSQL can answer the entire query by only reading the index and never touching the main table.

• The Optimized Command:

  -- (Optional) Drop the old index
  DROP INDEX idx_profiles_last_seen;
  
  -- Create the new covering index
  CREATE INDEX idx_profiles_last_seen_covering
  ON user_profiles(last_seen_at DESC)
  INCLUDE (user_id, reputation_score);

• What does INCLUDE do?: It attaches the user_id and reputation_score data to the index as "payload." These included columns are not part of the B-Tree structure used for searching, but they are right there, available immediately when the index is scanned.

The Query Plan (AFTER the fix)

Now, the execution plan changes magically.

                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..4.85 rows=100 width=12) (actual time=0.025..0.055 rows=100 loops=1)
   ->  Index Only Scan using idx_profiles_last_seen_covering on user_profiles  (cost=0.42..37150.15 rows=80000000 width=12) (actual time=0.023..0.048 rows=100 loops=1)
         Heap Fetches: 0
 Planning Time: 0.180 ms
 Execution Time: 0.075 ms

Analyzing the Result

1. The Big Change: The plan switched from Index Scan to Index-Only Scan.
2. Heap Fetches: 0: This is the most important metric here. It confirms that PostgreSQL got all the information it needed directly from the index and never had to visit the main table.
3. Performance Comparison:
   • Execution Time: Dropped from 0.485 ms to just 0.075 ms. That's almost 7 times faster. While the absolute numbers are tiny, in a high-traffic system running thousands of queries per second, this difference is enormous.

Conclusion

• An Index-Only Scan is the ultimate goal for an optimized SELECT query.
• When you have a frequent and performance-critical query, check if it can be improved with a Covering Index.
• Using the INCLUDE clause is the modern, efficient way to create a Covering Index without bloating the main structure of the index itself.