[SQL] Query for person statistics in week overview takes very long
The query for retrieving statistics on persons takes very long (approx. 2,5 s):
SELECT DISTINCT "core_person"."id",
"core_person"."site_id",
"core_person"."extended_data",
"core_person"."user_id",
"core_person"."is_active",
"core_person"."first_name",
"core_person"."last_name",
"core_person"."additional_name",
"core_person"."short_name",
"core_person"."street",
"core_person"."housenumber",
"core_person"."postal_code",
"core_person"."place",
"core_person"."phone_number",
"core_person"."mobile_number",
"core_person"."email",
"core_person"."date_of_birth",
"core_person"."sex",
"core_person"."photo",
"core_person"."primary_group_id",
"core_person"."description",
COUNT(DISTINCT "alsijil_personalnote"."id") FILTER (WHERE (("alsijil_personalnote"."lesson_period_id" IN (...) AND "alsijil_personalnote"."week" = 10 AND "alsijil_personalnote"."year" = 2021) AND "alsijil_personalnote"."absent")) AS "absences_count",
COUNT(DISTINCT "alsijil_personalnote"."id") FILTER (WHERE (("alsijil_personalnote"."lesson_period_id" IN (...) AND "alsijil_personalnote"."week" = 10 AND "alsijil_personalnote"."year" = 2021) AND "alsijil_personalnote"."absent" AND NOT "alsijil_personalnote"."excused")) AS "unexcused_count",
(
SELECT DISTINCT SUM(U2."late") AS "tardiness_sum"
FROM "core_person" U0
INNER JOIN "alsijil_personalnote" U2
ON (U0."id" = U2."person_id")
LEFT OUTER JOIN "chronos_event" U4
ON (U2."event_id" = U4."id")
LEFT OUTER JOIN "chronos_extralesson" U5
ON (U2."extra_lesson_id" = U5."id")
WHERE (U0."site_id" = 1 AND (U2."lesson_period_id" IN (...) AND U2."week" = 10 AND U2."year" = 2021) AND U0."id" = "core_person"."id")
GROUP BY U0."id"
) AS "tardiness_sum",
COUNT(DISTINCT "alsijil_personalnote"."id") FILTER (WHERE (("alsijil_personalnote"."lesson_period_id" IN (...) AND "alsijil_personalnote"."week" = 10 AND "alsijil_personalnote"."year" = 2021) AND NOT ("alsijil_personalnote"."late" = 0))) AS "tardiness_count",
COUNT(DISTINCT "alsijil_personalnote"."id") FILTER (WHERE (("alsijil_personalnote"."lesson_period_id" IN (...) AND "alsijil_personalnote"."week" = 10 AND "alsijil_personalnote"."year" = 2021) AND "alsijil_personalnote_extra_marks"."extramark_id" = 1)) AS "HA_count",
T18."id",
T18."site_id",
T18."extended_data",
T18."school_term_id",
T18."name",
T18."short_name",
T18."group_type_id"
FROM "core_person"
INNER JOIN "core_persongroupthrough"
ON ("core_person"."id" = "core_persongroupthrough"."person_id")
INNER JOIN "core_group"
ON ("core_persongroupthrough"."group_id" = "core_group"."id")
LEFT OUTER JOIN "chronos_lesson_groups"
ON ("core_group"."id" = "chronos_lesson_groups"."group_id")
LEFT OUTER JOIN "chronos_lesson"
ON ("chronos_lesson_groups"."lesson_id" = "chronos_lesson"."id")
LEFT OUTER JOIN "chronos_lessonperiod"
ON ("chronos_lesson"."id" = "chronos_lessonperiod"."lesson_id")
LEFT OUTER JOIN "chronos_event_groups"
ON ("core_group"."id" = "chronos_event_groups"."group_id")
LEFT OUTER JOIN "chronos_extralesson_groups"
ON ("core_group"."id" = "chronos_extralesson_groups"."group_id")
LEFT OUTER JOIN "alsijil_personalnote"
ON ("core_person"."id" = "alsijil_personalnote"."person_id")
LEFT OUTER JOIN "chronos_event" T14
ON ("alsijil_personalnote"."event_id" = T14."id")
LEFT OUTER JOIN "chronos_extralesson" T15
ON ("alsijil_personalnote"."extra_lesson_id" = T15."id")
LEFT OUTER JOIN "alsijil_personalnote_extra_marks"
ON ("alsijil_personalnote"."id" = "alsijil_personalnote_extra_marks"."personalnote_id")
LEFT OUTER JOIN "core_group" T18
ON ("core_person"."primary_group_id" = T18."id")
WHERE ("core_person"."site_id" = 1 AND "core_person"."is_active" AND "chronos_lessonperiod"."id" IN (...))
GROUP BY "core_person"."id",
T18."id"