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=1=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]
