Re: [Cuirass] Missing database indexes?

[Ludovic Courtès]

Hi,

Danny Milosavljevic <address@hidden> skribis:

> On Sun, 11 Nov 2018 18:06:00 +0100
> address@hidden (Ludovic Courtès) wrote:
>
>> I don’t really know what additional index to create (and I’d rather let
>> SQLite do it for me, if it were possible).
>
> Not exactly what you mean but there's this:
>
> https://www.sqlite.org/lang_analyze.html
>
> It does statistical analysis of queries that ran and will optimize for
> that case on subsequent connections.

That’s close to what I was hoping for.  We could do “PRAGMA optimize”
before closing the database session as they suggest:

diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm
index 8b83c18..fb037d1 100644
--- a/src/cuirass/database.scm
+++ b/src/cuirass/database.scm
@@ -403,7 +403,9 @@ a critical section that allows database operations to be 
serialized."
      ;; be costly and may defeat statement caching.
      (parameterize ((%db-channel (make-critical-section db)))
        body ...)
-     (db-close db))))
+     (begin
+       (sqlite-exec db "PRAGMA optimize;")
+       (db-close db)))))
 
 (define* (read-quoted-string #:optional (port (current-input-port)))
   "Read all of the characters out of PORT and return them as a SQL quoted

… though I think we never really close it properly because Cuirass runs
“forever.”

I tried doing this in a Guile session on berlin:

--8<---------------cut here---------------start------------->8---
scheme@(guile-user)> ,use(cuirass database)
scheme@(guile-user)> (%package-database "/var/lib/cuirass/cuirass.db")
$6 = 
"/gnu/store/g1q2lv75a2fibii4y52fndz5zpbmyl12-cuirass-0.0.1-21.0b40dca/var/lib/cuirass/cuirass.db"
scheme@(guile-user)>  (with-database (db-get-builds `((nr . 200)(order . 
finish-time)(status . done))) (with-db-critical-section db (sqlite-exec db 
"PRAGMA optimize;")))
$7 = ()
scheme@(guile-user)>  (with-database (db-get-builds `((nr . 200)(order . 
finish-time)(status . done))) (with-db-critical-section db (sqlite-exec db 
"PRAGMA optimize;")))
$8 = ()
scheme@(guile-user)> ,time (with-database (db-get-builds `((nr . 200)(order . 
finish-time)(status . done))) (with-db-critical-section db (sqlite-exec db 
"PRAGMA optimize;")))
$9 = ()
;; 13.215291s real time, 13.229189s run time.  0.016093s spent in GC.
scheme@(guile-user)> ,time (with-database (db-get-builds `((nr . 200)(order . 
finish-time)(status . done))) (with-db-critical-section db (sqlite-exec db 
"PRAGMA optimize;")))
$10 = ()
;; 13.204621s real time, 13.230655s run time.  0.029333s spent in GC.
--8<---------------cut here---------------end--------------->8---

It doesn’t seem to help much, perhaps because the query is too complex?

Thoughts?

Ludo’.