[Top][All Lists]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Cuirass] Missing database indexes?

From: Björn Höfling
Subject: Re: [Cuirass] Missing database indexes?
Date: Fri, 16 Nov 2018 23:31:20 +0100

On Tue, 13 Nov 2018 00:27:15 +0100
Danny Milosavljevic <address@hidden> wrote:

> Hi Björn,
> On Mon, 12 Nov 2018 19:50:44 +0100
> Björn Höfling <address@hidden> 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 =

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
> See also

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.


Attachment: pgpmD4YZKpJrH.pgp
Description: OpenPGP digital signature

reply via email to

[Prev in Thread] Current Thread [Next in Thread]