Merlin and Jeff, General remark again:It's hard for me to imagine that btree is superior for all the issues mentioned before. I still believe in hash index for primary keys and certain unique constraints where you need equality search and don't need ordering or range search.
2011/9/17 Jeff Janes <jeff.ja...@gmail.com>: (...) > Also, that link doesn't address concurrency of selects at all, only of > inserts. How would (or did) you test and benchmark concurrency of inserts and selects? Use pgbench with own config for a blackbox test? 2011/9/18 Merlin Moncure <mmonc...@gmail.com>: > Here again, I think that any proposed improvement in the current hash > index code should be measured against wrapping a btree index. You > get wal logging and high concurrency for free if you decide to do > that. As I understand, this would be an enhancement of btree. That's ok for btree but not really exploiting all advantages of a separate hash index, would'nt it? Stefan 2011/9/18 Merlin Moncure <mmonc...@gmail.com>: > On Sat, Sep 17, 2011 at 4:48 PM, Jeff Janes <jeff.ja...@gmail.com> wrote: >> On Tue, Sep 13, 2011 at 5:04 PM, Peter Geoghegan <pe...@2ndquadrant.com> >> wrote: >>> On 14 September 2011 00:04, Stefan Keller <sfkel...@gmail.com> wrote: >>>> Has this been verified on a recent release? I can't believe that hash >>>> performs so bad over all these points. Theory tells me otherwise and >>>> http://en.wikipedia.org/wiki/Hash_table seems to be a success. >> >> My understanding is that a huge amount of work has gone into making >> btree what it is in >> PG, and not nearly as much work has gone into making hash indexes what >> they could be. >> >> >>> Hash indexes have been improved since 2005 - their performance was >>> improved quite a bit in 9.0. Here's a more recent analysis: >>> >>> http://www.depesz.com/index.php/2010/06/28/should-you-use-hash-index/ >> >> They are 3 time faster to build. But if you rip the WAL logging out >> of btree, how much faster would those get? >> >> Also, that link doesn't address concurrency of selects at all, only of >> inserts. > > Of course hash indexes are faster to build than varlen string indexes > :-). I use natural keys 50-80% of the time and hash indexing would > remove some of the pain in cases where I don't need ordering and range > operations. In fact, if they are made to properly support wal logging > and uniqueness, I imagine they should supplant btree in a broad range > of cases, so much so that it would be awful nice to be able to have > syntax to choose hash for primary keys and unique constraints. > > @ Jeff: >>I think that adding WAL to hash indexes without first > addressing the heavy-weight locking issue would be a mistake. > Even if the WAL was fixed, the bad performance under > concurrent selects would still make it at best a narrow > niche thing. And fixing the locking *after* WAL is in place would > probably be very much harder than the other order. > > Here again, I think that any proposed improvement in the current hash > index code should be measured against wrapping a btree index. You > get wal logging and high concurrency for free if you decide to do > that. > > merlin > -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
