On Sat, Sep 29, 2018 at 5:33 PM Richard Hipp <d...@sqlite.org> wrote:
> On 9/29/18, Stephen F. Booth <m...@sbooth.org> 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 does fix the issue. Thanks! I'm sure there is a smarter way to write this query- you're probably right about ORDER BY instead of GROUP BY. Stephen > 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 > d...@sqlite.org > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
