From mboxrd@z Thu Jan 1 00:00:00 1970 From: =?UTF-8?B?QmrDtnJuIEjDtmZsaW5n?= Subject: Re: [Cuirass] Missing database indexes? Date: Sat, 10 Nov 2018 21:11:28 +0100 Message-ID: <20181110211128.6dc522da@alma-ubu> References: <87va54yh0c.fsf@gnu.org> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; boundary="Sig_/cBXzFT9c1jhfsET7h_ABp.8"; protocol="application/pgp-signature" Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:49025) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1gLZbc-0002qn-5j for guix-devel@gnu.org; Sat, 10 Nov 2018 15:11:44 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1gLZbX-00066O-P8 for guix-devel@gnu.org; Sat, 10 Nov 2018 15:11:44 -0500 In-Reply-To: <87va54yh0c.fsf@gnu.org> List-Id: "Development of GNU Guix and the GNU System distribution." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: guix-devel-bounces+gcggd-guix-devel=m.gmane.org@gnu.org Sender: "Guix-devel" To: Ludovic =?UTF-8?B?Q291cnTDqHM=?= Cc: guix-devel@gnu.org, clement@lassieur.org --Sig_/cBXzFT9c1jhfsET7h_ABp.8 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Sat, 10 Nov 2018 18:33:23 +0100 ludo@gnu.org (Ludovic Court=C3=A8s) wrote: > 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: >=20 > --8<---------------cut here---------------start------------->8--- > sqlite> EXPLAIN QUERY PLAN select * from builds where evaluation =3D 12 > sqlite> and status > 0 order by stoptime ; =20 > 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--- >=20 > I=E2=80=99m 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? > suggests it=E2=80=99s just a matt= er > of adding yet another index but I couldn=E2=80=99t get that. >=20 > Anything else we should do? The link you provided explains it: The column over which you are sorting (stoptime) is not indexed. Add it to the (same) index: --8<---------------cut here---------------start------------->8--- 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 =3D 12 and= status > 0 order by stoptime ; 0|0|0|SEARCH TABLE builds USING INDEX Builds_index_evaluation (evaluation= =3D?) --8<---------------cut here---------------end--------------->8--- If there is more SQL-trouble, I can try to help out. Bj=C3=B6rn --Sig_/cBXzFT9c1jhfsET7h_ABp.8 Content-Type: application/pgp-signature Content-Description: OpenPGP digital signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 iEYEARECAAYFAlvnO3AACgkQvyhstlk+X/0RogCfVcyzgF9VtHEXqICGn/nO0xwZ 6hIAoISnx/bPTNLBej+iEpvEsvDIblQn =TKsZ -----END PGP SIGNATURE----- --Sig_/cBXzFT9c1jhfsET7h_ABp.8--