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

[Christopher Baines]

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

commit 1a0c5599ebd8b2870b2c7aef3ec6797a4c203cd2
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Mon Nov 28 11:36:12 2022 +0000

    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.
---
 guix-data-service/jobs/load-new-guix-revision.scm | 16 ++++++
 guix-data-service/model/derivation.scm            | 65 ++++++++++++++++++++++-
 2 files changed, 80 insertions(+), 1 deletion(-)

diff --git a/guix-data-service/jobs/load-new-guix-revision.scm 
b/guix-data-service/jobs/load-new-guix-revision.scm
index f60eaad..c1c27af 100644
--- a/guix-data-service/jobs/load-new-guix-revision.scm
+++ b/guix-data-service/jobs/load-new-guix-revision.scm
@@ -2142,6 +2142,22 @@ SKIP LOCKED")
                   "vacuuming package derivations by guix revision range table"
                 (vacuum-package-derivations-table conn))
 
+              (with-time-logging
+                  "update-derivation-inputs-statistics"
+                (update-derivation-inputs-statistics conn))
+
+              (with-time-logging
+                  "vacuum-derivation-inputs-table"
+                (vacuum-derivation-inputs-table conn))
+
+              (with-time-logging
+                  "update-derivation-outputs-statistics"
+                (update-derivation-outputs-statistics conn))
+
+              (with-time-logging
+                  "vacuum-derivation-outputs-table"
+                (vacuum-derivation-outputs-table conn))
+
               #t)
             (begin
               (exec-query conn "ROLLBACK")
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"))