Hacker News Reader

Comment by aikorevs | Hacker News Reader

How do they work for range retrievals ?

EDIT: Isn't that the point of btree ?

No range lookups. No order by speedups. One thing that I can see Hash Indexes being really useful for is people who use UUID primary keys. Pretty much the only thing you do with a UUID is equality anyways, so if Hash Index is faster than btree, it makes sense to use that instead.

UUIDs can be chosen to have at least a rough ordering to make them more efficient to use as primary keys, discussion about this use here: https://github.com/jmcvetta/guid/blob/master/README.md

Until you hit the problem of needing to iterate over your table (batches).

As far as I can tell, the only real way to do efficient iteration over postgres tables is to continually bound ID at either end and then take an offset. (SELECT * from <foo table> where id > <largest id from last batch> limit 10). With btrees on UUID you get lexicographic ordering here I imagine, so it'll work. With hash indexes no dice.

Cursors don't end up working out because if you kill long-running queries or idle connections (which most websites should, in production) you'll kill applications using cursors. With pgbouncer, at least, reading from cursors doesn't seem to bump idle timeouts.

Surely hash indexes have some stable ordering you can order by, even if its meaning is obtuse.

Hash indexes don't support any operations other than = as far as I can tell, so they won't even be considered for >.

Presumably, hash indexes are based on hash tables underneath, which don't generally have a stable ordering, even an obtuse one. (Any write could cause the underlying table to get resized, which will cause the contents to get re-ordered.) Perhaps if you can guarantee that writes aren't happening, they might, but I don't think this is generally applicable to most people's use case.

(but range queries / being able to iterate are generally why I prefer btrees, unless I have a good reason. Keep in mind that the O(log n) on B-trees is log with a huge base — it's not power of two. It takes very few disk reads for a b-tree to find its query; it's just not the O(1) of a hash table.)

Ah, yes I see now.

Hash indexes only support the equality operator, they're not as versatile as btree indexes (see https://www.postgresql.org/docs/devel/static/indexes-types.h...).

Reply on news.ycombinator.com