From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | pgsql-committers(at)postgresql(dot)org |
Subject: | pgsql: Reduce "X = X" to "X IS NOT NULL", if it's easy to do so. |
Date: | 2017-10-08 16:23:41 |
Message-ID: | E1e1EMf-0004h3-0U@gemulon.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-committers |
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-_Z2bsw@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(-)
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Geoghegan | 2017-10-08 17:29:06 | Re: [COMMITTERS] pgsql: Fix freezing of a dead HOT-updated tuple |
Previous Message | Alvaro Herrera | 2017-10-07 23:25:24 | Re: [COMMITTERS] pgsql: Fix freezing of a dead HOT-updated tuple |