Mastering PostgreSQL 9.6

Making use of index only scans

So far, you have seen when an index is used and when it is not. In addition to that, 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