When calling the api request to mod-circulation /circulation/loans?query=(userId=<useId>)&limit=1 (the entry in the Jenkins perf job is mod-circulation GET 200 circulation_loans check loans by user d).
you issue the DB query:
SELECT * FROM diku_mod_inventory_storage.items_mt_view WHERE lower(f_unaccent(items_mt_view.jsonb->>'id')) LIKE lower(f_unaccent('d1600ee1-c084-4da7-9c85-cdbcc143cb4c')) LIMIT 1 OFFSET 0
This query does not scale well due to not using indexes. This is observed on tenants that are much larger then those that the vagrant box contains, on the order of 300k items
The preferred fix is to change the query to no longer use the view
Current theory is that the view misleads the cql translation into the current form rather then a higher performing and simpler version.
Eric Valuk list the CQL query string (requests) and generated SQL we want to optimize.
The query string: