Simplify COALESCE expressions using non-nullable arguments The COALESCE function returns the first of its arguments that is not null. When an argument is proven non-null, if it is the first non-null-constant argument, the entire COALESCE expression can be replaced by that argument. If it is a subsequent argument, all following arguments can be dropped, since they will never be reached.
Currently, we perform this simplification only for Const arguments. This patch extends the simplification to support any expression that can be proven non-nullable. This can help avoid the overhead of evaluating unreachable arguments. It can also lead to better plans when the first argument is proven non-nullable and replaces the expression, as the planner no longer has to treat the expression as non-strict, and can also leverage index scans on the resulting expression. There is an ensuing plan change in generated_virtual.out, and we have to modify the test to ensure that it continues to test what it is intended to. Author: Richard Guo <[email protected]> Reviewed-by: Tender Wang <[email protected]> Reviewed-by: Dagfinn Ilmari Mannsåker <[email protected]> Reviewed-by: David Rowley <[email protected]> Reviewed-by: Matheus Alcantara <[email protected]> Discussion: https://postgr.es/m/cambws49uhpbjm+nrpxerjaeufkyuzj_ajm3nbcsyk2jgz6v...@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/10c4fe074a92eccf9fa6f7a39fe4d3f97b9b87e2 Modified Files -------------- src/backend/optimizer/util/clauses.c | 18 +++++++--- src/test/regress/expected/generated_virtual.out | 48 +++++++++++++------------ src/test/regress/expected/predicate.out | 33 +++++++++++++++++ src/test/regress/sql/generated_virtual.sql | 11 +++--- src/test/regress/sql/predicate.sql | 20 +++++++++++ 5 files changed, 97 insertions(+), 33 deletions(-)
