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
