Attempt to speed up allocating builds

SQLite doesn't seem to be able to process the previously complicated query
quickly, I think because it wastes time working out if all the potential
builds meet the "does not share outputs with allocated builds" constraint,
rather than just a few builds as necessary. Therefore, move this logic to
Guile instead.

1 files changed, 43 insertions, 24 deletions

diff --git a/guix-build-coordinator/datastore/sqlite.scm b/guix-build-coordinator/datastore/sqlite.scm
index 415b90e..288af0a 100644
--- a/guix-build-coordinator/datastore/sqlite.scm
+++ b/guix-build-coordinator/datastore/sqlite.scm
@@ -1867,37 +1867,56 @@ INNER JOIN build_allocation_agent_requested_systems
 WHERE build_allocation_plan.agent_id = :agent_id
   AND builds.processed = 0
   AND builds.uuid NOT IN (SELECT build_id FROM allocated_builds)
-  AND NOT EXISTS (
-    SELECT 1
-    FROM derivation_outputs AS build_derivation_outputs
-    INNER JOIN allocated_builds
-      ON allocated_builds.agent_id = :agent_id
-    INNER JOIN builds AS allocated_build_details
-      ON allocated_build_details.uuid = allocated_builds.build_id
-    INNER JOIN derivation_outputs AS allocated_builds_derivation_outputs
-      ON allocated_build_details.derivation_name =
-         allocated_builds_derivation_outputs.derivation_name
-    WHERE build_derivation_outputs.derivation_name = builds.derivation_name
-      AND build_derivation_outputs.output =
-          allocated_builds_derivation_outputs.output
-  )
-ORDER BY build_allocation_plan.ordering ASC
-LIMIT 1"
-            #:cache? #t)))
+ORDER BY build_allocation_plan.ordering DESC"
+            #:cache? #t))
+          (output-conflicts-statement
+           (sqlite-prepare
+            db
+            "
+SELECT 1
+FROM derivation_outputs AS build_derivation_outputs
+INNER JOIN allocated_builds
+  ON allocated_builds.agent_id = :agent_id
+INNER JOIN builds AS allocated_build_details
+  ON allocated_build_details.uuid = allocated_builds.build_id
+INNER JOIN derivation_outputs AS allocated_builds_derivation_outputs
+  ON allocated_build_details.derivation_name =
+     allocated_builds_derivation_outputs.derivation_name
+WHERE build_derivation_outputs.derivation_name = :derivation_name
+  AND build_derivation_outputs.output =
+      allocated_builds_derivation_outputs.output")))
 
       (sqlite-bind-arguments
        statement
        #:agent_id agent-id)
 
-      (let ((build
-             (match (sqlite-step statement)
-               (#f #f)
-               (#(uuid derivation_name)
-                `((uuid            . ,uuid)
-                  (derivation-name . ,derivation_name))))))
+      (let ((builds
+             (sqlite-fold
+              (lambda (row result)
+                (cons
+                 (match row
+                   (#f #f)
+                   (#(uuid derivation_name)
+                    `((uuid            . ,uuid)
+                      (derivation-name . ,derivation_name))))
+                 result))
+              '()
+              statement)))
         (sqlite-reset statement)
 
-        build)))
+        (find (lambda (build-details)
+                (sqlite-bind-arguments
+                 output-conflicts-statement
+                 #:derivation_name (assq-ref build-details
+                                             'derivation-name))
+
+                (let ((result (sqlite-step output-conflicts-statement)))
+                  (sqlite-reset output-conflicts-statement)
+
+                  (match result
+                    (#f #t)
+                    (_ #f))))
+              builds))))
 
   (define (insert-to-allocated-builds db agent-id build-ids)
     (sqlite-exec