Re: index only scan question

Daniel Westermann wrote:
> question: Given these steps:
>
>
> postgres=# explain (analyze,buffers,costs off) select a from t1 where b = 5;
> QUERY PLAN
> --------------------------------------------------------------------------
> Index Only Scan using i2 on t1 (actual time=0.056..0.058 rows=1 loops=1)
> Index Cond: (b = 5)
> Heap Fetches: 0
> Buffers: shared hit=4
> Planning time: 0.421 ms
> Execution time: 0.111 ms
> (6 rows)
>
> postgres=# update t1 set a = 30 where b = 5;
> UPDATE 1
> postgres=# explain (analyze,buffers,costs off) select a from t1 where b = 5;
> QUERY PLAN
> --------------------------------------------------------------------------
> Index Only Scan using i2 on t1 (actual time=0.039..0.042 rows=1 loops=1)
> Index Cond: (b = 5)
> Heap Fetches: 2
> Buffers: shared hit=5
> Planning time: 0.176 ms
> Execution time: 0.082 ms
>
> The 2 heap fetches for the second run are clear to me, because of the pointer from the old version of the row to the new one. But why does the next execution only need one heap fetch?
>
>
> postgres=# explain (analyze,buffers,costs off) select a from t1 where b = 5;
> QUERY PLAN
> --------------------------------------------------------------------------
> Index Only Scan using i2 on t1 (actual time=0.046..0.049 rows=1 loops=1)
> Index Cond: (b = 5)
> Heap Fetches: 1
> Buffers: shared hit=5
> Planning time: 0.194 ms
> Execution time: 0.097 ms
>
> Is that because of some sort of caching?

I'd say the old index tuple was killed during the first scan:
https://www.cybertec-postgresql.com/en/killed-index-tuples/

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com