From 0ffcb80ebbaa2b177f03548035a2ef21ae7ac41d Mon Sep 17 00:00:00 2001
From: Mathieu Othacehe <othacehe@gnu.org>
Date: Mon, 28 Sep 2020 17:47:19 +0200
Subject: Optimize database queries.

Avoid full scans of Builds table that can be very time consuming by rewriting
some queries and using new indexes.

* src/sql/upgrade-12.sql: New file.
* Makefile.am (dist_sql_DATA): Add it.
* src/schema.sql (Builds_evaluation_index, Evaluations_status_index,
Evaluations_specification_index): New indexes.
* src/cuirass/database.scm (db-get-evaluations-build-summary,
db-get-evaluation-summary): Rewrite queries to avoid full Builds table scan
and use the new indexes.
---
 Makefile.am              |  3 ++-
 src/cuirass/database.scm | 29 ++++++++++-------------------
 src/schema.sql           |  6 ++++--
 src/sql/upgrade-12.sql   |  7 +++++++
 4 files changed, 23 insertions(+), 22 deletions(-)
 create mode 100644 src/sql/upgrade-12.sql

diff --git a/Makefile.am b/Makefile.am
index 0e4d3c8..60b1e24 100644
--- a/Makefile.am
+++ b/Makefile.am
@@ -79,7 +79,8 @@ dist_sql_DATA = 				\
   src/sql/upgrade-8.sql				\
   src/sql/upgrade-9.sql				\
   src/sql/upgrade-10.sql			\
-  src/sql/upgrade-11.sql
+  src/sql/upgrade-11.sql			\
+  src/sql/upgrade-12.sql
 
 dist_css_DATA =					\
   src/static/css/cuirass.css			\
diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm
index 666a20b..c67a234 100644
--- a/src/cuirass/database.scm
+++ b/src/cuirass/database.scm
@@ -1032,21 +1032,17 @@ FROM Evaluations ORDER BY id DESC LIMIT " limit ";"))
 (define (db-get-evaluations-build-summary spec limit border-low border-high)
   (with-db-worker-thread db
     (let loop ((rows (sqlite-exec db "
-SELECT E.id, E.status, B.succeeded, B.failed, B.scheduled
-FROM
-(SELECT id, status
-FROM Evaluations
+SELECT E.id, E.status, SUM(B.status=0) as succeeded,
+SUM(B.status>0) as failed, SUM(B.status<0) as scheduled FROM
+(SELECT id, status FROM Evaluations
 WHERE (specification=" spec ")
 AND (" border-low "IS NULL OR (id >" border-low "))
 AND (" border-high "IS NULL OR (id <" border-high "))
 ORDER BY CASE WHEN " border-low "IS NULL THEN id ELSE -id END DESC
 LIMIT " limit ") E
-LEFT JOIN
-(SELECT rowid, evaluation, SUM(status=0) as succeeded,
-SUM(status>0) as failed, SUM(status<0) as scheduled
-FROM Builds
-GROUP BY evaluation) B
+LEFT JOIN Builds as B
 ON B.evaluation=E.id
+GROUP BY E.id
 ORDER BY E.id ASC;"))
                (evaluations '()))
       (match rows
@@ -1081,16 +1077,11 @@ WHERE specification=" spec)))
   (with-db-worker-thread db
     (let ((rows (sqlite-exec db "
 SELECT E.id, E.status, E.timestamp, E.checkouttime, E.evaltime,
-B.total, B.succeeded, B.failed, B.scheduled
-FROM
- (SELECT id, status, timestamp, checkouttime, evaltime
-FROM Evaluations
-WHERE (id=" id ")) E
-LEFT JOIN
- (SELECT rowid, evaluation, SUM(status=0) as succeeded,
-SUM(status>0) as failed, SUM(status<0) as scheduled, SUM(status>-100) as total
-FROM Builds
-GROUP BY evaluation) B
+SUM(B.status>-100) as total, SUM(B.status=0) as succeeded,
+SUM(B.status>0) as failed, SUM(B.status<0) as scheduled FROM
+(SELECT id, status, timestamp, checkouttime, evaltime FROM
+        Evaluations WHERE (id=" id ")) E
+LEFT JOIN Builds as B
 ON B.evaluation=E.id
 ORDER BY E.id ASC;")))
       (and=> (expect-one-row rows)
diff --git a/src/schema.sql b/src/schema.sql
index ed5893e..cc9ad24 100644
--- a/src/schema.sql
+++ b/src/schema.sql
@@ -95,9 +95,11 @@ CREATE TABLE Events (
   event_json    TEXT NOT NULL
 );
 
--- Create indexes to speed up common queries, in particular those
--- corresponding to /api/latestbuilds and /api/queue HTTP requests.
+-- Create indexes to speed up common queries.
 CREATE INDEX Builds_status_index ON Builds (status);
+CREATE INDEX Builds_evaluation_index ON Builds (evaluation, status);
+CREATE INDEX Evaluations_status_index ON Evaluations (id, status);
+CREATE INDEX Evaluations_specification_index ON Evaluations (specification, id DESC);
 CREATE INDEX Outputs_derivation_index ON Outputs (derivation);
 CREATE INDEX Inputs_index ON Inputs(specification, name, branch);
 
diff --git a/src/sql/upgrade-12.sql b/src/sql/upgrade-12.sql
new file mode 100644
index 0000000..06aaffe
--- /dev/null
+++ b/src/sql/upgrade-12.sql
@@ -0,0 +1,7 @@
+BEGIN TRANSACTION;
+
+CREATE INDEX Builds_evaluation_index ON Builds (evaluation, status);
+CREATE INDEX Evaluations_status_index ON Evaluations (id, status);
+CREATE INDEX Evaluations_specification_index ON Evaluations (specification, id DESC);
+
+COMMIT;
-- 
cgit v1.2.3