On 12/10/2013 02:44 PM, skywind mailing lists wrote:
This is an example that the ANALYZE command leads to a wrong query plan for
RTrees:
CREATE TABLE A (ID INTEGER PRIMARY KEY, Latitude, Longitude, Altitude);
CREATE VIRTUAL TABLE B USING RTree (ID, FromLatitude, TillLatitude,
FromLongitude,TillLongitude);
INSERT INTO A VALUES(1,0,0,0);
INSERT INTO A VALUES(2,1,1,1);
INSERT INTO B VALUES(1,0,0,0,0);
INSERT INTO B VALUES(2,1,1,1,1);
Without an analyze command the query plan seems to be OK:
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM A,B WHERE (A.ID=B.ID) AND (B.FromLongitude
> 5) AND (B.TillLongitude < 10);
0|0|1|SCAN TABLE B VIRTUAL TABLE INDEX 2:EcCd (~0 rows)
0|1|0|SEARCH TABLE A USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
After running the ANALYZE command the query plan has changed and the result is
a worse query plan than before:
ANALYZE;
EXPLAIN QUERY PLAN SELECT COUNT(*) FROM A,B WHERE (A.ID=B.ID) AND (B.FromLongitude
> 5) AND (B.TillLongitude < 10);
0|0|0|SCAN TABLE A (~2 rows)
0|1|1|SCAN TABLE B VIRTUAL TABLE INDEX 1: (~0 rows)
If possible, please try with 3.8.2. 3.8.2 adds a way for virtual
table queries in general, and r-tree tables in particular, to take
advantage of ANALYZE data:
http://www.sqlite.org/src/info/5a3cfd747a
As Clemens says in the other post, with such small tables the join
order is not very important. So a better test would be with the
original database - the one you used to spot the problem in the
first place.
Dan.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
