Re: [PERFORM] [BUGS] BUG #2737: hash indexing large tablefails,while btree of same index works
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] [BUGS] BUG #2737: hash indexing largetablefails,while btree of same index works
On Fri, 2006-11-17 at 08:26 -0600, Kenneth Marshall wrote: 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. OK, you got it - keep hash indexes then. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] [BUGS] BUG #2737: hash indexing large tablefails,while btree of same index works
Simon Riggs [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] [BUGS] BUG #2737: hash indexing large tablefails,while
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] xlog lockup patch (was: BUG #2712: could not fsync segment: Permission)
me wrote: i've loaded 1gb of data without any xlog-problems, whereas with the 8.2b2 executable it locked up after ~100mb. the xlog-files are cycling... if i need to test for some specific behaviour let me know. what's the status on this patch for inclusion in future 8.2 builds? would be nice to see it in b4 or rc1... thanks, thomas ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
