pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [PERFORM] BUG #2737: hash indexing large tablefails, while btree of same index works
4+ messages / 4 participants
[nested] [flat]
* Re: [PERFORM] BUG #2737: hash indexing large tablefails, while btree of same index works
@ 2006-11-17 10:59 Simon Riggs <simon@2ndquadrant.com>
2006-11-17 14:26 ` Re: [PERFORM] BUG #2737: hash indexing large tablefails, while btree of same index works Kenneth Marshall <ktm@it.is.rice.edu>
2006-11-17 15:08 ` Re: [PERFORM] BUG #2737: hash indexing large tablefails, while btree of same index works Tom Lane <tgl@sss.pgh.pa.us>
2006-11-17 15:36 ` Re: [PERFORM] BUG #2737: hash indexing large tablefails,while Julius.Stroffek <Julius.Stroffek@Sun.COM>
0 siblings, 3 replies; 4+ messages in thread
From: Simon Riggs @ 2006-11-17 10:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Balazs Nagy <bnagy@thenewpush.com>; pgsql-bugs@postgresql.org; pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PERFORM] BUG #2737: hash indexing large tablefails, while btree of same index works
2006-11-17 10:59 Re: [PERFORM] BUG #2737: hash indexing large tablefails, while btree of same index works Simon Riggs <simon@2ndquadrant.com>
@ 2006-11-17 14:26 ` Kenneth Marshall <ktm@it.is.rice.edu>
2 siblings, 0 replies; 4+ messages in thread
From: Kenneth Marshall @ 2006-11-17 14:26 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Balazs Nagy <bnagy@thenewpush.com>; pgsql-bugs@postgresql.org; pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PERFORM] BUG #2737: hash indexing large tablefails, while btree of same index works
2006-11-17 10:59 Re: [PERFORM] BUG #2737: hash indexing large tablefails, while btree of same index works Simon Riggs <simon@2ndquadrant.com>
@ 2006-11-17 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2006-11-17 15:08 UTC (permalink / raw)
To: Simon Riggs <simon@2ndquadrant.com>; +Cc: Balazs Nagy <bnagy@thenewpush.com>; pgsql-bugs@postgresql.org; pgsql-performance
"Simon Riggs" <simon@2ndquadrant.com> writes:
> Do we think there is hope of improving hash indexes?
Sure. They lack WAL support, which is just a small matter of
programming. And no one has ever spent any time on performance
optimization for them, but it certainly seems like there ought to be
scope for improvement. I don't we should toss them unless it's been
proven that their theoretical performance advantages can't be realized
for some reason. (This is unlike the situation for rtree, because with
rtree there was no reason to expect that rtree could dominate gist along
any axis.)
> If there is hope, is there a specific place to look?
I recall some speculation that using bucket size == page size might
be a bad choice, because it leads to mostly-empty buckets for typical
key sizes and fill factors. Using a configurable bucket size could
help a lot.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: [PERFORM] BUG #2737: hash indexing large tablefails,while
2006-11-17 10:59 Re: [PERFORM] BUG #2737: hash indexing large tablefails, while btree of same index works Simon Riggs <simon@2ndquadrant.com>
@ 2006-11-17 15:36 ` Julius.Stroffek <Julius.Stroffek@Sun.COM>
2 siblings, 0 replies; 4+ messages in thread
From: Julius.Stroffek @ 2006-11-17 15:36 UTC (permalink / raw)
To: pgsql-bugs@postgresql.org; +Cc: pgsql-performance
Simon Riggs wrote:
> Do we think there is hope of improving hash indexes?
I thought about this a bit. I have an idea that the hash index might
have the fixed number of buckets specified in create index statement and
the tuples in each of these buckets should be stored in a b-tree. This
should give a constant performance improvement (but based on the number
of buckets) for each fetch of a tuple from index compared to a fetch
from b-tree index.
cheers
Julo
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-11-17 15:36 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-17 10:59 Re: [PERFORM] BUG #2737: hash indexing large tablefails, while btree of same index works Simon Riggs <simon@2ndquadrant.com>
2006-11-17 14:26 ` Kenneth Marshall <ktm@it.is.rice.edu>
2006-11-17 15:08 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-11-17 15:36 ` Re: [PERFORM] BUG #2737: hash indexing large tablefails,while Julius.Stroffek <Julius.Stroffek@Sun.COM>
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