During check out, the query below looks up a loan using the userId and loan's status name:
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:
Note that their definition the function "left"() was used, which is not present in the query.
In PTF environment, after recreating these indexes , 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
mod-circulation calls this mod-circulation-storage API:
Without percent encoding:
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.
The loan_userid_idx and loan_status_name_idx indexes would be utilized in the SQL query plan
The loan_userid_idx and loan_status_name_idx indexes were not utilized in the SQL query plan
URL: See https://wiki.folio.org/pages/viewpage.action?pageId=73538838#CheckincheckoutTestReport(Kiwi)-Longevitytest for more info.
Interested parties: julianladisch , marcjohnson , hollyolepm