Re: Save Hash Indexes

On Fri, Nov 01, 2013 at 01:31:10PM +0000, Dimitri Fontaine wrote:
> Hi,
>
> Here's an idea: when a user ask for an Hash Index transparently build a
> BTree index over an hash function instead.
>
> Advantages:
>
> - it works
> - it's crash safe
> - it's (much?) faster than a hash index anyways
>
> Drawbacks:
>
> - root access concurrency
> - we need a hash_any function stable against pg_upgrade
>
> After talking about it with Heikki, we don't seem to find ways in which
> the root access concurrency pattern would be visible enough to matter.
>
> Also, talking with Peter Geoghegan, it's unclear that there's a use case
> where a hash index would be faster than a btree index over the hash
> function.
>
> Comments?
> --
> Dimitri Fontaine
> http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support
>

Hi Dimitri,

This use of a function index as a "safe" hash index has been the substitute
for a while. Check the previous threads. It is not a true substitute because
a hash index is O(1) for lookups but a btree is O(log n) so hash indexes have
an advantage for very large numbers on entries. In fact a recent post compared
both the btree substitute and the current hash index and for large indexes the
hash allowed 2X the lookups than the equivalent btree, which is what you would
expect. The use-case is exactly for very large tables/indexes where the index
does not fit in memory, to say nothing of the data itself.

Regards,
Ken