Potentially optimisable database behaviour #17

New issue

Open

opened 2020-12-23 13:21:53 +01:00 by nik · 0 comments

nik commented

2020-12-23 13:21:53 +01:00

(Migrated from edugit.org)

Using the PostgreSQL Workload Analyser, the following queries have been identified that could potentially be optimised.

Run too often

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

SELECT "chronos_lesson"."id", "chronos_lesson"."site_id", "chronos_lesson"."extended_data", "chronos_lesson"."validity_id", "chronos_lesson"."subject_id" FROM "chronos_lesson" INNER JOIN "chronos_validityrange" ON ("chronos_lesson"."validity_id" = "chronos_validityrange"."id") INNER JOIN "chronos_subject" ON ("chronos_lesson"."subject_id" = "chronos_subject"."id") WHERE ("chronos_lesson"."site_id" = $1 AND ("chronos_lesson"."extended_data" -> $2) = $3 AND ("chronos_lesson"."extended_data" -> $4) = $5 AND "chronos_lesson"."validity_id" = $6) ORDER BY "chronos_validityrange"."date_start" ASC, "chronos_subject"."name" ASC, "chronos_subject"."short_name" ASC LIMIT $7

@hansegucker Can you please try to identify the code locations and annotate them here?

Using the PostgreSQL Workload Analyser, the following queries have been identified that could potentially be optimised. ## Run too often ```sql 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 ``` ```sql SELECT "chronos_lesson"."id", "chronos_lesson"."site_id", "chronos_lesson"."extended_data", "chronos_lesson"."validity_id", "chronos_lesson"."subject_id" FROM "chronos_lesson" INNER JOIN "chronos_validityrange" ON ("chronos_lesson"."validity_id" = "chronos_validityrange"."id") INNER JOIN "chronos_subject" ON ("chronos_lesson"."subject_id" = "chronos_subject"."id") WHERE ("chronos_lesson"."site_id" = $1 AND ("chronos_lesson"."extended_data" -> $2) = $3 AND ("chronos_lesson"."extended_data" -> $4) = $5 AND "chronos_lesson"."validity_id" = $6) ORDER BY "chronos_validityrange"."date_start" ASC, "chronos_subject"."name" ASC, "chronos_subject"."short_name" ASC LIMIT $7 ``` @hansegucker Can you please try to identify the code locations and annotate them here?

Rows
Columns