[no subject]

[Ludovic Courtès]

branch: main
commit f60e73b7b1e906349d2355d37807514c6e667f0c
Author: Ludovic Courtès <ludo@gnu.org>
AuthorDate: Fri Jun 28 10:21:50 2024 +0200

    database: Optimize ‘db-get-evaluations-absolute-summary’.
    
    On ci.guix.gnu.org, there are only 26K evaluations compared to 277M
    jobs.  So far the SQL query of ‘db-get-evaluations-absolute-summary’
    would entail a sequential scan on ‘Jobs’.  With this change, it is
    replaced by a sequential scan on ‘Evaluations’, which is much less
    expensive (going from ~60s to ~4s).
    
    * src/cuirass/database.scm (db-get-evaluations-absolute-summary): Select
    from ‘Jobs’ first and then join with ‘Evaluations’.
    * tests/database.scm ("db-register-builds"): Add a build for evaluation 3
    since it had zero builds so far.
    ("db-get-evaluations-absolute-summary"): Adjust accordingly.
---
 src/cuirass/database.scm |  6 +++---
 tests/database.scm       | 42 ++++++++++++++++++++++--------------------
 2 files changed, 25 insertions(+), 23 deletions(-)

diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm
index 81f291d..e1059cc 100644
--- a/src/cuirass/database.scm
+++ b/src/cuirass/database.scm
@@ -1959,12 +1959,12 @@ SUM(CASE WHEN Jobs.status > 0 THEN 1 ELSE 0 END) AS 
failed,
 SUM(CASE WHEN Jobs.status < 0 THEN 1 ELSE 0 END) AS scheduled,
 SUM(CASE WHEN (Builds.status > 0 AND Builds.weather = " (build-weather 
new-failure) ")\
     THEN 1 ELSE 0 END) as newfailures
-FROM Evaluations
-LEFT JOIN Jobs
+FROM Jobs
+LEFT JOIN Evaluations
 ON Jobs.evaluation = Evaluations.id
 LEFT JOIN Builds
 ON Builds.id = Jobs.build
-WHERE Evaluations.id = ANY(" eval-ids ")
+WHERE Jobs.evaluation = ANY(" eval-ids ")
 GROUP BY Evaluations.id
 ORDER BY Evaluations.id ASC;"))
                (summary '()))
diff --git a/tests/database.scm b/tests/database.scm
index 258ac2d..94a5cd0 100644
--- a/tests/database.scm
+++ b/tests/database.scm
@@ -263,25 +263,27 @@ timestamp, checkouttime, evaltime) VALUES ('guix', 0, 0, 
0, 0);")
 
   (test-assert "db-register-builds"
     (with-fibers
-      (let ((drv "/test.drv"))
-        (db-register-builds
-         (list (build (job-name "test")
-                      (evaluation-id 2)
-                      (specification-name "whatever")
-                      (derivation drv)
-                      (system "x86_64-linux")
-                      (nix-name "test")
-                      (log "log")
-                      (outputs
-                       (list (output
-                              (name "foo")
-                              (derivation drv)
-                              (item (string-append drv ".output")))
-                             (output
-                              (name "foo2")
-                              (derivation drv)
-                              (item (string-append drv ".output.2")))))))
-         (db-get-specification "guix")))))
+     (let ((drv "/test.drv"))
+       (for-each (lambda (evaluation)
+                   (db-register-builds
+                    (list (build (job-name "test")
+                                 (evaluation-id evaluation)
+                                 (specification-name "whatever")
+                                 (derivation drv)
+                                 (system "x86_64-linux")
+                                 (nix-name "test")
+                                 (log "log")
+                                 (outputs
+                                  (list (output
+                                         (name "foo")
+                                         (derivation drv)
+                                         (item (string-append drv ".output")))
+                                        (output
+                                         (name "foo2")
+                                         (derivation drv)
+                                         (item (string-append drv 
".output.2")))))))
+                    (db-get-specification "guix")))
+                 '(2 3)))))
 
   (test-assert "db-get-jobs"
     (with-fibers
@@ -446,7 +448,7 @@ timestamp, checkouttime, evaltime) VALUES ('guix', 0, 0, 0, 
0);")
               (evaluation-summary-status summary)))))
 
   (test-equal "db-get-evaluations-absolute-summary"
-    '((0 1 0 0) (0 0 0 0) (0 1 0 0))
+    '((0 1 0 0) (0 1 0 0) (0 1 0 0))
     (with-fibers
       (let* ((evaluations
               (db-get-evaluations-build-summary "guix" 3 #f #f))