On 6/26/18, Eric Grange <[email protected]> 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 [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
