8

For every version of Postgres that supported hash indexing, there is a warning or note that hash indexes are "similar or slower" or "not better" than btree indexes, at least up to version 8.3. From the docs:

Version 7.2:

Note: Because of the limited utility of hash indexes, a B-tree index should generally be preferred over a hash index. We do not have sufficient evidence that hash indexes are actually faster than B-trees even for = comparisons. Moreover, hash indexes require coarser locks; see Section 9.7.

Version 7.3 (and up to 8.2):

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.

Version 8.3:

Note: Testing has shown PostgreSQL's hash indexes to perform no better than B-tree indexes, and the index size and build time for hash indexes is much worse. Furthermore, hash index operations are not presently WAL-logged, so hash indexes might need to be rebuilt with REINDEX after a database crash. For these reasons, hash index use is presently discouraged.

In this version 8.0 thread, they claim that had never found a case where hash indexes were actually faster than btree.

Even in version 9.2, the performance gain for anything other than writing the actual index was almost nothing according to this blog post (14 March 2016):
Hash Indexes on Postgres by André Barbosa.

My question is how is that possible?

By definition, Hash indexes are a O(1) operation, where a btree is an O(log n) operation. So how is it possible for a O(1) lookup to be slower than (or even similar to) finding the correct branch, and then finding the correct record?

I want to know what about indexing theory could EVER make that a possibility!

7

Disk based Btree indexes truly are O(log N), but that is pretty much irrelevant for disk arrays that fit in this solar system. Due to caching, they are mostly O(1) with a very large constant plus O((log N)-1) with a small constant. Formally, that is the same thing as O(log N), because constants don't matter in big O notation. But they do matter in reality.

Much of the slow down in hash index lookups came from the need to protect against corruption or deadlocks caused by hash-table resizing concurrent with the lookups. Until recent versions (every version you mention is comically out of date), this need led to even higher constants and to rather poor concurrency. Vastly more man hours went into the optimization of BTree concurrency than hash concurrency.

  • Thank you. I'm very aware of how far past their expiration date those versions are, but I was still curious about how the performance was so far behind what I would have expected – Sampson Crowley Jul 19 '18 at 19:42
3

Hash lookup is theoretically an O(1) operation when the key hash maps directly to the physical location of the target record. The way it works in Postgres, if I understand it correctly, is a bit more complicated: the key hash maps to a bucket that contains the OID you're looking for. A bucket can potentially comprise more than one page, which you need to sequentially scan until you find your particular key (hash). This is why it appears slower than you expect.

The hash index access method README file in the source code repo has all the details.

  • so basically a hash index IS a type of branching index as far as psql is concerned – Sampson Crowley Jul 19 '18 at 19:22
  • that actually makes a lot more sense knowing they use buckets to store the actual keys – Sampson Crowley Jul 19 '18 at 19:24
  • also thank you for the link to the readme. I had no idea those existed in the repo – Sampson Crowley Jul 19 '18 at 19:27
  • 2
    The overflow pages do need to be searched linearly, and in worse-case degenerate cases there can be an unbounded number of them. But the searches within a page have a bounded number of items that can exist on a page so they are O(1) per overflow page, and they use a binary search so the constant is not too shabby either. It really was the provision to make operations concurrency safe that was the bottleneck. – jjanes Jul 19 '18 at 19:47
  • 1
    @AnoE -- you'll be surprised... There's always a trade-off between performance and [waste of] resources; in some cases one might favour performance. – mustaccio Jul 19 '18 at 23:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.