Обсуждение: index only scan question

Hi quick

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
newone. 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?

Thanks in advance
Daniel

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
newone. 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

Am 09.11.2018 um 13:58 schrieb Daniel Westermann:
> Is that because of some sort of caching?

no, but vacuum updated the visibility map in the meantime.

Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com

Daniel Westermann wrote:
> >I'd say the old index tuple was killed during the first scan:
> >https://www.cybertec-postgresql.com/en/killed-index-tuples/
> 
> ... from your blog: "Whenever an index scan fetches a heap tuple only to find that it is dead
> (that the entire “HOT chain” of tuples is dead, to be more precise), it marks the index tuple
> as “killed”. Then future index scans can simply ignore it.
> 
> I understand that, but in my case the chain is not dead so this does not explain the difference.
> Do I miss something?

I assume that the UPDATE was not HOT, because the first scan had to fetch two tuples.

After the UPDATE, the original tuple was dead (the HOT chain consists
only of a single tuple here, because it was no HOT update).
The first index scan detects that and marks the index tuple as killed.
The second index scan only visits the new tuple.

Yours,
Laurenz Albe