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: Wed, 14 Nov 2018 12:11:28 +0100 Message-ID: <87wopfj4m7.fsf@gnu.org> References: <87va54yh0c.fsf@gnu.org> <20181110211128.6dc522da@alma-ubu> <87k1ljr1c7.fsf@gnu.org> <20181113003140.1db9c770@scratchpost.org> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:36488) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1gMt5A-00085C-S9 for guix-devel@gnu.org; Wed, 14 Nov 2018 06:11:42 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1gMt54-0006Vh-9z for guix-devel@gnu.org; Wed, 14 Nov 2018 06:11:36 -0500 In-Reply-To: <20181113003140.1db9c770@scratchpost.org> (Danny Milosavljevic's message of "Tue, 13 Nov 2018 00:31:40 +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: Danny Milosavljevic Cc: guix-devel@gnu.org, clement@lassieur.org --=-=-= Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Hi, Danny Milosavljevic skribis: > On Sun, 11 Nov 2018 18:06:00 +0100 > ludo@gnu.org (Ludovic Court=C3=A8s) wrote: > >> 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). > > Not exactly what you mean but there's this: > > https://www.sqlite.org/lang_analyze.html > > It does statistical analysis of queries that ran and will optimize for > that case on subsequent connections. That=E2=80=99s close to what I was hoping for. We could do =E2=80=9CPRAGMA= optimize=E2=80=9D before closing the database session as they suggest: --=-=-= Content-Type: text/x-patch Content-Disposition: inline diff --git a/src/cuirass/database.scm b/src/cuirass/database.scm index 8b83c18..fb037d1 100644 --- a/src/cuirass/database.scm +++ b/src/cuirass/database.scm @@ -403,7 +403,9 @@ a critical section that allows database operations to be serialized." ;; be costly and may defeat statement caching. (parameterize ((%db-channel (make-critical-section db))) body ...) - (db-close db)))) + (begin + (sqlite-exec db "PRAGMA optimize;") + (db-close db))))) (define* (read-quoted-string #:optional (port (current-input-port))) "Read all of the characters out of PORT and return them as a SQL quoted --=-=-= Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable =E2=80=A6 though I think we never really close it properly because Cuirass = runs =E2=80=9Cforever.=E2=80=9D I tried doing this in a Guile session on berlin: --8<---------------cut here---------------start------------->8--- scheme@(guile-user)> ,use(cuirass database) scheme@(guile-user)> (%package-database "/var/lib/cuirass/cuirass.db") $6 =3D "/gnu/store/g1q2lv75a2fibii4y52fndz5zpbmyl12-cuirass-0.0.1-21.0b40dc= a/var/lib/cuirass/cuirass.db" scheme@(guile-user)> (with-database (db-get-builds `((nr . 200)(order . fi= nish-time)(status . done))) (with-db-critical-section db (sqlite-exec db "P= RAGMA optimize;"))) $7 =3D () scheme@(guile-user)> (with-database (db-get-builds `((nr . 200)(order . fi= nish-time)(status . done))) (with-db-critical-section db (sqlite-exec db "P= RAGMA optimize;"))) $8 =3D () scheme@(guile-user)> ,time (with-database (db-get-builds `((nr . 200)(order= . finish-time)(status . done))) (with-db-critical-section db (sqlite-exec = db "PRAGMA optimize;"))) $9 =3D () ;; 13.215291s real time, 13.229189s run time. 0.016093s spent in GC. scheme@(guile-user)> ,time (with-database (db-get-builds `((nr . 200)(order= . finish-time)(status . done))) (with-db-critical-section db (sqlite-exec = db "PRAGMA optimize;"))) $10 =3D () ;; 13.204621s real time, 13.230655s run time. 0.029333s spent in GC. --8<---------------cut here---------------end--------------->8--- It doesn=E2=80=99t seem to help much, perhaps because the query is too comp= lex? Thoughts? Ludo=E2=80=99. --=-=-=--