Re: Index Only Scan and Heap Fetches

On Tue, Jul 18, 2017 at 7:21 AM, Mikhail <bemewe(at)mail(dot)ru> wrote:

> Hi guys,
>
> I'm running the process, that executes "select * from sr where sr.id=210
> for update;", then some calculations and finally "update sr set usage =
> <somevalue> where sr.id = 210;". That operation is done in a loop.
>
> In parallel session i'm running the query:
>
> test=# explain (analyze, buffers) select id from sr where id = 210;
> QUERY PLAN
>
> ------------------------------------------------------------
> ----------------------------------------------------------
> ----------------
> Index Only Scan using sr_pk on sr (cost=0.57..8.59 rows=1 width=4) (actual
> time=0.018..1.172 rows=1 loops=1)
> Index Cond: (id = 210)
> Heap Fetches: 10
> Buffers: shared hit=592
> Planning time: 0.057 ms
> Execution time: 1.183 ms
> Running that several times I can see, that the number of "Heap Fetches" is
> varying in some range (from 1 to ~80-100), sequentaly growing till
> ~(80-100) than starting from 1.
> Considering that the autovacuum process is turned off (for research
> purposes only :) ), I was expecting the infinite growth of Heap
> Fetches since no cleaning of dead rows or visibility map support occurs.
>
> Can someone explain, what else can decrease the number of heap access
> needed to check the rows visibility?
>

Btree indexes have a micro-vacuum feature. If you visit a heap tuple based
on reference from an index tuple, and find that the heap tuple is
dead-to-all, then when you get back to the index you can kill that index's
reference to the heap tuple. Future accesses via that same index for the
same tuple then no longer need to visit the heap.

Cheers,

Jeff