On Tue, 13 Nov 2018 00:27:15 +0100
Danny Milosavljevic <dannym@scratchpost.org> wrote:

> Hi Björn,
> 
> On Mon, 12 Nov 2018 19:50:44 +0100
> Björn Höfling <bjoern.hoefling@bjoernhoefling.de> wrote:
> 
> > Hm. This code smells ... It looks too complicated.  
> 
> I was trying to cut down the number of prepared statements in use and
> prevent a combinatorial explosion while keeping the kinds of queries
> we can do open.
> 
> Either the value of a parameter is specified, in which case the
> associated column is filtered for it; or it isn't, then it's not.
> 
> > 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.   
> 
> Sounds like an easy fix in sqlite.  Could you report this upstream?

I thought about these things again, with the help of stackoverflow and
this was really silly: If the left part of the clause

(:jobset IS NULL OR (:jobset = Specifications.name))

is constant, null, then the whole clause is true. And if that is
the case for all, then everything is true, of cause resulting in a full
table scan. That is it I think what the query optimizer is thinking of
here. I guess it will be better if there are some non-null and some
null clauses mixed.

I have the full DB here now, I will look into it the next days.


> > 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.  
> 
> Something easy to try would be to use the row values in sqlite
> instead.
> 
> See also https://www.sqlite.org/rowvalue.html
> See also
> https://lists.gnu.org/archive/html/guix-devel/2018-07/msg00101.html

That would be another idea.

 
> > 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  
> 
> (1) Combinatorial explosion of the number of prepared statements
> (2) Slow to parse all those SQL statements
> 
> But if we don't use all combinations in practise then it's not so bad
> and we could generate those statements after all.  It's still a
> workaround if we have to do that.

You are right. That sounds reasonable. It just looks ugly to read.

Björn