[SQL] Query on full lesson data run very frequently in production
The following query runs a few million times within a few days in production:
SELECT "chronos_lessonperiod"."id",
"chronos_lessonperiod"."site_id",
"chronos_lessonperiod"."extended_data",
"chronos_lessonperiod"."lesson_id",
"chronos_lessonperiod"."period_id",
"chronos_lessonperiod"."room_id",
"chronos_lesson"."id",
"chronos_lesson"."site_id",
"chronos_lesson"."extended_data",
"chronos_lesson"."validity_id",
"chronos_lesson"."subject_id",
"chronos_validityrange"."id",
"chronos_validityrange"."site_id",
"chronos_validityrange"."extended_data",
"chronos_validityrange"."school_term_id",
"chronos_validityrange"."name",
"chronos_validityrange"."date_start",
"chronos_validityrange"."date_end",
"core_schoolterm"."id",
"core_schoolterm"."site_id",
"core_schoolterm"."extended_data",
"core_schoolterm"."name",
"core_schoolterm"."date_start",
"core_schoolterm"."date_end",
"chronos_subject"."id",
"chronos_subject"."site_id",
"chronos_subject"."extended_data",
"chronos_subject"."short_name",
"chronos_subject"."name",
"chronos_subject"."colour_fg",
"chronos_subject"."colour_bg",
"chronos_timeperiod"."id",
"chronos_timeperiod"."site_id",
"chronos_timeperiod"."extended_data",
"chronos_timeperiod"."validity_id",
"chronos_timeperiod"."weekday",
"chronos_timeperiod"."period",
"chronos_timeperiod"."time_start",
"chronos_timeperiod"."time_end",
"chronos_room"."id",
"chronos_room"."site_id",
"chronos_room"."extended_data",
"chronos_room"."short_name",
"chronos_room"."name"
FROM "chronos_lessonperiod"
INNER JOIN "chronos_lesson" ON ("chronos_lessonperiod"."lesson_id" = "chronos_lesson"."id")
LEFT OUTER JOIN "chronos_validityrange" ON ("chronos_lesson"."validity_id" = "chronos_validityrange"."id")
LEFT OUTER JOIN "core_schoolterm" ON ("chronos_validityrange"."school_term_id" = "core_schoolterm"."id")
INNER JOIN "chronos_subject" ON ("chronos_lesson"."subject_id" = "chronos_subject"."id")
INNER JOIN "chronos_timeperiod" ON ("chronos_lessonperiod"."period_id" = "chronos_timeperiod"."id")
LEFT OUTER JOIN "chronos_room" ON ("chronos_lessonperiod"."room_id" = "chronos_room"."id")
WHERE ("chronos_lessonperiod"."site_id" = $1
AND "chronos_lessonperiod"."lesson_id" = $2
AND ("chronos_lessonperiod"."extended_data" -> $3) = $4)
ORDER BY "chronos_validityrange"."date_start" ASC,
"chronos_timeperiod"."weekday" ASC,
"chronos_timeperiod"."period" ASC,
"chronos_subject"."name" ASC,
"chronos_subject"."short_name" ASC
LIMIT $5
We should find out where it coems from and if it has to run so often.