branch: main
commit e611adcca75f483eab08d4f94c4c985d23ff1696
Author: Ludovic Courtès <l...@gnu.org>
AuthorDate: Sat Oct 5 17:46:59 2024 +0200
database: Optimize ‘db-get-builds-{min,max}’.
This query would be very slow:
LOG: duration: 10805.150 ms plan:
Query Text: SELECT stoptime, id FROM Builds
WHERE evaluation = $1 AND
(( $2 = 'pending' AND Builds.status < 0) OR
( $3 = 'succeeded' AND Builds.status = 0) OR
( $4 = 'failed' AND Builds.status > 0) OR
( $5 = 'newly-failed' AND Builds.status = $6
AND Builds.weather = $7 ) OR
$8 ::text IS NULL)
ORDER BY stoptime DESC, id DESC
LIMIT 1
Limit (cost=0.43..213.80 rows=1 width=8)
-> Index Scan using builds_stoptime_id on builds
(cost=0.43..283141.54 rows=1327 width=8)
Filter: (evaluation = 242640)
Its plain direct counterpart appears to be more efficient:
cuirass=> explain analyze select stoptime,id from builds where evaluation
= 242640 and builds.status = 0 order by stoptime desc, id desc limit 1;
QUERY
PLAN
-----------------------------------------------------------------------------------------------------------------------
-----------------------
Limit (cost=0.43..464.06 rows=1 width=8) (actual
time=6197.445..6197.447 rows=1 loops=1)
-> Index Scan using builds_stoptime_id on builds
(cost=0.43..295794.55 rows=638 width=8) (actual time=6197.443..61
97.443 rows=1 loops=1)
Filter: ((evaluation = 242640) AND (status = 0))
Rows Removed by Filter: 4597414
Planning Time: 0.240 ms
Execution Time: 6197.527 ms
(6 rows)
Time: 6198.353 ms (00:06.198)
* src/cuirass/database.scm (db-get-builds-boundary): New procedure,
based on ‘db-get-builds-min’.
(db-get-builds-min, db-get-builds-max): Rewrite in terms of
‘db-get-builds-boundary’.
---
src/cuirass/database.scm | 69 ++++++++++++++++++++++++++----------------------
1 file changed, 37 insertions(+), 32 deletions(-)
diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm
index bc9046c..3ae0a42 100644
--- a/src/cuirass/database.scm
+++ b/src/cuirass/database.scm
@@ -2130,43 +2130,48 @@ AND (Builds.system = :system OR :system IS NULL);")
((max) (and max
(list (string->number max))))))))
-(define (db-get-builds-min eval status)
- "Return the min build (stoptime, rowid) pair for the given evaluation EVAL
-and STATUS."
- (with-db-connection db
- (match (expect-one-row
- (exec-query/bind db "SELECT stoptime, id FROM Builds
-WHERE evaluation = " eval " AND
-((" status " = 'pending' AND Builds.status < 0) OR
- (" status " = 'succeeded' AND Builds.status = 0) OR
- (" status " = 'failed' AND Builds.status > 0) OR
- (" status " = 'newly-failed' AND Builds.status = " (build-status failed) "
- AND Builds.weather = " (build-weather new-failure) ") OR
- " status "::text IS NULL)
-ORDER BY stoptime ASC, id ASC
-LIMIT 1"))
- ((stoptime id) (list (string->number stoptime)
- (string->number id)))
- (else #f))))
-
-(define (db-get-builds-max eval status)
+(define (db-get-builds-boundary eval status kind)
"Return the max build (stoptime, rowid) pair for the given evaluation EVAL
-and STATUS."
+and STATUS, a string such as \"pending\"."
+ (define (query . selector)
+ ;; Return the query for SELECTOR as a string. This is more efficient than
+ ;; making STATUS a query parameter and adding conditionals on its value in
+ ;; 'WHERE'.
+ (let ((order (match kind
+ ('min "ASC")
+ ('max "DESC"))))
+ (string-append "SELECT stoptime, id FROM Builds WHERE evaluation = $1"
+ " AND "
+ (string-concatenate
+ (map (match-lambda
+ ((? string? str) str)
+ ((? number? n) (number->string n)))
+ selector))
+ " ORDER BY stoptime " order
+ ", id " order " LIMIT 1;")))
+
(with-db-connection db
(match (expect-one-row
- (exec-query/bind db "SELECT stoptime, id FROM Builds
-WHERE evaluation = " eval " AND
-((" status " = 'pending' AND Builds.status < 0) OR
- (" status " = 'succeeded' AND Builds.status = 0) OR
- (" status " = 'failed' AND Builds.status > 0) OR
- (" status " = 'newly-failed' AND Builds.status = " (build-status failed) "
- AND Builds.weather = " (build-weather new-failure) ") OR
- " status "::text IS NULL)
-ORDER BY stoptime DESC, id DESC
-LIMIT 1"))
+ (exec-query db
+ (match status
+ ("pending" (query "status < 0"))
+ ("succeeded" (query "status = 0"))
+ ("failed" (query "status > 0"))
+ ("newly-failed"
+ (query "status = " (build-status failed)
+ " AND weather = "
+ (build-weather new-failure)))
+ (_ (query "TRUE")))
+ (list (number->string eval))))
((stoptime id) (list (string->number stoptime)
(string->number id)))
- (else #f))))
+ (_ #f))))
+
+(define db-get-builds-min
+ (cut db-get-builds-boundary <> <> 'min))
+
+(define db-get-builds-max
+ (cut db-get-builds-boundary <> <> 'max))
(define (db-get-evaluation-specification eval)
"Return specification of evaluation with id EVAL."
