pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Marinos Yannikos <mjy@geizhals.at>
Cc: Oleg Bartunov <oleg@sai.msu.su>
Cc: pgsql-performance@postgresql.org
Subject: Re: GiST indexes and concurrency (tsearch2)
Date: Sat, 05 Feb 2005 14:42:34 -0500
Message-ID: <28977.1107632554@sss.pgh.pa.us> (raw)
In-Reply-To: <4204C3B4.1050209@geizhals.at>
References: <4201AEB1.4000504@geizhals.at>
	<Pine.GSO.4.62.0502031255350.20328@ra.sai.msu.su>
	<4202053B.10207@geizhals.at>
	<Pine.GSO.4.62.0502031514090.20328@ra.sai.msu.su>
	<4204C3B4.1050209@geizhals.at>

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

inline patch 1: src/backend/access/gist/gistget.c (+21/-15 lines) download inline-1.patch*** 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);

view thread (17+ messages)  latest in thread

Message-ID: <28977.1107632554@sss.pgh.pa.us>
Permalink:  ../28977.1107632554@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/28977.1107632554@sss.pgh.pa.us

 ·  · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, mjy@geizhals.at, oleg@sai.msu.su
  Subject: Re: GiST indexes and concurrency (tsearch2)
  In-Reply-To: <28977.1107632554@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox