01/06: Speed up a query for derivation builds

[Christopher Baines]

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

commit af40c1ac139d43dd91d11c0af6fcf597833ccf83
Author: Christopher Baines <mail@cbaines.net>
AuthorDate: Fri Oct 2 17:51:55 2020 +0100

    Speed up a query for derivation builds
    
    This change removes a sequential scan from the query plan, making it much
    faster.
---
 guix-data-service/model/build.scm   | 19 ++++++++-----------
 guix-data-service/web/view/html.scm |  8 +++++---
 2 files changed, 13 insertions(+), 14 deletions(-)

diff --git a/guix-data-service/model/build.scm 
b/guix-data-service/model/build.scm
index 96a3ab8..3a2d630 100644
--- a/guix-data-service/model/build.scm
+++ b/guix-data-service/model/build.scm
@@ -205,27 +205,24 @@ ORDER BY latest_build_status.timestamp DESC NULLS LAST, 
derivations.file_name
          conn derivation-file-name)
   (define query
     "
-SELECT build_servers.id,
+SELECT DISTINCT ON (builds.id)
+       builds.id,
+       build_servers.id,
        build_servers.url,
        builds.build_server_build_id,
-       latest_build_status.timestamp,
-       latest_build_status.status
+       build_status.timestamp,
+       build_status.status
 FROM builds
 INNER JOIN build_servers ON build_servers.id = builds.build_server_id
-INNER JOIN
-(
-  SELECT DISTINCT ON (build_id) *
-  FROM build_status
-  ORDER BY build_id, id DESC
-) AS latest_build_status
-  ON latest_build_status.build_id = builds.id
+INNER JOIN build_status
+  ON build_status.build_id = builds.id
 INNER JOIN derivations_by_output_details_set
   ON builds.derivation_output_details_set_id =
      derivations_by_output_details_set.derivation_output_details_set_id
 INNER JOIN derivations
   ON derivations.id = derivations_by_output_details_set.derivation_id
 WHERE derivations.file_name = $1
-ORDER BY latest_build_status.timestamp DESC")
+ORDER BY builds.id, build_status.timestamp DESC")
 
   (exec-query conn query (list derivation-file-name)))
 
diff --git a/guix-data-service/web/view/html.scm 
b/guix-data-service/web/view/html.scm
index 4b11f76..8ad0f39 100644
--- a/guix-data-service/web/view/html.scm
+++ b/guix-data-service/web/view/html.scm
@@ -658,9 +658,11 @@ time."
                  ,(build-status-span "")))
               (map
                (match-lambda
-                 ((build-server-id build-server-url
-                                   build-server-build-id
-                                   timestamp status)
+                 ((build-id build-server-id
+                            build-server-url
+                            build-server-build-id
+                            timestamp
+                            status)
                   `(div
                     (@ (class "text-center"))
                     (div