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

From Tom Lane
Subject Re: [PERFORM] "Hash index" vs. "b-tree index" (PostgreSQL
Date
Msg-id 29017.1115777867@sss.pgh.pa.us
Whole thread Raw
In response to Re: [PERFORM] "Hash index" vs. "b-tree index" (PostgreSQL  (Greg Stark <gsstark@mit.edu>)
List pgsql-general
Greg Stark <gsstark@mit.edu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> No, not at all, because searching such an index will require a tree
>> descent, thus negating the one true advantage of hash indexes.

> The hash index still has to do a tree descent, it just has a larger branching
> factor than the btree index.

There is *no* tree descent in a hash index: you go directly to the
bucket you want.

If the bucket spans more than one page, you pay something, but this
strikes me as being equivalent to the case of multiple equal keys
spanning multiple pages in a btree.  It works, but it's not the design
center.

> btree indexes could have a special case hack to optionally use a large
> branching factor for the root node, effectively turning them into hash
> indexes.

No, because you'd still have to fetch and search the root node.

            regards, tom lane

pgsql-general by date:

Previous
From: Neil Conway
Date:
Subject: Re: [PERFORM] "Hash index" vs. "b-tree index" (PostgreSQL
Next
From: Bruno Wolff III
Date:
Subject: Re: WHERE