Some observations. It seems the WHERE pushdown optimization you cited only applies to subqueries with existing WHERE clause. In your example without WHERE, the SELECT specifies the whole table as the left hand side of the UNION. Scanning the whole table is likely more efficient than using an index to retrieve every row. Do you have a better example of the problem?
[Another suggestion in the form of a question: Is the more efficient UNION ALL completely ruled out because of duplicates?] Peter On Thu, Mar 1, 2018 at 2:37 AM, Adrián Medraño Calvo <amca...@prs.de> wrote: > Dear SQLite, > > The following SQL script shows a query selecting data from a recursive CTE > and filtering it. I expected the optimizer to apply the filter to the > recursive CTE directly, and indeed the documentation of pushDownWhereTerms > (src/select.c:3833) indicates this possibility when various conditions are > satisfied. As far as I can see, the conditions are satisfied, but the > query is nonetheless not optimized. This indicates a misunderstanding on > my part, or an oversight in SQLite. > > -- A table containing some numbers. > CREATE TABLE t (v INT PRIMARY KEY); > INSERT INTO t > VALUES (0), (1), (2), (3), (4), (5); > > -- Recursive query relating a number a sequence of numbers from "t" equal > or > -- greater than it. > EXPLAIN QUERY PLAN > WITH RECURSIVE > eqgrseq(initial, next) AS (SELECT v, v > FROM t > UNION > SELECT eqgrseq.initial, t.v > FROM eqgrseq > JOIN t > ON (t.v = eqgrseq.next + 1)) > SELECT eqgrseq.initial, eqgrseq.next > FROM eqgrseq > WHERE eqgrseq.initial = :initial; > -- selectid,order,from,detail > -- 2,0,0,"SCAN TABLE t" > -- 3,0,0,"SCAN TABLE eqgrseq" > -- 3,1,1,"SEARCH TABLE t USING COVERING INDEX sqlite_autoindex_t_1 (v=?)" > -- 1,0,0,"COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION)" > -- 0,0,0,"SCAN SUBQUERY 1” > > -- The same query with the WHERE condition manually placed in the > recursive CTE's > -- initial clause. > EXPLAIN QUERY PLAN > WITH RECURSIVE > eqgrseq(initial, next) AS (SELECT v, v > FROM t > WHERE v = :initial > UNION > SELECT eqgrseq.initial, t.v > FROM eqgrseq > JOIN t > ON (t.v = eqgrseq.next + 1)) > SELECT eqgrseq.initial, eqgrseq.next > FROM eqgrseq > WHERE eqgrseq.initial = :initial; > -- selectid,order,from,detail > -- 2,0,0,"SEARCH TABLE t USING COVERING INDEX sqlite_autoindex_t_1 (v=?)" > -- 3,0,0,"SCAN TABLE eqgrseq" > -- 3,1,1,"SEARCH TABLE t USING COVERING INDEX sqlite_autoindex_t_1 (v=?)" > -- 1,0,0,"COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION)" > -- 0,0,0,"SCAN SUBQUERY 1” > > Note the query plan difference: the first scans the “t” table, therefore > recurses for every value, while the second only recurses for the filtered > ones. > > In our application, the recursive CTE is hidden behind a view in order to > abstract over the details; manually inserting the WHERE clause would not be > possible while maintaining the view, as far as I can see. > > Thank you, > Adrián. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
