Re: [sqlite] Query optimizer suboptimal planning with virtual tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 15/03/13 05:25, Clemens Ladisch wrote: > If you had no xBestIndex implementation, SQLite would crash. He is using APSW (a Python wrapper). It provides a default implementation if the developer does not. Here is the documentation: http://apidoc.apsw.googlecode.com/hg/vtable.html#apsw.VTTable.BestIndex There was some confusion over estimated cost. APSW leaves it at the SQLite supplied default which is 1e99 divided by 2. This generally encourages to SQLite to use anything else (eg real indices) unless the developer using APSW knows better. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.11 (GNU/Linux) iEYEARECAAYFAlFGzgAACgkQmOOfHg372QSzoQCfWxH+ACpo3FbeYFli7DEZVGpO G+IAn2twe1Lva3sVE05jedQi/7VL3lpk =CFQH -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query optimizer suboptimal planning with virtual tables
Eleytherios Stamatogiannakis wrote: > right now our VTs do not implement xBestIndex at all The documentation says: | The xBestIndex method is required for every virtual table implementation. If you had no xBestIndex implementation, SQLite would crash. > so i guess SQLite uses a default value of 0. I'd guess your xBestIndex function pretends to succeed, but leaves all the output fields with zero values. This makes the query planner think that all your indexes have a cost of zero, which makes it choose some random one. You *must* return correct information from xBestIndex. Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query optimizer suboptimal planning with virtual tables
SQLite uses a cost-based algorithm to formulate a query plan. Therefore you
must return a proper cost estimate in your virtual tables' xBestIndex function.
This is usually interpreted as similar to "how many (disk) accesses are
required" or "how many records will be returned". For your virtual table these
numbers would be 0 (but you should probably return 1) and 100 respectively.
The available query plans are
a) FTS real table, FTS virtual table
b) FTS virtual table, FTS real table
c) Build index on real table, FTS virtual table, access covering index
If you return 0, your estimates will be
a) 100 * 0 = 0
b) 0 * 10 = 0
c) 100 * log(100) + 0 * 10 = 600
So SQLite will choose either (a) or (b).
If you return 1, your estimates will be
a) 100 * 1 = 100
b) 1 * 100 = 100
c) 100 * log(100) + 1 * 10 = 610
If you return 100, your estimates will be
a) 100 * 100 = 1
b) 100 * 100 = 1
c) 100 * log(100) + 100 * 10 = 6001000
So to make this work you need to return the number of rows...
OTOH you can always force SQLite to use the query plan suggested by the order
of joins using CROSS JOIN
Gunter
-Ursprüngliche Nachricht-
Von: Eleytherios Stamatogiannakis [mailto:est...@gmail.com]
Gesendet: Donnerstag, 14. März 2013 15:38
An: sqlite-users@sqlite.org
Betreff: [sqlite] Query optimizer suboptimal planning with virtual tables
Hi,
I have came across a glitch with how SQLite's query optimizer plans virtual
tables. Example follows:
I have a virtual table function named "range" that produces all numbers from 1
to range's arg. This virtual table does not have any index functionality.
With this i'll create the virtual table instance "t1":
> create virtual table t1 using range('100'); > select * from t1;
1
2
...
99
100
--Column names--
C1
Let's create a real table now:
> create table t2 as select * from t1;
The plan that the optimizer will produce when i join these two tables is
this:
> explain query plan select * from t1, t2 where t1.c1=t2.c1; 0 |0 |1 | SCAN
> TABLE t2 (~100 rows) 0 |1 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0
> rows)
Even thought, i have put VT t1 first in the join list, SQLite will do a nested
loop join (putting it on the right).
Wouldn't it had made more sense for SQLite to create an automatic index on the
real table t2 and do the join as such?
0 |0 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows) 0 |1 |1 | SEARCH TABLE
t2 USING AUTOMATIC COVERING INDEX idx (C1=?) (~10
rows)
Putting the VT on the right by default, doesn't make much sense to me, since it
cannot create an automatic index on it.
In general it seems to me to be a better default to always have the non
automatic indexable SQLite entities (views, virtual tables) on the left of the
joins and what can be automatically indexed on the right of the joins.
Also, i think, that it would be even better if SQLite had the ability to scan
the virtual table and build a temporary automatic covering index on it to do
the join (why isn't this case allowed?).
Thank you,
lefteris.
___
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
Re: [sqlite] Query optimizer suboptimal planning with virtual tables
Eleytherios Stamatogiannakis wrote: > On 14/03/13 17:05, Clemens Ladisch wrote: >> Eleytherios Stamatogiannakis wrote: explain query plan select * from t1, t2 where t1.c1=t2.c1; >>> 0 |0 |1 | SCAN TABLE t2 (~100 rows) >>> 0 |1 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows) >>> >>> Even thought, i have put VT t1 first in the join list, SQLite >>> will do a nested loop join (putting it on the right). >> >> How have you defined index 0 of your virtual table? > > The "INDEX 0:" is always there (even when no index is defined in > the VT function). I don't know what it means. Well, what does your xBestIndex callback return? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query optimizer suboptimal planning with virtual tables
On 14/03/13 17:05, Clemens Ladisch wrote: Eleytherios Stamatogiannakis wrote: explain query plan select * from t1, t2 where t1.c1=t2.c1; 0 |0 |1 | SCAN TABLE t2 (~100 rows) 0 |1 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows) Even thought, i have put VT t1 first in the join list, SQLite will do a nested loop join (putting it on the right). How have you defined index 0 of your virtual table? The "INDEX 0:" is always there (even when no index is defined in the VT function). I don't know what it means. l. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Query optimizer suboptimal planning with virtual tables
Eleytherios Stamatogiannakis wrote: >> explain query plan select * from t1, t2 where t1.c1=t2.c1; > 0 |0 |1 | SCAN TABLE t2 (~100 rows) > 0 |1 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows) > > Even thought, i have put VT t1 first in the join list, SQLite > will do a nested loop join (putting it on the right). How have you defined index 0 of your virtual table? Regards, Clemens ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Query optimizer suboptimal planning with virtual tables
Hi,
I have came across a glitch with how SQLite's query optimizer plans
virtual tables. Example follows:
I have a virtual table function named "range" that produces all numbers
from 1 to range's arg. This virtual table does not have any index
functionality.
With this i'll create the virtual table instance "t1":
> create virtual table t1 using range('100');
> select * from t1;
1
2
...
99
100
--Column names--
C1
Let's create a real table now:
> create table t2 as select * from t1;
The plan that the optimizer will produce when i join these two tables is
this:
> explain query plan select * from t1, t2 where t1.c1=t2.c1;
0 |0 |1 | SCAN TABLE t2 (~100 rows)
0 |1 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows)
Even thought, i have put VT t1 first in the join list, SQLite will do a
nested loop join (putting it on the right).
Wouldn't it had made more sense for SQLite to create an automatic index
on the real table t2 and do the join as such?
0 |0 |0 | SCAN TABLE t1 VIRTUAL TABLE INDEX 0: (~0 rows)
0 |1 |1 | SEARCH TABLE t2 USING AUTOMATIC COVERING INDEX idx (C1=?) (~10
rows)
Putting the VT on the right by default, doesn't make much sense to me,
since it cannot create an automatic index on it.
In general it seems to me to be a better default to always have the non
automatic indexable SQLite entities (views, virtual tables) on the left
of the joins and what can be automatically indexed on the right of the
joins.
Also, i think, that it would be even better if SQLite had the ability to
scan the virtual table and build a temporary automatic covering index on
it to do the join (why isn't this case allowed?).
Thank you,
lefteris.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
