Hello Peter, thank you for your response.
> > On 2. Mar 2018, at 06:30, petern <[email protected]> wrote: > > 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? I’m not sure that’s the case. For example: sqlite> .eqp on; sqlite> WITH RECURSIVE ...> eqgrseq(initial, next) AS (SELECT v, v ...> FROM t ...> WHERE 1 = 1 ...> 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 = 1; --EQP-- 2,0,0,SCAN TABLE t --EQP-- 3,0,0,SCAN TABLE eqgrseq --EQP-- 3,1,1,SEARCH TABLE t USING COVERING INDEX sqlite_autoindex_t_1 (v=?) --EQP-- 1,0,0,COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION) --EQP-- 0,0,0,SCAN SUBQUERY 1 The where expression is not pushed to the non-recursive case either. > [Another suggestion in the form of a question: Is the more efficient UNION > ALL completely ruled out because of duplicates?] You are right, it would make this query more performant without changing its meaning. Sincerely, Adrián. _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
