Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL - Mailing list pgsql-performance

From Tom Lane
Subject Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL
Date
Msg-id 9871.1115733198@sss.pgh.pa.us
Whole thread Raw
In response to Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Greg Stark <gsstark@mit.edu>)
Responses Re: [GENERAL] "Hash index" vs. "b-tree index" (PostgreSQL  (Greg Stark <gsstark@mit.edu>)
List pgsql-performance
Greg Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> I think that efficient implementation of this would require explicitly
>> storing the hash code for each index entry,

> It seems that means doubling the size of the hash index. That's a pretty big
> i/o to cpu tradeoff.

Hardly.  The minimum possible size of a hash entry today is 8 bytes
header plus 4 bytes datum, plus there's a 4-byte line pointer to factor
in.  So under the most pessimistic assumptions, storing the hash code
would add 25% to the size.  (On MAXALIGN=8 hardware, it might cost you
nothing at all.)

> What if the hash index stored *only* the hash code? That could be useful for
> indexing large datatypes that would otherwise create large indexes.

Hmm, that could be a thought.

            regards, tom lane

pgsql-performance by date:

Previous
From: Matt Olson
Date:
Subject: Prefetch
Next
From: Tom Lane
Date:
Subject: Re: Prefetch