4 ms·
> - 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/cu
by nextaccountic 4y ago
> - 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#INDEXES-TYPES-HASH 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)?
- h1fra 4y agoYou 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.
- ellisv 4y agoWe do this by making the md5 a char(32) generated column of the text column.