Re: GiST indexes and concurrency (tsearch2)

Marinos Yannikos <mjy(at)geizhals(dot)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);