From mboxrd@z Thu Jan 1 00:00:00 1970 From: =?UTF-8?B?QmrDtnJuIEjDtmZsaW5n?= Subject: Re: [Cuirass] Missing database indexes? Date: Fri, 16 Nov 2018 23:31:20 +0100 Message-ID: <20181116233120.1d449cac@alma-ubu> References: <87va54yh0c.fsf@gnu.org> <20181110211128.6dc522da@alma-ubu> <87k1ljr1c7.fsf@gnu.org> <20181112195044.6d64f51c@alma-ubu> <20181113002715.33a00ea1@scratchpost.org> Mime-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha1; boundary="Sig_/8JIfx9/u1DqGq0MLuBSuJe."; protocol="application/pgp-signature" Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:45205) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1gNmeE-0006yN-9Y for guix-devel@gnu.org; Fri, 16 Nov 2018 17:31:35 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1gNme9-0005ZQ-PJ for guix-devel@gnu.org; Fri, 16 Nov 2018 17:31:34 -0500 In-Reply-To: <20181113002715.33a00ea1@scratchpost.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: Danny Milosavljevic Cc: guix-devel@gnu.org, clement@lassieur.org --Sig_/8JIfx9/u1DqGq0MLuBSuJe. Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable On Tue, 13 Nov 2018 00:27:15 +0100 Danny Milosavljevic wrote: > Hi Bj=C3=B6rn, >=20 > On Mon, 12 Nov 2018 19:50:44 +0100 > Bj=C3=B6rn H=C3=B6fling wrote: >=20 > > Hm. This code smells ... It looks too complicated. =20 >=20 > I was trying to cut down the number of prepared statements in use and > prevent a combinatorial explosion while keeping the kinds of queries > we can do open. >=20 > Either the value of a parameter is specified, in which case the > associated column is filtered for it; or it isn't, then it's not. >=20 > > So, even when we have a constant part(23=3D23) in the OR clause, this > > leads to a full table scan. I think the optimizer cannot detect the > > fact that it is a constant boolean value. =20 >=20 > Sounds like an easy fix in sqlite. Could you report this upstream? I thought about these things again, with the help of stackoverflow and this was really silly: If the left part of the clause (:jobset IS NULL OR (:jobset =3D Specifications.name)) is constant, null, then the whole clause is true. And if that is the case for all, then everything is true, of cause resulting in a full table scan. That is it I think what the query optimizer is thinking of here. I guess it will be better if there are some non-null and some null clauses mixed. I have the full DB here now, I will look into it the next days. > > I double-checked with Postgresql and it is also performing a full > > table scan in the "boolean-constant OR :id=3Did" case. I could not > > find any references on the net about it. =20 >=20 > Something easy to try would be to use the row values in sqlite > instead. >=20 > See also https://www.sqlite.org/rowvalue.html > See also > https://lists.gnu.org/archive/html/guix-devel/2018-07/msg00101.html That would be another idea. =20 > > When this would be Java/JPA I would suggest to dynamically create > > the query. Can we do something in Scheme-DB too? I.e. pseudo-code =20 >=20 > (1) Combinatorial explosion of the number of prepared statements > (2) Slow to parse all those SQL statements >=20 > But if we don't use all combinations in practise then it's not so bad > and we could generate those statements after all. It's still a > workaround if we have to do that. You are right. That sounds reasonable. It just looks ugly to read. Bj=C3=B6rn --Sig_/8JIfx9/u1DqGq0MLuBSuJe. Content-Type: application/pgp-signature Content-Description: OpenPGP digital signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v2 iEYEARECAAYFAlvvRTgACgkQvyhstlk+X/2bpQCfR8nX1fhB6EFSwG4w+FzphRBV yIIAni+5c+A1PnGyk6gBbVEbthU0jeWx =SApI -----END PGP SIGNATURE----- --Sig_/8JIfx9/u1DqGq0MLuBSuJe.--