According to the postgresql documentation, the hash algorithm is discouraged compared to b-tree for performance reasons.
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=indexes-types.html Note: Testing has shown PostgreSQL's hash indexes to be similar or slower than B-tree indexes, and the index size and build time for hash indexes is much worse. Hash indexes also suffer poor performance under high concurrency. For these reasons, hash index use is discouraged. /jonas ----- Original Message ----- From: "Mrs. Brisby" <[EMAIL PROTECTED]> To: "Clark, Chris" <[EMAIL PROTECTED]> Cc: "D. Richard Hipp" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Thursday, November 06, 2003 4:14 AM Subject: RE: [sqlite] Performance problem > On Wed, 2003-11-05 at 13:44, Clark, Chris wrote: > > > -----Original Message----- > > > From: Mrs. Brisby [mailto:[EMAIL PROTECTED] > > > > > > MySQL has stated in-documentation that it uses a B-tree for > > > it's index. > > > I think this is a mistake- especially for larger indexes. > > > Using several B-trees attached to a hash-table is much faster > > > (if the hash is fast or your data is uniform). > > > > Following this train of thought (this isn't a feature request!); some DBMS's support different structures for tables/indices and the DBA can specify the required structure depending in the expected data usage/patterns (and a 2ndary index need not be the same structure as the primary table structure, allowing for, say, a hash table and b-tree 2ndary's as per the example above). E.g. Ingres has; Heap (yep, completely unstructured), B-tree, Hash, and ISAM (there is also an R-tree but that is only for spatial datatypes so it's not as interesting for this discussion). It all depends on the data and how it is used as to which structure should/could be used. > > > > A typical example of the hash primary and b-tree 2ndary is a unique customer id so that the customer record can be hit directly with the hash, or if the hash is not perfect, through a couple of overflow pages (compared to a b-tree which always will need to jump through a few pages in the index, admittedly that may only be an improvement of microsecs versus millisecs in lookup time). The b-tree 2ndary would then be for things like customer name (which are often "duplicated", potentual for lots of people called "Mr smith") in case one needs to perform searches on a customer name (who say, forgot their customer id). > > It wouldn't strictly require a hash-table either (after further > thought); perhaps just the first octet of the key could be permuted > though an 8->4 (15-bit) or 8->3 (7-bit) function. If the keys are > otherwise random this should be just as well and you wouldn't lose the > ability to use substrings... > > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]