RMB 33.1.1, Vert.x 4.2.1, Spring 5.2.18, PubSub 2.4.0: Wrong loan.userId index

Overview:

During check out, the query below looks up a loan using the userId and loan's status name:

SELECT jsonb,id FROM <tenantId>_mod_circulation_storage.loan WHERE (lower(<tenantId>_mod_circulation_storage.f_unaccent(loan.jsonb->>'userId')) LIKE lower(<tenantId>_mod_circulation_storage.f_unaccent('<user UUID>'))) AND (lower(<tenantId>_mod_circulation_storage.f_unaccent(loan.jsonb->'status'->>'name')) NOT LIKE lower(<tenantId>_mod_circulation_storage.f_unaccent('Closed')))

This query gradually performs slower and slower in the 24 hours test run, as there are more loans added to the table by the longevity test. At first it executed around 100ms, but eventually over 500ms and spiked up to over 1 second at times. 

There are already 2 indexes that could be applied to the query but they were not:

1. 

CREATE INDEX loan_userid_idx
    ON fs09000000_mod_circulation_storage.loan USING btree
    ("left"(lower(fs09000000_mod_circulation_storage.f_unaccent(jsonb ->> 'userId'::text)), 600) COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

2. 

CREATE INDEX loan_status_name_idx
    ON fs09000000_mod_circulation_storage.loan USING btree
    ("left"(lower(fs09000000_mod_circulation_storage.f_unaccent((jsonb -> 'status'::text) ->> 'name'::text)), 600) COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

Note that their definition the function "left"() was used, which is not present in the query.

In PTF environment, after recreating these indexes without the left() function, the indexes got used in query plan, consequently the DB CPU utilization stopped trending upward and these queries were not being caught as "slow" (taking over 100ms) anymore. 

This change needs to be made permanent in the DB script that creates the indexes.

Steps to Reproduce:

Execute the mod-circulation API  

GET /circulation/loans?query=(userId==${userID} and  status.name<>Closed)

 
mod-circulation calls this mod-circulation-storage API:

GET /loan-storage/loans?query=%28userId%3D%3D4022952a-a2ff-4538-a1ea-5d67de55fea4%20and%20%20status.name%3C%3EClosed%29

Without percent encoding:

GET /loan-storage/loans?query=(userId==4022952a-a2ff-4538-a1ea-5d67de55fea4 and  status.name<>Closed)

 
This would generate the SQL query above. Or execute the above SQL query directly. In a table that has 100K loans and will see the response time to be over 100ms. Do an EXPLAIN ANALYZE on it to see that the current indexes are not applied.

Expected Results:

The loan_userid_idx and loan_status_name_idx indexes would be utilized in the SQL query plan
Actual Results:

The loan_userid_idx and loan_status_name_idx indexes were not utilized in the SQL query plan
Additional Information:
URL: See https://wiki.folio.org/pages/viewpage.action?pageId=73538838#CheckincheckoutTestReport(Kiwi)-Longevitytest for more info.
Interested parties: julianladisch , marcjohnson , hollyolepm