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.
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
