X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5EA088B9C8D for ; Sat, 5 Feb 2005 19:42:50 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 44754-08 for ; Sat, 5 Feb 2005 19:42:36 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 8FE728B9C6C for ; Sat, 5 Feb 2005 19:42:36 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j15JgYTM028978; Sat, 5 Feb 2005 14:42:35 -0500 (EST) To: Marinos Yannikos Cc: Oleg Bartunov , pgsql-performance@postgresql.org Subject: Re: GiST indexes and concurrency (tsearch2) In-reply-to: <4204C3B4.1050209@geizhals.at> References: <4201AEB1.4000504@geizhals.at> <4202053B.10207@geizhals.at> <4204C3B4.1050209@geizhals.at> Comments: In-reply-to Marinos Yannikos message dated "Sat, 05 Feb 2005 14:01:40 +0100" Date: Sat, 05 Feb 2005 14:42:34 -0500 Message-ID: <28977.1107632554@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/82 X-Sequence-Number: 10411 Marinos Yannikos 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);