On Tue, Jul 18, 2017 at 7:21 AM, Mikhail <bemewe@mail.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 msRunning 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.