#33973: Performance regression when moving from 3.1 to 3.2
-------------------------------------+-------------------------------------
Reporter: Marc Parizeau | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 3.2
Severity: Normal | Resolution:
Keywords: performance | Triage Stage:
regression | Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Comment (by Marc Parizeau):
So the exact Django query that lead to the above results is:
{{{
Content.objects.filter(
teacher__user=user,
).exclude(
pk=content.pk,
).annotate(
unread=Greatest(
Exists(
# a thread has no user entry (0.03; 0.30)
Thread.objects.filter(
content=OuterRef('pk'),
).exclude(threadentry__user=user)
),
)
).filter(
unread=True,
)
}}}
Which produces the following SQL.
**On Django 3.1.14**:
{{{
SELECT "content_content"."id", "content_content"."created",
"content_content"."description", "content_content"."disabled",
"content_content"."modified", "content_content"."objectives",
"content_content"."published", "content_content"."title",
"content_content"."course_id", "content_content"."version_id",
"content_content"."exercise_weight", "content_content"."midterm_weight",
"content_content"."final_weight", "content_content"."project_weight",
"content_content"."forum_weight", "content_content"."welcome_video",
"content_content"."navigate_video", "content_content"."forum_video",
"content_content"."lambda_fct_id", "content_content"."dl_key_version",
"content_content"."dl_key_token", "content_content"."exam_key_token",
"content_content"."lectures_url", GREATEST(EXISTS(SELECT V0."id",
V0."created", V0."disabled", V0."modified", V0."title", V0."content_id",
V0."owner_id", V0."post_id", V0."tag", V0."start", V0."uid",
V0."question", V0."important", V0."resolved", V0."authorized", V0."votes",
V0."mptt_id", V0."linked_content_type_id", V0."linked_content_id" FROM
"forum_thread" V0 WHERE (V0."content_id" = "content_content"."id" AND NOT
(V0."id" IN (SELECT U1."thread_id" FROM "forum_threadentry" U1 WHERE
(U1."user_id" = 59 AND U1."thread_id" IS NOT NULL))))), False) AS "unread"
FROM "content_content" INNER JOIN "content_teacher" ON
("content_content"."id" = "content_teacher"."content_id") LEFT OUTER JOIN
"content_course" ON ("content_content"."course_id" =
"content_course"."id") LEFT OUTER JOIN "content_version" ON
("content_content"."version_id" = "content_version"."id") WHERE
("content_teacher"."user_id" = 59 AND NOT ("content_content"."id" = 58)
AND GREATEST(EXISTS(SELECT V0."id", V0."created", V0."disabled",
V0."modified", V0."title", V0."content_id", V0."owner_id", V0."post_id",
V0."tag", V0."start", V0."uid", V0."question", V0."important",
V0."resolved", V0."authorized", V0."votes", V0."mptt_id",
V0."linked_content_type_id", V0."linked_content_id" FROM "forum_thread" V0
WHERE (V0."content_id" = "content_content"."id" AND NOT (V0."id" IN
(SELECT U1."thread_id" FROM "forum_threadentry" U1 WHERE (U1."user_id" =
59 AND U1."thread_id" IS NOT NULL))))), False)) ORDER BY
"content_course"."uid" ASC, "content_version"."start" DESC
}}}
**On Django 3.2.15**:
{{{
SELECT "content_content"."id", "content_content"."created",
"content_content"."description", "content_content"."disabled",
"content_content"."modified", "content_content"."objectives",
"content_content"."published", "content_content"."title",
"content_content"."course_id", "content_content"."version_id",
"content_content"."exercise_weight", "content_content"."midterm_weight",
"content_content"."final_weight", "content_content"."project_weight",
"content_content"."forum_weight", "content_content"."welcome_video",
"content_content"."navigate_video", "content_content"."forum_video",
"content_content"."lambda_fct_id", "content_content"."dl_key_version",
"content_content"."dl_key_token", "content_content"."exam_key_token",
"content_content"."lectures_url", GREATEST(EXISTS(SELECT (1) AS "a" FROM
"forum_thread" V0 WHERE (V0."content_id" = "content_content"."id" AND NOT
(EXISTS(SELECT (1) AS "a" FROM "forum_threadentry" U1 WHERE (U1."user_id"
= 59 AND U1."thread_id" = V0."id") LIMIT 1))) LIMIT 1), False) AS "unread"
FROM "content_content" INNER JOIN "content_teacher" ON
("content_content"."id" = "content_teacher"."content_id") LEFT OUTER JOIN
"content_course" ON ("content_content"."course_id" =
"content_course"."id") LEFT OUTER JOIN "content_version" ON
("content_content"."version_id" = "content_version"."id") WHERE
("content_teacher"."user_id" = 59 AND NOT ("content_content"."id" = 58)
AND GREATEST(EXISTS(SELECT (1) AS "a" FROM "forum_thread" V0 WHERE
(V0."content_id" = "content_content"."id" AND NOT (EXISTS(SELECT (1) AS
"a" FROM "forum_threadentry" U1 WHERE (U1."user_id" = 59 AND
U1."thread_id" = V0."id") LIMIT 1))) LIMIT 1), False)) ORDER BY
"content_course"."uid" ASC, "content_version"."start" DESC
}}}
Again, hope this helps!
--
Ticket URL: <https://code.djangoproject.com/ticket/33973#comment:2>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/01070182f9e16378-54e1995f-f808-4b6a-b491-1d5db25ba413-000000%40eu-central-1.amazonses.com.
