Hello, We are facing performance regression on queries over r_tree tables with any new 3.8.X sqlite version and we are sure it is connected with new versions of sqlite especially with new query planner and r_tree module. We have distincted by now two bugs:
First one: We are convienced that new version of sqlite query planner does not utilize queries with joins over r_tree tables in the way the r_tree module is intended to. I will try to prove it on this example: we have table T of nodes with their 2-D coordinates(X,Y) and R_tree virtual table R_TREE of 2-D bounding rectangles (MIN_X/Y,MIN_X/Y) we want to select for every node from T its boung rectangles it lies in from R_TREE we do it by running this statement: select * from T join R_TREE on T.X >= R_TREE.MIN_X and T.X <= R_TREE.MAX_X and T.Y >= R_TREE.MIN_Y and T.Y <= R_TREE.MAX_Y Explain query plan of such query returns different results in 3.7.X versions and 3.8.X and we think that this difference is responsible for huge performance drops on such queries(actually our performance drops are very costly from minutes to days of execution times on large tables) 3.7.X explain query plan result: ORDER DETAIL 1. SCAN TABLE T USING INTEGER PRIMARY KEY (~1000000 rows) 2. SCAN TABLE R_TREE VIRTUAL TABLE INDEX 2:BaDbBc (~0 rows) word interpretation : For all nodes find all rectangles where node lies in rectangle. 3.8.X explain query plan results: ORDER DETAIL 1. SCAN TABLE R_TREE VIRTUAL TABLE INDEX 2: 2. SCAN TABLE T word interpretation: For all rectangles find nodes where node lies in rectangle.. Actually our and everybody‘s intention using r_tree in similiar way, is to have 3.7.X plan. According to documentation of r_tree module “R*Tree index is used to narrow a search down to a list of candidate objects and then more detailed and expensive computations are done on each candidate to find if the candidate truly meets the search criteria.“(source: http://www.sqlite.org/rtree.html ) This example is the case where sqlite 3.8.X query plan scans r_tree before any criteria could have been chosen. What is in conflict with documentation citation and makes R_tree module unusable efficiently. Can this be fixed to make r-tree module usable efficiently like in prior sqlite versions? Second one: Cross join has no effect on query plan on join over r_tree. In previous example when used with cross join instead of join , has no effect on plan in contrast to cross join on common(not virtual) table. There must be some bug whether in documentation(not mentioning that cross join has no effect over r_tree tables) or in implementation of cross join functionality in query planning. Can this be fixed too? Thank you for any reply. Best regards _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
