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) Regards, Hartwig _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
