Those lines on their own aren't any significantly slower or faster than each other.
What's happening is that when you add in a function the planner has a harder time determining if it can use certain optimizations. The "is not null" version is simple enough where the planner says "ohh, I'm ok to do something nifty here if I think it will help". And that "something nifty" is what results in the join order being reversed. (Because it doesn't have any info on the sizes of the tables to make a more informed decision with) In your case for example the two lines are not equivalent. If R.ID_ARGDAJ is 0, then the "is not null" version would include it, but the "ifnull" version would not. In general though the query planner "does not do algebra" to see if two things are functionally equivalent even if they're written slightly differently. Some similar situation have come up here where the solution was simply to replace a reference of "foo" with "+foo" because simply adding the unary plus operator disables some optimizations. -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of radovan5 Sent: Friday, December 6, 2019 10:16 AM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] Changes in version 3.28.0 makes sql run slower Can you explain why workaround is fast. Why one version is fast and other slow. I'm talking about this change in where: "and R.ID_ARHDAJ is not null" to "and ifnull(R.ID_ARHDAJ,0) <> 0" For me analyze is no improvement because data is loaded from RDBMS and would have to run always after load. Regards Radovan On 06.12.2019 14:20, Richard Hipp wrote: > On 12/6/19, Keith Medcalf <kmedc...@dessus.com> wrote: >> Perhaps the optimizer could make a "left join" that is not actually an outer >> join into a "cross join" and preserve the nesting order ... ? >> > It could do that, but that would kind of defeat the whole purpose of > the optimization, which was to free up the planner to choose a > different nesting order in cases where the LEFT JOIN didn't really > matter. > > I suspect that ANALYZE might also solve the OP's problem. _______________________________________________ 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
