[Git][qa/jenkins.debian.net][master] reproducible Arch Linux: speed up several sql queries, thanks Myon! <3

[Holger Levsen (@holger)]

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


Commits:
0950ab36 by Holger Levsen at 2023-07-10T22:51:23+02:00
reproducible Arch Linux: speed up several sql queries, thanks 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>

- - - - -


2 changed files:

- bin/reproducible_archlinux_scheduler.sh
- bin/reproducible_html_archlinux.sh


Changes:

=====================================
bin/reproducible_archlinux_scheduler.sh
=====================================
@@ -1,6 +1,6 @@
 #!/bin/bash
 
-# Copyright 2015-2019 Holger Levsen <holger at layer-acht.org>
+# Copyright 2015-2023 Holger Levsen <holger at layer-acht.org>
 # released under the GPLv2
 
 DEBUG=false
@@ -167,7 +167,7 @@ update_archlinux_repositories() {
 		AND s.architecture='x86_64'
 		AND (r.status LIKE 'DEPWAIT%' OR r.status LIKE '404%')
 		AND r.build_date < '$MINDATE'
-		AND s.id NOT IN (SELECT package_id FROM schedule WHERE build_type='ci_build')
+		AND NOT exists (SELECT FROM schedule WHERE package_id = s.id AND build_type='ci_build')
 		LIMIT $MAX;"
 	local DEPWAIT404=$(query_db "$QUERY")
 	if [ -n "$DEPWAIT404" ] ; then
@@ -198,7 +198,7 @@ update_archlinux_repositories() {
 			AND s.architecture='x86_64'
 			AND r.status != 'blacklisted'
 			AND r.build_date < '$MINDATE'
-			AND s.id NOT IN (SELECT schedule.package_id FROM schedule WHERE build_type='ci_build')
+			AND NOT exists (SELECT FROM schedule WHERE where package_id = s.id AND build_type='ci_build')
 			GROUP BY s.id, s.name
 			ORDER BY max_date
 			LIMIT $MAX;"


=====================================
bin/reproducible_html_archlinux.sh
=====================================
@@ -1,6 +1,6 @@
 #!/bin/bash
 
-# Copyright 2014-2019 Holger Levsen <holger at layer-acht.org>
+# Copyright 2014-2023 Holger Levsen <holger at layer-acht.org>
 #                2015 anthraxx <levente at leventepolyak.net>
 # released under the GPLv2
 
@@ -63,7 +63,7 @@ repostats(){
 		NR_404=$(query_db "SELECT count(*) FROM sources AS s JOIN results AS r ON s.id=r.package_id WHERE s.distribution=$DISTROID AND s.architecture='x86_64' AND s.suite='$SUITE' AND r.status LIKE '404_%';")
 		NR_BLACKLISTED=$(query_db "SELECT count(*) FROM sources AS s JOIN results AS r ON s.id=r.package_id WHERE s.distribution=$DISTROID AND s.architecture='x86_64' AND s.suite='$SUITE' AND r.status='blacklisted';")
 		NR_UNKNOWN=$(query_db "SELECT count(*) FROM sources AS s JOIN results AS r ON s.id=r.package_id WHERE s.distribution=$DISTROID AND  s.architecture='x86_64' AND s.suite='$SUITE' AND r.status LIKE 'UNKNOWN';")
-		NR_UNTESTED=$(query_db "SELECT count(s.name) FROM sources AS s WHERE s.architecture='x86_64' AND s.distribution=$DISTROID AND s.suite='$SUITE' AND s.id NOT IN (SELECT package_id FROM results)")
+		NR_UNTESTED=$(query_db "SELECT count(s.name) FROM sources AS s WHERE s.architecture='x86_64' AND s.distribution=$DISTROID AND s.suite='$SUITE' AND NOT exists (SELECT FROM results WHERE package_id = s.id )")
 		if [ $NR_UNTESTED -ne 0 ] ; then
 			let NR_UNKNOWN=$NR_UNKNOWN+$NR_UNTESTED
 		fi
@@ -263,7 +263,7 @@ state_pages(){
 			UNTESTED=$(query_db "SELECT count(s.name) FROM sources AS s
 					WHERE s.distribution=$DISTROID
 					AND s.architecture='x86_64'
-					AND s.id NOT IN (SELECT package_id FROM results)")
+					AND NOT exists (SELECT FROM results WHERE package_id = s.id )")
 			if [ $UNTESTED -ne 0 ] ; then
 				let TESTED=$TESTED+$UNTESTED
 			fi
@@ -289,7 +289,7 @@ state_pages(){
 					WHERE s.distribution=$DISTROID
 					AND s.architecture='x86_64'
 					AND s.suite='$SUITE'
-					AND s.id NOT IN (SELECT package_id FROM results)
+					AND NOT exists (SELECT FROM results WHERE package_id = s.id )
 					ORDER BY s.name")
 				for SRCPACKAGE in ${STATE_PKGS} ; do
 					include_pkg_html_in_page
@@ -322,7 +322,7 @@ repository_state_pages(){
 						WHERE s.distribution=$DISTROID
 						AND s.architecture='x86_64'
 						AND s.suite='$SUITE'
-						AND s.id NOT IN (SELECT package_id FROM results)")
+						AND NOT exists (SELECT FROM results WHERE package_id = s.id )")
 				if [ $UNTESTED -ne 0 ] ; then
 					let TESTED=$TESTED+$UNTESTED
 				fi
@@ -346,7 +346,7 @@ repository_state_pages(){
 					WHERE s.distribution=$DISTROID
 					AND s.architecture='x86_64'
 					AND s.suite='$SUITE'
-					AND s.id NOT IN (SELECT package_id FROM results)
+					AND NOT exists (SELECT FROM results WHERE package_id = s.id )
 					ORDER BY s.name")
 				for SRCPACKAGE in ${STATE_PKGS} ; do
 					include_pkg_html_in_page



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

-- 
View it on GitLab: https://salsa.debian.org/qa/jenkins.debian.net/-/commit/0950ab369427a1c53be2b1234d667b965ddac6e4
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/041fb925/attachment-0001.htm>