Re: Resurrecting per-page cleaner for btree

Bruce Momjian <bruce@momjian.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