pg.ddx.io  pgsql-students@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josh Berkus <josh@agliodbs.com>
To: Robert Haas <robertmhaas@gmail.com>
To: Peter Geoghegan <pg@heroku.com>
Cc: ktm@rice.edu <ktm@rice.edu>
Cc: Tan Tran <tankimtran@gmail.com>
Cc: Greg Stark <stark@mit.edu>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: GSoC on WAL-logging hash indexes
Date: Wed, 30 Apr 2014 10:58:29 -0700
Message-ID: <536139C5.7070601@agliodbs.com> (raw)
In-Reply-To: <WM!263ddb57d1f23053c5ddf7b24e617ce9f5edb3865dd84d14b24963d59082641b855785244c90e7ce97cc9690b7c339a6!@asav-3.01.com>
References: <B444B441-397F-4EDA-8A0C-877B9AF7033B@gmail.com>
	<54977A8C-4AF5-4BCC-B021-78E9CA978077@gmail.com>
	<CA+TgmoY4X7VKyC4dAwujuTuBoyxE2qSJF9aybhWZjxXWoc6J8w@mail.gmail.com>
	<CAM3SWZRBpAz=bZYCxvQDSGKR5OA5yEhGVOCit7AyStUtq2cBDA@mail.gmail.com>
	<20140430125544.GG5746@aart.rice.edu>
	<CAM3SWZR+OBRiMN=L6CrJDKtH+7eEJwhwYhT2fsP-B_oC0qHyVQ@mail.gmail.com>
	<CA+TgmoYqx312omye5wVhT_rsy7a6OGUe2YRWUt=b72wrOsou5w@mail.gmail.com>
	<WM!263ddb57d1f23053c5ddf7b24e617ce9f5edb3865dd84d14b24963d59082641b855785244c90e7ce97cc9690b7c339a6!@asav-3.01.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>

All,

(dropping pgsql-advocacy off the cc list)

On 04/30/2014 10:11 AM, Robert Haas wrote:
> On Wed, Apr 30, 2014 at 12:54 PM, Peter Geoghegan <pg@heroku.com> wrote:
>> On Wed, Apr 30, 2014 at 5:55 AM, ktm@rice.edu <ktm@rice.edu> wrote:
>>> I do not think that CPU costs matter as much as the O(1) probe to
>>> get a result value specifically for very large indexes/tables where
>>> even caching the upper levels of a B-tree index would kill your
>>> working set in memory. I know, I know, everyone has so much memory
>>> and can just buy more... but this does matter.
>>
>> Have you actually investigated how little memory it takes to store the
>> inner pages? It's typically less than 1% of the entire index. AFAIK,
>> hash indexes are not used much in any other system. I think MySQL has
>> them, and SQL Server 2014 has special in-memory hash table indexes for
>> in memory tables, but that's all I can find on Google.

Hash indexes are more important for MySQL because they have
index-organized tables.

> I thought the theoretical advantage of hash indexes wasn't that they
> were smaller but that you avoided a central contention point (the
> btree root).

Yes. And being smaller isn't insignificant; think of billion-row tables
with fairly random access over the whole table.  Also, *theoretically*,
a hash index could avoid the rebalancing issues which cause our btree
indexes to become bloated and need a REINDEX with certain update patterns.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers



view thread (36+ messages)  latest in thread

Message-ID: <536139C5.7070601@agliodbs.com>
Permalink:  ../536139C5.7070601@agliodbs.com/
Also on:    postgresql.org/message-id/536139C5.7070601@agliodbs.com

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-students@postgresql.org
  Cc: josh@agliodbs.com, robertmhaas@gmail.com, pg@heroku.com, ktm@rice.edu, tankimtran@gmail.com, stark@mit.edu, pgsql-hackers@postgresql.org
  Subject: Re: GSoC on WAL-logging hash indexes
  In-Reply-To: <536139C5.7070601@agliodbs.com>
  References: <B444B441-397F-4EDA-8A0C-877B9AF7033B@gmail.com> <54977A8C-4AF5-4BCC-B021-78E9CA978077@gmail.com> <CA+TgmoY4X7VKyC4dAwujuTuBoyxE2qSJF9aybhWZjxXWoc6J8w@mail.gmail.com> <CAM3SWZRBpAz=bZYCxvQDSGKR5OA5yEhGVOCit7AyStUtq2cBDA@mail.gmail.com> <20140430125544.GG5746@aart.rice.edu> <CAM3SWZR+OBRiMN=L6CrJDKtH+7eEJwhwYhT2fsP-B_oC0qHyVQ@mail.gmail.com> <CA+TgmoYqx312omye5wVhT_rsy7a6OGUe2YRWUt=b72wrOsou5w@mail.gmail.com> <WM!263ddb57d1f23053c5ddf7b24e617ce9f5edb3865dd84d14b24963d59082641b855785244c90e7ce97cc9690b7c339a6!@asav-3.01.com> <536139C5.7070601@agliodbs.com>

* 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