Uploaded image for project: 'mod-calendar'
  1. mod-calendar
  2. MODCAL-50

Add indexes to improve /calendar/periods/{servicePointId}/calculateopening performance

    XMLWordPrintable

Details

    • CP: sprint 72
    • 0.5
    • Core: Platform

    Description

      During BugFest test Q3.2-2019, it was noticed that /calendar/periods/

      {servicePointId}

      /calculateopening API took over 10 seconds. It turns out the generated db query (see below) was very slow. We added two indexes to the actual_opening_hours and the query execution time improved from over 10s to half ms. I will create PR to request adding these indexes.

      WITH openings_ids as (SELECT jsonb->>'id' opening_id FROM fs00001000_mod_calendar.openings WHERE jsonb->>'servicePointId' = 'db222815-8557-490a-aae7-e2c19d7e5dce'),closest_open_day as (SELECT aoh1.jsonb->>'actualDay' actual_day FROM fs00001000_mod_calendar.actual_opening_hours aoh1 WHERE aoh1.jsonb->>'openingId' IN (SELECT opening_id FROM openings_ids) AND aoh1.jsonb->>'actualDay' > '2019-12-19T00:00:00.000+0000' AND aoh1.jsonb->>'open' = 'true' AND (SELECT count(_id) FROM fs00001000_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 fs00001000_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);
      

      TestRail: Results

        Attachments

          Issue Links

            Activity

              People

                hji Hongwei Ji
                hji Hongwei Ji
                Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                  Created:
                  Updated:
                  Resolved:

                  TestRail: Runs

                    TestRail: Cases