[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: [Cuirass] Missing database indexes?
From: |
Ludovic Courtès |
Subject: |
Re: [Cuirass] Missing database indexes? |
Date: |
Sun, 11 Nov 2018 18:06:00 +0100 |
User-agent: |
Gnus/5.13 (Gnus v5.13) Emacs/26.1 (gnu/linux) |
Hi Björn,
Björn Höfling <address@hidden> 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
- [Cuirass] Missing database indexes?, Ludovic Courtès, 2018/11/10
- Re: [Cuirass] Missing database indexes?, Björn Höfling, 2018/11/10
- Re: [Cuirass] Missing database indexes?,
Ludovic Courtès <=
- Re: [Cuirass] Missing database indexes?, Björn Höfling, 2018/11/12
- Re: [Cuirass] Missing database indexes?, Amirouche Boubekki, 2018/11/12
- Re: [Cuirass] Missing database indexes?, Danny Milosavljevic, 2018/11/12
- Re: [Cuirass] Missing database indexes?, Ludovic Courtès, 2018/11/14
- Re: [Cuirass] Missing database indexes?, Björn Höfling, 2018/11/16
- Re: [Cuirass] Missing database indexes?, Clément Lassieur, 2018/11/13
- Re: [Cuirass] Missing database indexes?, Björn Höfling, 2018/11/16
- Re: [Cuirass] Missing database indexes?, Danny Milosavljevic, 2018/11/12
- Re: [Cuirass] Missing database indexes?, Danny Milosavljevic, 2018/11/12
- Re: [Cuirass] Missing database indexes?, Ludovic Courtès, 2018/11/14