[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 = 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
pgpmD4YZKpJrH.pgp
Description: OpenPGP digital signature
- [Cuirass] Missing database indexes?, Ludovic Courtès, 2018/11/10
- Re: [Cuirass] Missing database indexes?, Danny Milosavljevic, 2018/11/12
- Re: [Cuirass] Missing database indexes?, Danny Milosavljevic, 2018/11/12
- Re: [Cuirass] Missing database indexes?, Ludovic Courtès, 2018/11/14
- Re: [Cuirass] Missing database indexes?, Danny Milosavljevic, 2018/11/19
Re: [Cuirass] Missing database indexes?, swedebugia, 2018/11/19