On 9/29/18, Stephen F. Booth <[email protected]> wrote: > A query that ran fine under SQLite 3.24 is substantially slower in 3.25:
Thanks for the data sent off-list.... Your work-around is to add a plus sign "+" before the "a.id" in the GROUP BY clause. (And, BTW, shouldn't that really be an ORDER BY clause instead of a GROUP BY?) select a.id from a join c on a.id = case when c.b_a_name is not null then c.b_a_id else c.a_id end where a.id in (select a_fts.rowid from a_fts where a_fts match 'r*' order by rank) group by +a.id; The change is the single "+" near the end. This should be work for you on all releases. This is an interesting query planner problem. Recall that a query planner is really a kind of AI that has to infer or guess the best query algorithm based on incomplete information. In this particular case, the AI is making a bad choice. It will take some time for us to figure out why and perhaps come up with an improvement. Even in prior releases (such as 3.24.0) the AI was very very close to making a bad choice. A single minor tweak in one of the weights pushed the decision threshold over a limit and caused that bad choice to be taken. So the problem has been lurking just under the surface for a long time, apparently. The minor tweak in 3.25.0 (https://sqlite.org/src/info/85b9be) merely brought the problem to the surface. -- D. Richard Hipp [email protected] _______________________________________________ sqlite-users mailing list [email protected] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
