1) Querying the table using the primary key and selecting one of the columns from the table which is not part of the index.
explain (analyze, buffers) select date_created from schema_name.table1 where id = 200889258190298;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Index Scan using pk_id on table1 (cost=0.58..8.60 rows=1 width=11) (actual time=0.015..0.016 rows=1 loops=1)
Index Cond: (id = '200889258190298'::numeric)
Buffers: shared hit=5
Planning Time: 0.059 ms
Execution Time: 0.029 ms
(5 rows)
2) Querying the table using the primary key and selecting a constant so it doesn't need to fetch data from the table.
explain (analyze, buffers) select 1 from schema_name.table1 where id = 200889258190298;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Index Only Scan using pk_id on table1 (cost=0.58..8.60 rows=1 width=4) (actual time=0.020..0.021 rows=1 loops=1)
Index Cond: (id = '200889258190298'::numeric)
Heap Fetches: 1
Buffers: shared hit=6
Planning Time: 0.054 ms
Execution Time: 0.029 ms
(6 rows)
I was expecting SQL (2) to report fewer shared hits compared to SQL (1) but seeing the opposite. As in case (1) it has to read index+tale and case 2 it only has to read the index. Both queries are querying the same row and using the same index.
Is there any reason why, shared hit is reported higher for "Index Only Scan" querying the index only?
Thank you