The first query where you constrain "R.ID_ARHDAJ is not null" in the where clause means that your "left join" is a mis-stated "inner join" since you are discarding all outer join candidate rows from the result set. The optimizer recognizes this fact and "gets rid of" your superfluous use of the word "left" before the word "join" and processes the query as a regular inner join. This means that the optimizer is free to choose which table to put in the outer loop. Since it has no information about the sizes of the tables it places the index in the outer loop because it has the smallest size (fewer columns than either table) and therefore will require less disk I/O to scan.
Once you have run ANALYZE the query optimizer has better information from which it can build a better plan (you said the plan was correct after running ANALYZE). In the second query you change the constraint to "ifnull(R.ID_ARHDAJ,0) <> 0" in the where clause. The "am I excluding outer join results" optimization no longer recognizes that you are merely saying "R.ID_ARHDAJ is not null" (the parser does not do algebra) in a more convoluted way, so processes the query as a left join. In a left join the RHS table must be in an inner loop compared to the other tables mentioned in the ON clause and this cannot be changed without changing the meaning of the query (ie, obtaining different results). -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-----Original Message----- >From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On >Behalf Of radovan5 >Sent: Friday, 6 December, 2019 08:16 >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
