From: Simon Riggs <simon@2ndquadrant.com>
To: 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 10:59:11 +0000
Message-ID: <1163761151.27956.592.camel@silverbirch.site> (raw)
In-Reply-To: <1884.1163717296@sss.pgh.pa.us>
References: <200611051347.kA5DlVCk032222@wwwmaster.postgresql.org>
<7530.1163202951@sss.pgh.pa.us>
<1163233075.3634.944.camel@silverbirch.site>
<1884.1163717296@sss.pgh.pa.us>
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...
If there is hope, is there a specific place to look? It would be good to
brain dump some starting places for an investigation.
Does anybody have a perf test that shows hash indexes beating btrees by
any significant margin? (Not saying there isn't one...)
I can see the argument that fixed hash indexes would be faster, but
there are obviously major downsides to that approach.
--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com
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: 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: <1163761151.27956.592.camel@silverbirch.site>
* 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