...
| Code Block | ||
|---|---|---|
| ||
duration: 2159.699 ms statement: SELECT jsonb FROM fs09000000_mod_patron_blocks.item_checked_out_event WHERE (jsonb->>'userId') = 'a2a25161-e99f-433f-be37-2d08168ab589' LIMIT 10000
duration: 2154.077 ms statement: EXPLAIN ANALYZE SELECT jsonb FROM fs09000000_mod_patron_blocks.item_checked_out_event WHERE (jsonb->>'userId') = '1d4adbf2-4a0c-4777-b495-f29d45bd1711' LIMIT 10000
duration: 2527.494 ms statement: SELECT fs09000000_mod_patron_blocks.count_estimate('SELECT jsonb FROM fs09000000_mod_patron_blocks.item_checked_out_event WHERE (jsonb->>''userId'') = ''776665e5-5705-40b4-b8d6-4a5634342620''')
|
This SQL statement being slow and is even slower when wrapped by the count_estimate() function. This statement takes anywhere from 200ms to over 2s to execute, depending on the load and the availability of CPU resources. In the past when count_estimate() was slow a similar behavior in aggressive memory consumption was observed. Database logs show lots of the above count_estimate() calls and it could explain for high memory consumption this time.
Other slow queries:
...
| language | sql |
|---|
...
Analysis:
item_checked_out_event userId=="1d4adbf2-4a0c-4777-b495-f29d45bd1711" LIMIT 10000 already has a correct b-tree index (schema) since September 2020 (MODPATBLK-48 = PR 37). The long execution time of 2154 ms is caused by the high limit of 10000 records to fetch.
To calculate the user summary the code fetches all events for a user, processes them by time, and creates a summary of it: rebuild source code
If there were 5000 loans during the last 20 years all 5000 item_checked_out_events are loaded and processed.
One possible way to improve the performance: For each user store the timestamp of the latest event that has been processed at the last user summary calculation. Next time when rebuilding the user summary only fetch events > that timestamp. To make this fast create a combined index userId + metadata.createdDate and use a special SQL query jsonb->>'userId'
...
=$1 AND (jsonb->'metadata'->>'createdDate')::timestamp > $2
Other slow queries:
| Code Block | ||
|---|---|---|
| ||
duration: 606.358 ms statement: SELECT fs09000000_mod_inventory_storage.count_estimate('SELECT jsonb,id FROM fs09000000_mod_inventory_storage.item WHERE lower(f_unaccent(item.jsonb->>''barcode'')) LIKE lower(f_unaccent(''13137881''))') |
...
Searching item for barcode=="13137881" already has a correct b-tree index (schema.json) since March 2019 (MODINVSTOR-269). If it takes 606 ms this is probably caused by database server overload. We may get a slight speedup by changing the b-tree index into a unique b-tree index (MODINVSTOR-523).
...