|
I defined open hours for 4 service points in Lehigh's FOLIO test instance and extended them out to 2030 because we do loans that are a year long. In testing circulation, I found that it was taking almost a minute to checkout a single item. When I watched the database's logs, I found that the problem was with the query reproduced below, which is used to determine when the Library is open around the due date of the loan. I executed the query manually and observed that it took 50 seconds to finish with the calendars going out that far. I checked mod-calendar's actual_opening_hours table and found that it had ~16000 rows in it. I trimmed the calendars for the service points back to only extending out to 1/1/2021, leaving ~1800 rows in the actual_opening_hours table, and then it only took ~2 seconds to checkout an item. Here is the problematic query:
WITH openings_ids as (SELECT jsonb->>'id' opening_id FROM lu_mod_calendar.openings WHERE jsonb->>'servicePointId' = 'e89641e5-fa6f-45bc-a5a2-ba9f1f7c6baa'),closest_open_day as (SELECT aoh1.jsonb->>'actualDay' actual_day FROM lu_mod_calendar.actual_opening_hours aoh1 WHERE aoh1.jsonb->>'openingId' IN (SELECT opening_id FROM openings_ids) AND aoh1.jsonb->>'actualDay' > '2020-10-08T00:00:00.000+0000' AND aoh1.jsonb->>'open' = 'true' AND (SELECT count(_id) FROM lu_mod_calendar.actual_opening_hours aoh2 WHERE aoh2.jsonb->>'openingId' IN (SELECT opening_id FROM openings_ids) AND aoh2.jsonb->>'actualDay' = aoh1.jsonb->>'actualDay' AND aoh2.jsonb->>'exceptional' = 'true' AND aoh2.jsonb->>'open' = 'false') = 0 ORDER BY aoh1.jsonb->>'actualDay' asc LIMIT 1)SELECT jsonb FROM lu_mod_calendar.actual_opening_hours WHERE jsonb->>'openingId' IN (SELECT opening_id FROM openings_ids) AND jsonb->>'actualDay' = (SELECT actual_day FROM closest_open_day)
Perhaps an index is missing? Either that, or a more efficient way of finding the next available date the library is open is needed, because it's quite realistic for a library to define hours extending out for years.
|