Vacuuming big btree indexes without pages with deleted items - Mailing list pgsql-hackers

Hi all.<div class=""><br class="" /></div><div class="">I have described [0] a problem with delaying replicas after
vacuuminga relation with big btree index. It stucks in replaying WAL record of type XLOG_BTREE_VACUUM like that (with
lastBlockVacuumed0):</div><div class=""><br class="" /></div><div class="">rmgr: Btree       len (rec/tot):     20/  
 52,tx:          0, lsn: 4115/56126DC0, prev 4115/56126D90, bkp: 0000, desc: vacuum: rel 1663/16420/16796; blk
31222118,lastBlockVacuumed 0<br class="" /><div class=""><br class="webkit-block-placeholder" /></div><div
class="">Masterwrites this record to xlog in btvacuumscan [1] function after vacuuming of all index pages. And in case
ofno pages with deleted items xlog record would contain lastBlockVacuumed 0.</div><div class=""><br class=""
/></div><divclass="">In btree_xlog_vacuum [2] replica reads all blocks from lastBlockVacuumed to last block of the
indexwhile applying this record because there is no api in the buffer manager to understand if the page is
unpinned. </div><divclass=""><br class="" /></div><div class="">So if the index is quite big (200+ GB in described
case)it takes much time to do it. So the questions are:</div><div class=""><br class="" /></div><div class="">1. Aren’t
therestill any api in buffer manager to understand that the page is not in shared_buffers without reading it?</div><div
class="">2.Is it possible not to write to xlog record with lastBlockVacuumed 0 in some cases? For example, in case of
notdeleting any pages.</div><div class=""><br class="" /></div><div class="">Or maybe there are some better ways of
improvingthis situation?</div><div class=""><br class="" /></div><div class="">[0] <a class=""
href="http://www.postgresql.org/message-id/FE82A9A7-0D52-41B5-A9ED-967F6927CB8A@simply.name">http://www.postgresql.org/message-id/FE82A9A7-0D52-41B5-A9ED-967F6927CB8A@simply.name</a></div><div
class="">[1] <aclass=""
href="http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/nbtree/nbtree.c;hb=refs/heads/REL9_4_STABLE#l813">http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/nbtree/nbtree.c;hb=refs/heads/REL9_4_STABLE#l813</a></div><div
class="">[2] <aclass=""
href="http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/nbtree/nbtxlog.c;hb=refs/heads/REL9_4_STABLE#l482">http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/access/nbtree/nbtxlog.c;hb=refs/heads/REL9_4_STABLE#l482</a></div><div
apple-content-edited="true"class=""><div class="" style="color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px;
font-style:normal; font-variant: normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans:
auto;text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px;
-webkit-text-stroke-width:0px;"><br class="Apple-interchange-newline" />--</div><div class="" style="color: rgb(0, 0,
0);font-family: Helvetica; font-size: 12px; font-style: normal; font-variant: normal; font-weight: normal;
letter-spacing:normal; line-height: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none;
white-space:normal; widows: auto; word-spacing: 0px; -webkit-text-stroke-width: 0px;">May the force be with
you…</div><divclass="" style="color: rgb(0, 0, 0); font-family: Helvetica; font-size: 12px; font-style: normal;
font-variant:normal; font-weight: normal; letter-spacing: normal; line-height: normal; orphans: auto; text-align:
start;text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px;
-webkit-text-stroke-width:0px;"><a class="" href="https://simply.name">https://simply.name</a></div></div><br class=""
/></div>