Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.