Hello all, Le lun. 12 nov. 2018 à 19:51, Björn Höfling < bjoern.hoefl...@bjoernhoefling.de> a écrit :
> Hi Ludo, > > On Sun, 11 Nov 2018 18:06:00 +0100 > l...@gnu.org (Ludovic Courtès) wrote: > > > Indeed, that solves the problem for this simple example, thanks! > > > > Now, if I go back to the big query that /api/latestbuilds makes¹, the > > result is still pretty bad: > > > > --8<---------------cut here---------------start------------->8--- > > sqlite> EXPLAIN QUERY PLAN SELECT * FROM ( > > ...> SELECT Builds.derivation, 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 (:id IS NULL OR (:id = Builds.rowid)) > > ...> AND (:derivation IS NULL OR (:derivation = Builds.derivation)) > > ...> AND (:jobset IS NULL OR (:jobset = Specifications.name)) > > ...> AND (:job IS NULL OR (:job = Builds.job_name)) > > ...> AND (:system IS NULL OR (:system = Builds.system)) > > ...> AND (:evaluation IS NULL OR (:evaluation = Builds.evaluation)) > > ...> AND (:status IS NULL OR (:status = 'done' AND Builds.status > > >= 0) ...> OR (:status = 'pending' AND > > >Builds.status < 0) > > ...> OR (:status = 'succeeded' AND > > Builds.status = 0) ...> OR (:status = 'failed' > > AND Builds.status > 0)) ...> AND (:borderlowtime IS NULL > > OR :borderlowid IS NULL ...> OR ((:borderlowtime, :borderlowid) < > > (Builds.stoptime, Builds.rowid))) ...> AND (:borderhightime IS NULL > > OR :borderhighid IS NULL ...> OR ((:borderhightime, :borderhighid) > > > (Builds.stoptime, Builds.rowid))) ...> ORDER BY > > ...> CASE WHEN :borderlowtime IS NULL > > ...> OR :borderlowid IS NULL THEN Builds.stoptime > > ...> ELSE -Builds.stoptime > > ...> END DESC, > > ...> CASE WHEN :borderlowtime IS NULL > > ...> OR :borderlowid IS NULL THEN Builds.rowid > > ...> ELSE -Builds.rowid > > ...> END DESC > > ...> LIMIT :nr) > > ...> ORDER BY stoptime, rowid ASC; > > 1|0|0|SCAN TABLE Builds > > 1|1|1|SEARCH TABLE Evaluations USING INTEGER PRIMARY KEY (rowid=?) > > 1|2|2|SEARCH TABLE Specifications USING COVERING INDEX > > sqlite_autoindex_Specifications_1 (name=?) 1|0|0|USE TEMP B-TREE FOR > > ORDER BY 0|0|0|SCAN SUBQUERY 1 > > 0|0|0|USE TEMP B-TREE FOR ORDER BY > > --8<---------------cut here---------------end--------------->8--- > > > > I don’t really know what additional index to create (and I’d rather > > let SQLite do it for me, if it were possible). > > I don't know if there is any automated process to assist you. I have > the feeling that query optimization is more an art than science. > > Hm. This code smells ... It looks too complicated. > Agreed. > > I don't know if this brings you further concerning performance, here > are some thoughts: > > One problematic part is this construct: > > :variable IS NULL OR :variable=my_column) > > Here is a very simple example: > > sqlite> CREATE TABLE tst ( > ...> id INTEGER PRIMARY KEY AUTOINCREMENT, > ...> name TEXT NOT NULL, > ...> age INTEGER NOT NULL); > sqlite> CREATE INDEX tst_name_age_idx ON tst(name, age); > > sqlite> EXPLAIN QUERY PLAN > ...> SELECT * FROM tst WHERE (23=23 OR id=:id); > 0|0|0|SCAN TABLE tst > > sqlite> EXPLAIN QUERY PLAN > ...> SELECT * FROM tst WHERE id=:id; > 0|0|0|SEARCH TABLE tst USING INTEGER PRIMARY KEY (rowid=?) > > sqlite> EXPLAIN QUERY PLAN > ...> SELECT * FROM tst WHERE name=:name AND age < 42; > 0|0|0|SEARCH TABLE tst USING COVERING INDEX tst_name_age_idx (name=? AND > age<?) > > So, even when we have a constant part(23=23) 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. In the other examples, it is > using the index. > > Even this OR-clause with two variables looks better: > > SELECT * FROM tst WHERE (id=:id1 OR id=:id2); > 0|0|0|SEARCH TABLE tst USING INTEGER PRIMARY KEY (rowid=?) > 0|0|0|EXECUTE LIST SUBQUERY 1 > > I double-checked with Postgresql and it is also performing a full table > scan in the "boolean-constant OR :id=id" case. I could not find any > references on the net about it. > > 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 > > (string-append sql-prefix > (unless (empty? derivation) "AND :derivation=Builds.derivation") > (unless (empty? jobset) "AND :jobset=Builds.jobset) > ...) > ;;; Should be more some kind of folding, because of the "AND" > > ;;; Parameter-filling needs to be considered too > > > Two more things I noticed that are not directly performance oriented: > > We are directly relying on the rowid here, there is no explicit > id-column. > > This could lead to unpredicted results and reorderings (6th Quirk in > document): > > https://www.sqlite.org/rowidtable.html > > We should add a column: > > id INTEGER PRIMARY KEY AUTOINCREMENT > > Problem is that this concept of AUTOINCREMENT does only work for > Primary Keys in Sqlite. So we need to degrade "derivation" to a > secondary key, i.e. make it non-null and unique: > > derivation TEXT NOT NULL UNIQUE, > > Is there anything speaking against that? > > > Lastly, the query has a limit and an order-by. The question is: Will > the result be first ordered and then the limit taken? The answer (I know > only for Postgresql and MySql, but I think it is the same for Sqlite, > I haven't found any reference): The order is always executed first, but > it has to be stable. In this case it is, because we order by > Builds.rowid, which is a key. Did this happen intentionally or just by > chance? Should we better add a note about that to the SQL code? > > Björn > What Björn said. I might be wrong but if :derivation :jobset :job :system and :evalutation are exculsive ie. if one is set the other are null. They could all inherit from a "Buildable" class in Object-Oriented terms. Then you'd rather use a Generic Foreign Key pattern where you have two columns 'object_type' and 'object_id' where 'object_type' is one of the "object" type that is buildable and 'object_id' is the identifier of the row in the table named by 'object_type'. Otherwise you can try narrow the search to a given 'stoptime' or 'starttime' slice and index those columns. Something like: SELECT Builds.derivation, 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 ... INNER JOIN ... WHERE Builds.stoptime > yesterday AND Builds.stoptime < now(); HTH
