On Thu, 18 Apr 2019 at 09:48, Hick Gunter <h...@scigames.at> wrote: > So the QP is attemopting to determine which cost product is less: > > My guess is it will probably choose the undesired, invalid plan. A linear > cost penalty for desired but optional inputs is probably not going to cut > it. In this case, the returned cost needs to be greater than 4 (more if > there are more records in the IN set). I feel there needs to be a > multiplicative penalty of at least 10. > > > As I mentioned in other post, the direct test with my current implementation and IN operator gave a good result, but I understand this can't be a rule. I see your points about cost adjustments so will give them a try.
I also decided to look at the sources, estimatedCost as usable input only mentioned once, it goes to rRun field being translated with sqlite3LogEstFromDouble call. The LogEst value is nicely explained in the sources (below is the compacted version of the comment in the sources) Estimated quantities used for query planning are stored as 16-bit > logarithms. For quantity X, the value stored is 10*log2(X). This gives a > possible range of values of approximately 1.0e986 to 1e-986. But the > allowed values are "grainy". Not every value is representable. For > example, quantities 16 and 17 are both represented by a LogEst of 40. > However, since LogEst quantities are suppose to be estimates, not exact > values, this imprecision is not a problem. > "LogEst" is short for "Logarithmic Estimate". > Examples: > 1 -> 0 20 -> 43 10000 -> 132 > 2 -> 10 25 -> 46 25000 -> 146 > 3 -> 16 100 -> 66 1000000 -> 199 > 4 -> 20 1000 -> 99 1048576 -> 200 > 10 -> 33 1024 -> 100 4294967296 -> 320 > The LogEst can be negative to indicate fractional values. > Examples: > 0.5 -> -10 0.1 -> -33 0.0625 -> -40 > It seems that as a general rule it's not good to have close estimatedCost values since they will sometimes yield the same LogEst values. rRun evaluations in the sources are too complex to analyze Max _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
