Re:Hash indexes are faster than btree indexes?
by throwaway91111 on Monday March 20, 2017 @10:34AM
(#13914516)
Surely hash indexes have some stable ordering you can order by, even if its meaning is obtuse.
Re:Hash indexes are faster than btree indexes?
by deathanatos on Monday March 20, 2017 @11:50AM
(#13915161)
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.)
Re:Hash indexes are faster than btree indexes?
by throwaway91111 on Monday March 20, 2017 @07:22PM
(#13918503)
Ah, yes I see now.
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.