Re: Hash index use presently(?) discouraged since 2005: revive or bury it? - Mailing list pgsql-performance

On Sun, Sep 18, 2011 at 9:31 PM, Stefan Keller <sfkeller@gmail.com> wrote:
> I'm simply referring to literature (like the intro Ramakrishnan & Gehrke).
> I just know that Oracle an Mysql actually do have them too and use it
> without those current implementation specific restrictions in
> Postgres.

Where exactly do you take that from that Oracle has hash indexes?  I
can't seem to find them:
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/indexiot.htm#sthref293

Are you mixing this up with hash partitioning?
http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/schemaob.htm#sthref443

Or am I missing something?

> IMHO by design Hash Index (e.g. linear hashing) work best when:
> 1. only equal (=) tests are used (on whole values)
> 2. columns (key values) have very-high cardinality
>
> And ideally but not necessarily when index values do not change and
> number of rows are known ahead of time (avoiding O(N) worst case - but
> there are approaches to chaining with dynamic resizing).
>
> I just collected this to encourage ourselves that enhancing hash
> indexes could be worthwhile.

There's still the locking issue Jeff mentioned.  At least every time a
table resize occurs the whole index must be locked.  Or is there a
more fine granular locking strategy which I am overlooking?

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/