Avoiding no-op expressions in predicates to change the optimizer estimate

A "no-op" coalesce() predicate of the form COALESCE(X, X) = X introduces an estimation error into the planning of any query that uses it. Currently the Db2® query compiler does not have the capability of dissecting that predicate and determining that all rows actually satisfy it.

As a result, the predicate artificially reduces the estimated number of rows coming from some part of a query plan. This smaller row estimate usually reduces the row and cost estimates for the rest of query planning, and sometimes results in a different plan being chosen, because relative estimates between different candidate plans have changed.

Why can this do-nothing predicate sometimes improve query performance? The addition of the no-op coalesce() predicate introduces an error that masks something else that is preventing optimal performance.

What some performance enhancement tools do is a brute-force test: the tool repeatedly introduces the predicate into different places in a query, operating on different columns, to try to find a case where, by introducing an error, it stumbles onto a better-performing plan. This is also true of a query developer hand-coding the "no-op" predicate into a query. Typically, the developer will have some insight on the data to guide the placement of the predicate.

Using this method to improve query performance is a short-term solution that does not address root cause and might have the following implications:
  • Potential areas for performance improvements are hidden.
  • There are no guarantees that this workaround will provide permanent performance improvements, because the Db2 query compiler might eventually handle the predicate better, or other random factors might affect it.
  • There might be other queries that are affected by the same root cause and the performance of your system in general might suffer as a result.

If you have followed best practices recommendations, but you believe that you are still getting less than optimal performance, you can provide explicit optimization guidelines to the Db2 optimizer, rather than introducing a no-op predicate. See Optimization profiles and guidelines.