Subject: bug#43791: closed (Re: bug#43791: cuirass: Search queries are too slow.)

From: [email-masked] (GNU bug Tracking System)
Date: Mon, 05 Oct 2020 13:03:02 +0000

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 [email-masked].

-- 
43791: http://debbugs.gnu.org/cgi/bugreport.cgi?bug=43791
GNU Bug Tracking System
Contact [email-masked] with problems

Subject: Re: bug#43791: cuirass: Search queries are too slow.
From: Mathieu Othacehe <[email-masked]>
To: [email-masked]
Date: Mon, 05 Oct 2020 15:02:27 +0200
List-Id: <debbugs-submit.debbugs.gnu.org>

> 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

Subject: cuirass: Search queries are too slow.
From: Mathieu Othacehe <[email-masked]>
To: [email-masked]
Date: Sun, 04 Oct 2020 13:17:31 +0200
List-Id: <debbugs-submit.debbugs.gnu.org>

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