Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@danbst
Created April 27, 2019 10:40
Show Gist options
  • Star 2 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save danbst/eb7cb6c831163a1964d068c1ff1293d1 to your computer and use it in GitHub Desktop.
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)
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