Thanks Richard! Changing the inner join to a cross join works as well in that case, though is it enough to always disable the left join optimization ?
I have other variants of the query with different/more left joined tables/subqueries, and varying filtering conditions, as the query is dynamically generated from user options and filters (which can indeed lead to SQL that is not really "optimal"). Is having a cross join somewhere among the joins enough to "disable" the left join strength reduction for other joins? On Tue, Jun 26, 2018 at 5:58 PM, Richard Hipp <d...@sqlite.org> wrote: > On 6/26/18, Eric Grange <zar...@gmail.com> wrote: > > I am experiencing a massive performance issue on a query with a left join > > in 3.24, now taking 40 seconds, whereas in 3.22 it was just a few > > milliseconds. > > The problematic query looks like > > > > select d.key_field, count(*) nb > > from low_volume_table b > > join mid_volume_table c on c.key_b = b.id > > left join high_volume_table d on d.key_c = c.id > > where b.id >= $1 > > and b.filter1 = 0 > > and c.filter2 > 0 > > and d.filter3 > 0 > > and d.filter4 = 1 > > group by d.key_field > > order by nb desc > > Dan bisected and found the speed reduction coincides with the > introduction of the LEFT JOIN strength reduction optimization > (https://sqlite.org/optoverview.html#leftjoinreduction) in version > 3.23.0. (Dan bisected to the specific check-in > https://sqlite.org/src/info/dd568c27). > > Your work-around is to change the LEFT JOIN into CROSS JOIN, thus > forcing the query planner to preserve the same join order as it did > before the string reduction optimization. > > We (the SQLite devs) will take an action to try to improve the query > planner so that it picks a better plan for your case. > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > 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
