Thread: Explain plan shows fewer shared blocks when index+table compared to index alone?
Explain plan shows fewer shared blocks when index+table compared to index alone?
From
Amin Jaffer
Date:
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?
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
Re:Explain plan shows fewer shared blocks when index+table compared to index alone?
From
Sergei Kornilov
Date:
Hello This block is reading and checking the visibility map, I think. We don't have to check the visibility map during the indexscan - we still need to get a tuple from the table, we can check the visibility for current transaction there. Withindex only scan, we need to check the visibility map: if the tuple is visible to all transactions, then we return it.Otherwise, we read the tuple from the table as in the index scan (this is your case, as indicated by "Heap Fetches: 1") Index only scan does not mean that we will not read the tuple from the table. It means that we can skip reading the tableif the visibility map allows it for given tuple. regards, Sergei