tag:blogger.com,1999:blog-20038672.post4281726503950733235..comments2024-03-28T00:58:29.187-04:00Comments on Robert Haas: PostgreSQL's Hash Indexes Are Now CoolRobert Haashttp://www.blogger.com/profile/08393677427643988650noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-20038672.post-87010911325131098622018-01-14T00:48:35.132-05:002018-01-14T00:48:35.132-05:00Thanks so much for this guys.
Just wanted to add ...Thanks so much for this guys.<br /><br />Just wanted to add some context about unique hash indexes. For a variety of reasons, mostly around federation and distributed computing, our product is moving toward use of random uuids as the primary keys for our domain objects -- so i'd love to see unique hash indexes supported by pg. <br /><br />in my very simple and unscientific microbenchmarking using pgcrypto, inserts of random uuids are about 25% faster with hashes than btrees - on pg9.6! I expected this as I understand that btree performance is a lot poorer than hashes on random data, simply because the cache footprint is so much higher as the tree is traversed. In addition, the size of the hash index was actually bit lower for hashes. Again, 9.6. (Yeah I'll upgrade to 10 soon enough :)<br /><br />We used hash indexes extensively when working with a previous database server in the 90s for just this reason; and it's extremely rare for us to use range queries on primary keys. If I could, I would make all primary keys in our database use hash indexes.<br /><br />Sadly I don't have the PG chops to add this functionality myself. :-(Anonymousnoreply@blogger.comtag:blogger.com,1999:blog-20038672.post-13395926254578317992018-01-05T14:35:04.500-05:002018-01-05T14:35:04.500-05:00I don't know of a plan to implement that featu...I don't know of a plan to implement that feature. I think it would be cool if someone did.<br /><br />Mailing list discussion here: http://postgr.es/m/6318fb86-0a64-61e7-e4e2-714db2b3407a@anastigmatix.netRobert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-21931592619199240532017-11-30T04:01:45.490-05:002017-11-30T04:01:45.490-05:00This is great news. I went to try it out to make u...This is great news. I went to try it out to make unique field indexes smaller. Like Uuid and integer primary keys that don't need range support. Sadly it returned an error that unique is not supported. Is there any plan to implement that? It would save lots of space for larger unique key indexes and also be fast to lookup in the case of foreign keysAnonymoushttps://www.blogger.com/profile/06881640826137624077noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-37623355240194855632017-10-07T15:01:29.583-04:002017-10-07T15:01:29.583-04:00Please go ahead!Please go ahead!Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-65212384656771741232017-10-03T21:17:49.582-04:002017-10-03T21:17:49.582-04:00Hello,I want to translate this article into Chines...Hello,I want to translate this article into Chinese, and share it on the web.Of course, I will clearly mark the author and some necessary information.Would you like to authorize?Anonymoushttps://www.blogger.com/profile/07244211346629159280noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-45079731128751574762017-09-29T14:18:29.114-04:002017-09-29T14:18:29.114-04:00Thanks. As a default behavior, I think it's b...Thanks. As a default behavior, I think it's better to always hash. The performance cost probably isn't much, and it avoids problems if your UUIDs are less than random (which is quite possible they are anything other than v4 UUIDs, the only kind that are randomly generated).<br /><br />However, if for a particular application you really want some other behavior, you could create a custom hash operator class that defines the hash function in any way you like - for example, you could make it a function which just extracts the first 32 bits. This would take a handful of lines of SQL and a small C extension module, but no core changes.<br /><br />If anyone decides to try it out, I'd be interested in hearing how the performance compares with the standard uuid_ops.Robert Haashttps://www.blogger.com/profile/08393677427643988650noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-85040245164689880982017-09-29T07:23:17.970-04:002017-09-29T07:23:17.970-04:00most excellent post.
for uuid/digest any thoughts...most excellent post.<br /><br />for uuid/digest any thoughts on using the first 32 bits as hash value, instead of hashing the uuid?John the Scotthttps://www.blogger.com/profile/14024505963810601849noreply@blogger.comtag:blogger.com,1999:blog-20038672.post-63724952754512136632017-09-28T09:06:48.395-04:002017-09-28T09:06:48.395-04:00Excellent post, Robert!Excellent post, Robert!Anonymoushttps://www.blogger.com/profile/07729566178732510627noreply@blogger.com