> -----Original Message-----
> From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
> boun...@sqlite.org] On Behalf Of Richard Hipp
> Sent: vrijdag 30 augustus 2013 21:16
> To: General Discussion of SQLite Database
> Subject: Re: [sqlite] Inefficient covering index used for Subversion with
> SQLite 3.8.0
>
> On Fri, Aug 30, 2013 at 2:44 PM, <rhuij...@apache.org> wrote:
>
> >
> > The query
> > [[
> > DELETE FROM nodes WHERE wc_id = ?1 AND (local_relpath = ?2 OR
> > (((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
> > ]]
> >
>
>
> > Is handled by 3.7.17 as:
> > 0|0|0|SEARCH TABLE nodes USING COVERING INDEX
> sqlite_autoindex_NODES_1
> > (wc_id=? AND local_relpath=? AND op_depth=?) (~1 rows)
> > 0|0|0|SEARCH TABLE nodes USING COVERING INDEX
> sqlite_autoindex_NODES_1
> > (wc_id=? AND local_relpath>? AND local_relpath<?) (~1 rows)
> >
> > Which I read as two separate operations, under the 'OR' optimization
> >
> > But 3.8.0.1 does:
> > 0|0|0|SEARCH TABLE nodes USING COVERING INDEX I_NODES_PARENT
> (wc_id=?)
> >
> > Which in our case is far worse than using the primary key on the normal
> > table as wc_id is constant and local_relpath +- our primary key.
> >
> > But the query planner has know way of knowing that wc_id is always the
> same value, unless you run ANALYZE. Can you do that, please: Run
> ANALYZE,
> then send in the content of the resulting "sqlite_stat1" table.
The analyze on the very small database (which I used for the comparison
between 3.7 and 3.8) is:
[[
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
ANALYZE sqlite_master;
INSERT INTO "sqlite_stat1" VALUES('REPOSITORY','I_ROOT','1 1');
INSERT INTO "sqlite_stat1" VALUES('REPOSITORY','I_UUID','1 1');
INSERT INTO "sqlite_stat1"
VALUES('REPOSITORY','sqlite_autoindex_REPOSITORY_1','1 1');
INSERT INTO "sqlite_stat1" VALUES('WCROOT','I_LOCAL_ABSPATH','1 1');
INSERT INTO "sqlite_stat1" VALUES('WCROOT','sqlite_autoindex_WCROOT_1','1
1');
INSERT INTO "sqlite_stat1" VALUES('PRISTINE','I_PRISTINE_MD5','45 1');
INSERT INTO "sqlite_stat1"
VALUES('PRISTINE','sqlite_autoindex_PRISTINE_1','45 1');
INSERT INTO "sqlite_stat1" VALUES('NODES','I_NODES_PARENT','7 7 4 1 1');
INSERT INTO "sqlite_stat1" VALUES('NODES','I_NODES_MOVED','7 7 7 7');
INSERT INTO "sqlite_stat1" VALUES('NODES','sqlite_autoindex_NODES_1','7 7 1
1');
COMMIT;
]]
A larger Subversion working copy gets
[[
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
ANALYZE sqlite_master;
INSERT INTO "sqlite_stat1" VALUES('REPOSITORY','I_ROOT','2 1');
INSERT INTO "sqlite_stat1" VALUES('REPOSITORY','I_UUID','2 2');
INSERT INTO "sqlite_stat1"
VALUES('REPOSITORY','sqlite_autoindex_REPOSITORY_1','2 1');
INSERT INTO "sqlite_stat1" VALUES('WCROOT','I_LOCAL_ABSPATH','1 1');
INSERT INTO "sqlite_stat1" VALUES('WCROOT','sqlite_autoindex_WCROOT_1','1
1');
INSERT INTO "sqlite_stat1" VALUES('PRISTINE','I_PRISTINE_MD5','9451 1');
INSERT INTO "sqlite_stat1"
VALUES('PRISTINE','sqlite_autoindex_PRISTINE_1','9451 1');
INSERT INTO "sqlite_stat1" VALUES('NODES','I_NODES_PARENT','18420 18420 9 1
1');
INSERT INTO "sqlite_stat1" VALUES('NODES','I_NODES_MOVED','18420 18420 18420
18420');
INSERT INTO "sqlite_stat1" VALUES('NODES','sqlite_autoindex_NODES_1','18420
18420 1 1');
COMMIT;
]]
But in general we don't run analyze in the working copies. We would most
likely have to insert some initial analyze data for our use cases as our
users always start from an empty database and there is no real time where we
can run analyze
As I guessed after your question 3.8.0 optimizes this correctly after
analyzing; 3.7.17 also without.
Bert
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
