From mboxrd@z Thu Jan 1 00:00:00 1970 From: ludo@gnu.org (Ludovic =?utf-8?Q?Court=C3=A8s?=) Subject: Re: [Cuirass] Missing database indexes? Date: Sun, 11 Nov 2018 18:06:00 +0100 Message-ID: <87k1ljr1c7.fsf@gnu.org> References: <87va54yh0c.fsf@gnu.org> <20181110211128.6dc522da@alma-ubu> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:35567) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1gLtBU-0006AL-2t for guix-devel@gnu.org; Sun, 11 Nov 2018 12:06:05 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1gLtBS-0002J6-Ul for guix-devel@gnu.org; Sun, 11 Nov 2018 12:06:04 -0500 In-Reply-To: <20181110211128.6dc522da@alma-ubu> (=?utf-8?Q?=22Bj=C3=B6rn?= =?utf-8?Q?_H=C3=B6fling=22's?= message of "Sat, 10 Nov 2018 21:11:28 +0100") 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: =?utf-8?Q?Bj=C3=B6rn_H=C3=B6fling?= Cc: guix-devel@gnu.org, clement@lassieur.org Hi Bj=C3=B6rn, Bj=C3=B6rn H=C3=B6fling skribis: > The link you provided explains it: The column over which you are sorting > (stoptime) is not indexed. Add it to the (same) index: > > sqlite> DROP INDEX Builds_index_evaluation; > sqlite> CREATE INDEX Builds_index_evaluation ON Builds(evaluation, stopti= me); > sqlite> EXPLAIN QUERY PLAN select * from builds where evaluation =3D 12 a= nd status > 0 order by stoptime ; > 0|0|0|SEARCH TABLE builds USING INDEX Builds_index_evaluation (evaluation= =3D?) > > If there is more SQL-trouble, I can try to help out. Indeed, that solves the problem for this simple example, thanks! Now, if I go back to the big query that /api/latestbuilds makes=C2=B9, the result is still pretty bad: --8<---------------cut here---------------start------------->8--- sqlite> EXPLAIN QUERY PLAN SELECT * FROM ( ...> SELECT Builds.derivation, Builds.rowid, Builds.timestamp, Builds.st= arttime, ...> Builds.stoptime, Builds.log, Builds.status, Builds.job_name, Builds= .system, ...> Builds.nix_name, Specifications.name ...> FROM Builds ...> INNER JOIN Evaluations ON Builds.evaluation =3D Evaluations.id ...> INNER JOIN Specifications ON Evaluations.specification =3D Specific= ations.name ...> WHERE (:id IS NULL OR (:id =3D Builds.rowid)) ...> AND (:derivation IS NULL OR (:derivation =3D Builds.derivation)) ...> AND (:jobset IS NULL OR (:jobset =3D Specifications.name)) ...> AND (:job IS NULL OR (:job =3D Builds.job_name)) ...> AND (:system IS NULL OR (:system =3D Builds.system)) ...> AND (:evaluation IS NULL OR (:evaluation =3D Builds.evaluation)) ...> AND (:status IS NULL OR (:status =3D 'done' AND Builds.status >=3D = 0) ...> OR (:status =3D 'pending' AND Builds.status < = 0) ...> OR (:status =3D 'succeeded' AND Builds.status = =3D 0) ...> OR (:status =3D 'failed' AND Builds.status > 0= )) ...> AND (:borderlowtime IS NULL OR :borderlowid IS NULL ...> OR ((:borderlowtime, :borderlowid) < (Builds.stoptime, Builds.rowi= d))) ...> AND (:borderhightime IS NULL OR :borderhighid IS NULL ...> OR ((:borderhightime, :borderhighid) > (Builds.stoptime, Builds.ro= wid))) ...> ORDER BY ...> CASE WHEN :borderlowtime IS NULL ...> OR :borderlowid IS NULL THEN Builds.stoptime ...> ELSE -Builds.stoptime ...> END DESC, ...> CASE WHEN :borderlowtime IS NULL ...> OR :borderlowid IS NULL THEN Builds.rowid ...> ELSE -Builds.rowid ...> END DESC ...> LIMIT :nr) ...> ORDER BY stoptime, rowid ASC; 1|0|0|SCAN TABLE Builds 1|1|1|SEARCH TABLE Evaluations USING INTEGER PRIMARY KEY (rowid=3D?) 1|2|2|SEARCH TABLE Specifications USING COVERING INDEX sqlite_autoindex_Spe= cifications_1 (name=3D?) 1|0|0|USE TEMP B-TREE FOR ORDER BY 0|0|0|SCAN SUBQUERY 1 0|0|0|USE TEMP B-TREE FOR ORDER BY --8<---------------cut here---------------end--------------->8--- I don=E2=80=99t really know what additional index to create (and I=E2=80=99= d rather let SQLite do it for me, if it were possible). Thoughts? Thanks, Ludo=E2=80=99. =C2=B9 https://git.savannah.gnu.org/cgit/guix/guix-cuirass.git/tree/src/cui= rass/database.scm#n550