Re: [sqlite] Regression with sqlite 3.8.7

[Luigi Iemma]

thanks for answers

Luigi.

2014-10-25 15:46 GMT+02:00 Richard Hipp :

> On Thu, Oct 23, 2014 at 11:55 AM, Luigi Iemma 
> wrote:
>
> > Hi,
> >
> > SELECT TdoIdoc,RdoCart,RdoQuat
> >  FROM Tesdoc
> >  INNER JOIN Rigdoc ON RdoIdoc BETWEEN TdoIdoc*1000 AND
> > TdoIdoc*1000+999
> >  WHERE TdoTipo=60 AND TdoAnno BETWEEN 2014 AND 2014
> >  GROUP BY TdoIdoc
> >
> > When I run this query on 3.8.5 it takes 0.126 seconds,
> > when I run this query on 3.8.7 it takes 17.37 seconds
> >
>
> Version 3.8.7 (also version 3.8.6) is using a different query plan.
>
> Actually, 3.8.6 and 3.8.7 are making the better choice, based on the
> information at hand.  The query planner just doesn't have sufficient
> information to make a good choice in this case.  As Simon pointed out, you
> can run ANALYZE to give the query planner more information.  Or you can
> give it hints.  One possible hint is to add a "likelihood() function around
> the TdoTipo=60 constraint:
>
> SELECT TdoIdoc,RdoCart,RdoQuat
>  FROM Tesdoc, Rigdoc
> WHERE RdoIdoc BETWEEN TdoIdoc*1000 AND TdoIdoc*1000+999
>   AND likelihood(TdoTipo=60, 0.01)  <<< HERE
>   AND TdoAnno BETWEEN 2014 AND 2014
>  GROUP BY TdoIdoc;
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regression with sqlite 3.8.7

[Richard Hipp]

On Thu, Oct 23, 2014 at 11:55 AM, Luigi Iemma  wrote:

> Hi,
>
> SELECT TdoIdoc,RdoCart,RdoQuat
>  FROM Tesdoc
>  INNER JOIN Rigdoc ON RdoIdoc BETWEEN TdoIdoc*1000 AND
> TdoIdoc*1000+999
>  WHERE TdoTipo=60 AND TdoAnno BETWEEN 2014 AND 2014
>  GROUP BY TdoIdoc
>
> When I run this query on 3.8.5 it takes 0.126 seconds,
> when I run this query on 3.8.7 it takes 17.37 seconds
>

Version 3.8.7 (also version 3.8.6) is using a different query plan.

Actually, 3.8.6 and 3.8.7 are making the better choice, based on the
information at hand.  The query planner just doesn't have sufficient
information to make a good choice in this case.  As Simon pointed out, you
can run ANALYZE to give the query planner more information.  Or you can
give it hints.  One possible hint is to add a "likelihood() function around
the TdoTipo=60 constraint:

SELECT TdoIdoc,RdoCart,RdoQuat
 FROM Tesdoc, Rigdoc
WHERE RdoIdoc BETWEEN TdoIdoc*1000 AND TdoIdoc*1000+999
  AND likelihood(TdoTipo=60, 0.01)  <<< HERE
  AND TdoAnno BETWEEN 2014 AND 2014
 GROUP BY TdoIdoc;



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regression with sqlite 3.8.7

[Luigi Iemma]

Hi,

this is the result of .fullschema *after* i have run ANALYZE

CREATE TABLE "rigdoc"(RdoIdoc INTEGER NOT NULL,RdoCcau TEXT NOT NULL
COLLATE NOCASE,RdoCdep TEXT NOT NULL COLLATE NOCASE,RdoCart TEXT NOT
NULL COLLATE NOCASE,RdoDesc TEXT NOT NULL COLLATE NOCASE,RdoCumi TEXT
NOT NULL COLLATE NOCASE,RdoCiva TEXT NOT NULL COLLATE NOCASE,RdoCven
TEXT NOT NULL COLLATE NOCASE,RdoQu00 REAL NOT NULL,RdoQu01 REAL NOT
NULL,RdoQu02 REAL NOT NULL,RdoQu03 REAL NOT NULL,RdoQu04 REAL NOT
NULL,RdoQuat REAL NOT NULL,RdoPrez REAL NOT NULL,RdoLord REAL NOT
NULL,RdoSc00 REAL NOT NULL,RdoSc01 REAL NOT NULL,RdoSc02 REAL NOT
NULL,RdoNett REAL NOT NULL,RdoTpro INTEGER NOT NULL,RdoProv REAL NOT
NULL,RdoIdri INTEGER NOT NULL,RdoIdor INTEGER NOT NULL,RdoDtsc DATEINT
NOT NULL,RdoCcad TEXT NOT NULL COLLATE NOCASE,RdoCded TEXT NOT NULL
COLLATE NOCASE,RdoCard TEXT NOT NULL COLLATE NOCASE,RdoQuad REAL NOT
NULL,RdoVarb INTEGER NOT NULL,RdoNupa TEXT NOT NULL COLLATE
NOCASE,RdoTipo INTEGER NOT NULL,RdoTe00 INTEGER NOT NULL,RdoTe01
INTEGER NOT NULL,RdoTe02 INTEGER NOT NULL,RdoTe03 INTEGER NOT
NULL,RdoTe04 INTEGER NOT NULL,RdoTe05 INTEGER NOT NULL,RdoTe06 INTEGER
NOT NULL,RdoTe07 INTEGER NOT NULL,RdoTe08 INTEGER NOT NULL,RdoTe09
INTEGER NOT NULL,RdoNote TEXT NOT NULL COLLATE NOCASE,PRIMARY
KEY(RdoIdoc));
CREATE TABLE "tesdoc"(TdoIdoc INTEGER NOT NULL,TdoTipo INTEGER NOT
NULL,TdoAnno INTEGER NOT NULL,TdoCatt TEXT NOT NULL COLLATE
NOCASE,TdoCdep TEXT NOT NULL COLLATE NOCASE,TdoSeri TEXT NOT NULL
COLLATE NOCASE,TdoNdoc INTEGER NOT NULL,TdoDt00 DATEINT NOT
NULL,TdoDt01 DATEINT NOT NULL,TdoDt02 DATEINT NOT NULL,TdoDt03 DATEINT
NOT NULL,TdoDt04 DATEINT NOT NULL,TdoCleg TEXT NOT NULL COLLATE
NOCASE,TdoCcau TEXT NOT NULL COLLATE NOCASE,TdoTcau INTEGER NOT
NULL,TdoTsog TEXT NOT NULL COLLATE NOCASE,TdoCsog TEXT NOT NULL
COLLATE NOCASE,TdoCval TEXT NOT NULL COLLATE NOCASE,TdoCamb REAL NOT
NULL,TdoDtca DATEINT NOT NULL,TdoCpag TEXT NOT NULL COLLATE
NOCASE,TdoDtip DATEINT NOT NULL,TdoCban TEXT NOT NULL COLLATE
NOCASE,TdoCage TEXT NOT NULL COLLATE NOCASE,TdoScch REAL NOT
NULL,TdoImsc REAL NOT NULL,TdoTpro INTEGER NOT NULL,TdoProv REAL NOT
NULL,TdoNcol REAL NOT NULL,TdoPesl REAL NOT NULL,TdoPesn REAL NOT
NULL,TdoDtit DATEINT NOT NULL,TdoNcop INTEGER NOT NULL,TdoAnpa INTEGER
NOT NULL,TdoNupa INTEGER NOT NULL,TdoCocf TEXT NOT NULL COLLATE
NOCASE,TdoNrec INTEGER NOT NULL,TdoNpri INTEGER NOT NULL,TdoCo00 TEXT
NOT NULL COLLATE NOCASE,TdoCo01 TEXT NOT NULL COLLATE NOCASE,TdoCo02
TEXT NOT NULL COLLATE NOCASE,TdoCo03 TEXT NOT NULL COLLATE
NOCASE,TdoCo04 TEXT NOT NULL COLLATE NOCASE,TdoCo05 TEXT NOT NULL
COLLATE NOCASE,TdoCo06 TEXT NOT NULL COLLATE NOCASE,TdoCo07 TEXT NOT
NULL COLLATE NOCASE,TdoCo08 TEXT NOT NULL COLLATE NOCASE,TdoCo09 TEXT
NOT NULL COLLATE NOCASE,TdoCo10 TEXT NOT NULL COLLATE NOCASE,TdoCo11
TEXT NOT NULL COLLATE NOCASE,TdoCo12 TEXT NOT NULL COLLATE
NOCASE,TdoCo13 TEXT NOT NULL COLLATE NOCASE,TdoCo14 TEXT NOT NULL
COLLATE NOCASE,TdoCo15 TEXT NOT NULL COLLATE NOCASE,TdoCo16 TEXT NOT
NULL COLLATE NOCASE,TdoCo17 TEXT NOT NULL COLLATE NOCASE,TdoCo18 TEXT
NOT NULL COLLATE NOCASE,TdoCo19 TEXT NOT NULL COLLATE NOCASE,TdoSp00
REAL NOT NULL,TdoSp01 REAL NOT NULL,TdoSp02 REAL NOT NULL,TdoSp03 REAL
NOT NULL,TdoSp04 REAL NOT NULL,TdoTo00 REAL NOT NULL,TdoTo01 REAL NOT
NULL,TdoTo02 REAL NOT NULL,TdoTo03 REAL NOT NULL,TdoTo04 REAL NOT
NULL,TdoTe00 INTEGER NOT NULL,TdoTe01 INTEGER NOT NULL,TdoTe02 INTEGER
NOT NULL,TdoTe03 INTEGER NOT NULL,TdoTe04 INTEGER NOT NULL,TdoTe05
INTEGER NOT NULL,TdoTe06 INTEGER NOT NULL,TdoTe07 INTEGER NOT
NULL,TdoTe08 INTEGER NOT NULL,TdoTe09 INTEGER NOT NULL,TdoRifi TEXT
NOT NULL COLLATE NOCASE,TdoRife TEXT NOT NULL COLLATE NOCASE,TdoNote
TEXT NOT NULL COLLATE NOCASE,TdoIrif INTEGER NOT NULL,TdoFdoc TEXT NOT
NULL COLLATE NOCASE,PRIMARY KEY(TdoIdoc));
ANALYZE sqlite_master;
ANALYZE sqlite_master;
INSERT INTO sqlite_stat1 VALUES('rigdoc',NULL,'39120');
INSERT INTO sqlite_stat1 VALUES('tesdoc',NULL,'11218');
ANALYZE sqlite_master;

Luigi.


2014-10-24 14:48 GMT+02:00 Richard Hipp :

> On Fri, Oct 24, 2014 at 7:45 AM, Luigi Iemma  wrote:
>
> > Hi,
> >
> > if I do an ANALYZE then works, bat because it changes if I use left
> instead
> > of inner
> >
> > this is the result of .fullschema
> >
>
> Can you send us the ".fullschema" *after* you have run ANALYZE?
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regression with sqlite 3.8.7

[Richard Hipp]

On Fri, Oct 24, 2014 at 7:45 AM, Luigi Iemma  wrote:

> Hi,
>
> if I do an ANALYZE then works, bat because it changes if I use left instead
> of inner
>
> this is the result of .fullschema
>

Can you send us the ".fullschema" *after* you have run ANALYZE?

-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regression with sqlite 3.8.7

[Luigi Iemma]

Hi,

if I do an ANALYZE then works, bat because it changes if I use left instead
of inner

this is the result of .fullschema

CREATE TABLE "rigdoc"(RdoIdoc INTEGER NOT NULL,RdoCcau TEXT NOT NULL
COLLATE NOCASE,RdoCdep TEXT NOT NULL COLLATE NOCASE,RdoCart TEXT NOT NULL
COLLATE NOCASE,RdoDesc TEXT NOT NULL COLLATE NOCASE,RdoCumi TEXT NOT NULL
COLLATE NOCASE,RdoCiva TEXT NOT NULL COLLATE NOCASE,RdoCven TEXT NOT NULL
COLLATE NOCASE,RdoQu00 REAL NOT NULL,RdoQu01 REAL NOT NULL,RdoQu02 REAL NOT
NULL,RdoQu03 REAL NOT NULL,RdoQu04 REAL NOT NULL,RdoQuat REAL NOT
NULL,RdoPrez REAL NOT NULL,RdoLord REAL NOT NULL,RdoSc00 REAL NOT
NULL,RdoSc01 REAL NOT NULL,RdoSc02 REAL NOT NULL,RdoNett REAL NOT
NULL,RdoTpro INTEGER NOT NULL,RdoProv REAL NOT NULL,RdoIdri INTEGER NOT
NULL,RdoIdor INTEGER NOT NULL,RdoDtsc DATEINT NOT NULL,RdoCcad TEXT NOT
NULL COLLATE NOCASE,RdoCded TEXT NOT NULL COLLATE NOCASE,RdoCard TEXT NOT
NULL COLLATE NOCASE,RdoQuad REAL NOT NULL,RdoVarb INTEGER NOT NULL,RdoNupa
TEXT NOT NULL COLLATE NOCASE,RdoTipo INTEGER NOT NULL,RdoTe00 INTEGER NOT
NULL,RdoTe01 INTEGER NOT NULL,RdoTe02 INTEGER NOT NULL,RdoTe03 INTEGER NOT
NULL,RdoTe04 INTEGER NOT NULL,RdoTe05 INTEGER NOT NULL,RdoTe06 INTEGER NOT
NULL,RdoTe07 INTEGER NOT NULL,RdoTe08 INTEGER NOT NULL,RdoTe09 INTEGER NOT
NULL,RdoNote TEXT NOT NULL COLLATE NOCASE,PRIMARY KEY(RdoIdoc));
CREATE TABLE "tesdoc"(TdoIdoc INTEGER NOT NULL,TdoTipo INTEGER NOT
NULL,TdoAnno INTEGER NOT NULL,TdoCatt TEXT NOT NULL COLLATE NOCASE,TdoCdep
TEXT NOT NULL COLLATE NOCASE,TdoSeri TEXT NOT NULL COLLATE NOCASE,TdoNdoc
INTEGER NOT NULL,TdoDt00 DATEINT NOT NULL,TdoDt01 DATEINT NOT NULL,TdoDt02
DATEINT NOT NULL,TdoDt03 DATEINT NOT NULL,TdoDt04 DATEINT NOT NULL,TdoCleg
TEXT NOT NULL COLLATE NOCASE,TdoCcau TEXT NOT NULL COLLATE NOCASE,TdoTcau
INTEGER NOT NULL,TdoTsog TEXT NOT NULL COLLATE NOCASE,TdoCsog TEXT NOT NULL
COLLATE NOCASE,TdoCval TEXT NOT NULL COLLATE NOCASE,TdoCamb REAL NOT
NULL,TdoDtca DATEINT NOT NULL,TdoCpag TEXT NOT NULL COLLATE NOCASE,TdoDtip
DATEINT NOT NULL,TdoCban TEXT NOT NULL COLLATE NOCASE,TdoCage TEXT NOT NULL
COLLATE NOCASE,TdoScch REAL NOT NULL,TdoImsc REAL NOT NULL,TdoTpro INTEGER
NOT NULL,TdoProv REAL NOT NULL,TdoNcol REAL NOT NULL,TdoPesl REAL NOT
NULL,TdoPesn REAL NOT NULL,TdoDtit DATEINT NOT NULL,TdoNcop INTEGER NOT
NULL,TdoAnpa INTEGER NOT NULL,TdoNupa INTEGER NOT NULL,TdoCocf TEXT NOT
NULL COLLATE NOCASE,TdoNrec INTEGER NOT NULL,TdoNpri INTEGER NOT
NULL,TdoCo00 TEXT NOT NULL COLLATE NOCASE,TdoCo01 TEXT NOT NULL COLLATE
NOCASE,TdoCo02 TEXT NOT NULL COLLATE NOCASE,TdoCo03 TEXT NOT NULL COLLATE
NOCASE,TdoCo04 TEXT NOT NULL COLLATE NOCASE,TdoCo05 TEXT NOT NULL COLLATE
NOCASE,TdoCo06 TEXT NOT NULL COLLATE NOCASE,TdoCo07 TEXT NOT NULL COLLATE
NOCASE,TdoCo08 TEXT NOT NULL COLLATE NOCASE,TdoCo09 TEXT NOT NULL COLLATE
NOCASE,TdoCo10 TEXT NOT NULL COLLATE NOCASE,TdoCo11 TEXT NOT NULL COLLATE
NOCASE,TdoCo12 TEXT NOT NULL COLLATE NOCASE,TdoCo13 TEXT NOT NULL COLLATE
NOCASE,TdoCo14 TEXT NOT NULL COLLATE NOCASE,TdoCo15 TEXT NOT NULL COLLATE
NOCASE,TdoCo16 TEXT NOT NULL COLLATE NOCASE,TdoCo17 TEXT NOT NULL COLLATE
NOCASE,TdoCo18 TEXT NOT NULL COLLATE NOCASE,TdoCo19 TEXT NOT NULL COLLATE
NOCASE,TdoSp00 REAL NOT NULL,TdoSp01 REAL NOT NULL,TdoSp02 REAL NOT
NULL,TdoSp03 REAL NOT NULL,TdoSp04 REAL NOT NULL,TdoTo00 REAL NOT
NULL,TdoTo01 REAL NOT NULL,TdoTo02 REAL NOT NULL,TdoTo03 REAL NOT
NULL,TdoTo04 REAL NOT NULL,TdoTe00 INTEGER NOT NULL,TdoTe01 INTEGER NOT
NULL,TdoTe02 INTEGER NOT NULL,TdoTe03 INTEGER NOT NULL,TdoTe04 INTEGER NOT
NULL,TdoTe05 INTEGER NOT NULL,TdoTe06 INTEGER NOT NULL,TdoTe07 INTEGER NOT
NULL,TdoTe08 INTEGER NOT NULL,TdoTe09 INTEGER NOT NULL,TdoRifi TEXT NOT
NULL COLLATE NOCASE,TdoRife TEXT NOT NULL COLLATE NOCASE,TdoNote TEXT NOT
NULL COLLATE NOCASE,TdoIrif INTEGER NOT NULL,TdoFdoc TEXT NOT NULL COLLATE
NOCASE,PRIMARY KEY(TdoIdoc));
/* No STAT tables available */

Thanks in advance,

Luigi.


2014-10-23 18:23 GMT+02:00 Richard Hipp :

> On Thu, Oct 23, 2014 at 11:55 AM, Luigi Iemma 
> wrote:
>
> > Hi,
> >
> > SELECT TdoIdoc,RdoCart,RdoQuat
> >  FROM Tesdoc
> >  INNER JOIN Rigdoc ON RdoIdoc BETWEEN TdoIdoc*1000 AND
> > TdoIdoc*1000+999
> >  WHERE TdoTipo=60 AND TdoAnno BETWEEN 2014 AND 2014
> >  GROUP BY TdoIdoc
> >
> > When I run this query on 3.8.5 it takes 0.126 seconds,
> > when I run this query on 3.8.7 it takes 17.37 seconds
> >
>
> Please run the ".fullschema" command from the command-line shell and send
> us the output.
>
>
>
> > if I change 'INNER JOIN Rigdoc' with 'LEFT JOIN Rigdoc' it takes 0.063
> > seconds
> >
> > the table Rigdoc have column RdoIdoc as PRIMARY KEY
> >
> >
> > Best regards
> > Luigi.
> > ___
> > sqlite-users mailing list
> > sqlite-users@sqlite.org
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list

Re: [sqlite] Regression with sqlite 3.8.7

[Richard Hipp]

On Thu, Oct 23, 2014 at 11:55 AM, Luigi Iemma  wrote:

> Hi,
>
> SELECT TdoIdoc,RdoCart,RdoQuat
>  FROM Tesdoc
>  INNER JOIN Rigdoc ON RdoIdoc BETWEEN TdoIdoc*1000 AND
> TdoIdoc*1000+999
>  WHERE TdoTipo=60 AND TdoAnno BETWEEN 2014 AND 2014
>  GROUP BY TdoIdoc
>
> When I run this query on 3.8.5 it takes 0.126 seconds,
> when I run this query on 3.8.7 it takes 17.37 seconds
>

Please run the ".fullschema" command from the command-line shell and send
us the output.



> if I change 'INNER JOIN Rigdoc' with 'LEFT JOIN Rigdoc' it takes 0.063
> seconds
>
> the table Rigdoc have column RdoIdoc as PRIMARY KEY
>
>
> Best regards
> Luigi.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Regression with sqlite 3.8.7

[Simon Slavin]

On 23 Oct 2014, at 4:55pm, Luigi Iemma  wrote:

> When I run this query on 3.8.5 it takes 0.126 seconds,
> when I run this query on 3.8.7 it takes 17.37 seconds

Can you do an ANALYZE then try it again ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Regression with sqlite 3.8.7

[Luigi Iemma]

Hi,

SELECT TdoIdoc,RdoCart,RdoQuat
 FROM Tesdoc
 INNER JOIN Rigdoc ON RdoIdoc BETWEEN TdoIdoc*1000 AND
TdoIdoc*1000+999
 WHERE TdoTipo=60 AND TdoAnno BETWEEN 2014 AND 2014
 GROUP BY TdoIdoc

When I run this query on 3.8.5 it takes 0.126 seconds,
when I run this query on 3.8.7 it takes 17.37 seconds
if I change 'INNER JOIN Rigdoc' with 'LEFT JOIN Rigdoc' it takes 0.063 seconds

the table Rigdoc have column RdoIdoc as PRIMARY KEY


Best regards
Luigi.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users