Making use of index-only scans

So far, you have seen when an index is used and when it is not. In addition to this, bitmap scans have been discussed.

However, there is more to indexing. The following two examples will only differ slightly, although the performance difference might be fairly large. Here is the first query:

test=# EXPLAIN SELECT * FROM t_test WHERE id = 34234;  
                           QUERY PLAN 
---------------------------------------------------------------- 
 Index Scan using idx_id on t_test 
   (cost=0.43..8.45 rows=1 width=9) 
   Index Cond: (id = 34234)

There is nothing unusual here. PostgreSQL uses an index to find a single row. What happens if only a single column is selected?

test=# EXPLAIN SELECT id FROM t_test WHERE id = 34234; 
                         QUERY PLAN 
---------------------------------------------------------------- 
 Index Only Scan using idx_id on t_test 
   (cost=0.43..8.45 rows=1 width=4) 
   Index Cond: (id = 34234) 
(2 rows)

As you can see, the plan has changed from an index scan to an index-only scan. In our example, the id column has been indexed, so its content is naturally in the index. There is no need to go to the table in most cases if all the data can already be taken out of the index. Going to the table is (almost) only required if additional fields are queried, which is not the case here. Therefore, the index-only scan will promise significantly better performance than a normal index scan.

Practically, it can even make sense to include an additional column in an index here and there to enjoy the benefit of this feature. In MS SQL, adding additional columns is known as covering indexes. Since PostgreSQL 11, we have the same functionality, which uses the INCLUDE keyword in CREATE INDEX.