On 2. Mar 2018, at 15:55, E.Pasma <pasm...@concepts.nl> wrote: > > >> Adrián Medraño Calvo wrote: >>> 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. >> > Clemens Ladisch wrote: > >> Rule 22 of <http://www.sqlite.org/optoverview.html#flattening> forbids >> subquery flattening in this case. I suspect pushDownWhereTerms() is >> not >> called at all. >> > > Hello, "push down where terms" into a complex view can sometimes be > achieved by correlation. The view/CTE must then be wrapped in a new > query that is joinable via indexes. Your example is just perfect to > show the trick. E. Pasma. > > > .eqp on > WITH eqgrseq(initial, next) AS ( > SELECT push.v, pull.v > FROM t push, t pull > WHERE pull.v IN ( > WITH RECURSIVE r AS ( > SELECT push.v > UNION ALL > SELECT t.v > FROM r > JOIN t > ON t.v = r.v + 1) > SELECT v FROM r)) > SELECT initial, next > FROM eqgrseq > WHERE initial = 1; --:initial; > > Output: > --EQP-- 0,0,0,SEARCH TABLE t AS push USING COVERING INDEX > sqlite_autoindex_t_1 (v=?) > --EQP-- 0,1,1,SEARCH TABLE t AS pull USING COVERING INDEX > sqlite_autoindex_t_1 (v=?) > --EQP-- 0,0,0,EXECUTE CORRELATED LIST SUBQUERY 1 > --EQP-- 4,0,0,SCAN TABLE r > --EQP-- 4,1,1,SEARCH TABLE t USING COVERING INDEX sqlite_autoindex_t_1 > (v=?) > --EQP-- 2,0,0,COMPOUND SUBQUERIES 0 AND 0 (UNION ALL) > --EQP-- 1,0,0,SCAN SUBQUERY 2 > 1|1 > 1|2 > 1|3 > 1|4 > 1|5
Dear E. Pasma, wow, that is remarkable! The way I interpret it, by wrapping it in this way we extract the constant column out of the recursive CTE, where the WHERE clause shall not be pushed (as I learned from Dan), into the outer query, where it can be pushed. The trade-off would be that, due to it being a correlated subquery, the recursive query would be rerun for each filtered value. (I wonder whether the performance is very different from what one gets by manually inserting the WHERE clause in the base case of the recursive CTE.) I see that it could also lead to duplicate results (that is, with queries different than the example); I’d say that recursive CTEs using UNION should change to use UNION ALL plus the DISTINCT keyword on the outer query. Thank you for your answer. Sincerely, Adrián. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
