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

Previous Next

To add a comment to this bug, you must first unarchive it, by sending
a message to control AT debbugs.gnu.org, with unarchive 43791 in the body.
You can then email your comments to 43791 AT debbugs.gnu.org in the normal way.

Toggle the display of automated, internal messages from the tracker.

Message #5 received at submit <at> debbugs.gnu.org (full text, mbox):

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

Message #10 received at 43791-done <at> debbugs.gnu.org (full text, mbox):

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

Previous Next

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