Re: [Cuirass] Missing database indexes?

[ludo@gnu.org (Ludovic Courtès)]

Hi Björn,

Björn Höfling <bjoern.hoefling@bjoernhoefling.de> skribis:

> The link you provided explains it: The column over which you are sorting
> (stoptime) is not indexed. Add it to the (same) index:
>
> sqlite> DROP INDEX Builds_index_evaluation;
> sqlite> CREATE INDEX Builds_index_evaluation ON Builds(evaluation, stoptime);
> sqlite> EXPLAIN QUERY PLAN select * from builds where evaluation = 12 and status > 0 order by stoptime ;
> 0|0|0|SEARCH TABLE builds USING INDEX Builds_index_evaluation (evaluation=?)
>
> If there is more SQL-trouble, I can try to help out.

Indeed, that solves the problem for this simple example, thanks!

Now, if I go back to the big query that /api/latestbuilds makes¹, the
result is still pretty bad:

--8<---------------cut here---------------start------------->8---
sqlite> EXPLAIN QUERY PLAN SELECT * FROM (
   ...> SELECT Builds.derivation, Builds.rowid, Builds.timestamp, Builds.starttime,
   ...> Builds.stoptime, Builds.log, Builds.status, Builds.job_name, Builds.system,
   ...> Builds.nix_name, Specifications.name
   ...> FROM Builds
   ...> INNER JOIN Evaluations ON Builds.evaluation = Evaluations.id
   ...> INNER JOIN Specifications ON Evaluations.specification = Specifications.name
   ...> WHERE (:id IS NULL OR (:id = Builds.rowid))
   ...> AND (:derivation IS NULL OR (:derivation = Builds.derivation))
   ...> AND (:jobset IS NULL OR (:jobset = Specifications.name))
   ...> AND (:job IS NULL OR (:job = Builds.job_name))
   ...> AND (:system IS NULL OR (:system = Builds.system))
   ...> AND (:evaluation IS NULL OR (:evaluation = Builds.evaluation))
   ...> AND (:status IS NULL OR (:status = 'done' AND Builds.status >= 0)
   ...>                      OR (:status = 'pending' AND Builds.status < 0)
   ...>                      OR (:status = 'succeeded' AND Builds.status = 0)
   ...>                      OR (:status = 'failed' AND Builds.status > 0))
   ...> AND (:borderlowtime IS NULL OR :borderlowid IS NULL
   ...>  OR ((:borderlowtime, :borderlowid) < (Builds.stoptime, Builds.rowid)))
   ...> AND (:borderhightime IS NULL OR :borderhighid IS NULL
   ...>  OR ((:borderhightime, :borderhighid) > (Builds.stoptime, Builds.rowid)))
   ...> ORDER BY
   ...> CASE WHEN :borderlowtime IS NULL
   ...>        OR :borderlowid IS NULL THEN Builds.stoptime
   ...>                                ELSE -Builds.stoptime
   ...> END DESC,
   ...> CASE WHEN :borderlowtime IS NULL
   ...>        OR :borderlowid IS NULL THEN Builds.rowid
   ...>                                ELSE -Builds.rowid
   ...> END DESC
   ...> LIMIT :nr)
   ...> ORDER BY stoptime, rowid ASC;
1|0|0|SCAN TABLE Builds
1|1|1|SEARCH TABLE Evaluations USING INTEGER PRIMARY KEY (rowid=?)
1|2|2|SEARCH TABLE Specifications USING COVERING INDEX sqlite_autoindex_Specifications_1 (name=?)
1|0|0|USE TEMP B-TREE FOR ORDER BY
0|0|0|SCAN SUBQUERY 1
0|0|0|USE TEMP B-TREE FOR ORDER BY
--8<---------------cut here---------------end--------------->8---

I don’t really know what additional index to create (and I’d rather let
SQLite do it for me, if it were possible).

Thoughts?

Thanks,
Ludo’.

¹ https://git.savannah.gnu.org/cgit/guix/guix-cuirass.git/tree/src/cuirass/database.scm#n550