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
