Aurora postgres Can I create a partial GIN index on a JSONB column

Question

We have two different applications which will be using my search table. They have two different filters that will be always be applied and it is different for each of the application. So i created a partial index as given below so that the filter is already applied in the index. The column on which index is created is a JSONB column.

CREATE INDEX search_text_ndx_1 ON x_search_ms.x_search_tbl USING gin(x_details)
WHERE x_details @@ '($.c_data.sub_data == "A" ||$.c_data.sub_data == "B")
&& $.c_data.details.flag == "Z"'

CREATE INDEX search_text_ndx_2 ON x_search_ms.x_search_tbl USING gin(x_details)
WHERE x_details @@ '($.c_data.sub_data == "D" ||$.c_data.sub_data == "E")
&& $.c_data.details.flag == "Y"'

There are totally about 400K rows in the table and i know applying the above filter will almost half the number of satisfying rows for each index (i.e.) about 200K rows satisfying the criteria for search_text_ndx_1 and 200K rows satisfying for search_text_ndx_2

But these partial index does not get used when checking as below even though these have the filters specified in the query.

explain analyze select * from x_search_ms.x_search_tbl 
where  x_details @@ '($.c_data.sub_data == "A" ||$.c_data.sub_data == "B")
&& $.c_data.details.flag == "Z"
&& $.c_data.sub_data.flag == "X flag"
&& $.c_data.filter_a == "a_value"
&& $.c_data.filter_b == "b_value" 
&& $.c_data.filter_c[*] == "c_value"'

But they use the GIN index when it is created as a single index as below

CREATE INDEX search_text_ndx_2 ON x_search_ms.x_search_tbl USING gin(x_details)

Not sure why the partial index is not used by the query

The version on which I am trying is PostgreSQL 12.7

Answer 1

The partial index code doesn't understand the internal semantics of jsonpath. You would need to use two @@ with two different jsonpath strings (one matching the one used to define the index) and AND them together in SQL in order for the system to understand that the index can be used.