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 04A268B9F7A for ; Thu, 10 Feb 2005 01:13:56 +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 58306-03 for ; Thu, 10 Feb 2005 01:13:39 +0000 (GMT) Received: from mailbox.samurai.com (mailbox.samurai.com [205.207.28.82]) by svr1.postgresql.org (Postfix) with ESMTP id DC3FA8B9F8B for ; Thu, 10 Feb 2005 01:13:41 +0000 (GMT) Received: from localhost (mailbox.samurai.com [205.207.28.82]) by mailbox.samurai.com (Postfix) with ESMTP id 8C41F18CD0A; Wed, 9 Feb 2005 20:13:55 -0500 (EST) Received: from mailbox.samurai.com ([205.207.28.82]) by localhost (mailbox.samurai.com [205.207.28.82]) (amavisd-new, port 10024) with LMTP id 31617-01-4; Wed, 9 Feb 2005 20:13:54 -0500 (EST) Received: from fjgateway (unknown [61.88.101.19]) (using TLSv1 with cipher RC4-MD5 (128/128 bits)) (No client certificate requested) by mailbox.samurai.com (Postfix) with ESMTP id 88B3A18CD30; Wed, 9 Feb 2005 20:13:52 -0500 (EST) Subject: Re: GiST indexes and concurrency (tsearch2) From: Neil Conway To: Tom Lane Cc: Marinos Yannikos , Oleg Bartunov , pgsql-performance@postgresql.org In-Reply-To: <28977.1107632554@sss.pgh.pa.us> References: <4201AEB1.4000504@geizhals.at> <4202053B.10207@geizhals.at> <4204C3B4.1050209@geizhals.at> <28977.1107632554@sss.pgh.pa.us> Content-Type: text/plain Date: Thu, 10 Feb 2005 12:13:03 +1100 Message-Id: <1107997983.1286.132.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.3 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at mailbox.samurai.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.054 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/162 X-Sequence-Number: 10491 On Sat, 2005-02-05 at 14:42 -0500, Tom Lane wrote: > 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 sure it will help much either, but there is more low-hanging fruit in this area: GiST currently does a ReadBuffer() for each tuple produced by the index scan, which is grossly inefficient. I recently applied a patch to change rtree to keep a pin on the scan's current buffer in between invocations of the index scan API (which is how btree and hash already work), and it improved performance by about 10% (according to contrib/rtree_gist's benchmark). I've made similar changes for GiST, but unfortunately it is part of a larger GiST improvement patch that I haven't had a chance to commit to 8.1 yet: http://archives.postgresql.org/pgsql-patches/2004-11/msg00144.php I'll try and get this cleaned up for application to HEAD next week. -Neil