From mboxrd@z Thu Jan  1 00:00:00 1970
From: ludo@gnu.org (Ludovic =?utf-8?Q?Court=C3=A8s?=)
Subject: [Cuirass] Missing database indexes?
Date: Sat, 10 Nov 2018 18:33:23 +0100
Message-ID: <87va54yh0c.fsf@gnu.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Return-path: <guix-devel-bounces+gcggd-guix-devel=m.gmane.org@gnu.org>
Received: from eggs.gnu.org ([2001:4830:134:3::10]:38206)
	by lists.gnu.org with esmtp (Exim 4.71)
	(envelope-from <ludo@gnu.org>) id 1gLX8j-0001kY-R7
	for guix-devel@gnu.org; Sat, 10 Nov 2018 12:33:46 -0500
Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71)
	(envelope-from <ludo@gnu.org>) id 1gLX8d-0000vU-Th
	for guix-devel@gnu.org; Sat, 10 Nov 2018 12:33:43 -0500
List-Id: "Development of GNU Guix and the GNU System distribution."
	<guix-devel.gnu.org>
List-Unsubscribe: <https://lists.gnu.org/mailman/options/guix-devel>,
	<mailto:guix-devel-request@gnu.org?subject=unsubscribe>
List-Archive: <http://lists.gnu.org/archive/html/guix-devel/>
List-Post: <mailto:guix-devel@gnu.org>
List-Help: <mailto:guix-devel-request@gnu.org?subject=help>
List-Subscribe: <https://lists.gnu.org/mailman/listinfo/guix-devel>,
	<mailto:guix-devel-request@gnu.org?subject=subscribe>
Errors-To: guix-devel-bounces+gcggd-guix-devel=m.gmane.org@gnu.org
Sender: "Guix-devel" <guix-devel-bounces+gcggd-guix-devel=m.gmane.org@gnu.org>
To: Danny Milosavljevic <dannym@scratchpost.org>, clement@lassieur.org
Cc: guix-devel@gnu.org

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
=E2=80=98schema.sql=E2=80=99, basically everything involves a table scan:

--8<---------------cut here---------------start------------->8---
sqlite> EXPLAIN QUERY PLAN select * from builds where system =3D "x";=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20
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 =3D 12;
0|0|0|SEARCH TABLE builds USING INDEX Builds_index_evaluation (evaluation=
=3D?)
sqlite> EXPLAIN QUERY PLAN select * from builds where evaluation =3D 12 and=
 system =3D"x";
0|0|0|SEARCH TABLE builds USING INDEX Builds_index_system (system=3D? AND e=
valuation=3D?)
sqlite> EXPLAIN QUERY PLAN select * from builds where evaluation =3D 12 and=
 status =3D 0;
0|0|0|SEARCH TABLE builds USING INDEX Builds_index_status (status=3D?)
--8<---------------cut here---------------end--------------->8---

Now, =E2=80=98db-get-builds=E2=80=99 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 =3D 12 and=
 status > 0 order by stoptime ;
0|0|0|SEARCH TABLE builds USING INDEX Builds_index_evaluation (evaluation=
=3D?)
0|0|0|USE TEMP B-TREE FOR ORDER BY
--8<---------------cut here---------------end--------------->8---

I=E2=80=99m pretty much a database newbie so please forgive the naive quest=
ion,
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=E2=80=99s just a matter of adding yet another index but I could=
n=E2=80=99t
get that.

Anything else we should do?

Thanks in advance!  :-)

Ludo=E2=80=99.