unofficial mirror of guix-devel@gnu.org 
 help / color / mirror / code / Atom feed
From: "Björn Höfling" <bjoern.hoefling@bjoernhoefling.de>
To: Danny Milosavljevic <dannym@scratchpost.org>
Cc: guix-devel@gnu.org, clement@lassieur.org
Subject: Re: [Cuirass] Missing database indexes?
Date: Fri, 16 Nov 2018 23:31:20 +0100	[thread overview]
Message-ID: <20181116233120.1d449cac@alma-ubu> (raw)
In-Reply-To: <20181113002715.33a00ea1@scratchpost.org>

[-- Attachment #1: Type: text/plain, Size: 2379 bytes --]

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

[-- Attachment #2: OpenPGP digital signature --]
[-- Type: application/pgp-signature, Size: 181 bytes --]

  parent reply	other threads:[~2018-11-16 22:31 UTC|newest]

Thread overview: 16+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2018-11-10 17:33 [Cuirass] Missing database indexes? Ludovic Courtès
2018-11-10 20:11 ` Björn Höfling
2018-11-11 17:06   ` Ludovic Courtès
2018-11-12 18:50     ` Björn Höfling
2018-11-12 19:42       ` Amirouche Boubekki
2018-11-12 23:27       ` Danny Milosavljevic
2018-11-14 11:14         ` Ludovic Courtès
2018-11-16 22:31         ` Björn Höfling [this message]
2018-11-13  8:10       ` Clément Lassieur
2018-11-16 22:42         ` Björn Höfling
2018-11-12 23:31     ` Danny Milosavljevic
2018-11-13  0:04       ` Danny Milosavljevic
2018-11-14 11:11       ` Ludovic Courtès
2018-11-19 10:44         ` Danny Milosavljevic
2018-12-19 22:45           ` Amirouche Boubekki
2018-11-19  9:47 ` swedebugia

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

  List information: https://guix.gnu.org/

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to=20181116233120.1d449cac@alma-ubu \
    --to=bjoern.hoefling@bjoernhoefling.de \
    --cc=clement@lassieur.org \
    --cc=dannym@scratchpost.org \
    --cc=guix-devel@gnu.org \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line before the message body.
Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/guix.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).