4 ms·

How do they work for range retrievals ? EDIT: Isn't that the point of btree ?

fabian2k 10y ago
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.html https://www.postgresql.org/docs/devel/static/indexes-types.h...).
Tostino 10y ago
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.
- DougBTX 10y ago
  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 https://github.com/jmcvetta/guid/blob/master/README.md
- bpicolo 10y ago
  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.
  - throwaway91111 10y ago
    Surely hash indexes have some stable ordering you can order by, even if its meaning is obtuse.
    - bpicolo 10y ago
      Hash indexes don't support any operations other than = as far as I can tell, so they won't even be considered for >.
    - deathanatos 10y ago
      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.)
      - throwaway91111 10y ago
        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.html https://www.postgresql.org/docs/devel/static/indexes-types.h...).

Tostino 10y ago

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.

DougBTX 10y ago
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 https://github.com/jmcvetta/guid/blob/master/README.md
bpicolo 10y ago
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.
- throwaway91111 10y ago
  Surely hash indexes have some stable ordering you can order by, even if its meaning is obtuse.
  - bpicolo 10y ago
    Hash indexes don't support any operations other than = as far as I can tell, so they won't even be considered for >.
  - deathanatos 10y ago
    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.)
    - throwaway91111 10y ago
      Ah, yes I see now.