Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WfZ2Z-0005M0-I3 for pgsql-advocacy@arkaria.postgresql.org; Wed, 30 Apr 2014 18:15:31 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WfZ2Y-0003sF-Ow for pgsql-advocacy@arkaria.postgresql.org; Wed, 30 Apr 2014 18:15:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WfZ2X-0003rz-BV; Wed, 30 Apr 2014 18:15:29 +0000 Received: from mout.kundenserver.de ([212.227.126.131]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WfZ2U-0003IN-9b; Wed, 30 Apr 2014 18:15:29 +0000 Received: from anarazel.de (p5DDC7A39.dip0.t-ipconnect.de [93.220.122.57]) by mrelayeu.kundenserver.de (node=mreue001) with ESMTP (Nemesis) id 0MR7Py-1WJ2VB2keM-00UIqs; Wed, 30 Apr 2014 20:15:16 +0200 Received: by anarazel.de (Postfix, from userid 1000) id 01D1010029A; Wed, 30 Apr 2014 20:15:14 +0200 (CEST) Date: Wed, 30 Apr 2014 20:15:14 +0200 From: Andres Freund To: Jeff Janes Cc: Peter Geoghegan , Robert Haas , "ktm@rice.edu" , Tan Tran , Greg Stark , pgsql-advocacy , PostgreSQL-development Subject: Re: [HACKERS] GSoC on WAL-logging hash indexes Message-ID: <20140430181514.GA28822@awork2.anarazel.de> References: <54977A8C-4AF5-4BCC-B021-78E9CA978077@gmail.com> <20140430125544.GG5746@aart.rice.edu> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline In-Reply-To: Content-Transfer-Encoding: quoted-printable X-Provags-ID: V02:K0:HFjDabT+4ZFUc/vHYL1Dk7gGu8MKlC7dlsDy410r5ir Lzf/8O6joR7j304ZOA+n0K/D2WPv+UupzzxJ6JmEdfQMfc9u8o meEZid7Mjw41k/A5oVRf1danVaRSu+fJsaJe0jF/+FJ2WPrpAP uOk8cEXV+BQfNcl2dr84YFhzAWCTWRKAfmqSP9MtlymOu8v/kB JR623uddCGWHuUYNdlKkuIyo1WYolNzT94Q3jbFioxuVACtLJ9 ZV4CrI/h9PO0R9An5UxhKmU+BgH2qsZjZTh6JJsDbP0pcJCTGY +Oit4+ZzmmZCcWlBnDNULYBd+KukdAf5ji5JdLYMrsncJ4bbAG OuTbMjt3s0tbGjJmOhMEbU75H2+QLlnZpJTl1VBIQ X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-advocacy Precedence: bulk Sender: pgsql-advocacy-owner@postgresql.org On 2014-04-30 11:10:22 -0700, Jeff Janes wrote: > I've seen the simple pinning and unpinning of the root page (or the fast = root, > whatever the first page we bother to pin on a regular basis is called) be= a > point of contention. =A0When one index dominates the entire system worklo= ad, that > one page also drives contention on the spin lock that protects the lwlock= that > share-protects whichever buffer mapping partition happens to contain it. To quite some degree that's an implementation deficiency of our lwlocks though. I've seen *massive* improvements with my lwlock patch for that problem. Additionally we need to get rid of the spinlock around pin/unpin. That said, even after those optimizations, there remains a significant amount of cacheline bouncing. That's much easier to avoid for something like hash indexes than btrees. I think another advantage is that hash indexes can be *much* smaller than btree when the individual rows are wide. I wonder though if we couldn't solve that better by introducing "transforms" around the looked up data. E.g. allow to *transparently* use a hash(indexed_column) to be used. If you currently do that a lot of work has to be done in every query... Greetings, Andres Freund --=20 Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services --=20 Sent via pgsql-advocacy mailing list (pgsql-advocacy@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-advocacy