There's no optimizer transformation for N+0=1 🤷♂️
Conversation
Replying to
An interesting debate is whether you want the DBMS optimizer to spend CPU cycles on fixing problems that query authors can avoid. Of course, tool generated queries are more likely to have such problems and also to generate huge queries for which the checks are costly.
1
2
Long ago I fixed a perf bug in duplicate predicate elimination code for your favorite DBMS, before the fix it was O(N*N)
2
In practice the cases that people truly care about tend to get fixed. In general obviousness does not predict real world relevance. *How* somebody really came up with their example where optimizer fails to do obvious algebraic reduction (or whatever) is absolutely relevant IMO.
1
1
OTOH things like join strength reduction absolutely make sense given the prevalence of ORMs. Even though leaving those optimizations out is, in a certain sense, just giving the user what they explicitly asked for (an outer join).
The cost of such features is larger when there is a per-core license on the DBMS
1
1
I have occasionally wondered if the problem can be fixed by making the plan cache work really well -- maybe the plan time overhead of marginal optimizations are acceptable once you do that. Hard to say.
2