Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

Hello Adrián, as you say (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 wonder too. Still the trick is meant to make a view (without manually inserted predicates inside) Thanks for

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

Hello Peter, thank you for your response. > > On 2. Mar 2018, at 06:30, petern 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 th

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

> On 2. Mar 2018, at 21:39, Dan Kennedy wrote: > > 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 di

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

On 2. Mar 2018, at 15:55, E.Pasma 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 documentati

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

Hello Clemens, thank you for your answer. > On 2. Mar 2018, at 11:19, Clemens Ladisch wrote: > > Rule 22 of forbids > subquery flattening in this case. I suspect pushDownWhereTerms() is not > called at all. Although this is definitely over m

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

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/se

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

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 possibi

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

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 ind

Re: [sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

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 possibi

[sqlite] Missing "pushDownWhereTerms" optimisation on recursive CTE

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 c