GitLab

Having an explicit model using the through= option allows avoiding the JOIN on TestJob model.

Compare this manual query optimisation:

Before 207.758ms: https://explain.depesz.com/s/Li9o

After 20.182ms: https://explain.depesz.com/s/LIZM

Only a small change allow for 10 times better performance.

Instead of always joining TestJob on ViewingGroups we can just search ViewingGroups

Before:

            NOT lava_scheduler_app_testjob.id IN (
                SELECT
                    u0.id
                FROM
                    lava_scheduler_app_testjob AS u0
                    JOIN lava_scheduler_app_testjob_viewing_groups AS u1 ON u0.id = u1.testjob_id
                WHERE
                    u1.group_id IS NOT NULL
            )

After:

            NOT lava_scheduler_app_testjob.id IN (
                SELECT
                    u1.testjob_id
                FROM
                    lava_scheduler_app_testjob_viewing_groups AS u1
                WHERE
                    u1.testjob_id = "lava_scheduler_app_testjob"."id"
            )

This is impossible to express in Django without having access to actual viewing groups model.

Creating an explicit model would require a complex migration. I would prefer other migration requiring merge requests like !1953 merged first before working on it.