02/03: Speed up deleting blocked_builds entries

[Christopher Baines]

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

commit 2d96fbff48d6274ebc2a9cb21f88d9e326115d97
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Mon Feb 27 22:52:43 2023 +0000

    Speed up deleting blocked_builds entries
---
 guix-data-service/data-deletion.scm | 28 +++++++++++++++++++++-------
 1 file changed, 21 insertions(+), 7 deletions(-)

diff --git a/guix-data-service/data-deletion.scm 
b/guix-data-service/data-deletion.scm
index 243dcbc..e266590 100644
--- a/guix-data-service/data-deletion.scm
+++ b/guix-data-service/data-deletion.scm
@@ -388,14 +388,28 @@ DELETE FROM builds WHERE id IN ("
   (define (delete-blocked-builds-for-derivation-output-details-set
            conn
            derivation-output-details-set-id)
-    (exec-query
-     conn
-     "
+    ;; Do this for each build server individually, as that helps PostgreSQL
+    ;; efficiently check the partitions
+    (let ((build-server-ids
+           (map
+            car
+            (exec-query
+             conn
+             "SELECT id FROM build_servers"))))
+      (for-each
+       (lambda (build-server-id)
+         (exec-query
+          conn
+          "
 DELETE FROM blocked_builds
-WHERE blocked_derivation_output_details_set_id = $1
-   OR blocking_derivation_output_details_set_id = $2"
-     (list derivation-output-details-set-id
-           derivation-output-details-set-id)))
+WHERE build_server_id = $1
+  AND (
+    blocked_derivation_output_details_set_id = $2 OR 
blocking_derivation_output_details_set_id = $3
+  )"
+          (list build-server-id
+                derivation-output-details-set-id
+                derivation-output-details-set-id)))
+       build-server-ids)))
 
   (define (delete-unreferenced-derivations-source-files conn)
     (exec-query