I'm working on a somewhat large database where sqlite is apparently
missing an optimization in its query planner.
I've attached a simple schema to reproduce the issue.
The problem lies with the ports view.
It does grab data from some other tables using left join,
and also some aggregated data from a coaelescing view that
uses group_concat to piece some field together. That data
is also a left join.
Now, if I do a partial select from my view, say
select fullpkgpath, c from ports;
the query planner correctly tells me I don't pull anything from _keyword.
However, notice I still see the whole subquery from _depends, even though
its result is unused, and not even needed thanks to the left join.
sqlite> explain query plan select fullpkgpath, c from ports;
QUERY PLAN
|--MATERIALIZE 2
| |--CO-ROUTINE 1
| | |--SCAN TABLE _Depends
| | `--USE TEMP B-TREE FOR ORDER BY
| |--SCAN SUBQUERY 1
| `--USE TEMP B-TREE FOR GROUP BY
|--SCAN TABLE _Ports
|--SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX (FullPkgPath=? AND T=?)
`--SEARCH TABLE _Paths USING INTEGER PRIMARY KEY (rowid=?)
Looks to me like this could be recognized and optimized away ?
Or am I missing something.
(a second copy of the schema inlined, as I'm unsure how this mailing-list
deals with attachments)
CREATE TABLE _Depends (FullPkgPath INTEGER NOT NULL REFERENCES _Paths(Id),
FullDepends TEXT NOT NULL, T TEXT NOT NULL, N INTEGER NOT NULL);
CREATE TABLE _Ports (FullPkgPath INTEGER NOT NULL REFERENCES _Paths(Id), C
TEXT, K INTEGER REFERENCES _Keyword(Id));
CREATE TABLE _Keyword (Id INTEGER PRIMARY Key, VALUE TEXT);
CREATE TABLE _Paths (Id INTEGER PRIMARY KEY, FullPkgPath TEXT NOT NULL UNIQUE);
CREATE VIEW Depends_ordered AS
WITH o AS
(SELECT
FullPkgPath,
FullDepends AS Value,
T
FROM _Depends
ORDER BY N)
SELECT
FullPkgPath,
group_concat(Value, ' ') AS Value,
T
FROM o
GROUP BY FullPkgPath, T;
CREATE VIEW Ports AS
SELECT
_Paths.FullPkgPath AS FullPkgPath,
Depends_ordered.Value AS BUILD_DEPENDS,
C,
_Keyword.value as Keyword
FROM _Ports
LEFT JOIN Depends_ordered
ON Depends_ordered.FullPkgpath=_Ports.FullPkgpath AND
Depends_ordered.T=2
JOIN _Paths
ON _Paths.Id=_Ports.FullPkgpath
LEFT JOIN _Keyword
ON _Keyword.Id=_Ports.K;
insert into _paths (Id, FullPkgPath) VALUES (1, "devel/cmake");
insert into _paths (Id, FullPkgPath) VALUES (2, "devel/coin");
insert into _depends (FullPkgPath, FullDepends, T, N) VALUES (1,
"devel/autoconf", 2, 0);
insert into _depends (FullPkgPath, FullDepends, T, N) VALUES (1,
"devel/automake", 2, 1);
insert into _keyword (Id, VALUE) VALUES (0, "amd64");
insert into _keyword (Id, VALUE) VALUES (1, "sparc");
insert into _ports (FullPkgPath, C, K) VALUES(1, "I am cmake", 0);
insert into _ports (FullPkgPath, C, K) VALUES(2, "I am coin", 0);
CREATE TABLE _Depends (FullPkgPath INTEGER NOT NULL REFERENCES _Paths(Id),
FullDepends TEXT NOT NULL, T TEXT NOT NULL, N INTEGER NOT NULL);
CREATE TABLE _Ports (FullPkgPath INTEGER NOT NULL REFERENCES _Paths(Id), C
TEXT, K INTEGER REFERENCES _Keyword(Id));
CREATE TABLE _Keyword (Id INTEGER PRIMARY Key, VALUE TEXT);
CREATE TABLE _Paths (Id INTEGER PRIMARY KEY, FullPkgPath TEXT NOT NULL UNIQUE);
CREATE VIEW Depends_ordered AS
WITH o AS
(SELECT
FullPkgPath,
FullDepends AS Value,
T
FROM _Depends
ORDER BY N)
SELECT
FullPkgPath,
group_concat(Value, ' ') AS Value,
T
FROM o
GROUP BY FullPkgPath, T;
CREATE VIEW Ports AS
SELECT
_Paths.FullPkgPath AS FullPkgPath,
Depends_ordered.Value AS BUILD_DEPENDS,
C,
_Keyword.value as Keyword
FROM _Ports
LEFT JOIN Depends_ordered
ON Depends_ordered.FullPkgpath=_Ports.FullPkgpath AND
Depends_ordered.T=2
JOIN _Paths
ON _Paths.Id=_Ports.FullPkgpath
LEFT JOIN _Keyword
ON _Keyword.Id=_Ports.K;
insert into _paths (Id, FullPkgPath) VALUES (1, "devel/cmake");
insert into _paths (Id, FullPkgPath) VALUES (2, "devel/coin");
insert into _depends (FullPkgPath, FullDepends, T, N) VALUES (1,
"devel/autoconf", 2, 0);
insert into _depends (FullPkgPath, FullDepends, T, N) VALUES (1,
"devel/automake", 2, 1);
insert into _keyword (Id, VALUE) VALUES (0, "amd64");
insert into _keyword (Id, VALUE) VALUES (1, "sparc");
insert into _ports (FullPkgPath, C, K) VALUES(1, "I am cmake", 0);
insert into _ports (FullPkgPath, C, K) VALUES(2, "I am coin", 0);
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
