Re: [sqlite] The next-generation query planner

[Wolfgang Enzinger]

Richard Hipp  writes:

> The combination of schema, sqlite_stat1, and query is normally sufficient,
> yes.
> 
> Can you change (and shorten) the table and index and column names to
> obscure their meaning and origin, and give us written permission to include
> your case in the published test suite for SQLite?  That would be even
> better.

Done; please find the sample database here:
www.enzinger.net/archives/ngqptest.zip

No problem with using it in the published test suite.

The query in question is in VIEW ngqp_test. As said, it demonstrates a
dramatic performance regression since version 3.7.15 whenever sqlite_stat1
is absent.

Thanks,
Wolfgang

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

Re: [sqlite] The next-generation query planner

[Richard Hipp]

On Fri, Jun 28, 2013 at 8:45 AM, Wolfgang Enzinger wrote:

> Richard Hipp  writes:
>
> > Nevertheless, it is important that you test the NGQP in your application.
>
> Getting back at the issue described in
> http://article.gmane.org/gmane.comp.db.sqlite.general/81564
>
> (which is *not* critical because an ANALYZE solves it, but nevertheless
> might be interesting):
>
> This particular query ran fine (~3 secs.) up to version 3.7.14.1; from
> version 3.7.15 on, it seems to never return.
>
> Seems a similar problem like described today by Elan Feingold, however in
> my
> case unfortunately the issue is still present in version 3.8 with the NGQP.
>
> The data contained in the concerned database are a bit sensitive; given you
> are interested in this matter, would it be enough if I sent you the schema
> (database with actual data deleted), the query and the content of
> sqlite_stat1?
>

The combination of schema, sqlite_stat1, and query is normally sufficient,
yes.

Can you change (and shorten) the table and index and column names to
obscure their meaning and origin, and give us written permission to include
your case in the published test suite for SQLite?  That would be even
better.

-- 
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] The next-generation query planner

[Wolfgang Enzinger]

Richard Hipp  writes:

> > I try to compile with SQLITE_HAS_CODEC defined I get errors saying that
> > sqlite3_key_v2 and sqlite3_rekey_v2 functions are undefined. Are these new
> > APIs?
> >
> 
> Yes.  You need to use your login and password to download the latest SEE
> source - the latest SEE sources on the trunk of the SEE source tree, not
> the latest SEE release.  The latest SEE has been updated to work with the
> NGQP.

Thanks for your confirmation.

SEE is not an option because for encryption I'm using the routines in the
ADO.NET 2.0 Data Provider.

Will do my tests with un-encrypted data for now.

Wolfgang

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

Re: [sqlite] The next-generation query planner

[Richard Hipp]

On Fri, Jun 28, 2013 at 7:21 AM, E.Pasma  wrote:

>
> I tested the draft version ...
>

Thanks for the test report!


> Unfortunately I also found a bug. For some reason the optimizer may ignore
> a where clause on an outerjoined table. The example below shows this, as
> far as it appears in the execution plan. In my test there was also a
> difference in the number of returned rows.
>
> $ sqlite3
> SQLite version 3.8.0 2013-06-26 13:22:28
> Enter ".help" for instructions
> Enter SQL statements terminated with a ";"
> sqlite> .read ttt130627.sql
> create table t (t integer primary key, x, y);
> create table t0 (t0 integer primary key, t, z);
> explain query plan
> select z from t0 left outer join t using (t) where t.t is null;
> 0|0|0|SCAN TABLE t0
>
>
There is a new optimization (only tangentially related to NGQP) that
attempts to remove tables from a join if the presence of those tables
cannot effect the output.  I think this issue is that optimization being
overzealous.  We will investigate.  Thanks.

-- 
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] The next-generation query planner

[E.Pasma]

Op 26 jun 2013, om 16:08 heeft Richard Hipp het volgende geschreven:

The next-generation query planner (NGQP) is a rewrite of the query  
planner

for SQLite that is faster (reduced run-time for sqlite3_prepare()) and
generates better plans for queries (reducing the run-time for
sqlite3_step()).  More information about the NGQP is available here:

http://www.sqlite.org/draft/queryplanner-ng.html

We've run literally millions and millions of test cases on the NGQP  
with no
problems.  We've tested in on Linux, Windows, Mac, OpenBSD, and  
Solaris,

and on 32-bit and 64-bit systems.  The Fossil server that manages the
SQLite website is running NGQP.  The version of Firefox on which  
this email
is being composed is running the NGQP.  Everything seems to work  
great.


Nevertheless, it is important that you test the NGQP in your  
application.


Amalgamations for the latest SQLite containing the NGQP are  
available from

the http://www.sqlite.org/draft/download.html page.  This should be a
drop-in replacement for the amalgamation you are currently using.   
There
are no new APIs or compiler flags to fuss with.  Everything works  
exactly

as it did before, only a little faster.  You should be able to simply
recompile and end up with an application that (hopefully) runs a  
little

faster.

Please try this out, and let me know if of your successes and of any
problems you encounter.

--
D. Richard Hipp
d...@sqlite.org


(with regards to the original post of DRH only)

I tested the draft version with a set of query that was extensively  
tuned and that admittedly is not a nice target for improvement. Still  
in some queries the execution plan is changed favourably (small tables  
on top).
Also I noted a favourable change with subqueries, where the optimizer  
can do a sort of 'partial' query flattening if a subquery is a join.
Further, if a table that has been analyzed with just a single row, it  
is now scanned instead of searched by index. Or is this a cosmetical  
change in the output of explain query plan? In another case, with two  
rows, the optimizer now opts for a scan on a secundary covering index,  
where it searched by primary key before. I can not say much about  
performance as these queries are executed in milliseconds and the  
timings are fluctuating.
Unfortunately I also found a bug. For some reason the optimizer may  
ignore a where clause on an outerjoined table. The example below shows  
this, as far as it appears in the execution plan. In my test there was  
also a difference in the number of returned rows.


$ sqlite3
SQLite version 3.8.0 2013-06-26 13:22:28
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .read ttt130627.sql
create table t (t integer primary key, x, y);
create table t0 (t0 integer primary key, t, z);
explain query plan
select z from t0 left outer join t using (t) where t.t is null;
0|0|0|SCAN TABLE t0

Thanks for the opportunity for feedback, Edzard Pasma

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

Re: [sqlite] The next-generation query planner

[Richard Hipp]

On Fri, Jun 28, 2013 at 6:50 AM, Wolfgang Enzinger wrote:

> Richard Hipp  writes:
>
> > Amalgamations for the latest SQLite containing the NGQP are available
> from
> > the http://www.sqlite.org/draft/download.html page.  This should be a
> > drop-in replacement for the amalgamation you are currently using.  There
> > are no new APIs or compiler flags to fuss with.  Everything works exactly
> > as it did before, only a little faster.  You should be able to simply
> > recompile and end up with an application that (hopefully) runs a little
> > faster.
>
> I would like to run tests with some of my encrypted databases, however when
> I try to compile with SQLITE_HAS_CODEC defined I get errors saying that
> sqlite3_key_v2 and sqlite3_rekey_v2 functions are undefined. Are these new
> APIs?
>

Yes.  You need to use your login and password to download the latest SEE
source - the latest SEE sources on the trunk of the SEE source tree, not
the latest SEE release.  The latest SEE has been updated to work with the
NGQP.

-- 
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] The next-generation query planner

[Wolfgang Enzinger]

Richard Hipp  writes:

> Amalgamations for the latest SQLite containing the NGQP are available from
> the http://www.sqlite.org/draft/download.html page.  This should be a
> drop-in replacement for the amalgamation you are currently using.  There
> are no new APIs or compiler flags to fuss with.  Everything works exactly
> as it did before, only a little faster.  You should be able to simply
> recompile and end up with an application that (hopefully) runs a little
> faster.

I would like to run tests with some of my encrypted databases, however when
I try to compile with SQLITE_HAS_CODEC defined I get errors saying that
sqlite3_key_v2 and sqlite3_rekey_v2 functions are undefined. Are these new APIs?

Wolfgang

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

Re: [sqlite] The next-generation query planner

[Richard Hipp]

On Thu, Jun 27, 2013 at 2:34 AM, Hick Gunter <h...@scigames.at> wrote:

> Two questions:
>
> Does the NGQP change the way virtual table methods (specfically
> xBestIndex) are called? I have been using virtual table virtual fields
> (named __use_index_) to make the xBestIndex method return large costs
> when not using the suggested index.
>

I'm not sure what you mean here...

The virtual table API is unchanged.  The NGQP calls the xBestIndex method
as before, but it calls xBestIndex a different number of times and in a
different order.  This should not make any difference to the virtual table
implementation, though.

The NGQP is more sensitive to having proper costs.  I had to fix bugs in
the cost computations of some of the existing virtual tables.  The legacy
query planner is tolerant of those bugs, while the NGQP is not.


>
> Does the new draft sqlite3_stmt_status interface tally virtual table
> method calls (i.e. xNext)?
>

No, I don't think it does.  the SQLITE_STMTSTATUS_FULLSCAN_STEP counter is
only incremented for OP_Next or OP_Prev operations that are part of a full
table scan.  But SQLite has no way of knowing if the underlying virtual
table is doing a full table scan or not, so it does not increment the
counter on OP_VNext operations (which invoke xNext).




>
> -Ursprüngliche Nachricht-
> Von: Richard Hipp [mailto:d...@sqlite.org]
> Gesendet: Mittwoch, 26. Juni 2013 16:09
> An: General Discussion of SQLite Database
> Betreff: [sqlite] The next-generation query planner
>
> The next-generation query planner (NGQP) is a rewrite of the query planner
> for SQLite that is faster (reduced run-time for sqlite3_prepare()) and
> generates better plans for queries (reducing the run-time for
> sqlite3_step()).  More information about the NGQP is available here:
>
>  http://www.sqlite.org/draft/queryplanner-ng.html
>
> We've run literally millions and millions of test cases on the NGQP with
> no problems.  We've tested in on Linux, Windows, Mac, OpenBSD, and Solaris,
> and on 32-bit and 64-bit systems.  The Fossil server that manages the
> SQLite website is running NGQP.  The version of Firefox on which this email
> is being composed is running the NGQP.  Everything seems to work great.
>
> Nevertheless, it is important that you test the NGQP in your application.
>
> Amalgamations for the latest SQLite containing the NGQP are available from
> the http://www.sqlite.org/draft/download.html page.  This should be a
> drop-in replacement for the amalgamation you are currently using.  There
> are no new APIs or compiler flags to fuss with.  Everything works exactly
> as it did before, only a little faster.  You should be able to simply
> recompile and end up with an application that (hopefully) runs a little
> faster.
>
> Please try this out, and let me know if of your successes and of any
> problems you encounter.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
> --
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> Klitschgasse 2 – 4, A - 1130 Vienna, Austria
> FN 157284 a, HG Wien
> Tel: +43 1 80100 0
> E-Mail: h...@scigames.at
>
> This e-mail is confidential and may well also be legally privileged. If
> you have received it in error, you are on notice as to its status and
> accordingly please notify us immediately by reply e-mail and then delete
> this message from your system. Please do not copy it or use it for any
> purposes, or disclose its contents to any person as to do so could be a
> breach of confidence. Thank you for your cooperation.
> ___
> 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

[sqlite] The next-generation query planner

[Richard Hipp]

The next-generation query planner (NGQP) is a rewrite of the query planner
for SQLite that is faster (reduced run-time for sqlite3_prepare()) and
generates better plans for queries (reducing the run-time for
sqlite3_step()).  More information about the NGQP is available here:

 http://www.sqlite.org/draft/queryplanner-ng.html

We've run literally millions and millions of test cases on the NGQP with no
problems.  We've tested in on Linux, Windows, Mac, OpenBSD, and Solaris,
and on 32-bit and 64-bit systems.  The Fossil server that manages the
SQLite website is running NGQP.  The version of Firefox on which this email
is being composed is running the NGQP.  Everything seems to work great.

Nevertheless, it is important that you test the NGQP in your application.

Amalgamations for the latest SQLite containing the NGQP are available from
the http://www.sqlite.org/draft/download.html page.  This should be a
drop-in replacement for the amalgamation you are currently using.  There
are no new APIs or compiler flags to fuss with.  Everything works exactly
as it did before, only a little faster.  You should be able to simply
recompile and end up with an application that (hopefully) runs a little
faster.

Please try this out, and let me know if of your successes and of any
problems you encounter.

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