From: Kenneth Marshall <ktm@it.is.rice.edu>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Balazs Nagy <bnagy@thenewpush.com>
Cc: pgsql-bugs@postgresql.org
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] BUG #2737: hash indexing large tablefails, while btree of same index works
Date: Fri, 17 Nov 2006 08:26:57 -0600
Message-ID: <20061117142657.GQ12572@it.is.rice.edu> (raw)
In-Reply-To: <1163761151.27956.592.camel@silverbirch.site>
References: <200611051347.kA5DlVCk032222@wwwmaster.postgresql.org>
<7530.1163202951@sss.pgh.pa.us>
<1163233075.3634.944.camel@silverbirch.site>
<1884.1163717296@sss.pgh.pa.us>
<1163761151.27956.592.camel@silverbirch.site>
On Fri, Nov 17, 2006 at 10:59:11AM +0000, Simon Riggs wrote:
> On Thu, 2006-11-16 at 17:48 -0500, Tom Lane wrote:
>
> > AFAICS, any hash index exceeding a single segment is at serious risk.
> > The fact that we've not heard gripes before suggests that no one is
> > using gigabyte-sized hash indexes.
>
> Seems so.
>
> > But it seems mighty late in the beta cycle to be making subtle changes
> > in the smgr API. What I'm inclined to do for now is to hack
> > _hash_expandtable() to write a page of zeroes at the end of each file
> > segment when an increment in hashm_ovflpoint causes the logical EOF to
> > cross segment boundary(s). This is pretty ugly and nonmodular, but it
> > will fix the bug without risking breakage of any non-hash code.
> > I'll revisit the cleaner solution once 8.3 devel begins. Comments?
>
> Do we think there is hope of improving hash indexes? If not, I'm
> inclined to remove them rather than to spend time bolstering them. We
> can remap the keyword as was done with RTREE. It's somewhat embarrassing
> having an index without clear benefit that can't cope across crashes. We
> wouldn't accept that in other parts of the software...
>
While I understand that there are currently serious problems in terms of
recovery with the hash index as it stands currently, it is theoretically
possible to get a result back with a single I/O probe from a hash and
that is not the case with btree indexes. At the top end of the performance
curve, every little bit that can be done to minimize actual I/Os is needed.
I certainly hold out some hope that they can improved. I would like to see
them still included. Once they are gone, it will be much harder to ever
add them back.
Ken Marshall
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: ktm@it.is.rice.edu, simon@2ndquadrant.com, tgl@sss.pgh.pa.us, bnagy@thenewpush.com, pgsql-bugs@postgresql.org
Subject: Re: [PERFORM] BUG #2737: hash indexing large tablefails, while btree of same index works
In-Reply-To: <20061117142657.GQ12572@it.is.rice.edu>
* 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