From mboxrd@z Thu Jan 1 00:00:00 1970 From: =?UTF-8?B?QmrDtnJuIEjDtmZsaW5n?= Subject: Re: [Cuirass] Missing database indexes? Date: Mon, 12 Nov 2018 19:50:44 +0100 Message-ID: <20181112195044.6d64f51c@alma-ubu> References: <87va54yh0c.fsf@gnu.org> <20181110211128.6dc522da@alma-ubu> <87k1ljr1c7.fsf@gnu.org> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; boundary="Sig_/aMCryo.nGMGGn=VNhUkrAep"; protocol="application/pgp-signature" Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:43949) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1gMHIb-00048i-Ka for guix-devel@gnu.org; Mon, 12 Nov 2018 13:51:02 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1gMHIX-00064o-Fe for guix-devel@gnu.org; Mon, 12 Nov 2018 13:51:01 -0500 In-Reply-To: <87k1ljr1c7.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_/aMCryo.nGMGGn=VNhUkrAep Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi Ludo, On Sun, 11 Nov 2018 18:06:00 +0100 ludo@gnu.org (Ludovic Court=C3=A8s) wrote: > Indeed, that solves the problem for this simple example, thanks! >=20 > Now, if I go back to the big query that /api/latestbuilds makes=C2=B9, the > result is still pretty bad: >=20 > --8<---------------cut here---------------start------------->8--- > sqlite> EXPLAIN QUERY PLAN SELECT * FROM ( > ...> SELECT Builds.derivation, Builds.rowid, Builds.timestamp, > Builds.starttime, ...> 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 > Specifications.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.rowid))) ...> AND (:borderhightime IS NULL > OR :borderhighid IS NULL ...> OR ((:borderhightime, :borderhighid) > > (Builds.stoptime, Builds.rowid))) ...> 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; =20 > 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_Specifications_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--- >=20 > I don=E2=80=99t really know what additional index to create (and I=E2=80= =99d rather > let SQLite do it for me, if it were possible). I don't know if there is any automated process to assist you. I have the feeling that query optimization is more an art than science. Hm. This code smells ... It looks too complicated. I don't know if this brings you further concerning performance, here are some thoughts: One problematic part is this construct: :variable IS NULL OR :variable=3Dmy_column) Here is a very simple example: sqlite> CREATE TABLE tst ( ...> id INTEGER PRIMARY KEY AUTOINCREMENT, ...> name TEXT NOT NULL, ...> age INTEGER NOT NULL); sqlite> CREATE INDEX tst_name_age_idx ON tst(name, age); sqlite> EXPLAIN QUERY PLAN ...> SELECT * FROM tst WHERE (23=3D23 OR id=3D:id); 0|0|0|SCAN TABLE tst sqlite> EXPLAIN QUERY PLAN ...> SELECT * FROM tst WHERE id=3D:id; 0|0|0|SEARCH TABLE tst USING INTEGER PRIMARY KEY (rowid=3D?) sqlite> EXPLAIN QUERY PLAN ...> SELECT * FROM tst WHERE name=3D:name AND age < 42; 0|0|0|SEARCH TABLE tst USING COVERING INDEX tst_name_age_idx (name=3D? AND = age