[Git][qa/jenkins.debian.net][master] reproducible Debian: speed up scheduler a lot, thanks to Myon! <3

[Holger Levsen (@holger)]

Holger Levsen pushed to branch master at Debian QA / jenkins.debian.net


Commits:
c27c9ddf by Holger Levsen at 2023-07-10T16:59:27+02:00
reproducible Debian: speed up scheduler a lot, thanks to Myon! <3

explaination in https://wiki.postgresql.org/wiki/Don%27t_Do_This#Don.27t_use_NOT_IN

Signed-off-by: Holger Levsen <holger at layer-acht.org>

- - - - -


1 changed file:

- bin/reproducible_scheduler.py


Changes:

=====================================
bin/reproducible_scheduler.py
=====================================
@@ -485,10 +485,10 @@ def query_untested_packages(suite, arch, limit):
                FROM (
                     SELECT sources.id, sources.name FROM sources
                     WHERE sources.suite='{suite}' AND sources.architecture='{arch}'
-                    AND sources.id NOT IN
-                       (SELECT schedule.package_id FROM schedule WHERE build_type='ci_build')
-                    AND sources.id NOT IN
-                       (SELECT results.package_id FROM results)
+                    AND NOT exists
+                       (SELECT schedule.package_id FROM schedule WHERE build_type='ci_build' AND sources.id = schedule.package_id )
+                    AND NOT exists
+                       (SELECT FROM results AND sources.id = results.package_id )
                     ORDER BY random()
                 ) AS tmp
                 LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit)
@@ -505,7 +505,7 @@ def query_new_versions(suite, arch, limit):
                AND s.version != r.version
                AND r.status != 'blacklisted'
                AND s.id IN (SELECT package_id FROM results)
-               AND s.id NOT IN (SELECT schedule.package_id FROM schedule WHERE build_type='ci_build')
+               AND NOT exists (SELECT schedule.package_id FROM schedule WHERE build_type='ci_build' AND s.id = schedule.package_id)
                GROUP BY s.id, s.name, s.version, r.version
                ORDER BY max_date
                LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit)
@@ -530,7 +530,7 @@ def query_old_ftbfs_versions(suite, arch, limit):
                 AND r.status='FTBFS'
                 AND ( n.bugs = '[]' OR n.bugs IS NULL )
                 AND r.build_date < '{date}'
-                AND s.id NOT IN (SELECT schedule.package_id FROM schedule WHERE build_type='ci_build')
+                AND NOT exists (SELECT FROM schedule WHERE build_type='ci_build' AND s.id = schedule.package_id )
                 GROUP BY s.id, s.name
                 ORDER BY max_date
                 LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit,
@@ -549,7 +549,7 @@ def query_old_depwait_versions(suite, arch, limit):
                 WHERE s.suite='{suite}' AND s.architecture='{arch}'
                 AND r.status='depwait'
                 AND r.build_date < '{date}'
-                AND s.id NOT IN (SELECT schedule.package_id FROM schedule WHERE build_type='ci_build')
+                AND NOT exists (SELECT FROM schedule WHERE build_type='ci_build' AND s.id = schedule.package_id )
                 GROUP BY s.id, s.name
                 ORDER BY max_date
                 LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit,
@@ -569,7 +569,7 @@ def query_old_versions(suite, arch, limit):
                 WHERE s.suite='{suite}' AND s.architecture='{arch}'
                 AND r.status != 'blacklisted'
                 AND r.build_date < '{date}'
-                AND s.id NOT IN (SELECT schedule.package_id FROM schedule WHERE build_type='ci_build')
+                AND NOT exists (SELECT FROM schedule WHERE build_type='ci_build' AND s.id = schedule.package_id )
                 GROUP BY s.id, s.name
                 ORDER BY max_date
                 LIMIT {limit}""".format(suite=suite, arch=arch,
@@ -587,7 +587,7 @@ def query_e404_versions(suite, arch, limit):
                 WHERE s.suite='{suite}' AND s.architecture='{arch}'
                 AND r.status = 'E404'
                 AND r.build_date < '{date}'
-                AND s.id NOT IN (SELECT schedule.package_id FROM schedule WHERE build_type='ci_build')
+                AND NOT exists (SELECT FROM schedule WHERE build_type='ci_build' AND s.id = schedule.package_id )
                 GROUP BY s.id, s.name
                 ORDER BY max_date
                 LIMIT {limit}""".format(suite=suite, arch=arch, limit=limit,



View it on GitLab: https://salsa.debian.org/qa/jenkins.debian.net/-/commit/c27c9ddf30da95813775d69db20fa99b833f54a7

-- 
View it on GitLab: https://salsa.debian.org/qa/jenkins.debian.net/-/commit/c27c9ddf30da95813775d69db20fa99b833f54a7
You're receiving this email because of your account on salsa.debian.org.


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://alioth-lists.debian.net/pipermail/qa-jenkins-scm/attachments/20230710/c9a2cf70/attachment-0001.htm>