Hacker News Reader

> - You can speed up, by a huge margin, big string indices with md5/hash index (only relevant for exact match)

Do you mean a https://www.postgresql.org/docs/current/indexes-types.html#I... index? It's a 32-bit hash (but which hash is it, is it CRC32?). How to do a MD5 index?

Anyway, MD5 is slow, does Postgres offer fast hashes like SipHash (DoS resistant) or FNV (not DoS resistant)?

View on HN

You can store the md5 (or any hash) in a new column and use it in the index instead of the string column. It will still be a string index but much shorter. You have to be aware of hash collision but in my case it was a multi column index so the risk was close to zero. MD5 was maybe not the best choice but it's builtin so available everywhere.

What I did to not maintain a second column is to use the function directly in the index:

``` CREATE UNIQUE INDEX CONCURRENTLY "groupid_md5_uniq" ON "crawls" ("group_id", md5("url")); ```

``` SELECT * FROM crawls WHERE group_id= $0 AND md5(url) = md5($1) ```

This simple trick, that did not required an extensive refactor, speed up the query time by a factor of thousand.

We do this by making the md5 a char(32) generated column of the text column.

Reply on news.ycombinator.com