Re: Computed index on transformation of jsonb key set - Mailing list pgsql-general

>>>>> "Steven" == Steven Schlansker <stevenschlansker@gmail.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)