branch master updated: database: Optimize the percentage query.

[Mathieu Othacehe]

This is an automated email from the git hooks/post-receive script.

mothacehe pushed a commit to branch master
in repository guix-cuirass.

The following commit(s) were added to refs/heads/master by this push:
     new 72a30ec  database: Optimize the percentage query.
72a30ec is described below

commit 72a30ecd0a15aab23414212305e1fe94f7961163
Author: Mathieu Othacehe <othacehe@gnu.org>
AuthorDate: Fri Nov 26 13:55:10 2021 +0000

    database: Optimize the percentage query.
    
    This decreases the query duration from ~20 seconds to ~35ms.
    
    * src/sql/upgrade-14.sql: New file.
    * Makefile.am (dist_sql_DATA): Add it.
    * src/cuirass/database.scm (db-get-build-percentages): Optimize it.
    * src/schema.sql (Builds_id_job_status_index): Add it.
---
 Makefile.am              | 3 ++-
 src/cuirass/database.scm | 2 +-
 src/schema.sql           | 1 +
 src/sql/upgrade-14.sql   | 5 +++++
 4 files changed, 9 insertions(+), 2 deletions(-)

diff --git a/Makefile.am b/Makefile.am
index 68cedbc..9a1518d 100644
--- a/Makefile.am
+++ b/Makefile.am
@@ -101,7 +101,8 @@ dist_sql_DATA =                             \
   src/sql/upgrade-10.sql                       \
   src/sql/upgrade-11.sql                       \
   src/sql/upgrade-12.sql                       \
-  src/sql/upgrade-13.sql
+  src/sql/upgrade-13.sql                       \
+  src/sql/upgrade-14.sql
 
 dist_css_DATA =                                        \
   src/static/css/choices.min.css               \
diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm
index 7e036b3..89417b0 100644
--- a/src/cuirass/database.scm
+++ b/src/cuirass/database.scm
@@ -680,7 +680,7 @@ FROM (SELECT  DISTINCT ON (b1.id) b1.id AS id,
 COALESCE((b2.stoptime - b2.starttime), 0) AS last_duration,
 (extract(epoch from now())::int - b1.starttime) AS duration FROM builds AS b1
 LEFT JOIN builds AS b2 ON b1.job_name = b2.job_name
-AND b2.status = 0 OR b2.status = 1 WHERE b1.id IN
+AND b2.status >= 0 AND b2.status < 2 WHERE b1.id IN
 (SELECT id FROM builds WHERE id = ANY(" build-ids "))
 ORDER BY b1.id,  b2.id DESC) d;"))
                (percentages '()))
diff --git a/src/schema.sql b/src/schema.sql
index cd9c512..70fa90a 100644
--- a/src/schema.sql
+++ b/src/schema.sql
@@ -160,6 +160,7 @@ CREATE INDEX Builds_stoptime_id on Builds(stoptime DESC, id 
DESC);
 CREATE INDEX Builds_status_ts_id on Builds(status DESC, timestamp DESC, id 
ASC);
 CREATE INDEX Builds_priority_timestamp on Builds(priority ASC, timestamp DESC);
 CREATE INDEX Builds_weather_evaluation ON Builds (weather, evaluation);
+CREATE INDEX Builds_id_job_status_index ON Builds (id DESC, job_name, status);
 CREATE INDEX Jobs_name ON Jobs (name);
 CREATE INDEX Jobs_system_status ON Jobs (system, status);
 CREATE INDEX Jobs_build ON Jobs (build); --speeds up delete cascade.
diff --git a/src/sql/upgrade-14.sql b/src/sql/upgrade-14.sql
new file mode 100644
index 0000000..f4e8c6a
--- /dev/null
+++ b/src/sql/upgrade-14.sql
@@ -0,0 +1,5 @@
+BEGIN TRANSACTION;
+
+CREATE INDEX Builds_id_job_status_index ON Builds (id DESC, job_name, status);
+
+COMMIT;