Skip to content

GitLab

  • Menu
Projects Groups Snippets
    • Loading...
  • Help
    • Help
    • Support
    • Community forum
    • Submit feedback
  • Sign in / Register
  • lava lava
  • Project information
    • Project information
    • Activity
    • Labels
    • Members
  • Repository
    • Repository
    • Files
    • Commits
    • Branches
    • Tags
    • Contributors
    • Graph
    • Compare
  • Issues 90
    • Issues 90
    • List
    • Boards
    • Service Desk
    • Milestones
  • Merge requests 44
    • Merge requests 44
  • CI/CD
    • CI/CD
    • Pipelines
    • Jobs
    • Schedules
  • Deployments
    • Deployments
    • Environments
    • Releases
  • Monitor
    • Monitor
    • Incidents
  • Packages & Registries
    • Packages & Registries
    • Container Registry
  • Analytics
    • Analytics
    • CI/CD
    • Repository
    • Value stream
  • Wiki
    • Wiki
  • Snippets
    • Snippets
  • Activity
  • Graph
  • Create a new issue
  • Jobs
  • Commits
  • Issue Boards
Collapse sidebar
  • lava
  • lavalava
  • Issues
  • #541

Closed
Open
Created Apr 28, 2022 by Igor Ponomarev@igo95862Contributor

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:

  1. Upgrade Django but there is no guarantee that generated SQL can be any good.
  2. 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
Assignee
Assign to
Time tracking