[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
branch master updated: Optimize database queries.
From: |
Mathieu Othacehe |
Subject: |
branch master updated: Optimize database queries. |
Date: |
Mon, 28 Sep 2020 11:51:26 -0400 |
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 0ffcb80 Optimize database queries.
0ffcb80 is described below
commit 0ffcb80ebbaa2b177f03548035a2ef21ae7ac41d
Author: Mathieu Othacehe <othacehe@gnu.org>
AuthorDate: Mon Sep 28 17:47:19 2020 +0200
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(-)
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;
[Prev in Thread] |
Current Thread |
[Next in Thread] |
- branch master updated: Optimize database queries.,
Mathieu Othacehe <=