Re: Computed index on transformation of jsonb key set

>>>>> "Steven" == Steven Schlansker <stevenschlansker(at)gmail(dot)com> writes:

Steven> I figured I'd end up with significantly better storage and
Steven> performance characteristics if I first compute a uuid[] value
Steven> and build the GIN over that, and use the array operator class
Steven> instead. Additionally, this eliminates possible confusion about
Steven> uuid casing (text is case sensitive, uuid is not) and this has
Steven> already caused at least one bug in our application.

Steven> I attempted to optimize a query like:
Steven> select * from tbl where array(select jsonb_object_keys(mapData)::uuid) && array['320982a7-cfaa-572a-b5ea-2074d7f3b014'::uuid];

Obvious solution:

create function uuid_keys(mapData jsonb) returns uuid[]
language plpgsql immutable strict
as $$
begin
return array(select jsonb_object_keys(mapData)::uuid);
end;
$$;

create index on tbl using gin (uuid_keys(mapData));

select * from tbl where uuid_keys(mapData) && array[...];

--
Andrew (irc:RhodiumToad)