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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
