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
