Reduce "X = X" to "X IS NOT NULL", if it's easy to do so.
If the operator is a strict btree equality operator, and X isn't volatile, then the clause must yield true for any non-null value of X, or null if X is null. At top level of a WHERE clause, we can ignore the distinction between false and null results, so it's valid to simplify the clause to "X IS NOT NULL". This is a useful improvement mainly because we'll get a far better selectivity estimate in most cases. Because such cases seldom arise in well-written queries, it is unappetizing to expend a lot of planner cycles looking for them ... but it turns out that there's a place we can shoehorn this in practically for free, because equivclass.c already has to detect and reject candidate equivalences of the form X = X. That doesn't catch every place that it would be valid to simplify to X IS NOT NULL, but it catches the typical case. Working harder doesn't seem justified. Patch by me, reviewed by Petr Jelinek Discussion: https://postgr.es/m/camjna7cc4x9yr-vajs-jsycajhrdvjqnn7m2slh1wlh-_z2...@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/8ec5429e2f422f4d570d4909507db0d4ca83bbac Modified Files -------------- src/backend/optimizer/path/equivclass.c | 66 +++++++++++++++++++++++++------- src/backend/optimizer/plan/initsplan.c | 5 ++- src/include/optimizer/paths.h | 3 +- src/test/regress/expected/equivclass.out | 18 +++++++++ src/test/regress/sql/equivclass.sql | 8 ++++ 5 files changed, 83 insertions(+), 17 deletions(-) -- Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-committers