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