branch master updated: metrics: Optimize queries.

[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 4d7864c  metrics: Optimize queries.
4d7864c is described below

commit 4d7864c0465fda9bb9f2d00de2f0ca0fd6a75ff0
Author: Mathieu Othacehe <othacehe@gnu.org>
AuthorDate: Tue Sep 29 17:44:40 2020 +0200

    metrics: Optimize queries.
    
    Avoid full Builds table scans.
    
    * src/cuirass/metrics.scm (db-average-build-start-time-per-eval,
    db-average-build-complete-time-per-eval, db-evaluation-completion-speed):
    Rewrite query to avoid full Builds table scans.
---
 src/cuirass/metrics.scm | 9 +++------
 1 file changed, 3 insertions(+), 6 deletions(-)

diff --git a/src/cuirass/metrics.scm b/src/cuirass/metrics.scm
index 1fa94cd..2c61555 100644
--- a/src/cuirass/metrics.scm
+++ b/src/cuirass/metrics.scm
@@ -103,8 +103,7 @@ SELECT 100 * CAST(SUM(status > 0) as float) / COUNT(*) FROM
 SELECT AVG(B.starttime - E.evaltime) FROM
 (SELECT id, evaltime
 FROM Evaluations WHERE id = " eval ") E
-LEFT JOIN
-(SELECT id, evaluation, starttime FROM Builds) B
+LEFT JOIN Builds as B
 ON E.id = B.evaluation and B.starttime > 0
 GROUP BY E.id;")))
       (and=> (expect-one-row rows) (cut vector-ref <> 0)))))
@@ -116,8 +115,7 @@ GROUP BY E.id;")))
 SELECT AVG(B.stoptime - E.evaltime) FROM
 (SELECT id, evaltime
 FROM Evaluations WHERE id = " eval ") E
-LEFT JOIN
-(SELECT id, evaluation, stoptime FROM Builds) B
+LEFT JOIN Builds as B
 ON E.id = B.evaluation and B.stoptime > 0
 GROUP BY E.id;")))
       (and=> (expect-one-row rows) (cut vector-ref <> 0)))))
@@ -142,8 +140,7 @@ SELECT
 END - E.evaltime) FROM
 (SELECT id, evaltime
 FROM Evaluations WHERE id = " eval ") E
-LEFT JOIN
-(SELECT id, evaluation, status, stoptime FROM Builds) B
+LEFT JOIN Builds as B
 ON E.id = B.evaluation and B.stoptime > 0
 GROUP BY E.id;")))
       (and=> (expect-one-row rows) (cut vector-ref <> 0)))))