On 03/01/2018 05:37 PM, Adrián Medraño Calvo 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;
It's quite a special case really. You can push the WHERE term down only
because it refers to a column that is always copied without modification
from the initial result set into any recursive results. You could not
push down a term like:
WHERE eqgrseq.next = :next:
Dan.
-- 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
