From: David Rowley <drowley@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Add better handling of redundant IS [NOT] NULL quals
Date: Tue, 23 Jan 2024 05:09:43 +0000
Message-ID: <E1rS92d-002gbO-EJ@gemulon.postgresql.org> (raw)
Add better handling of redundant IS [NOT] NULL quals
Until now PostgreSQL has not been very smart about optimizing away IS
NOT NULL base quals on columns defined as NOT NULL. The evaluation of
these needless quals adds overhead. Ordinarily, anyone who came
complaining about that would likely just have been told to not include
the qual in their query if it's not required. However, a recent bug
report indicates this might not always be possible.
Bug 17540 highlighted that when we optimize Min/Max aggregates the IS NOT
NULL qual that the planner adds to make the rewritten plan ignore NULLs
can cause issues with poor index choice. That particular case
demonstrated that other quals, especially ones where no statistics are
available to allow the planner a chance at estimating an approximate
selectivity for can result in poor index choice due to cheap startup paths
being prefered with LIMIT 1.
Here we take generic approach to fixing this by having the planner check
for NOT NULL columns and just have the planner remove these quals (when
they're not needed) for all queries, not just when optimizing Min/Max
aggregates.
Additionally, here we also detect IS NULL quals on a NOT NULL column and
transform that into a gating qual so that we don't have to perform the
scan at all. This also works for join relations when the Var is not
nullable by any outer join.
This also helps with the self-join removal work as it must replace
strict join quals with IS NOT NULL quals to ensure equivalence with the
original query.
Author: David Rowley, Richard Guo, Andy Fan
Reviewed-by: Richard Guo, David Rowley
Discussion: https://postgr.es/m/CAApHDvqg6XZDhYRPz0zgOcevSMo0d3vxA9DvHrZtKfqO30WTnw@mail.gmail.com
Discussion: https://postgr.es/m/17540-7aa1855ad5ec18b4%40postgresql.org
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/b262ad440edecda0b1aba81d967ab560a83acb8a
Modified Files
--------------
contrib/postgres_fdw/expected/postgres_fdw.out | 16 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 4 +-
src/backend/optimizer/plan/initsplan.c | 197 +++++++++++++++++++-
src/backend/optimizer/util/joininfo.c | 28 +++
src/backend/optimizer/util/plancat.c | 19 ++
src/backend/optimizer/util/relnode.c | 3 +
src/include/nodes/pathnodes.h | 7 +-
src/include/optimizer/planmain.h | 4 +
src/test/regress/expected/equivclass.out | 18 +-
src/test/regress/expected/join.out | 67 ++++---
src/test/regress/expected/predicate.out | 244 +++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/predicate.sql | 122 +++++++++++++
13 files changed, 664 insertions(+), 67 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: drowley@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Add better handling of redundant IS [NOT] NULL quals
In-Reply-To: <E1rS92d-002gbO-EJ@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox