[Cuirass] Missing database indexes?

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

Hello!

I was investigating the slowness of our /api/latestbuilds requests on
berlin.

I found that if we have just the two indexes currently defined in
‘schema.sql’, basically everything involves a table scan:

--8<---------------cut here---------------start------------->8---
sqlite> EXPLAIN QUERY PLAN select * from builds where system = "x";                                                                                                       
0|0|0|SCAN TABLE builds
--8<---------------cut here---------------end--------------->8---

I tentatively defined new indexes that seem to help:

--8<---------------cut here---------------start------------->8---
sqlite> CREATE INDEX Builds_index_evaluation ON Builds(evaluation);
sqlite> CREATE INDEX Builds_index_status ON Builds(status);
sqlite> CREATE INDEX Builds_index_system ON Builds(system, evaluation);
sqlite> EXPLAIN QUERY PLAN select * from builds where evaluation = 12;
0|0|0|SEARCH TABLE builds USING INDEX Builds_index_evaluation (evaluation=?)
sqlite> EXPLAIN QUERY PLAN select * from builds where evaluation = 12 and system ="x";
0|0|0|SEARCH TABLE builds USING INDEX Builds_index_system (system=? AND evaluation=?)
sqlite> EXPLAIN QUERY PLAN select * from builds where evaluation = 12 and status = 0;
0|0|0|SEARCH TABLE builds USING INDEX Builds_index_status (status=?)
--8<---------------cut here---------------end--------------->8---

Now, ‘db-get-builds’ in Cuirass uses a more complex query.  In
particular, it orders things, very roughly along these lines:

--8<---------------cut here---------------start------------->8---
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=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY
--8<---------------cut here---------------end--------------->8---

I’m pretty much a database newbie so please forgive the naive question,
but is there something we can do to avoid this extra B-tree step, which
seems costly in space and time?  <http://www.sqlite.com/matrix/eqp.html>
suggests it’s just a matter of adding yet another index but I couldn’t
get that.

Anything else we should do?

Thanks in advance!  :-)

Ludo’.