[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Cuirass] Missing database indexes?
From: |
Ludovic Courtès |
Subject: |
[Cuirass] Missing database indexes? |
Date: |
Sat, 10 Nov 2018 18:33:23 +0100 |
User-agent: |
Gnus/5.13 (Gnus v5.13) Emacs/26.1 (gnu/linux) |
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’.
- [Cuirass] Missing database indexes?,
Ludovic Courtès <=
- Re: [Cuirass] Missing database indexes?, Björn Höfling, 2018/11/10
- Re: [Cuirass] Missing database indexes?, Ludovic Courtès, 2018/11/11
- 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