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

From: Amin Jaffer <aminjaffer(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: Explain plan shows fewer shared blocks when index+table compared to index alone?
Date: 2023-04-04 06:21:11
Message-ID: CANaSfLAsterrqpNciuSQb2=k0rpNpLJo5ZR0CpK8uTVYGOjLcQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Sergei Kornilov 2023-04-04 07:51:03 Re:Explain plan shows fewer shared blocks when index+table compared to index alone?
Previous Message David Rowley 2023-03-15 21:20:23 Re: multicolumn partitioning help