If GET by collection API always returns either 0(Not found) or 1(Found) totalRecords then do not call count_estimate() query which does https://github.com/folio-org/raml-module-builder#estimated-totalrecords
GET /item-storage/items?query=barcode==actual_barcode will always return either 0 or 1 because the barcode is unique as per https://issues.folio.org/browse/MODINVSTOR-523. But, for every API call, SELECT count_estimate() query is trigger which takes more time than the actual call and adds unnecessary latency. We can improve the performance by more than 50% if we remove count_estimate() call under such circumstances.
As we are testing various FOLIO workflows, we are seeing this pattern being repeated in many API calls just to lookup by a unique ID that surely returns only one record but getting taxed by the count_estimate() calls.
Add a flag in the CQL query to indicate that this is a unique lookup then RMB would not issue the count_estimate() call.
Steps to Reproduce:
Make a API call to /item-storage/items?query=barcode==28705124 for random barcode
Postgresql query log:
- Log into perf env https://goldenrod-cap1.int.aws.folio.org environment as User folio using valid okapi token
GET by collection with a query API request should not make calls to SELECT count_estimate() query in the database under certain circumstances as mentioned above and improve performance.
GET by collection with a query API request is making calls to SELECT count_estimate() query in the database which is adding unnecessary latency
Looks for comments in https://issues.folio.org/browse/PERF-79
All consumers of backend API who want to improve their performance