Explain plan shows fewer shared blocks when index+table compared to index alone?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux