04/05: Avoid a recursive CTE for finding blocked builds where possible

[Christopher Baines]

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

commit 659dcc6a0d0c569eb9a088beec0f13bd1cbe843f
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Thu Mar 9 08:36:51 2023 +0000

    Avoid a recursive CTE for finding blocked builds where possible
    
    Use the new approach of looking up the distribution of the derivations, and
    building a non recursive query specifically for this revision. This should
    avoid PostgreSQL picking a poor plan for performing the query.
---
 guix-data-service/model/blocked-builds.scm | 17 +++++++++++++----
 1 file changed, 13 insertions(+), 4 deletions(-)

diff --git a/guix-data-service/model/blocked-builds.scm 
b/guix-data-service/model/blocked-builds.scm
index 4b8122f..10219df 100644
--- a/guix-data-service/model/blocked-builds.scm
+++ b/guix-data-service/model/blocked-builds.scm
@@ -26,6 +26,7 @@
   #:use-module (guix-data-service model utils)
   #:use-module (guix-data-service model system)
   #:use-module (guix-data-service model guix-revision)
+  #:use-module (guix-data-service model guix-revision-package-derivation)
   #:use-module (guix-data-service model build)
   #:export (handle-blocked-builds-entries-for-scheduled-builds
             handle-populating-blocked-builds-for-build-failures
@@ -353,7 +354,14 @@ WHERE status IN ('failed', 'failed-dependency', 
'failed-other', 'canceled')
                                  limit)
   (define query
     (string-append
-     "
+     (or
+      (get-sql-to-select-package-and-related-derivations-for-revision
+       conn
+       (commit->revision-id conn revision-commit)
+       #:system-id (system->system-id conn system)
+       #:target target)
+      (string-append
+       "
 WITH RECURSIVE all_derivations AS (
   (
     SELECT derivation_id
@@ -361,7 +369,7 @@ WITH RECURSIVE all_derivations AS (
     INNER JOIN guix_revision_package_derivations
       ON package_derivations.id
           = guix_revision_package_derivations.package_derivation_id
-    WHERE revision_id = $1"
+    WHERE revision_id = " (commit->revision-id conn revision-commit)
      (if system
          (simple-format
           #f "
@@ -383,7 +391,8 @@ WITH RECURSIVE all_derivations AS (
       ON all_derivations.derivation_id = derivation_inputs.derivation_id
     INNER JOIN derivation_outputs
       ON derivation_inputs.derivation_output_id = derivation_outputs.id
-), all_derivation_output_details_set_ids AS (
+)"))
+     ", all_derivation_output_details_set_ids AS (
   SELECT derivations_by_output_details_set.*
   FROM derivations_by_output_details_set
   WHERE derivation_id IN (
@@ -452,4 +461,4 @@ LIMIT " (number->string limit))
                     (eq? #f builds))
                 #()
                 (json-string->scm builds))))))
-   (exec-query conn query (list (commit->revision-id conn revision-commit)))))
+   (exec-query conn query)))