Jsonb index optimization

[ndevilleBE]

Dear,
I'm trying to optimize Sensorthings responsiveness by indexing Jsonb records. But I can't figure out how to make it work properly.
The url I'm testing is:
https://sensors.naturalsciences.be/sta/v1.1/Locations?$filter=properties/cruise_identifier%20eq%20'2022/08'%20&$expand=HistoricalLocations($select=time)
As you can see, I have a key 'cruise_identifier' in the JSONB properties column of the LOCATIONS table. The values I'm trying to find is 2022/08.
The response can take sometimes up to 1 minute which is too much for a simple query like that. If I check the logs, the query is:

select "e0"."ID", "e0"."PROPERTIES", "e0"."NAME", "e0"."ENCODING_TYPE", "e0"."DESCRIPTION", "e0"."LOCATION" from "LOCATIONS" as "e0" where (true and "e0"."PROPERTIES"::jsonb#>>'{ cruise_identifier }' = cast('2022/08' as varchar)) order by "e0"."ID" asc offset 0 rows fetch next 101 rows only

In PostgreSQL 15, the execution plan is:

Limit (cost=0.56..12818.33 rows=101 width=138) (actual time=27311.820..27312.395 rows=101 loops=1)
-> Index Scan using "LOCATIONS_PKEY" on "LOCATIONS" e0 (cost=0.56..25072058.57 rows=197560 width=138) (actual time=27311.808..27312.371 rows=101 loops=1)
Filter: (("PROPERTIES" #>> '{cruise_identifier}'::text[]) = '2022/08'::text)
Rows Removed by Filter: 25755132
Planning Time: 0.221 ms
Execution Time: 27312.454 ms

So it uses the index on the Primary Key of the Locations.
I try to create an index on the actual key value but it makes no difference to the query planning.

CREATE INDEX locations_properties_idx ON public."LOCATIONS" using btree ((("PROPERTIES" ->> 'cruise_identifier')::text));
Do you have any suggestion on the best way to improve the performances?!

Many thanks

[hylkevds]

Can you try the index:

CREATE INDEX locations_properties_idx ON public."LOCATIONS" using btree ((("PROPERTIES" #>> '{cruise_identifier}')::text)); 

[ndevilleBE]

Well, that improves the response time from 1 minutes to 78ms. Not bad :)
The only limitation is if you use the function $top with a small number (e.g. 2), it doesn't uses the index but the scan and it is slow again.
Thanks for your answer!

[hylkevds]

PostgreSQL index use is mysterious!
You could try playing around with a combined index that also includes the ID, since FROST adds an orderby "ID" to make pagination more efficient.

[ndevilleBE]

CREATE INDEX locations_properties_id_idx ON public."LOCATIONS" using btree ("ID", (("PROPERTIES" #>> '{cruise_identifier}')::text));

This improves greatly the queries with the $top function, but slows down a bit the query without. It's an equilibrium to find.