Do derivation inputs and outputs housekeeping at the end of each job

This should help with query performance, as the recursive queries using
derivation_inputs and derivation_outputs are particularly sensitive to the
n_distinct values for these tables.

1 files changed, 64 insertions, 1 deletions

diff --git a/guix-data-service/model/derivation.scm b/guix-data-service/model/derivation.scm
index 7a4b214..05cb9ea 100644
--- a/guix-data-service/model/derivation.scm
+++ b/guix-data-service/model/derivation.scm
@@ -19,6 +19,7 @@
   #:use-module (srfi srfi-1)
   #:use-module (ice-9 vlist)
   #:use-module (ice-9 match)
+  #:use-module (ice-9 format)
   #:use-module (ice-9 binary-ports)
   #:use-module (rnrs bytevectors)
   #:use-module (gcrypt hash)
@@ -61,7 +62,11 @@
             select-existing-derivations
             select-derivations-by-id
             select-derivations-and-build-status
-            derivation-file-names->derivation-ids))
+            derivation-file-names->derivation-ids
+            update-derivation-inputs-statistics
+            vacuum-derivation-inputs-table
+            update-derivation-outputs-statistics
+            vacuum-derivation-outputs-table))
 
 (define (valid-targets conn)
   '("arm-linux-gnueabihf"
@@ -1917,3 +1922,61 @@ INNER JOIN derivation_source_files
               (insert-source-files-missing-nars all-ids))
 
             all-ids)))))
+
+(define (update-derivation-inputs-statistics conn)
+  (let ((query
+         "
+SELECT COUNT(DISTINCT derivation_id), COUNT(DISTINCT derivation_output_id)
+FROM derivation_inputs"))
+
+    (match (exec-query conn query)
+      (((derivation_id_count derivation_output_id_count))
+
+       (exec-query
+        conn
+        (simple-format
+         #f
+         "
+ALTER TABLE derivation_inputs
+  ALTER COLUMN derivation_id
+  SET (n_distinct = ~A)"
+         derivation_id_count))
+
+       (exec-query
+        conn
+        (simple-format
+         #f
+         "
+ALTER TABLE derivation_inputs
+  ALTER COLUMN derivation_output_id
+  SET (n_distinct = ~A)"
+         derivation_output_id_count))))))
+
+(define (vacuum-derivation-inputs-table conn)
+  (exec-query
+   conn
+   "VACUUM (VERBOSE, ANALYZE) derivation_inputs"))
+
+(define (update-derivation-outputs-statistics conn)
+  (let ((query
+         "
+SELECT COUNT(DISTINCT derivation_id), COUNT(*) FROM derivation_outputs"))
+
+    (match (exec-query conn query)
+      (((derivation_id_count all_count))
+
+       (exec-query
+        conn
+        (format
+         #f
+         "
+ALTER TABLE derivation_outputs
+  ALTER COLUMN derivation_id
+  SET (n_distinct = ~7f)"
+         (* -1 (/ (string->number derivation_id_count)
+                  (string->number all_count)))))))))
+
+(define (vacuum-derivation-outputs-table conn)
+  (exec-query
+   conn
+   "VACUUM (VERBOSE, ANALYZE) derivation_outputs"))