[SQL] Person statistics in "My students" take very long
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 T8."id") FILTER (WHERE (T8."absent" AND ("chronos_validityrange"."school_term_id" = 2 OR "chronos_extralesson"."school_term_id" = 2 OR "chronos_event"."school_term_id" = 2) AND ("chronos_lesson_groups"."group_id" = ... OR "core_group_parent_groups"."to_group_id" = ... OR "chronos_extralesson_groups"."group_id" = ... OR T23."to_group_id" = ... OR "chronos_event_groups"."group_id" = ... OR T27."to_group_id" = ...))) AS "absences_count",
COUNT(DISTINCT T8."id") FILTER (WHERE (T8."absent" AND T8."excuse_type_id" IS NULL AND T8."excused" AND ("chronos_validityrange"."school_term_id" = 2 OR "chronos_extralesson"."school_term_id" = 2 OR "chronos_event"."school_term_id" = 2) AND ("chronos_lesson_groups"."group_id" = ... OR "core_group_parent_groups"."to_group_id" = ... OR "chronos_extralesson_groups"."group_id" = ... OR T23."to_group_id" = ... OR "chronos_event_groups"."group_id" = ... OR T27."to_group_id" = ...))) AS "excused",
COUNT(DISTINCT T8."id") FILTER (WHERE (T8."absent" AND NOT T8."excused" AND ("chronos_validityrange"."school_term_id" = 2 OR "chronos_extralesson"."school_term_id" = 2 OR "chronos_event"."school_term_id" = 2) AND ("chronos_lesson_groups"."group_id" = ... OR "core_group_parent_groups"."to_group_id" = ... OR "chronos_extralesson_groups"."group_id" = ... OR T23."to_group_id" = ... OR "chronos_event_groups"."group_id" = ... OR T27."to_group_id" = ...))) AS "unexcused",
(
SELECT DISTINCT SUM(U2."late") AS "tardiness"
FROM "core_person" U0
INNER JOIN "alsijil_personalnote" U2
ON (U0."id" = U2."person_id")
LEFT OUTER JOIN "chronos_lessonperiod" U3
ON (U2."lesson_period_id" = U3."id")
LEFT OUTER JOIN "chronos_lesson" U4
ON (U3."lesson_id" = U4."id")
LEFT OUTER JOIN "chronos_validityrange" U5
ON (U4."validity_id" = U5."id")
LEFT OUTER JOIN "chronos_extralesson" U7
ON (U2."extra_lesson_id" = U7."id")
LEFT OUTER JOIN "chronos_event" U9
ON (U2."event_id" = U9."id")
LEFT OUTER JOIN "chronos_lesson_groups" U11
ON (U4."id" = U11."lesson_id")
LEFT OUTER JOIN "core_group" U12
ON (U11."group_id" = U12."id")
LEFT OUTER JOIN "core_group_parent_groups" U13
ON (U12."id" = U13."from_group_id")
LEFT OUTER JOIN "chronos_extralesson_groups" U15
ON (U7."id" = U15."extralesson_id")
LEFT OUTER JOIN "core_group" U16
ON (U15."group_id" = U16."id")
LEFT OUTER JOIN "core_group_parent_groups" U17
ON (U16."id" = U17."from_group_id")
LEFT OUTER JOIN "chronos_event_groups" U19
ON (U9."id" = U19."event_id")
LEFT OUTER JOIN "core_group" U20
ON (U19."group_id" = U20."id")
LEFT OUTER JOIN "core_group_parent_groups" U21
ON (U20."id" = U21."from_group_id")
WHERE (U0."site_id" = 1 AND (U5."school_term_id" = 2 OR U7."school_term_id" = 2 OR U9."school_term_id" = 2) AND (U11."group_id" = ... OR U13."to_group_id" = ... OR U15."group_id" = ... OR U17."to_group_id" = ... OR U19."group_id" = ... OR U21."to_group_id" = ...) AND U0."id" = "core_person"."id")
GROUP BY U0."id"
) AS "tardiness",
COUNT(DISTINCT T8."id") FILTER (WHERE (NOT (T8."late" = 0 AND T8."late" IS NOT NULL) AND ("chronos_validityrange"."school_term_id" = 2 OR "chronos_extralesson"."school_term_id" = 2 OR "chronos_event"."school_term_id" = 2) AND ("chronos_lesson_groups"."group_id" = ... OR "core_group_parent_groups"."to_group_id" = ... OR "chronos_extralesson_groups"."group_id" = ... OR T23."to_group_id" = ... OR "chronos_event_groups"."group_id" = ... OR T27."to_group_id" = ...))) AS "tardiness_count",
COUNT(DISTINCT T8."id") FILTER (WHERE ("alsijil_personalnote_extra_marks"."extramark_id" = 1 AND ("chronos_validityrange"."school_term_id" = 2 OR "chronos_extralesson"."school_term_id" = 2 OR "chronos_event"."school_term_id" = 2) AND ("chronos_lesson_groups"."group_id" = ... OR "core_group_parent_groups"."to_group_id" = ... OR "chronos_extralesson_groups"."group_id" = ... OR T23."to_group_id" = ... OR "chronos_event_groups"."group_id" = ... OR T27."to_group_id" = ...))) AS "HA_count",
COUNT(DISTINCT T8."absent") FILTER (WHERE (T8."absent" AND T8."excuse_type_id" = 1 AND ("chronos_validityrange"."school_term_id" = 2 OR "chronos_extralesson"."school_term_id" = 2 OR "chronos_event"."school_term_id" = 2) AND ("chronos_lesson_groups"."group_id" = ... OR "core_group_parent_groups"."to_group_id" = ... OR "chronos_extralesson_groups"."group_id" = ... OR T23."to_group_id" = ... OR "chronos_event_groups"."group_id" = ... OR T27."to_group_id" = ...))) AS "s_count"
FROM "core_person"
INNER JOIN "core_persongroupthrough"
ON ("core_person"."id" = "core_persongroupthrough"."person_id")
INNER JOIN "alsijil_personalnote"
ON ("core_person"."id" = "alsijil_personalnote"."person_id")
INNER JOIN "alsijil_personalnote_groups_of_person"
ON ("alsijil_personalnote"."id" = "alsijil_personalnote_groups_of_person"."personalnote_id")
LEFT OUTER JOIN "alsijil_personalnote" T8
ON ("core_person"."id" = T8."person_id")
LEFT OUTER JOIN "chronos_lessonperiod"
ON (T8."lesson_period_id" = "chronos_lessonperiod"."id")
LEFT OUTER 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 "chronos_extralesson"
ON (T8."extra_lesson_id" = "chronos_extralesson"."id")
LEFT OUTER JOIN "chronos_event"
ON (T8."event_id" = "chronos_event"."id")
LEFT OUTER JOIN "chronos_lesson_groups"
ON ("chronos_lesson"."id" = "chronos_lesson_groups"."lesson_id")
LEFT OUTER JOIN "core_group" T18
ON ("chronos_lesson_groups"."group_id" = T18."id")
LEFT OUTER JOIN "core_group_parent_groups"
ON (T18."id" = "core_group_parent_groups"."from_group_id")
LEFT OUTER JOIN "chronos_extralesson_groups"
ON ("chronos_extralesson"."id" = "chronos_extralesson_groups"."extralesson_id")
LEFT OUTER JOIN "core_group" T22
ON ("chronos_extralesson_groups"."group_id" = T22."id")
LEFT OUTER JOIN "core_group_parent_groups" T23
ON (T22."id" = T23."from_group_id")
LEFT OUTER JOIN "chronos_event_groups"
ON ("chronos_event"."id" = "chronos_event_groups"."event_id")
LEFT OUTER JOIN "core_group" T26
ON ("chronos_event_groups"."group_id" = T26."id")
LEFT OUTER JOIN "core_group_parent_groups" T27
ON (T26."id" = T27."from_group_id")
LEFT OUTER JOIN "alsijil_personalnote_extra_marks"
ON (T8."id" = "alsijil_personalnote_extra_marks"."personalnote_id")
WHERE ("core_person"."site_id" = 1 AND "core_persongroupthrough"."group_id" = ... AND "alsijil_personalnote_groups_of_person"."group_id" = ... AND ("chronos_validityrange"."school_term_id" = 2 OR "chronos_extralesson"."school_term_id" = 2 OR "chronos_event"."school_term_id" = 2))
GROUP BY "core_person"."id"This query takes 18020 ms (example in production).
Activity
added part::backend type::bug workflow::confirmed labels
Example plan form production:
Unique (cost=8046541.85..8046717.27 rows=2506 width=210) (actual time=25504.349..25504.471 rows=26 loops=1) Output: 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 t8.id) FILTER (WHERE (t8.absent AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), (count(DISTINCT t8.id) FILTER (WHERE (t8.absent AND (t8.excuse_type_id IS NULL) AND t8.excused AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), (count(DISTINCT t8.id) FILTER (WHERE (t8.absent AND (NOT t8.excused) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), ((SubPlan 1)), (count(DISTINCT t8.id) FILTER (WHERE (((t8.late <> 0) OR (t8.late IS NULL)) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), (count(DISTINCT t8.id) FILTER (WHERE ((alsijil_personalnote_extra_marks.extramark_id = 1) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), (count(DISTINCT t8.absent) FILTER (WHERE (t8.absent AND (t8.excuse_type_id = 1) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))) Buffers: shared hit=18990721 read=111 -> Sort (cost=8046541.85..8046548.12 rows=2506 width=210) (actual time=25504.348..25504.396 rows=26 loops=1) Output: 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 t8.id) FILTER (WHERE (t8.absent AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), (count(DISTINCT t8.id) FILTER (WHERE (t8.absent AND (t8.excuse_type_id IS NULL) AND t8.excused AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), (count(DISTINCT t8.id) FILTER (WHERE (t8.absent AND (NOT t8.excused) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), ((SubPlan 1)), (count(DISTINCT t8.id) FILTER (WHERE (((t8.late <> 0) OR (t8.late IS NULL)) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), (count(DISTINCT t8.id) FILTER (WHERE ((alsijil_personalnote_extra_marks.extramark_id = 1) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), (count(DISTINCT t8.absent) FILTER (WHERE (t8.absent AND (t8.excuse_type_id = 1) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))) Sort Key: core_person.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 t8.id) FILTER (WHERE (t8.absent AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), (count(DISTINCT t8.id) FILTER (WHERE (t8.absent AND (t8.excuse_type_id IS NULL) AND t8.excused AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), (count(DISTINCT t8.id) FILTER (WHERE (t8.absent AND (NOT t8.excused) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), ((SubPlan 1)), (count(DISTINCT t8.id) FILTER (WHERE (((t8.late <> 0) OR (t8.late IS NULL)) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), (count(DISTINCT t8.id) FILTER (WHERE ((alsijil_personalnote_extra_marks.extramark_id = 1) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))), (count(DISTINCT t8.absent) FILTER (WHERE (t8.absent AND (t8.excuse_type_id = 1) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856))))) Sort Method: quicksort Memory: 38kB Buffers: shared hit=18990721 read=111 -> GroupAggregate (cost=48.63..8046400.38 rows=2506 width=210) (actual time=2946.403..25504.119 rows=26 loops=1) Output: 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 t8.id) FILTER (WHERE (t8.absent AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856)))), count(DISTINCT t8.id) FILTER (WHERE (t8.absent AND (t8.excuse_type_id IS NULL) AND t8.excused AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856)))), count(DISTINCT t8.id) FILTER (WHERE (t8.absent AND (NOT t8.excused) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856)))), (SubPlan 1), count(DISTINCT t8.id) FILTER (WHERE (((t8.late <> 0) OR (t8.late IS NULL)) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856)))), count(DISTINCT t8.id) FILTER (WHERE ((alsijil_personalnote_extra_marks.extramark_id = 1) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856)))), count(DISTINCT t8.absent) FILTER (WHERE (t8.absent AND (t8.excuse_type_id = 1) AND ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) AND ((chronos_lesson_groups.group_id = 5856) OR (core_group_parent_groups.to_group_id = 5856) OR (chronos_extralesson_groups.group_id = 5856) OR (t23.to_group_id = 5856) OR (chronos_event_groups.group_id = 5856) OR (t27.to_group_id = 5856)))) Group Key: core_person.id Buffers: shared hit=18990702 read=111 -> Nested Loop Left Join (cost=48.63..56111.28 rows=88412 width=206) (actual time=2158.051..25261.753 rows=492384 loops=1) Output: 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, t8.id, t8.absent, chronos_validityrange.school_term_id, chronos_extralesson.school_term_id, chronos_event.school_term_id, chronos_lesson_groups.group_id, core_group_parent_groups.to_group_id, chronos_extralesson_groups.group_id, t23.to_group_id, chronos_event_groups.group_id, t27.to_group_id, t8.excuse_type_id, t8.excused, t8.late, alsijil_personalnote_extra_marks.extramark_id Buffers: shared hit=18907006 read=109 -> Nested Loop Left Join (cost=48.35..42780.70 rows=30892 width=206) (actual time=2158.034..24953.786 rows=492384 loops=1) Output: 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, t8.id, t8.absent, t8.excuse_type_id, t8.excused, t8.late, chronos_validityrange.school_term_id, chronos_extralesson.school_term_id, chronos_event.school_term_id, chronos_lesson_groups.group_id, core_group_parent_groups.to_group_id, chronos_extralesson_groups.group_id, t22.id, chronos_event_groups.group_id, t27.to_group_id, alsijil_personalnote_extra_marks.extramark_id Join Filter: (chronos_event.id = chronos_event_groups.event_id) Rows Removed by Join Filter: 8370528 Buffers: shared hit=18907006 read=109 -> Nested Loop Left Join (cost=47.79..35746.41 rows=10794 width=202) (actual time=2157.765..23509.580 rows=492384 loops=1) Output: 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, t8.id, t8.absent, t8.excuse_type_id, t8.excused, t8.late, chronos_validityrange.school_term_id, chronos_extralesson.school_term_id, chronos_event.school_term_id, chronos_event.id, chronos_lesson_groups.group_id, core_group_parent_groups.to_group_id, chronos_extralesson_groups.group_id, t22.id, alsijil_personalnote_extra_marks.extramark_id Buffers: shared hit=18906930 read=108 -> Nested Loop Left Join (cost=47.51..32425.59 rows=10794 width=198) (actual time=2157.741..22588.217 rows=492384 loops=1) Output: 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, t8.id, t8.absent, t8.excuse_type_id, t8.excused, t8.late, chronos_validityrange.school_term_id, chronos_extralesson.school_term_id, chronos_event.school_term_id, chronos_event.id, chronos_lesson_groups.group_id, core_group_parent_groups.to_group_id, chronos_extralesson_groups.group_id, t22.id Buffers: shared hit=17912031 read=108 -> Nested Loop Left Join (cost=47.23..30797.89 rows=3772 width=198) (actual time=2157.706..21644.067 rows=492384 loops=1) Output: 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, t8.id, t8.absent, t8.excuse_type_id, t8.excused, t8.late, chronos_validityrange.school_term_id, chronos_extralesson.school_term_id, chronos_event.school_term_id, chronos_event.id, chronos_lesson_groups.group_id, t18.id, chronos_extralesson_groups.group_id, t22.id Buffers: shared hit=16927262 read=108 -> Nested Loop Left Join (cost=46.81..25442.06 rows=3370 width=194) (actual time=2157.689..21231.304 rows=492384 loops=1) Output: 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, t8.id, t8.absent, t8.excuse_type_id, t8.excused, t8.late, chronos_validityrange.school_term_id, chronos_extralesson.school_term_id, chronos_extralesson.id, chronos_event.school_term_id, chronos_event.id, chronos_lesson_groups.group_id, t18.id Inner Unique: true Buffers: shared hit=16927262 read=108 -> Nested Loop Left Join (cost=46.53..24230.45 rows=3370 width=190) (actual time=2157.670..20145.244 rows=492384 loops=1) Output: 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, t8.id, t8.absent, t8.excuse_type_id, t8.excused, t8.late, chronos_validityrange.school_term_id, chronos_extralesson.school_term_id, chronos_extralesson.id, chronos_event.school_term_id, chronos_event.id, chronos_lesson_groups.group_id Buffers: shared hit=15450109 read=108 -> Nested Loop Left Join (cost=46.25..23088.71 rows=3370 width=190) (actual time=2157.641..18925.858 rows=492384 loops=1) Output: 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, t8.id, t8.absent, t8.excuse_type_id, t8.excused, t8.late, chronos_lesson.id, chronos_validityrange.school_term_id, chronos_extralesson.school_term_id, chronos_extralesson.id, chronos_event.school_term_id, chronos_event.id Inner Unique: true Filter: ((chronos_validityrange.school_term_id = 2) OR (chronos_extralesson.school_term_id = 2) OR (chronos_event.school_term_id = 2)) Rows Removed by Filter: 1570997 Buffers: shared hit=13972956 read=108 -> Nested Loop Left Join (cost=46.10..20364.32 rows=15131 width=186) (actual time=2153.024..17933.461 rows=2063381 loops=1) Output: 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, t8.id, t8.absent, t8.excuse_type_id, t8.excused, t8.late, t8.extra_lesson_id, chronos_lesson.id, chronos_validityrange.school_term_id, chronos_event.school_term_id, chronos_event.id Inner Unique: true Buffers: shared hit=13972956 read=108 -> Nested Loop Left Join (cost=45.96..18018.65 rows=15131 width=182) (actual time=2153.017..16666.081 rows=2063381 loops=1) Output: 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, t8.id, t8.absent, t8.excuse_type_id, t8.excused, t8.late, t8.extra_lesson_id, t8.event_id, chronos_lesson.id, chronos_validityrange.school_term_id Inner Unique: true Join Filter: (chronos_lesson.validity_id = chronos_validityrange.id) Rows Removed by Join Filter: 11196096 Buffers: shared hit=13972956 read=108 -> Nested Loop Left Join (cost=45.96..15710.09 rows=15131 width=182) (actual time=2152.993..14168.297 rows=2063381 loops=1) Output: 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, t8.id, t8.absent, t8.excuse_type_id, t8.excused, t8.late, t8.extra_lesson_id, t8.event_id, chronos_lesson.id, chronos_lesson.validity_id Inner Unique: true Buffers: shared hit=13972955 read=108 -> Nested Loop Left Join (cost=45.68..10710.17 rows=15131 width=178) (actual time=2152.981..9830.726 rows=2063381 loops=1) Output: 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, t8.id, t8.absent, t8.excuse_type_id, t8.excused, t8.late, t8.extra_lesson_id, t8.event_id, chronos_lessonperiod.lesson_id Inner Unique: true Buffers: shared hit=7782812 read=108 -> Nested Loop Left Join (cost=45.40..6109.06 rows=15131 width=178) (actual time=2152.959..4966.810 rows=2063381 loops=1) Output: 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, t8.id, t8.absent, t8.excuse_type_id, t8.excused, t8.late, t8.lesson_period_id, t8.extra_lesson_id, t8.event_id Buffers: shared hit=1592669 read=108 -> Nested Loop (cost=44.97..4210.92 rows=73 width=154) (actual time=2152.927..2278.370 rows=3667 loops=1) Output: 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 Inner Unique: true Buffers: shared hit=55394 read=108 -> Nested Loop (cost=44.54..968.86 rows=4767 width=158) (actual time=2147.952..2178.476 rows=14630 loops=1) Output: 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, alsijil_personalnote.id Buffers: shared hit=11238 -> Merge Join (cost=44.12..370.82 rows=23 width=158) (actual time=2147.928..2149.761 rows=26 loops=1) Output: 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, core_persongroupthrough.person_id Merge Cond: (core_person.id = core_persongroupthrough.person_id) Buffers: shared hit=339 -> Index Scan using core_person_pkey on public.core_person (cost=0.28..322.73 rows=2506 width=154) (actual time=2147.639..2149.154 rows=2177 loops=1) Output: core_person.id, core_person.extended_data, 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.description, core_person.primary_group_id, core_person.site_id, core_person.user_id Filter: (core_person.site_id = 1) Buffers: shared hit=317 -> Sort (cost=43.70..43.76 rows=23 width=4) (actual time=0.174..0.221 rows=26 loops=1) Output: core_persongroupthrough.person_id Sort Key: core_persongroupthrough.person_id Sort Method: quicksort Memory: 26kB Buffers: shared hit=22 -> Index Scan using core_persongroupthrough_group_id_62316e93 on public.core_persongroupthrough (cost=0.29..43.18 rows=23 width=4) (actual time=0.043..0.152 rows=26 loops=1) Output: core_persongroupthrough.person_id Index Cond: (core_persongroupthrough.group_id = 5856) Buffers: shared hit=22 -> Index Scan using alsijil_personalnote_person_id_35fbe0f4 on public.alsijil_personalnote (cost=0.42..19.86 rows=614 width=8) (actual time=0.017..0.838 rows=563 loops=26) Output: alsijil_personalnote.id, alsijil_personalnote.extended_data, alsijil_personalnote.week, alsijil_personalnote.absent, alsijil_personalnote.late, alsijil_personalnote.excused, alsijil_personalnote.remarks, alsijil_personalnote.lesson_period_id, alsijil_personalnote.person_id, alsijil_personalnote.site_id, alsijil_personalnote.excuse_type_id, alsijil_personalnote.year, alsijil_personalnote.event_id, alsijil_personalnote.extra_lesson_id Index Cond: (alsijil_personalnote.person_id = core_person.id) Buffers: shared hit=10899 -> Index Only Scan using alsijil_personalnote_gro_personalnote_id_group_id_2fbbd65b_uniq on public.alsijil_personalnote_groups_of_person (cost=0.43..0.68 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=14630) Output: alsijil_personalnote_groups_of_person.personalnote_id, alsijil_personalnote_groups_of_person.group_id Index Cond: ((alsijil_personalnote_groups_of_person.personalnote_id = alsijil_personalnote.id) AND (alsijil_personalnote_groups_of_person.group_id = 5856)) Heap Fetches: 373 Buffers: shared hit=44156 read=108 -> Index Scan using alsijil_personalnote_person_id_35fbe0f4 on public.alsijil_personalnote t8 (cost=0.42..19.86 rows=614 width=28) (actual time=0.009..0.604 rows=563 loops=3667) Output: t8.id, t8.extended_data, t8.week, t8.absent, t8.late, t8.excused, t8.remarks, t8.lesson_period_id, t8.person_id, t8.site_id, t8.excuse_type_id, t8.year, t8.event_id, t8.extra_lesson_id Index Cond: (t8.person_id = core_person.id) Buffers: shared hit=1537275 -> Index Scan using chronos_lessonperiod_pkey on public.chronos_lessonperiod (cost=0.29..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2063381) Output: chronos_lessonperiod.id, chronos_lessonperiod.extended_data, chronos_lessonperiod.lesson_id, chronos_lessonperiod.period_id, chronos_lessonperiod.room_id, chronos_lessonperiod.site_id Index Cond: (chronos_lessonperiod.id = t8.lesson_period_id) Buffers: shared hit=6190143 -> Index Scan using chronos_lesson_pkey on public.chronos_lesson (cost=0.28..0.33 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=2063381) Output: chronos_lesson.id, chronos_lesson.extended_data, chronos_lesson.site_id, chronos_lesson.subject_id, chronos_lesson.validity_id Index Cond: (chronos_lesson.id = chronos_lessonperiod.lesson_id) Buffers: shared hit=6190143 -> Materialize (cost=0.00..1.17 rows=11 width=8) (actual time=0.000..0.000 rows=6 loops=2063381) Output: chronos_validityrange.school_term_id, chronos_validityrange.id Buffers: shared hit=1 -> Seq Scan on public.chronos_validityrange (cost=0.00..1.11 rows=11 width=8) (actual time=0.014..0.021 rows=10 loops=1) Output: chronos_validityrange.school_term_id, chronos_validityrange.id Buffers: shared hit=1 -> Index Scan using chronos_event_pkey on public.chronos_event (cost=0.14..0.16 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=2063381) Output: chronos_event.id, chronos_event.extended_data, chronos_event.title, chronos_event.date_start, chronos_event.date_end, chronos_event.period_from_id, chronos_event.period_to_id, chronos_event.site_id, chronos_event.school_term_id Index Cond: (chronos_event.id = t8.event_id) -> Index Scan using chronos_extralesson_pkey on public.chronos_extralesson (cost=0.14..0.16 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=2063381) Output: chronos_extralesson.id, chronos_extralesson.extended_data, chronos_extralesson.week, chronos_extralesson.comment, chronos_extralesson.period_id, chronos_extralesson.room_id, chronos_extralesson.site_id, chronos_extralesson.subject_id, chronos_extralesson.school_term_id, chronos_extralesson.year Index Cond: (chronos_extralesson.id = t8.extra_lesson_id) -> Index Only Scan using chronos_lesson_groups_lesson_id_group_id_21a8a21a_uniq on public.chronos_lesson_groups (cost=0.28..0.33 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=492384) Output: chronos_lesson_groups.lesson_id, chronos_lesson_groups.group_id Index Cond: (chronos_lesson_groups.lesson_id = chronos_lesson.id) Heap Fetches: 492384 Buffers: shared hit=1477153 -> Index Only Scan using core_group_pkey on public.core_group t18 (cost=0.28..0.36 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=492384) Output: t18.id Index Cond: (t18.id = chronos_lesson_groups.group_id) Heap Fetches: 492384 Buffers: shared hit=1477153 -> Nested Loop Left Join (cost=0.43..1.58 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=492384) Output: chronos_extralesson_groups.group_id, chronos_extralesson_groups.extralesson_id, t22.id Inner Unique: true -> Index Scan using chronos_extralesson_groups_extralesson_id_cd22f42a on public.chronos_extralesson_groups (cost=0.15..0.23 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=492384) Output: chronos_extralesson_groups.id, chronos_extralesson_groups.extralesson_id, chronos_extralesson_groups.group_id Index Cond: (chronos_extralesson_groups.extralesson_id = chronos_extralesson.id) -> Index Only Scan using core_group_pkey on public.core_group t22 (cost=0.28..1.34 rows=1 width=4) (never executed) Output: t22.id Index Cond: (t22.id = chronos_extralesson_groups.group_id) Heap Fetches: 0 -> Index Only Scan using core_group_parent_groups_from_group_id_to_group_i_cfd5deea_uniq on public.core_group_parent_groups (cost=0.28..0.40 rows=3 width=8) (actual time=0.001..0.001 rows=1 loops=492384) Output: core_group_parent_groups.from_group_id, core_group_parent_groups.to_group_id Index Cond: (core_group_parent_groups.from_group_id = t18.id) Heap Fetches: 0 Buffers: shared hit=984769 -> Index Only Scan using alsijil_personalnote_ext_personalnote_id_extramar_b2178135_uniq on public.alsijil_personalnote_extra_marks (cost=0.28..0.30 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=492384) Output: alsijil_personalnote_extra_marks.personalnote_id, alsijil_personalnote_extra_marks.extramark_id Index Cond: (alsijil_personalnote_extra_marks.personalnote_id = t8.id) Heap Fetches: 10130 Buffers: shared hit=994899 -> Materialize (cost=0.56..72.27 rows=43 width=12) (actual time=0.000..0.001 rows=17 loops=492384) Output: chronos_event_groups.group_id, chronos_event_groups.event_id, t27.to_group_id Buffers: shared hit=76 read=1 -> Nested Loop Left Join (cost=0.56..72.05 rows=43 width=12) (actual time=0.087..0.249 rows=17 loops=1) Output: chronos_event_groups.group_id, chronos_event_groups.event_id, t27.to_group_id Buffers: shared hit=76 read=1 -> Nested Loop Left Join (cost=0.28..65.58 rows=15 width=12) (actual time=0.053..0.152 rows=17 loops=1) Output: chronos_event_groups.group_id, chronos_event_groups.event_id, t26.id Inner Unique: true Buffers: shared hit=43 -> Seq Scan on public.chronos_event_groups (cost=0.00..1.15 rows=15 width=8) (actual time=0.024..0.028 rows=17 loops=1) Output: chronos_event_groups.id, chronos_event_groups.event_id, chronos_event_groups.group_id Buffers: shared hit=1 -> Index Only Scan using core_group_pkey on public.core_group t26 (cost=0.28..4.29 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=17) Output: t26.id Index Cond: (t26.id = chronos_event_groups.group_id) Heap Fetches: 7 Buffers: shared hit=42 -> Index Only Scan using core_group_parent_groups_from_group_id_to_group_i_cfd5deea_uniq on public.core_group_parent_groups t27 (cost=0.28..0.40 rows=3 width=8) (actual time=0.005..0.005 rows=0 loops=17) Output: t27.from_group_id, t27.to_group_id Index Cond: (t27.from_group_id = t26.id) Heap Fetches: 0 Buffers: shared hit=33 read=1 -> Index Only Scan using core_group_parent_groups_from_group_id_to_group_i_cfd5deea_uniq on public.core_group_parent_groups t23 (cost=0.28..0.40 rows=3 width=8) (actual time=0.000..0.000 rows=0 loops=492384) Output: t23.from_group_id, t23.to_group_id Index Cond: (t23.from_group_id = t22.id) Heap Fetches: 0 SubPlan 1 -> Unique (cost=3182.80..3182.81 rows=1 width=12) (actual time=5.178..5.180 rows=1 loops=26) Output: (sum(u2.late)), u0.id Buffers: shared hit=83696 read=2 -> Sort (cost=3182.80..3182.81 rows=1 width=12) (actual time=5.174..5.175 rows=1 loops=26) Output: (sum(u2.late)), u0.id Sort Key: (sum(u2.late)) Sort Method: quicksort Memory: 25kB Buffers: shared hit=83696 read=2 -> GroupAggregate (cost=3022.07..3182.79 rows=1 width=12) (actual time=5.157..5.158 rows=1 loops=26) Output: sum(u2.late), u0.id Group Key: u0.id Buffers: shared hit=83696 read=2 -> Hash Right Join (cost=3022.07..3180.84 rows=389 width=6) (actual time=5.106..5.127 rows=134 loops=26) Output: u0.id, u2.late Hash Cond: (u21.from_group_id = u20.id) Filter: ((u11.group_id = 5856) OR (u13.to_group_id = 5856) OR (u15.group_id = 5856) OR (u17.to_group_id = 5856) OR (u19.group_id = 5856) OR (u21.to_group_id = 5856)) Buffers: shared hit=83696 read=2 -> Seq Scan on public.core_group_parent_groups u21 (cost=0.00..55.83 rows=3483 width=8) (actual time=0.005..0.340 rows=3408 loops=26) Output: u21.id, u21.from_group_id, u21.to_group_id Buffers: shared hit=546 -> Hash (cost=3006.37..3006.37 rows=1256 width=30) (actual time=3.996..3.997 rows=134 loops=26) Output: u0.id, u2.late, u11.group_id, u13.to_group_id, u15.group_id, u17.to_group_id, u19.group_id, u20.id Buckets: 2048 Batches: 1 Memory Usage: 23kB Buffers: shared hit=83150 read=2 -> Hash Left Join (cost=888.54..3006.37 rows=1256 width=30) (actual time=2.269..3.943 rows=134 loops=26) Output: u0.id, u2.late, u11.group_id, u13.to_group_id, u15.group_id, u17.to_group_id, u19.group_id, u20.id Hash Cond: (u7.id = u15.extralesson_id) Buffers: shared hit=83150 read=2 -> Nested Loop Left Join (cost=589.30..2686.72 rows=392 width=26) (actual time=2.216..3.853 rows=134 loops=26) Output: u0.id, u2.late, u7.id, u11.group_id, u13.to_group_id, u19.group_id, u20.id Buffers: shared hit=83009 -> Nested Loop Left Join (cost=589.02..2627.60 rows=137 width=26) (actual time=2.207..3.528 rows=134 loops=26) Output: u0.id, u2.late, u7.id, u11.group_id, u12.id, u19.group_id, u20.id Inner Unique: true Buffers: shared hit=76026 -> Hash Left Join (cost=588.74..2578.34 rows=137 width=22) (actual time=2.198..3.240 rows=134 loops=26) Output: u0.id, u2.late, u7.id, u11.group_id, u19.group_id, u20.id Hash Cond: (u9.id = u19.event_id) Buffers: shared hit=65552 -> Nested Loop Left Join (cost=522.97..2511.03 rows=137 width=18) (actual time=2.192..3.203 rows=134 loops=26) Output: u0.id, u2.late, u7.id, u9.id, u11.group_id Buffers: shared hit=65509 -> Nested Loop (cost=522.69..2464.61 rows=137 width=18) (actual time=2.182..2.840 rows=134 loops=26) Output: u0.id, u2.late, u4.id, u7.id, u9.id Buffers: shared hit=55035 -> Index Scan using core_person_pkey on public.core_person u0 (cost=0.28..8.30 rows=1 width=4) (actual time=0.008..0.010 rows=1 loops=26) Output: u0.id, u0.extended_data, u0.is_active, u0.first_name, u0.last_name, u0.additional_name, u0.short_name, u0.street, u0.housenumber, u0.postal_code, u0.place, u0.phone_number, u0.mobile_number, u0.email, u0.date_of_birth, u0.sex, u0.photo, u0.description, u0.primary_group_id, u0.site_id, u0.user_id Index Cond: (u0.id = core_person.id) Filter: (u0.site_id = 1) Buffers: shared hit=78 -> Hash Left Join (cost=522.41..2454.94 rows=137 width=18) (actual time=2.170..2.804 rows=134 loops=26) Output: u2.late, u2.person_id, u4.id, u7.id, u9.id Inner Unique: true Hash Cond: (u2.event_id = u9.id) Filter: ((u5.school_term_id = 2) OR (u7.school_term_id = 2) OR (u9.school_term_id = 2)) Rows Removed by Filter: 428 Buffers: shared hit=54957 -> Hash Left Join (cost=520.96..2451.88 rows=614 width=26) (actual time=0.343..2.703 rows=563 loops=26) Output: u2.late, u2.person_id, u2.event_id, u4.id, u5.school_term_id, u7.id, u7.school_term_id Inner Unique: true Hash Cond: (u2.extra_lesson_id = u7.id) Buffers: shared hit=54956 -> Hash Left Join (cost=510.67..2439.98 rows=614 width=22) (actual time=0.335..2.570 rows=563 loops=26) Output: u2.late, u2.person_id, u2.extra_lesson_id, u2.event_id, u4.id, u5.school_term_id Inner Unique: true Hash Cond: (u4.validity_id = u5.id) Buffers: shared hit=54951 -> Nested Loop Left Join (cost=509.42..2436.50 rows=614 width=22) (actual time=0.330..2.414 rows=563 loops=26) Output: u2.late, u2.person_id, u2.extra_lesson_id, u2.event_id, u4.id, u4.validity_id Inner Unique: true Buffers: shared hit=54950 -> Hash Left Join (cost=509.14..2233.60 rows=614 width=18) (actual time=0.320..1.244 rows=563 loops=26) Output: u2.late, u2.person_id, u2.extra_lesson_id, u2.event_id, u3.lesson_id Inner Unique: true Hash Cond: (u2.lesson_period_id = u3.id) Buffers: shared hit=11060 -> Bitmap Heap Scan on public.alsijil_personalnote u2 (cost=9.18..1732.03 rows=614 width=18) (actual time=0.113..0.774 rows=563 loops=26) Output: u2.id, u2.extended_data, u2.week, u2.absent, u2.late, u2.excused, u2.remarks, u2.lesson_period_id, u2.person_id, u2.site_id, u2.excuse_type_id, u2.year, u2.event_id, u2.extra_lesson_id Recheck Cond: (u2.person_id = core_person.id) Heap Blocks: exact=10807 Buffers: shared hit=10897 -> Bitmap Index Scan on alsijil_personalnote_person_id_35fbe0f4 (cost=0.00..9.03 rows=614 width=0) (actual time=0.056..0.056 rows=563 loops=26) Index Cond: (u2.person_id = core_person.id) Buffers: shared hit=89 -> Hash (cost=312.76..312.76 rows=14976 width=8) (actual time=5.030..5.031 rows=14905 loops=1) Output: u3.id, u3.lesson_id Buckets: 16384 Batches: 1 Memory Usage: 711kB Buffers: shared hit=163 -> Seq Scan on public.chronos_lessonperiod u3 (cost=0.00..312.76 rows=14976 width=8) (actual time=0.007..2.741 rows=14905 loops=1) Output: u3.id, u3.lesson_id Buffers: shared hit=163 -> Index Scan using chronos_lesson_pkey on public.chronos_lesson u4 (cost=0.28..0.33 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=14630) Output: u4.id, u4.extended_data, u4.site_id, u4.subject_id, u4.validity_id Index Cond: (u4.id = u3.lesson_id) Buffers: shared hit=43890 -> Hash (cost=1.11..1.11 rows=11 width=8) (actual time=0.018..0.018 rows=11 loops=1) Output: u5.id, u5.school_term_id Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=1 -> Seq Scan on public.chronos_validityrange u5 (cost=0.00..1.11 rows=11 width=8) (actual time=0.012..0.014 rows=11 loops=1) Output: u5.id, u5.school_term_id Buffers: shared hit=1 -> Hash (cost=7.35..7.35 rows=235 width=8) (actual time=0.121..0.122 rows=235 loops=1) Output: u7.id, u7.school_term_id Buckets: 1024 Batches: 1 Memory Usage: 17kB Buffers: shared hit=5 -> Seq Scan on public.chronos_extralesson u7 (cost=0.00..7.35 rows=235 width=8) (actual time=0.013..0.068 rows=235 loops=1) Output: u7.id, u7.school_term_id Buffers: shared hit=5 -> Hash (cost=1.20..1.20 rows=20 width=8) (actual time=0.016..0.017 rows=29 loops=1) Output: u9.id, u9.school_term_id Buckets: 1024 Batches: 1 Memory Usage: 10kB Buffers: shared hit=1 -> Seq Scan on public.chronos_event u9 (cost=0.00..1.20 rows=20 width=8) (actual time=0.007..0.011 rows=29 loops=1) Output: u9.id, u9.school_term_id Buffers: shared hit=1 -> Index Only Scan using chronos_lesson_groups_lesson_id_group_id_21a8a21a_uniq on public.chronos_lesson_groups u11 (cost=0.28..0.33 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=3491) Output: u11.lesson_id, u11.group_id Index Cond: (u11.lesson_id = u4.id) Heap Fetches: 3491 Buffers: shared hit=10474 -> Hash (cost=65.58..65.58 rows=15 width=12) (actual time=0.053..0.054 rows=17 loops=1) Output: u19.event_id, u19.group_id, u20.id Buckets: 1024 Batches: 1 Memory Usage: 9kB Buffers: shared hit=43 -> Nested Loop Left Join (cost=0.28..65.58 rows=15 width=12) (actual time=0.018..0.049 rows=17 loops=1) Output: u19.event_id, u19.group_id, u20.id Inner Unique: true Buffers: shared hit=43 -> Seq Scan on public.chronos_event_groups u19 (cost=0.00..1.15 rows=15 width=8) (actual time=0.006..0.007 rows=17 loops=1) Output: u19.id, u19.event_id, u19.group_id Buffers: shared hit=1 -> Index Only Scan using core_group_pkey on public.core_group u20 (cost=0.28..4.29 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=17) Output: u20.id Index Cond: (u20.id = u19.group_id) Heap Fetches: 7 Buffers: shared hit=42 -> Index Only Scan using core_group_pkey on public.core_group u12 (cost=0.28..0.36 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=3491) Output: u12.id Index Cond: (u12.id = u11.group_id) Heap Fetches: 3491 Buffers: shared hit=10474 -> Index Only Scan using core_group_parent_groups_from_group_id_to_group_i_cfd5deea_uniq on public.core_group_parent_groups u13 (cost=0.28..0.40 rows=3 width=8) (actual time=0.002..0.002 rows=1 loops=3491) Output: u13.from_group_id, u13.to_group_id Index Cond: (u13.from_group_id = u12.id) Heap Fetches: 0 Buffers: shared hit=6983 -> Hash (cost=289.83..289.83 rows=753 width=12) (actual time=1.292..1.295 rows=265 loops=1) Output: u15.extralesson_id, u15.group_id, u17.to_group_id Buckets: 1024 Batches: 1 Memory Usage: 19kB Buffers: shared hit=141 read=2 -> Hash Right Join (cost=213.41..289.83 rows=753 width=12) (actual time=1.230..1.256 rows=265 loops=1) Output: u15.extralesson_id, u15.group_id, u17.to_group_id Hash Cond: (u17.from_group_id = u16.id) Buffers: shared hit=141 read=2 -> Seq Scan on public.core_group_parent_groups u17 (cost=0.00..55.83 rows=3483 width=8) (actual time=0.015..0.304 rows=3408 loops=1) Output: u17.id, u17.from_group_id, u17.to_group_id Buffers: shared hit=21 -> Hash (cost=210.13..210.13 rows=263 width=12) (actual time=0.588..0.590 rows=265 loops=1) Output: u15.extralesson_id, u15.group_id, u16.id Buckets: 1024 Batches: 1 Memory Usage: 20kB Buffers: shared hit=120 read=2 -> Merge Left Join (cost=21.79..210.13 rows=263 width=12) (actual time=0.127..0.551 rows=265 loops=1) Output: u15.extralesson_id, u15.group_id, u16.id Inner Unique: true Merge Cond: (u15.group_id = u16.id) Buffers: shared hit=120 read=2 -> Sort (cost=15.20..15.86 rows=263 width=8) (actual time=0.083..0.099 rows=265 loops=1) Output: u15.extralesson_id, u15.group_id Sort Key: u15.group_id Sort Method: quicksort Memory: 37kB Buffers: shared hit=2 -> Seq Scan on public.chronos_extralesson_groups u15 (cost=0.00..4.63 rows=263 width=8) (actual time=0.009..0.034 rows=265 loops=1) Output: u15.extralesson_id, u15.group_id Buffers: shared hit=2 -> Index Only Scan using core_group_pkey on public.core_group u16 (cost=0.28..346.23 rows=1217 width=4) (actual time=0.008..0.353 rows=665 loops=1) Output: u16.id Heap Fetches: 198 Buffers: shared hit=118 read=2 Planning: Buffers: shared hit=1822 read=2 Planning Time: 17.074 ms JIT: Functions: 201 Options: Inlining true, Optimization true, Expressions true, Deforming true Timing: Generation 22.134 ms, Inlining 80.809 ms, Optimization 1289.741 ms, Emission 772.041 ms, Total 2164.725 ms Execution Time: 25581.276 ms-
No lengthy analysis done until now, but a few low-hanging fruits that might help:
- Both timetable and register objects should be re-modelled into polymorphic models — all lesosn objects should have a common parent class, so we do not have to join over a row of semi-identical tables.
- We should get rid of empty personal notes (#134 (closed))
- Limit returned fields to those that are needed — most importantly, get everything but
idout of theDISTINCTquery
Additionally, a hint from credativ DB team:
-
(U11."group_id" = 5856 OR U13."to_group_id" = 5856 OR U15."group_id" = 5856 OR U17."to_group_id" = 5856 OR U19."group_id" = 5856 OR U21."to_group_id" = 5856)should probably be part of theLEFT OUTER JOINconditions further up (same forschool_term)
Edited by Nik | Klampfradler -
https://docs.djangoproject.com/en/3.1/ref/models/querysets/#filteredrelation-objects might be of some help
-
Moving the most of the
WHEREclause into the joins gives us an improvement of (only) a few seconds:https://explain.depesz.com/s/6ziN
--- foo.sql 2021-03-18 12:52:18.177894411 +0100 +++ foo2.sql 2021-03-18 14:34:54.184927744 +0100 @@ -1,3 +1,4 @@ +EXPLAIN (ANALYZE, VERBOSE, BUFFERS) SELECT DISTINCT "core_person"."id", "core_person"."site_id", "core_person"."extended_data", @@ -32,30 +33,30 @@ LEFT OUTER JOIN "chronos_lesson" U4 ON (U3."lesson_id" = U4."id") LEFT OUTER JOIN "chronos_validityrange" U5 - ON (U4."validity_id" = U5."id") + ON (U4."validity_id" = U5."id" AND U5."school_term_id" = 2) LEFT OUTER JOIN "chronos_extralesson" U7 - ON (U2."extra_lesson_id" = U7."id") + ON (U2."extra_lesson_id" = U7."id" AND U5."school_term_id" = 2) LEFT OUTER JOIN "chronos_event" U9 - ON (U2."event_id" = U9."id") + ON (U2."event_id" = U9."id" AND U5."school_term_id" = 2) LEFT OUTER JOIN "chronos_lesson_groups" U11 - ON (U4."id" = U11."lesson_id") + ON (U4."id" = U11."lesson_id" AND U11."group_id" = 5856) LEFT OUTER JOIN "core_group" U12 ON (U11."group_id" = U12."id") LEFT OUTER JOIN "core_group_parent_groups" U13 - ON (U12."id" = U13."from_group_id") + ON (U12."id" = U13."from_group_id" AND U13."to_group_id" = 5856) LEFT OUTER JOIN "chronos_extralesson_groups" U15 - ON (U7."id" = U15."extralesson_id") + ON (U7."id" = U15."extralesson_id" AND U15."group_id" = 5856) LEFT OUTER JOIN "core_group" U16 ON (U15."group_id" = U16."id") LEFT OUTER JOIN "core_group_parent_groups" U17 - ON (U16."id" = U17."from_group_id") + ON (U16."id" = U17."from_group_id" AND U17."to_group_id" = 5856) LEFT OUTER JOIN "chronos_event_groups" U19 - ON (U9."id" = U19."event_id") + ON (U9."id" = U19."event_id" AND U19."group_id" = 5856) LEFT OUTER JOIN "core_group" U20 ON (U19."group_id" = U20."id") LEFT OUTER JOIN "core_group_parent_groups" U21 - ON (U20."id" = U21."from_group_id") - WHERE (U0."site_id" = 1 AND (U5."school_term_id" = 2 OR U7."school_term_id" = 2 OR U9."school_term_id" = 2) AND (U11."group_id" = 5856 OR U13."to_group_id" = 5856 OR U15."group_id" = 5856 OR U17."to_group_id" = 5856 OR U19."group_id" = 5856 OR U21."to_group_id" = 5856) AND U0."id" = "core_person"."id") + ON (U20."id" = U21."from_group_id" AND U21."to_group_id" = 5856) + WHERE (U0."site_id" = 1 AND U0."id" = "core_person"."id") GROUP BY U0."id" ) AS "tardiness", COUNT(DISTINCT T8."id") FILTER (WHERE (NOT (T8."late" = 0 AND T8."late" IS NOT NULL) AND ("chronos_validityrange"."school_term_id" = 2 OR "chronos_extralesson"."school_term_id" = 2 OR "chronos_event"."school_term_id" = 2) AND ("chronos_lesson_groups"."group_id" = 5856 OR "core_group_parent_groups"."to_group_id" = 5856 OR "chronos_extralesson_groups"."group_id" = 5856 OR T23."to_group_id" = 5856 OR "chronos_event_groups"."group_id" = 5856 OR T27."to_group_id" = 5856))) AS "tardiness_count",
assigned to @hansegucker
created merge request !162 (merged) to address this issue
mentioned in merge request !162 (merged)
mentioned in commit f6a1b817
closed with merge request !162 (merged)
-
For documentation, here's the query after !162 (merged), and its plan:
SELECT "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(filtered_personal_notes."id") FILTER ( WHERE filtered_personal_notes."absent") AS "absences_count", COUNT(filtered_personal_notes."id") FILTER ( WHERE (filtered_personal_notes."absent" AND filtered_personal_notes."excuse_type_id" IS NULL AND filtered_personal_notes."excused")) AS "excused", COUNT(filtered_personal_notes."id") FILTER ( WHERE (filtered_personal_notes."absent" AND NOT filtered_personal_notes."excused")) AS "unexcused", SUM(filtered_personal_notes."late") AS "tardiness", COUNT(filtered_personal_notes."id") FILTER ( WHERE filtered_personal_notes."late" > 0) AS "tardiness_count", COUNT(filtered_personal_notes."id") FILTER ( WHERE "alsijil_personalnote_extra_marks"."extramark_id" = 1) AS "HA_count", COUNT(filtered_personal_notes."absent") FILTER ( WHERE (filtered_personal_notes."absent" AND filtered_personal_notes."excuse_type_id" = 1)) AS "s_count" FROM "core_person" INNER JOIN "core_persongroupthrough" ON ("core_person"."id" = "core_persongroupthrough"."person_id") LEFT OUTER JOIN "alsijil_personalnote" filtered_personal_notes ON ("core_person"."id" = filtered_personal_notes."person_id" AND ((filtered_personal_notes."event_id" IN (SELECT U0."id" FROM "chronos_event" U0 INNER JOIN "chronos_event_groups" U3 ON (U0."id" = U3."event_id") INNER JOIN "core_group" U4 ON (U3."group_id" = U4."id") LEFT OUTER JOIN "core_group_parent_groups" U5 ON (U4."id" = U5."from_group_id") WHERE (U0."site_id" = 1 AND U0."school_term_id" = 2 AND (U3."group_id" = 5849 OR U5."to_group_id" = 5849))) OR filtered_personal_notes."lesson_period_id" IN (SELECT U0."id" FROM "chronos_lessonperiod" U0 INNER JOIN "chronos_lesson" U2 ON (U0."lesson_id" = U2."id") INNER JOIN "chronos_validityrange" U3 ON (U2."validity_id" = U3."id") INNER JOIN "chronos_lesson_groups" U5 ON (U2."id" = U5."lesson_id") INNER JOIN "core_group" U6 ON (U5."group_id" = U6."id") LEFT OUTER JOIN "core_group_parent_groups" U7 ON (U6."id" = U7."from_group_id") WHERE (U0."site_id" = 1 AND U3."school_term_id" = 2 AND (U5."group_id" = 5849 OR U7."to_group_id" = 5849))) OR filtered_personal_notes."extra_lesson_id" IN (SELECT U0."id" FROM "chronos_extralesson" U0 INNER JOIN "chronos_extralesson_groups" U3 ON (U0."id" = U3."extralesson_id") INNER JOIN "core_group" U4 ON (U3."group_id" = U4."id") LEFT OUTER JOIN "core_group_parent_groups" U5 ON (U4."id" = U5."from_group_id") WHERE (U0."site_id" = 1 AND U0."school_term_id" = 2 AND (U3."group_id" = 5849 OR U5."to_group_id" = 5849)))))) LEFT OUTER JOIN "alsijil_personalnote_extra_marks" ON (filtered_personal_notes."id" = "alsijil_personalnote_extra_marks"."personalnote_id") WHERE ("core_person"."site_id" = 1 AND "core_persongroupthrough"."group_id" = 5849) GROUP BY "core_person"."id"HashAggregate (cost=2841.27..2866.31 rows=2504 width=210) (actual time=46.576..46.657 rows=30 loops=1) Output: 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(filtered_personal_notes.id) FILTER (WHERE filtered_personal_notes.absent), count(filtered_personal_notes.id) FILTER (WHERE (filtered_personal_notes.absent AND (filtered_personal_notes.excuse_type_id IS NULL) AND filtered_personal_notes.excused)), count(filtered_personal_notes.id) FILTER (WHERE (filtered_personal_notes.absent AND (NOT filtered_personal_notes.excused))), sum(filtered_personal_notes.late), count(filtered_personal_notes.id) FILTER (WHERE (filtered_personal_notes.late > 0)), count(filtered_personal_notes.id) FILTER (WHERE (alsijil_personalnote_extra_marks.extramark_id = 1)), count(filtered_personal_notes.absent) FILTER (WHERE (filtered_personal_notes.absent AND (filtered_personal_notes.excuse_type_id = 1))) Group Key: core_person.id Batches: 1 Memory Usage: 145kB Buffers: shared hit=18020 -> Nested Loop Left Join (cost=583.82..2691.09 rows=5461 width=170) (actual time=9.995..45.424 rows=4212 loops=1) Output: 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, filtered_personal_notes.id, filtered_personal_notes.absent, filtered_personal_notes.excuse_type_id, filtered_personal_notes.excused, filtered_personal_notes.late, alsijil_personalnote_extra_marks.extramark_id Buffers: shared hit=18020 -> Hash Join (cost=120.89..173.95 rows=30 width=154) (actual time=2.427..2.592 rows=30 loops=1) Output: 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 Inner Unique: true Hash Cond: (core_persongroupthrough.person_id = core_person.id) Buffers: shared hit=83 -> Index Scan using core_persongroupthrough_group_id_62316e93 on public.core_persongroupthrough (cost=0.29..53.27 rows=30 width=4) (actual time=0.024..0.133 rows=30 loops=1) Output: core_persongroupthrough.id, core_persongroupthrough.extended_data, core_persongroupthrough.group_id, core_persongroupthrough.person_id, core_persongroupthrough.site_id Index Cond: (core_persongroupthrough.group_id = 5849) Buffers: shared hit=25 -> Hash (cost=89.30..89.30 rows=2504 width=154) (actual time=2.364..2.365 rows=2504 loops=1) Output: 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 Buckets: 4096 Batches: 1 Memory Usage: 492kB Buffers: shared hit=58 -> Seq Scan on public.core_person (cost=0.00..89.30 rows=2504 width=154) (actual time=0.012..0.985 rows=2504 loops=1) Output: 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 Filter: (core_person.site_id = 1) Buffers: shared hit=58 -> Hash Right Join (cost=462.93..526.00 rows=540 width=20) (actual time=1.338..1.398 rows=140 loops=30) Output: filtered_personal_notes.id, filtered_personal_notes.absent, filtered_personal_notes.excuse_type_id, filtered_personal_notes.excused, filtered_personal_notes.late, filtered_personal_notes.person_id, alsijil_personalnote_extra_marks.extramark_id Inner Unique: true Hash Cond: (alsijil_personalnote_extra_marks.personalnote_id = filtered_personal_notes.id) Buffers: shared hit=17937 -> Seq Scan on public.alsijil_personalnote_extra_marks (cost=0.00..53.91 rows=3491 width=8) (actual time=0.002..0.263 rows=3491 loops=30) Output: alsijil_personalnote_extra_marks.id, alsijil_personalnote_extra_marks.personalnote_id, alsijil_personalnote_extra_marks.extramark_id Buffers: shared hit=570 -> Hash (cost=456.18..456.18 rows=540 width=16) (actual time=0.751..0.751 rows=140 loops=30) Output: filtered_personal_notes.id, filtered_personal_notes.absent, filtered_personal_notes.excuse_type_id, filtered_personal_notes.excused, filtered_personal_notes.late, filtered_personal_notes.person_id Buckets: 1024 Batches: 1 Memory Usage: 16kB Buffers: shared hit=17367 -> Index Scan using alsijil_personalnote_person_id_35fbe0f4 on public.alsijil_personalnote filtered_personal_notes (cost=432.21..456.18 rows=540 width=16) (actual time=0.607..0.724 rows=140 loops=30) Output: filtered_personal_notes.id, filtered_personal_notes.absent, filtered_personal_notes.excuse_type_id, filtered_personal_notes.excused, filtered_personal_notes.late, filtered_personal_notes.person_id Index Cond: (filtered_personal_notes.person_id = core_person.id) Filter: ((hashed SubPlan 1) OR (hashed SubPlan 2) OR (hashed SubPlan 3)) Rows Removed by Filter: 502 Buffers: shared hit=17367 SubPlan 1 -> Nested Loop Left Join (cost=0.56..4.94 rows=1 width=4) (actual time=0.108..0.112 rows=0 loops=1) Output: u0.id Filter: ((u3.group_id = 5849) OR (u5.to_group_id = 5849)) Rows Removed by Filter: 2 Buffers: shared hit=19 -> Nested Loop (cost=0.28..4.53 rows=1 width=12) (actual time=0.049..0.086 rows=2 loops=1) Output: u0.id, u3.group_id, u4.id Inner Unique: true Buffers: shared hit=15 -> Nested Loop (cost=0.00..2.82 rows=1 width=8) (actual time=0.027..0.061 rows=2 loops=1) Output: u0.id, u3.group_id Join Filter: (u0.id = u3.event_id) Rows Removed by Join Filter: 151 Buffers: shared hit=10 -> Seq Scan on public.chronos_event u0 (cost=0.00..1.44 rows=1 width=4) (actual time=0.010..0.012 rows=9 loops=1) Output: u0.id, u0.extended_data, u0.title, u0.date_start, u0.date_end, u0.period_from_id, u0.period_to_id, u0.site_id, u0.school_term_id Filter: ((u0.site_id = 1) AND (u0.school_term_id = 2)) Rows Removed by Filter: 20 Buffers: shared hit=1 -> Seq Scan on public.chronos_event_groups u3 (cost=0.00..1.17 rows=17 width=8) (actual time=0.001..0.003 rows=17 loops=9) Output: u3.id, u3.event_id, u3.group_id Buffers: shared hit=9 -> Index Only Scan using core_group_pkey on public.core_group u4 (cost=0.28..1.71 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=2) Output: u4.id Index Cond: (u4.id = u3.group_id) Heap Fetches: 0 Buffers: shared hit=5 -> Index Only Scan using core_group_parent_groups_from_group_id_to_group_i_cfd5deea_uniq on public.core_group_parent_groups u5 (cost=0.28..0.37 rows=3 width=8) (actual time=0.011..0.011 rows=0 loops=2) Output: u5.from_group_id, u5.to_group_id Index Cond: (u5.from_group_id = u4.id) Heap Fetches: 0 Buffers: shared hit=4 SubPlan 2 -> Nested Loop (cost=91.12..410.43 rows=47 width=4) (actual time=1.695..4.885 rows=78 loops=1) Output: u0_1.id Buffers: shared hit=2471 -> Nested Loop Left Join (cost=90.84..400.69 rows=21 width=8) (actual time=1.662..4.725 rows=36 loops=1) Output: u2.id, u5_1.lesson_id Filter: ((u5_1.group_id = 5849) OR (u7.to_group_id = 5849)) Rows Removed by Filter: 1632 Buffers: shared hit=2361 -> Hash Join (cost=90.56..198.89 rows=484 width=16) (actual time=1.304..2.980 rows=1142 loops=1) Output: u2.id, u5_1.lesson_id, u5_1.group_id, u6.id Inner Unique: true Hash Cond: (u5_1.group_id = u6.id) Buffers: shared hit=76 -> Hash Join (cost=42.17..149.23 rows=484 width=12) (actual time=0.846..2.215 rows=1142 loops=1) Output: u2.id, u5_1.lesson_id, u5_1.group_id Hash Cond: (u5_1.lesson_id = u2.id) Buffers: shared hit=55 -> Seq Scan on public.chronos_lesson_groups u5_1 (cost=0.00..82.25 rows=5325 width=8) (actual time=0.005..0.500 rows=5325 loops=1) Output: u5_1.id, u5_1.lesson_id, u5_1.group_id Buffers: shared hit=29 -> Hash (cost=34.74..34.74 rows=595 width=4) (actual time=0.674..0.676 rows=1142 loops=1) Output: u2.id Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 57kB Buffers: shared hit=26 -> Nested Loop (cost=0.28..34.74 rows=595 width=4) (actual time=0.025..0.459 rows=1142 loops=1) Output: u2.id Buffers: shared hit=26 -> Seq Scan on public.chronos_validityrange u3_1 (cost=0.00..1.14 rows=1 width=4) (actual time=0.006..0.007 rows=2 loops=1) Output: u3_1.id, u3_1.extended_data, u3_1.name, u3_1.date_start, u3_1.date_end, u3_1.school_term_id, u3_1.site_id Filter: (u3_1.school_term_id = 2) Rows Removed by Filter: 9 Buffers: shared hit=1 -> Index Scan using chronos_lesson_validity_id_da417884 on public.chronos_lesson u2 (cost=0.28..27.65 rows=595 width=8) (actual time=0.010..0.154 rows=571 loops=2) Output: u2.id, u2.extended_data, u2.site_id, u2.subject_id, u2.validity_id Index Cond: (u2.validity_id = u3_1.id) Buffers: shared hit=25 -> Hash (cost=33.17..33.17 rows=1217 width=4) (actual time=0.438..0.439 rows=1217 loops=1) Output: u6.id Buckets: 2048 Batches: 1 Memory Usage: 59kB Buffers: shared hit=21 -> Seq Scan on public.core_group u6 (cost=0.00..33.17 rows=1217 width=4) (actual time=0.007..0.215 rows=1217 loops=1) Output: u6.id Buffers: shared hit=21 -> Index Only Scan using core_group_parent_groups_from_group_id_to_group_i_cfd5deea_uniq on public.core_group_parent_groups u7 (cost=0.28..0.37 rows=3 width=8) (actual time=0.001..0.001 rows=1 loops=1142) Output: u7.from_group_id, u7.to_group_id Index Cond: (u7.from_group_id = u6.id) Heap Fetches: 0 Buffers: shared hit=2285 -> Index Scan using chronos_lessonperiod_lesson_id_daa368e9 on public.chronos_lessonperiod u0_1 (cost=0.29..0.44 rows=2 width=8) (actual time=0.003..0.004 rows=2 loops=36) Output: u0_1.id, u0_1.extended_data, u0_1.lesson_id, u0_1.period_id, u0_1.room_id, u0_1.site_id Index Cond: (u0_1.lesson_id = u2.id) Filter: (u0_1.site_id = 1) Buffers: shared hit=110 SubPlan 3 -> Nested Loop Left Join (cost=0.56..16.29 rows=1 width=4) (actual time=0.049..0.051 rows=0 loops=1) Output: u0_2.id Filter: ((u3_2.group_id = 5849) OR (u5_2.to_group_id = 5849)) Buffers: shared hit=4 -> Nested Loop (cost=0.28..15.87 rows=1 width=12) (actual time=0.049..0.050 rows=0 loops=1) Output: u0_2.id, u3_2.group_id, u4_1.id Inner Unique: true Buffers: shared hit=4 -> Nested Loop (cost=0.00..15.49 rows=1 width=8) (actual time=0.048..0.050 rows=0 loops=1) Output: u0_2.id, u3_2.group_id Join Filter: (u0_2.id = u3_2.extralesson_id) Buffers: shared hit=4 -> Seq Scan on public.chronos_extralesson u0_2 (cost=0.00..7.53 rows=1 width=4) (actual time=0.048..0.049 rows=0 loops=1) Output: u0_2.id, u0_2.extended_data, u0_2.week, u0_2.comment, u0_2.period_id, u0_2.room_id, u0_2.site_id, u0_2.subject_id, u0_2.school_term_id, u0_2.year Filter: ((u0_2.site_id = 1) AND (u0_2.school_term_id = 2)) Rows Removed by Filter: 235 Buffers: shared hit=4 -> Seq Scan on public.chronos_extralesson_groups u3_2 (cost=0.00..4.65 rows=265 width=8) (never executed) Output: u3_2.id, u3_2.extralesson_id, u3_2.group_id -> Index Only Scan using core_group_pkey on public.core_group u4_1 (cost=0.28..0.39 rows=1 width=4) (never executed) Output: u4_1.id Index Cond: (u4_1.id = u3_2.group_id) Heap Fetches: 0 -> Index Only Scan using core_group_parent_groups_from_group_id_to_group_i_cfd5deea_uniq on public.core_group_parent_groups u5_2 (cost=0.28..0.37 rows=3 width=8) (never executed) Output: u5_2.from_group_id, u5_2.to_group_id Index Cond: (u5_2.from_group_id = u4_1.id) Heap Fetches: 0 Planning: Buffers: shared hit=1555 Planning Time: 10.385 ms Execution Time: 47.376 msExecution time went down from ~24,000ms to ~40ms.
Please register or sign in to reply