From e6205e988ae95c5dca57cf257a90b87e06dd16a7 Mon Sep 17 00:00:00 2001
From: Christopher Baines <mail@cbaines.net>
Date: Fri, 21 Jun 2024 15:08:37 +0100
Subject: Speed up querying for revision package derivations

By splitting it up by system.
---
 guix-data-service/model/derivation.scm | 41 ++++++++++++++++++----------------
 1 file changed, 22 insertions(+), 19 deletions(-)

(limited to 'guix-data-service/model/derivation.scm')

diff --git a/guix-data-service/model/derivation.scm b/guix-data-service/model/derivation.scm
index 170be49..02fd175 100644
--- a/guix-data-service/model/derivation.scm
+++ b/guix-data-service/model/derivation.scm
@@ -198,7 +198,7 @@ ORDER BY systems.system DESC,
 (define* (select-package-derivations-in-revision conn
                                                  commit-hash
                                                  #:key
-                                                 systems
+                                                 system
                                                  targets
                                                  minimum-builds
                                                  maximum-builds
@@ -214,19 +214,25 @@ ORDER BY systems.system DESC,
     (string-join
      `(,@(filter-map
           (lambda (field values)
-            (if values
-                (string-append
-                 field " IN ("
-                 (string-join (map (lambda (value)
-                                     (simple-format #f "'~A'" value))
-                                   values)
-                              ",")
-                 ")")
-                #f))
-          '("systems.system"
-            "target")
-          (list systems
-                targets))
+            (cond
+             ((list? values)
+              (string-append
+               field " IN ("
+               (string-join (map (lambda (value)
+                                   (simple-format #f "'~A'" value))
+                                 values)
+                            ",")
+               ")"))
+             ((number? values)
+              (string-append
+               field " = " (number->string values)))
+             (else
+              #f)))
+          '("package_derivations.system_id" "target")
+          (list
+           (and=> system (lambda (system)
+                           (system->system-id conn system)))
+           targets))
        ,@(if minimum-builds
              (list
               (string-append
@@ -346,7 +352,6 @@ EXISTS (
     (string-append
      "
 SELECT derivations.file_name,
-       systems.system,
        package_derivations.target"
      (if include-builds?
          ",
@@ -371,8 +376,6 @@ SELECT derivations.file_name,
          "")
      "
 FROM derivations
-INNER JOIN systems
-  ON derivations.system_id = systems.id
 INNER JOIN derivations_by_output_details_set
   ON derivations.id = derivations_by_output_details_set.derivation_id
 INNER JOIN package_derivations
@@ -400,7 +403,7 @@ ORDER BY derivations.file_name
          "")))
 
   (map (match-lambda
-         ((file_name system target builds)
+         ((file_name target builds)
           (list file_name
                 system
                 target
@@ -408,7 +411,7 @@ ORDER BY derivations.file_name
                         (eq? #f builds))
                     #()
                     (json-string->scm builds))))
-         ((file_name system target)
+         ((file_name target)
           (list file_name system target)))
        (exec-query conn
                    query
-- 
cgit v1.2.3