From: ludo@gnu.org (Ludovic Courtès)
To: "Björn Höfling" <bjoern.hoefling@bjoernhoefling.de>
Cc: guix-devel@gnu.org, clement@lassieur.org
Subject: Re: [Cuirass] Missing database indexes?
Date: Sun, 11 Nov 2018 18:06:00 +0100 [thread overview]
Message-ID: <87k1ljr1c7.fsf@gnu.org> (raw)
In-Reply-To: <20181110211128.6dc522da@alma-ubu> ("Björn Höfling"'s message of "Sat, 10 Nov 2018 21:11:28 +0100")
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
next prev parent reply other threads:[~2018-11-11 17:06 UTC|newest]
Thread overview: 16+ messages / expand[flat|nested] mbox.gz Atom feed top
2018-11-10 17:33 [Cuirass] Missing database indexes? Ludovic Courtès
2018-11-10 20:11 ` Björn Höfling
2018-11-11 17:06 ` Ludovic Courtès [this message]
2018-11-12 18:50 ` Björn Höfling
2018-11-12 19:42 ` Amirouche Boubekki
2018-11-12 23:27 ` Danny Milosavljevic
2018-11-14 11:14 ` Ludovic Courtès
2018-11-16 22:31 ` Björn Höfling
2018-11-13 8:10 ` Clément Lassieur
2018-11-16 22:42 ` Björn Höfling
2018-11-12 23:31 ` Danny Milosavljevic
2018-11-13 0:04 ` Danny Milosavljevic
2018-11-14 11:11 ` Ludovic Courtès
2018-11-19 10:44 ` Danny Milosavljevic
2018-12-19 22:45 ` Amirouche Boubekki
2018-11-19 9:47 ` swedebugia
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
Avoid top-posting and favor interleaved quoting:
https://en.wikipedia.org/wiki/Posting_style#Interleaved_style
List information: https://guix.gnu.org/
* Reply using the --to, --cc, and --in-reply-to
switches of git-send-email(1):
git send-email \
--in-reply-to=87k1ljr1c7.fsf@gnu.org \
--to=ludo@gnu.org \
--cc=bjoern.hoefling@bjoernhoefling.de \
--cc=clement@lassieur.org \
--cc=guix-devel@gnu.org \
/path/to/YOUR_REPLY
https://kernel.org/pub/software/scm/git/docs/git-send-email.html
* If your mail client supports setting the In-Reply-To header
via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line
before the message body.
Code repositories for project(s) associated with this public inbox
https://git.savannah.gnu.org/cgit/guix.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).