GNU bug report logs - #43791
cuirass: Search queries are too slow.

Previous Next

Full log

View this message in rfc822 format

From: help-debbugs <at> gnu.org (GNU bug Tracking System)
To: Mathieu Othacehe <othacehe <at> gnu.org>
Subject: bug#43791: closed (Re: bug#43791: cuirass: Search queries are too
 slow.)
Date: Mon, 05 Oct 2020 13:03:02 +0000

[Message part 1 (text/plain, inline)]

Your bug report

#43791: cuirass: Search queries are too slow.

which was filed against the guix package, has been closed.

The explanation is attached below, along with your original report.
If you require more details, please reply to 43791 <at> debbugs.gnu.org.

-- 
43791: http://debbugs.gnu.org/cgi/bugreport.cgi?bug=43791
GNU Bug Tracking System
Contact help-debbugs <at> gnu.org with problems

[Message part 2 (message/rfc822, inline)]

From: Mathieu Othacehe <othacehe <at> gnu.org>
To: 43791-done <at> debbugs.gnu.org
Subject: Re: bug#43791: cuirass: Search queries are too slow.
Date: Mon, 05 Oct 2020 15:02:27 +0200

> However, searching for 'hurd-barebones.qcow2%' also skips the index.

That's because the index was not created in case insensitive mode.

I dropped support for searching with "^" and "$" characters as this is
not compatible with using an index and hence way too slow.

The new search behaviour is to remove any "%" that could be added by the
user, and add one at the end of the query. It should be enough for the
modest search needs in Cuirass and reasonable in term of performance
(< 10 ms).

Anyway, fixed with: 930c2f315c6a9768d31a80d35d2a2550b588deaa.

Thanks,

Mathieu

[Message part 3 (message/rfc822, inline)]

From: Mathieu Othacehe <othacehe <at> gnu.org>
To: bug-guix <at> gnu.org
Subject: cuirass: Search queries are too slow.
Date: Sun, 04 Oct 2020 13:17:31 +0200

Hello,

Search queries can take a long time to complete.

This query took 658.67 seconds to complete:

--8<---------------cut here---------------start------------->8---
SELECT * FROM ( SELECT 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 = Evaluations.id INNER JOIN Specifications ON Evaluations.specification = Specifications.name WHERE (Builds.nix_name LIKE '%hurd-barebones.qcow2%') AND (0 IS NULL OR (Builds.status = 0)) AND ('guix-master' IS NULL OR (Specifications.name = 'guix-master')) AND ('x86_64-linux' IS NULL OR (Builds.system = 'x86_64-linux')) AND (NULL IS NULL OR (NULL < Builds.rowid)) AND (NULL IS NULL OR (NULL > Builds.rowid)) ORDER BY CASE WHEN NULL IS NULL THEN Builds.rowid ELSE -Builds.rowid END DESC LIMIT 1) ORDER BY rowid DESC 658.67
--8<---------------cut here---------------end--------------->8---

and this one:

--8<---------------cut here---------------start------------->8---
SELECT MIN(Builds.rowid) FROM Builds INNER JOIN Evaluations ON Builds.evaluation = Evaluations.id INNER JOIN Specifications ON Evaluations.specification = Specifications.name WHERE (Builds.nix_name LIKE '%ganeti%') AND (NULL IS NULL OR (Builds.status = NULL)) AND (NULL IS NULL OR (Specifications.name = NULL)) AND (NULL IS NULL OR (Builds.system = NULL)) 146.70
--8<---------------cut here---------------end--------------->8---

took 146.70 seconds.

The naive solution of adding an index on nix_name column does not work
as it is still ignored for some reason.

According to https://www.sqlite.org/optoverview.html, starting the LIKE
search pattern with '%' prevent the index use.

However, searching for 'hurd-barebones.qcow2%' also skips the index.

Thanks,

Mathieu

Previous Next

GNU bug tracking system
Copyright (C) 1999 Darren O. Benham, 1997,2003 nCipher Corporation Ltd, 1994-97 Ian Jackson.