Viewing groups filter can be 100 times faster but is limited by Django version and SQL generated.
Before:
( "lava_scheduler_app_testjob"."id" IN (
SELECT U0."id"
FROM "lava_scheduler_app_testjob" U0
INNER JOIN "lava_scheduler_app_testjob_viewing_groups" U1
ON (U0."id" = U1."testjob_id")
WHERE U1."group_id" IS NOT NULL)
AND NOT ("lava_scheduler_app_testjob"."id" IN (
SELECT V1."testjob_id"
FROM "lava_scheduler_app_testjob_viewing_groups" V1
WHERE V1."group_id" IN
(SELECT U0."id" FROM "auth_group" U0 WHERE NOT (U0."id" IN (121))))))
Execution Time: 7015.967 ms
After:
(
NOT EXISTS (
(SELECT "group_id" FROM "lava_scheduler_app_testjob_viewing_groups" WHERE testjob_id = lava_scheduler_app_testjob."id")
EXCEPT
(SELECT "group_id" FROM auth_user_groups WHERE user_id = 2)))
Execution Time: 71.505 ms
The issue is I can't make Django actually generate the better query.
This is my best attempt:
test = TestJob.viewing_groups.through.objects.filter(
testjob_id=OuterRef('id')).values('group_id')
test2 = test.difference(user.groups.values('id'))
filters |= Q(~Exists(test3))
The issue is that Exists
cannot be used as a part of the filter in Django < 3.
Debian Bullseye comes with Django 2.2. Attempt to install version 3.2 from backports was not successful as it conflicted with python3-django-tables2
.
There are 2 solutions I can think of:
- Upgrade Django but there is no guarantee that generated SQL can be any good.
- Use raw SQL. Will be very ugly.
To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information