Re: GiST indexes and concurrency (tsearch2) - Mailing list pgsql-performance

Marinos Yannikos <mjy@geizhals.at> writes:
> Some more things I tried:

You might try the attached patch (which I just applied to HEAD).
It cuts down the number of acquisitions of the BufMgrLock by merging
adjacent bufmgr calls during a GIST index search.  I'm not hugely
hopeful that this will help, since I did something similar to btree
last spring without much improvement for context swap storms involving
btree searches ... but it seems worth trying.

            regards, tom lane

*** src/backend/access/gist/gistget.c.orig    Fri Dec 31 17:45:27 2004
--- src/backend/access/gist/gistget.c    Sat Feb  5 14:19:52 2005
***************
*** 60,69 ****
      BlockNumber blk;
      IndexTuple    it;

      b = ReadBuffer(s->indexRelation, GISTP_ROOT);
      p = BufferGetPage(b);
      po = (GISTPageOpaque) PageGetSpecialPointer(p);
-     so = (GISTScanOpaque) s->opaque;

      for (;;)
      {
--- 60,70 ----
      BlockNumber blk;
      IndexTuple    it;

+     so = (GISTScanOpaque) s->opaque;
+
      b = ReadBuffer(s->indexRelation, GISTP_ROOT);
      p = BufferGetPage(b);
      po = (GISTPageOpaque) PageGetSpecialPointer(p);

      for (;;)
      {
***************
*** 75,86 ****

          while (n < FirstOffsetNumber || n > maxoff)
          {
!             ReleaseBuffer(b);
!             if (so->s_stack == NULL)
                  return false;

!             stk = so->s_stack;
!             b = ReadBuffer(s->indexRelation, stk->gs_blk);
              p = BufferGetPage(b);
              po = (GISTPageOpaque) PageGetSpecialPointer(p);
              maxoff = PageGetMaxOffsetNumber(p);
--- 76,89 ----

          while (n < FirstOffsetNumber || n > maxoff)
          {
!             stk = so->s_stack;
!             if (stk == NULL)
!             {
!                 ReleaseBuffer(b);
                  return false;
+             }

!             b = ReleaseAndReadBuffer(b, s->indexRelation, stk->gs_blk);
              p = BufferGetPage(b);
              po = (GISTPageOpaque) PageGetSpecialPointer(p);
              maxoff = PageGetMaxOffsetNumber(p);
***************
*** 89,94 ****
--- 92,98 ----
                  n = OffsetNumberPrev(stk->gs_child);
              else
                  n = OffsetNumberNext(stk->gs_child);
+
              so->s_stack = stk->gs_parent;
              pfree(stk);

***************
*** 116,123 ****
              it = (IndexTuple) PageGetItem(p, PageGetItemId(p, n));
              blk = ItemPointerGetBlockNumber(&(it->t_tid));

!             ReleaseBuffer(b);
!             b = ReadBuffer(s->indexRelation, blk);
              p = BufferGetPage(b);
              po = (GISTPageOpaque) PageGetSpecialPointer(p);
          }
--- 120,126 ----
              it = (IndexTuple) PageGetItem(p, PageGetItemId(p, n));
              blk = ItemPointerGetBlockNumber(&(it->t_tid));

!             b = ReleaseAndReadBuffer(b, s->indexRelation, blk);
              p = BufferGetPage(b);
              po = (GISTPageOpaque) PageGetSpecialPointer(p);
          }
***************
*** 137,142 ****
--- 140,147 ----
      BlockNumber blk;
      IndexTuple    it;

+     so = (GISTScanOpaque) s->opaque;
+
      blk = ItemPointerGetBlockNumber(&(s->currentItemData));
      n = ItemPointerGetOffsetNumber(&(s->currentItemData));

***************
*** 148,154 ****
      b = ReadBuffer(s->indexRelation, blk);
      p = BufferGetPage(b);
      po = (GISTPageOpaque) PageGetSpecialPointer(p);
-     so = (GISTScanOpaque) s->opaque;

      for (;;)
      {
--- 153,158 ----
***************
*** 157,176 ****

          while (n < FirstOffsetNumber || n > maxoff)
          {
!             ReleaseBuffer(b);
!             if (so->s_stack == NULL)
                  return false;

!             stk = so->s_stack;
!             b = ReadBuffer(s->indexRelation, stk->gs_blk);
              p = BufferGetPage(b);
-             maxoff = PageGetMaxOffsetNumber(p);
              po = (GISTPageOpaque) PageGetSpecialPointer(p);

              if (ScanDirectionIsBackward(dir))
                  n = OffsetNumberPrev(stk->gs_child);
              else
                  n = OffsetNumberNext(stk->gs_child);
              so->s_stack = stk->gs_parent;
              pfree(stk);

--- 161,183 ----

          while (n < FirstOffsetNumber || n > maxoff)
          {
!             stk = so->s_stack;
!             if (stk == NULL)
!             {
!                 ReleaseBuffer(b);
                  return false;
+             }

!             b = ReleaseAndReadBuffer(b, s->indexRelation, stk->gs_blk);
              p = BufferGetPage(b);
              po = (GISTPageOpaque) PageGetSpecialPointer(p);
+             maxoff = PageGetMaxOffsetNumber(p);

              if (ScanDirectionIsBackward(dir))
                  n = OffsetNumberPrev(stk->gs_child);
              else
                  n = OffsetNumberNext(stk->gs_child);
+
              so->s_stack = stk->gs_parent;
              pfree(stk);

***************
*** 198,205 ****
              it = (IndexTuple) PageGetItem(p, PageGetItemId(p, n));
              blk = ItemPointerGetBlockNumber(&(it->t_tid));

!             ReleaseBuffer(b);
!             b = ReadBuffer(s->indexRelation, blk);
              p = BufferGetPage(b);
              po = (GISTPageOpaque) PageGetSpecialPointer(p);

--- 205,211 ----
              it = (IndexTuple) PageGetItem(p, PageGetItemId(p, n));
              blk = ItemPointerGetBlockNumber(&(it->t_tid));

!             b = ReleaseAndReadBuffer(b, s->indexRelation, blk);
              p = BufferGetPage(b);
              po = (GISTPageOpaque) PageGetSpecialPointer(p);