Re: Resurrecting per-page cleaner for btree

Bruce Momjian <bruce(at)momjian(dot)us> wrote:

> The attached patch requires the new row to fit, and 10% to be free on
> the page. Would someone test that?

This is another solution for the same purpose. We can avoid to call
PageIndexMultiDelete() to remove only one tuple. _bt_split() becomes
to ignore tuples with LP_DELETE because such tuples are passed now.
The cleaner threshold (currently 2) might have to be more tweaked.

Can this change resolve your performance problem?

diff -cpr pgsql-orig/src/backend/access/nbtree/nbtinsert.c pgsql/src/backend/access/nbtree/nbtinsert.c
*** pgsql-orig/src/backend/access/nbtree/nbtinsert.c Wed Jul 26 11:15:20 2006
--- pgsql/src/backend/access/nbtree/nbtinsert.c Thu Jul 27 18:27:25 2006
*************** _bt_split(Relation rel, Buffer buf, Offs
*** 788,793 ****
--- 788,798 ----
for (i = P_FIRSTDATAKEY(oopaque); i <= maxoff; i = OffsetNumberNext(i))
{
itemid = PageGetItemId(origpage, i);
+
+ /* We can ignore items with LP_DELETE. */
+ if (ItemIdDeleted(itemid))
+ continue;
+
itemsz = ItemIdGetLength(itemid);
item = (IndexTuple) PageGetItem(origpage, itemid);

*************** _bt_vacuum_one_page(Relation rel, Buffer
*** 1707,1713 ****
deletable[ndeletable++] = offnum;
}

! if (ndeletable > 0)
_bt_delitems(rel, buffer, deletable, ndeletable);
/*
* Note: if we didn't find any LP_DELETE items, then the page's
--- 1712,1723 ----
deletable[ndeletable++] = offnum;
}

! /*
! * We delete items if there are at least 2 deletable index tuples
! * because scanning the page over and over again to get just a little
! * free space is inefficient.
! */
! if (ndeletable >= 2)
_bt_delitems(rel, buffer, deletable, ndeletable);
/*
* Note: if we didn't find any LP_DELETE items, then the page's

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center