On Sun, Jun 1, 2014 at 7:25 AM, Bert Huijben <rhuij...@apache.org> wrote:
> Hi, > > I just tested Sqlite 3.8.5 on Subversion's schema and found that one of our > queries started using a temporary B-Tree while executing, which it didn't > use in any older Sqlite version. I wouldn't expect a usage of a temporary > table as the data is already properly ordered when it uses the primary key > index. > Thanks for the beta test! The unnecessary B-Tree was being used to sort the "SELECT ?2" query - the one without a FROM clause. Since that query only returns one row, the sorting is pointless. But it is also harmless in the sense that it still gives the correct answer and the extra work of creating a B-Tree for a single row does not cost many CPU cycles. The fact that it is harmless and does not noticeably impact performance is why this did not show up in our internal tests. Nevertheless, it is good to find and fix this sort of thing, so the problem has now been resolved, and new tests have been added to prevent a recurrence. Thanks for the report! New snapshots are available on the main download page: http://www.sqlite.org/download.html > > The query in its readable form is: > [[ > INSERT INTO delete_list(local_relpath) > SELECT ?2 > UNION ALL > SELECT local_relpath FROM nodes AS n > WHERE wc_id = ?1 > AND IS_STRICT_DESCENDANT_OF(local_relpath, ?2) > AND op_depth >= ?3 > AND op_depth = (SELECT MAX(s.op_depth) FROM nodes AS s > WHERE s.wc_id = ?1 > AND s.local_relpath = n.local_relpath) > AND presence NOT IN (MAP_BASE_DELETED, MAP_NOT_PRESENT, MAP_EXCLUDED, > MAP_SERVER_EXCLUDED) > AND file_external IS NULL > ORDER by local_relpath > ]] > > The actual query passed to sqlite is: > [[ > INSERT INTO delete_list(local_relpath) SELECT ?2 UNION ALL SELECT > local_relpath FROM nodes AS n WHERE wc_id = ?1 AND (((local_relpath) > > (CASE (?2) WHEN '' THEN '' ELSE (?2) || '/' END)) AND ((local_relpath) < > CASE (?2) WHEN '' THEN X'FFFF' ELSE (?2) || '0' END)) AND op_depth >= ?3 > AND op_depth = (SELECT MAX(s.op_depth) FROM nodes AS s > WHERE s.wc_id = ?1 AND s.local_relpath = > n.local_relpath) AND presence NOT IN ('base-deleted', 'not-present', > 'excluded', 'server-excluded') AND file_external IS NULL ORDER by > local_relpath > ]] > > The query plan in 3.8.5-201405271818 is: > [[ > USE TEMP B-TREE FOR ORDER BY > SEARCH TABLE nodes AS n USING INDEX sqlite_autoindex_NODES_1 (wc_id=? AND > local_relpath>? AND local_relpath<?) > EXECUTE CORRELATED SCALAR SUBQUERY 3 > SEARCH TABLE nodes AS s USING COVERING INDEX sqlite_autoindex_NODES_1 > (wc_id=? AND local_relpath=?) > EXECUTE LIST SUBQUERY 4 > COMPOUND SUBQUERIES 1 AND 2 (UNION ALL) > ]] > > In 3.7.17 and 3.8.4 it was: > [[ > 2|0|0|SEARCH TABLE nodes AS n USING INDEX sqlite_autoindex_NODES_1 (wc_id=? > AND > local_relpath>? AND local_relpath<?) > 2|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 3 > 3|0|0|SEARCH TABLE nodes AS s USING COVERING INDEX sqlite_autoindex_NODES_1 > (wc_ > id=? AND local_relpath=?) > 2|0|0|EXECUTE LIST SUBQUERY 4 > 0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION ALL) > ]] > > Relevant schema: > [[ > CREATE TEMPORARY TABLE delete_list ( > local_relpath TEXT PRIMARY KEY NOT NULL UNIQUE > ) > > CREATE TABLE NODES ( > wc_id INTEGER NOT NULL REFERENCES WCROOT (id), > local_relpath TEXT NOT NULL, > op_depth INTEGER NOT NULL, > parent_relpath TEXT, > repos_id INTEGER REFERENCES REPOSITORY (id), > repos_path TEXT, > revision INTEGER, > presence TEXT NOT NULL, > moved_here INTEGER, > moved_to TEXT, > kind TEXT NOT NULL, > properties BLOB, > depth TEXT, > checksum TEXT REFERENCES PRISTINE (checksum), > symlink_target TEXT, > changed_revision INTEGER, > changed_date INTEGER, > changed_author TEXT, > translated_size INTEGER, > last_mod_time INTEGER, > dav_cache BLOB, > file_external INTEGER, > inherited_props BLOB, > PRIMARY KEY (wc_id, local_relpath, op_depth) ); > CREATE UNIQUE INDEX I_NODES_PARENT ON NODES (wc_id, parent_relpath, > local_relpath, op_depth); > CREATE UNIQUE INDEX I_NODES_MOVED ON NODES (wc_id, moved_to, op_depth); > INSERT INTO "sqlite_stat1" VALUES('NODES','sqlite_autoindex_NODES_1','8000 > 8000 2 1'); > INSERT INTO "sqlite_stat1" VALUES('NODES','I_NODES_PARENT','8000 8000 10 2 > 1'); > INSERT INTO "sqlite_stat1" VALUES('NODES','I_NODES_MOVED','8000 8000 1 1'); > ]] > > Bert > > _______________________________________________ > 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
