Computed index on transformation of jsonb key set

From: Steven Schlansker <stevenschlansker(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Computed index on transformation of jsonb key set
Date: 2019-04-26 21:25:47
Message-ID: 8A0F49D1-CA16-445B-BC4E-F53E9925809C@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi Postgres fans,

We store a Java Map<UUID, Integer> in Postgres as a jsonb column.
As json does not have a UUID type, it is of course stored as text.

A simple value might be:
{"04e623c0-6940-542f-a0de-4c999c626dfe": 50000000, "6d3e24b6-9e8c-5eb1-9e4c-f32cc40864c9": 208250000}

I am trying to implement an efficient set-intersection test.
"Find all rows where the map has a key in (uuid1, uuid2, uuid3)"

The most straightforward approach is to create a GIN over the whole column.
This has the documented disadvantage that all keys and values are stored in the GIN repeatedly, with the kicker that the UUID will be stored as text.

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

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

with:
create index my_idx on tbl using gin((array(select jsonb_object_keys(mapData)::uuid)));

Of course, this fails, ERROR: cannot use subquery in index expression
Clever me says, "well, I'll just use array_agg instead!"

create index my_idx on tbl using gin((array_agg(jsonb_object_keys(mapData)::uuid)));

Postgres 9.6 says:
ERROR: aggregate functions are not allowed in index expressions

Just to see what happened, knowing that GIN supports multi-valued data, I tried:

create index my_idx on tbl using gin((jsonb_object_keys(mapData)::uuid));
ERROR: index expression cannot return a set

How can I efficiently implement the feature I've described? It seems difficult to use computed indexing with GIN.

Thank you for any advice and have a great weekend,
Steven

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2019-04-26 21:32:15 Re: Computed index on transformation of jsonb key set
Previous Message Jeremy Harris 2019-04-26 19:52:26 Re: TCP Resets when closing connection opened via SSL