Created
April 27, 2019 10:40
-
-
Save danbst/eb7cb6c831163a1964d068c1ff1293d1 to your computer and use it in GitHub Desktop.
Postgresql UUID Bloom index support (best for UUIDv4) (https://www.postgresql.org/docs/current/bloom.html)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create extension "uuid-ossp"; | |
-- https://www.postgresql.org/docs/current/bloom.html | |
create extension bloom; | |
CREATE or replace FUNCTION hashuuid(u uuid) RETURNS integer AS $$ | |
DECLARE | |
a bytea := uuid_send(u); | |
BEGIN | |
RETURN (get_byte(a, 3) << 24) + (get_byte(a, 2) << 16) + (get_byte(a, 1) << 8) + get_byte(a, 0); | |
END; | |
$$ LANGUAGE plpgsql IMMUTABLE; | |
CREATE OPERATOR CLASS uuid_bloom | |
DEFAULT FOR TYPE uuid USING bloom AS | |
OPERATOR 1 =(uuid, uuid), | |
FUNCTION 1 hashuuid(uuid); | |
-- create index on "table" using bloom(column1,column2,column3) with (length = 20) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment