01/02: Improve performance of select-fixed-output-package-derivations-in-revision

[Christopher Baines]

cbaines pushed a commit to branch master
in repository data-service.

commit 1a0eaeb672c0f7688a9567ad2adf7569686df062
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Sat Mar 11 18:19:19 2023 +0000

    Improve performance of select-fixed-output-package-derivations-in-revision
---
 guix-data-service/model/derivation.scm | 43 ++++++++++------------------------
 1 file changed, 13 insertions(+), 30 deletions(-)

diff --git a/guix-data-service/model/derivation.scm 
b/guix-data-service/model/derivation.scm
index 05a94e3..9f9a3fb 100644
--- a/guix-data-service/model/derivation.scm
+++ b/guix-data-service/model/derivation.scm
@@ -35,6 +35,8 @@
   #:use-module (guix-data-service utils)
   #:use-module (guix-data-service database)
   #:use-module (guix-data-service model utils)
+  #:use-module (guix-data-service model guix-revision)
+  #:use-module (guix-data-service model guix-revision-package-derivation)
   #:use-module (guix-data-service model system)
   #:export (valid-targets
             count-derivations
@@ -641,28 +643,12 @@ ORDER BY derivations.file_name
           latest-build-status)
   (define query
     (string-append
+     (get-sql-to-select-package-and-related-derivations-for-revision
+      conn
+      (commit->revision-id conn commit)
+      #:system-id (system->system-id conn system)
+      #:target target)
      "
-WITH RECURSIVE all_derivations(id) AS (
-    SELECT package_derivations.derivation_id
-    FROM package_derivations
-    INNER JOIN systems
-      ON package_derivations.system_id = systems.id
-    INNER JOIN guix_revision_package_derivations
-      ON package_derivations.id =
-         guix_revision_package_derivations.package_derivation_id
-    INNER JOIN guix_revisions
-      ON guix_revision_package_derivations.revision_id = guix_revisions.id
-    WHERE guix_revisions.commit = $1
-      AND systems.system = $2
-      AND package_derivations.target = $3
-  UNION
-    SELECT derivation_outputs.derivation_id
-    FROM all_derivations
-    INNER JOIN derivation_inputs
-      ON all_derivations.id = derivation_inputs.derivation_id
-    INNER JOIN derivation_outputs
-      ON derivation_inputs.derivation_output_id = derivation_outputs.id
-)
 SELECT DISTINCT ON (derivations.file_name)
        derivations.file_name,
        (
@@ -679,9 +665,9 @@ SELECT DISTINCT ON (derivations.file_name)
        ) AS latest_build
 FROM all_derivations
 INNER JOIN derivations
-  ON all_derivations.id = derivations.id
+  ON all_derivations.derivation_id = derivations.id
 INNER JOIN derivation_outputs
-  ON all_derivations.id = derivation_outputs.derivation_id
+  ON all_derivations.derivation_id = derivation_outputs.derivation_id
 INNER JOIN derivation_output_details
   ON derivation_outputs.derivation_output_details_id = 
derivation_output_details.id
 LEFT JOIN builds
@@ -695,18 +681,18 @@ LEFT JOIN latest_build_status
 WHERE derivation_output_details.hash IS NOT NULL"
      (if after-derivation-file-name
          "
-  AND derivations.file_name > $5"
+  AND derivations.file_name > $2"
          "")
      (if latest-build-status
          (simple-format
           #f
           "
   AND latest_build_status.status = $~A"
-          (if after-derivation-file-name 6 5))
+          (if after-derivation-file-name 3 2))
          "")
      "
 ORDER BY derivations.file_name, latest_build_status.timestamp DESC
-LIMIT $4"))
+LIMIT $1"))
 
   (map (match-lambda
          ((derivation_file_name latest_build)
@@ -721,10 +707,7 @@ LIMIT $4"))
        (exec-query-with-null-handling
         conn
         query
-        `(,commit
-          ,system
-          ,target
-          ,(number->string (or limit-results 999999)) ; TODO
+        `(,(number->string (or limit-results 999999)) ; TODO
           ,@(if after-derivation-file-name
                 (list after-derivation-file-name)
                 '())