unofficial mirror of guix-devel@gnu.org 
 help / color / mirror / code / Atom feed
* [Cuirass] Missing database indexes?
@ 2018-11-10 17:33 Ludovic Courtès
  2018-11-10 20:11 ` Björn Höfling
  2018-11-19  9:47 ` swedebugia
  0 siblings, 2 replies; 16+ messages in thread
From: Ludovic Courtès @ 2018-11-10 17:33 UTC (permalink / raw)
  To: Danny Milosavljevic, clement; +Cc: guix-devel

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’.

^ permalink raw reply	[flat|nested] 16+ messages in thread

end of thread, other threads:[~2018-12-19 22:45 UTC | newest]

Thread overview: 16+ messages (download: mbox.gz follow: Atom feed
-- links below jump to the message on this page --
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
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

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).