(Moving discussion from -bugs [1] to -hackers for more visibility.)
Background:
This started out as a performance fix for bug #17540 but has now
extended beyond that as fixing that only requires we don't add
redundant IS NOT NULL quals to Min/Max aggregate rewrites. The
attached gets rid of all IS NOT NULL quals on columns that are
provably not null and replaces any IS NULL quals on NOT NULL columns
with a const-false gating qual which could result in not having to
scan the relation at all.
explain (costs off) select * from pg_class where oid is null;
QUERY PLAN
--------------------------
Result
One-Time Filter: false
The need for this is slightly higher than it once was as the self-join
removal code must add IS NOT NULL quals when removing self-joins when
the join condition is strict.
explain select c1.* from pg_class c1 inner join pg_class c2 on c1.oid=c2.oid;
QUERY PLAN
----------------------------------------------------------------
Seq Scan on pg_class c2 (cost=0.00..18.15 rows=415 width=273)
master would contain an oid IS NOT NULL filter condition.
On Fri, 1 Dec 2023 at 23:07, Richard Guo <guofengli...@gmail.com> wrote:
>
>
> On Wed, Nov 29, 2023 at 8:48 AM David Rowley <dgrowle...@gmail.com> wrote:
>> On looking deeper, I see you're overwriting the rinfo_serial of the
>> const-false RestrictInfo with the one from the original RestrictInfo.
>> If that's the correct thing to do then the following comment would
>> need to be updated to mention this exception of why the rinfo_serial
>> isn't unique.
>
>
> Right, that's what we need to do.
>
>>
>> Looking at the tests, I see:
>>
>> select * from pred_tab t1 left join pred_tab t2 on true left join
>> pred_tab t3 on t2.a is null;
>>
>> I'm wondering if you can come up with a better test for this? I don't
>> quite see any reason why varnullingrels can't be empty for t2.a in the
>> join qual as the "ON true" join condition between t1 and t2 means that
>> there shouldn't ever be any NULL t2.a rows. My thoughts are that if
>> we improve how varnullingrels are set in the future then this test
>> will be broken.
>>
>> Also, I also like to write exactly what each test is testing so that
>> it's easier in the future to maintain the expected results. It's
>> often tricky when making planner changes to know if some planner
>> changes makes a test completely useless or if the expected results
>> just need to be updated. If someone changes varnullingrels to be
>> empty for this case, then if they accept the actual results as
>> expected results then the test becomes useless. I tend to do this
>> with comments in the .sql file along the lines of "-- Ensure ..."
>>
>> I also would rather see the SQLs in the test wrap their lines before
>> each join and the keywords to be upper case.
>
>
> Thanks for the suggestions on the tests. I had a go at improving the
> test queries and their comments.
Thanks. I made a pass over this patch which resulted in just adding
and tweaking some comments.
The other thing that bothers me about this patch now is the lack of
simplification of OR clauses with a redundant condition. For example:
postgres=# explain (costs off) select * from pg_class where oid is
null or relname = 'non-existent';
QUERY PLAN
---------------------------------------------------------------------
Bitmap Heap Scan on pg_class
Recheck Cond: ((oid IS NULL) OR (relname = 'non-existant'::name))
-> BitmapOr
-> Bitmap Index Scan on pg_class_oid_index
Index Cond: (oid IS NULL)
-> Bitmap Index Scan on pg_class_relname_nsp_index
Index Cond: (relname = 'non-existant'::name)
(7 rows)
oid is null is const-false and if we simplified that to remove the
redundant OR branch and run it through the constant folding code, we'd
end up with just the relname = 'non-existent' and we'd end up with a
more simple plan as a result.
I don't think that's a blocker. I think the patch is ready to go even
without doing anything to improve that.
Happy to hear other people's thoughts on this patch. Otherwise, I
currently don't think the missed optimisation is a reason to block
what we've ended up with so far.
David
[1] https://postgr.es/m/flat/17540-7aa1855ad5ec18b4%40postgresql.org
From e690e0f622f69e6896f16f1729ff1b531b65ef4e Mon Sep 17 00:00:00 2001
From: David Rowley <dgrowley@gmail.com>
Date: Thu, 7 Dec 2023 22:52:34 +1300
Subject: [PATCH v10] Reduce NullTest quals to constant TRUE or FALSE
---
.../postgres_fdw/expected/postgres_fdw.out | 16 +-
contrib/postgres_fdw/sql/postgres_fdw.sql | 4 +-
src/backend/optimizer/plan/initsplan.c | 202 +++++++++++++--
src/backend/optimizer/util/joininfo.c | 28 ++
src/backend/optimizer/util/plancat.c | 10 +
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 | 52 ++--
src/test/regress/expected/predicate.out | 244 ++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/predicate.sql | 122 +++++++++
13 files changed, 648 insertions(+), 64 deletions(-)
create mode 100644 src/test/regress/expected/predicate.out
create mode 100644 src/test/regress/sql/predicate.sql
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index c988745b92..bc231c15a2 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -656,20 +656,20 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 =
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" = 100)) AND ((c2 = 0))
(3 rows)
-EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest
- QUERY PLAN
--------------------------------------------------------------------------------------------------
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS NULL; -- NullTest
+ QUERY PLAN
+----------------------------------------------------------------------------------------------
Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7, c8
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" IS NULL))
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c3 IS NULL))
(3 rows)
-EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest
- QUERY PLAN
------------------------------------------------------------------------------------------------------
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS NOT NULL; -- NullTest
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------
Foreign Scan on public.ft1 t1
Output: c1, c2, c3, c4, c5, c6, c7, c8
- Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" IS NOT NULL))
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE ((c3 IS NOT NULL))
(3 rows)
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index cb40540702..731201f232 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -332,8 +332,8 @@ EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft_empty ORDER BY c1;
-- ===================================================================
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 1; -- Var, OpExpr(b), Const
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE t1.c1 = 100 AND t1.c2 = 0; -- BoolExpr
-EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NULL; -- NullTest
-EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 IS NOT NULL; -- NullTest
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS NULL; -- NullTest
+EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c3 IS NOT NULL; -- NullTest
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE round(abs(c1), 0) = 1; -- FuncExpr
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE c1 = -c1; -- OpExpr(l)
EXPLAIN (VERBOSE, COSTS OFF) SELECT * FROM ft1 t1 WHERE (c1 IS NOT NULL) IS DISTINCT FROM (c1 IS NOT NULL); -- DistinctExpr
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index 8295e7753d..83bdc8b23a 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2618,6 +2618,192 @@ check_redundant_nullability_qual(PlannerInfo *root, Node *clause)
return false;
}
+/*
+ * add_base_clause_to_rel
+ * Add 'restrictinfo' as a baserestrictinfo to the base relation denoted
+ * by 'relid' with some prechecks to try to determine if the qual is
+ * always true, in which case we ignore it rather than add it, or if the
+ * qual is always false, in which case we replace it with constant-FALSE.
+ */
+static void
+add_base_clause_to_rel(PlannerInfo *root, Index relid,
+ RestrictInfo *restrictinfo)
+{
+ RelOptInfo *rel = find_base_rel(root, relid);
+
+ Assert(bms_membership(restrictinfo->required_relids) == BMS_SINGLETON);
+
+ /* Don't add the clause if it is always true */
+ if (restriction_is_always_true(root, restrictinfo))
+ return;
+
+ /*
+ * Substitute the origin qual with constant-FALSE if it is provably always
+ * false. Note that we keep the same rinfo_serial.
+ */
+ if (restriction_is_always_false(root, restrictinfo))
+ {
+ int save_rinfo_serial = restrictinfo->rinfo_serial;
+
+ restrictinfo = make_restrictinfo(root,
+ (Expr *) makeBoolConst(false, false),
+ restrictinfo->is_pushed_down,
+ restrictinfo->has_clone,
+ restrictinfo->is_clone,
+ restrictinfo->pseudoconstant,
+ 0, /* security_level */
+ restrictinfo->required_relids,
+ restrictinfo->incompatible_relids,
+ restrictinfo->outer_relids);
+ restrictinfo->rinfo_serial = save_rinfo_serial;
+ }
+
+ /* Add clause to rel's restriction list */
+ rel->baserestrictinfo = lappend(rel->baserestrictinfo, restrictinfo);
+
+ /* Update security level info */
+ rel->baserestrict_min_security = Min(rel->baserestrict_min_security,
+ restrictinfo->security_level);
+}
+
+/*
+ * expr_is_nonnullable
+ * Check to see if the Expr cannot be NULL
+ *
+ * If the Expr is a simple Var that is defined NOT NULL and meanwhile is not
+ * nulled by any outer joins, then we can know that it cannot be NULL.
+ */
+static bool
+expr_is_nonnullable(PlannerInfo *root, Expr *expr)
+{
+ RelOptInfo *rel;
+ Var *var;
+
+ /* For now only check simple Vars */
+ if (!IsA(expr, Var))
+ return false;
+
+ var = (Var *) expr;
+
+ /* could the Var be nulled by any outer joins? */
+ if (!bms_is_empty(var->varnullingrels))
+ return false;
+
+ /* system columns cannot be NULL */
+ if (var->varattno < 0)
+ return true;
+
+ /* is the column defined NOT NULL? */
+ rel = find_base_rel(root, var->varno);
+ if (var->varattno > 0 &&
+ bms_is_member(var->varattno, rel->notnullattnums))
+ return true;
+
+ return false;
+}
+
+/*
+ * restriction_is_always_true
+ * Check to see if the RestrictInfo is always true.
+ *
+ * Currently we only check for NullTest quals and OR clauses that include
+ * NullTest quals. We may extend it in the future.
+ */
+bool
+restriction_is_always_true(PlannerInfo *root,
+ RestrictInfo *restrictinfo)
+{
+ /* Check for NullTest qual */
+ if (IsA(restrictinfo->clause, NullTest))
+ {
+ NullTest *nulltest = (NullTest *) restrictinfo->clause;
+
+ /* is this NullTest an IS_NOT_NULL qual? */
+ if (nulltest->nulltesttype != IS_NOT_NULL)
+ return false;
+
+ return expr_is_nonnullable(root, nulltest->arg);
+ }
+
+ /* If it's an OR, check its sub-clauses */
+ if (restriction_is_or_clause(restrictinfo))
+ {
+ ListCell *lc;
+
+ Assert(is_orclause(restrictinfo->orclause));
+
+ /*
+ * if any of the given OR branches is provably always true then the
+ * entire condition is true.
+ */
+ foreach(lc, ((BoolExpr *) restrictinfo->orclause)->args)
+ {
+ Node *orarg = (Node *) lfirst(lc);
+
+ if (!IsA(orarg, RestrictInfo))
+ continue;
+
+ if (restriction_is_always_true(root, (RestrictInfo *) orarg))
+ return true;
+ }
+ }
+
+ return false;
+}
+
+/*
+ * restriction_is_always_false
+ * Check to see if the RestrictInfo is always false.
+ *
+ * Currently we only check for NullTest quals and OR clauses that include
+ * NullTest quals. We may extend it in the future.
+ */
+bool
+restriction_is_always_false(PlannerInfo *root,
+ RestrictInfo *restrictinfo)
+{
+ /* Check for NullTest qual */
+ if (IsA(restrictinfo->clause, NullTest))
+ {
+ NullTest *nulltest = (NullTest *) restrictinfo->clause;
+
+ /* is this NullTest an IS_NULL qual? */
+ if (nulltest->nulltesttype != IS_NULL)
+ return false;
+
+ return expr_is_nonnullable(root, nulltest->arg);
+ }
+
+ /* If it's an OR, check its sub-clauses */
+ if (restriction_is_or_clause(restrictinfo))
+ {
+ ListCell *lc;
+
+ Assert(is_orclause(restrictinfo->orclause));
+
+ /*
+ * Currently, when processing OR expressions, we only return true when
+ * all of the OR branches are always false. This could perhaps be
+ * expanded to remove OR branches that are provably false. This may
+ * be a useful thing to do as it could result in the OR being left
+ * with a single arg. That's useful as it would allow the OR
+ * condition to be replaced with its single argument which may allow
+ * use of an index for faster filtering on the remaining condition.
+ */
+ foreach(lc, ((BoolExpr *) restrictinfo->orclause)->args)
+ {
+ Node *orarg = (Node *) lfirst(lc);
+
+ if (!IsA(orarg, RestrictInfo) ||
+ !restriction_is_always_false(root, (RestrictInfo *) orarg))
+ return false;
+ }
+ return true;
+ }
+
+ return false;
+}
+
/*
* distribute_restrictinfo_to_rels
* Push a completed RestrictInfo into the proper restriction or join
@@ -2632,27 +2818,13 @@ distribute_restrictinfo_to_rels(PlannerInfo *root,
RestrictInfo *restrictinfo)
{
Relids relids = restrictinfo->required_relids;
- RelOptInfo *rel;
if (!bms_is_empty(relids))
{
int relid;
if (bms_get_singleton_member(relids, &relid))
- {
- /*
- * There is only one relation participating in the clause, so it
- * is a restriction clause for that relation.
- */
- rel = find_base_rel(root, relid);
-
- /* Add clause to rel's restriction list */
- rel->baserestrictinfo = lappend(rel->baserestrictinfo,
- restrictinfo);
- /* Update security level info */
- rel->baserestrict_min_security = Min(rel->baserestrict_min_security,
- restrictinfo->security_level);
- }
+ add_base_clause_to_rel(root, relid, restrictinfo);
else
{
/*
diff --git a/src/backend/optimizer/util/joininfo.c b/src/backend/optimizer/util/joininfo.c
index 968a5a488e..926f77b5e1 100644
--- a/src/backend/optimizer/util/joininfo.c
+++ b/src/backend/optimizer/util/joininfo.c
@@ -14,9 +14,12 @@
*/
#include "postgres.h"
+#include "nodes/makefuncs.h"
#include "optimizer/joininfo.h"
#include "optimizer/pathnode.h"
#include "optimizer/paths.h"
+#include "optimizer/planmain.h"
+#include "optimizer/restrictinfo.h"
/*
@@ -98,6 +101,31 @@ add_join_clause_to_rels(PlannerInfo *root,
{
int cur_relid;
+ /* Don't add the clause if it is always true */
+ if (restriction_is_always_true(root, restrictinfo))
+ return;
+
+ /*
+ * Substitute constant-FALSE for the origin qual if it is always false.
+ * Note that we keep the same rinfo_serial.
+ */
+ if (restriction_is_always_false(root, restrictinfo))
+ {
+ int save_rinfo_serial = restrictinfo->rinfo_serial;
+
+ restrictinfo = make_restrictinfo(root,
+ (Expr *) makeBoolConst(false, false),
+ restrictinfo->is_pushed_down,
+ restrictinfo->has_clone,
+ restrictinfo->is_clone,
+ restrictinfo->pseudoconstant,
+ 0, /* security_level */
+ restrictinfo->required_relids,
+ restrictinfo->incompatible_relids,
+ restrictinfo->outer_relids);
+ restrictinfo->rinfo_serial = save_rinfo_serial;
+ }
+
cur_relid = -1;
while ((cur_relid = bms_next_member(join_relids, cur_relid)) >= 0)
{
diff --git a/src/backend/optimizer/util/plancat.c b/src/backend/optimizer/util/plancat.c
index 7159c775fb..7b8da0fe31 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -163,6 +163,16 @@ get_relation_info(PlannerInfo *root, Oid relationObjectId, bool inhparent,
rel->attr_widths = (int32 *)
palloc0((rel->max_attr - rel->min_attr + 1) * sizeof(int32));
+ /* record which columns are defined as NOT NULL */
+ for (int i = 0; i < relation->rd_att->natts; i++)
+ {
+ FormData_pg_attribute *attr = &relation->rd_att->attrs[i];
+
+ if (attr->attnotnull)
+ rel->notnullattnums = bms_add_member(rel->notnullattnums,
+ attr->attnum);
+ }
+
/*
* Estimate relation size --- unless it's an inheritance parent, in which
* case the size we want is not the rel's own size but the size of its
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 5d83f60eb9..4edccd6da6 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -221,6 +221,7 @@ build_simple_rel(PlannerInfo *root, int relid, RelOptInfo *parent)
rel->relid = relid;
rel->rtekind = rte->rtekind;
/* min_attr, max_attr, attr_needed, attr_widths are set below */
+ rel->notnullattnums = NULL;
rel->lateral_vars = NIL;
rel->indexlist = NIL;
rel->statlist = NIL;
@@ -705,6 +706,7 @@ build_join_rel(PlannerInfo *root,
joinrel->max_attr = 0;
joinrel->attr_needed = NULL;
joinrel->attr_widths = NULL;
+ joinrel->notnullattnums = NULL;
joinrel->nulling_relids = NULL;
joinrel->lateral_vars = NIL;
joinrel->lateral_referencers = NULL;
@@ -903,6 +905,7 @@ build_child_join_rel(PlannerInfo *root, RelOptInfo *outer_rel,
joinrel->max_attr = 0;
joinrel->attr_needed = NULL;
joinrel->attr_widths = NULL;
+ joinrel->notnullattnums = NULL;
joinrel->nulling_relids = NULL;
joinrel->lateral_vars = NIL;
joinrel->lateral_referencers = NULL;
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index ed85dc7414..2c1623d520 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -913,6 +913,8 @@ typedef struct RelOptInfo
Relids *attr_needed pg_node_attr(read_write_ignore);
/* array indexed [min_attr .. max_attr] */
int32 *attr_widths pg_node_attr(read_write_ignore);
+ /* zero-based set containing attnums of NOT NULL columns */
+ Bitmapset *notnullattnums;
/* relids of outer joins that can null this baserel */
Relids nulling_relids;
/* LATERAL Vars and PHVs referenced by rel */
@@ -2588,7 +2590,10 @@ typedef struct RestrictInfo
* 2. If we manufacture a commuted version of a qual to use as an index
* condition, it copies the original's rinfo_serial, since it is in
* practice the same condition.
- * 3. RestrictInfos made for a child relation copy their parent's
+ * 3. If we reduce a qual to constant-FALSE, the new constant-FALSE qual
+ * copies the original's rinfo_serial, since it is in practice the same
+ * condition.
+ * 4. RestrictInfos made for a child relation copy their parent's
* rinfo_serial. Likewise, when an EquivalenceClass makes a derived
* equality clause for a child relation, it copies the rinfo_serial of
* the matching equality clause for the parent. This allows detection
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index 2bc857745a..a4edd2f61d 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -76,6 +76,10 @@ extern void add_vars_to_targetlist(PlannerInfo *root, List *vars,
extern void find_lateral_references(PlannerInfo *root);
extern void create_lateral_join_info(PlannerInfo *root);
extern List *deconstruct_jointree(PlannerInfo *root);
+extern bool restriction_is_always_true(PlannerInfo *root,
+ RestrictInfo *restrictinfo);
+extern bool restriction_is_always_false(PlannerInfo *root,
+ RestrictInfo *restrictinfo);
extern void distribute_restrictinfo_to_rels(PlannerInfo *root,
RestrictInfo *restrictinfo);
extern RestrictInfo *process_implied_equality(PlannerInfo *root,
diff --git a/src/test/regress/expected/equivclass.out b/src/test/regress/expected/equivclass.out
index de71441052..3d5de28354 100644
--- a/src/test/regress/expected/equivclass.out
+++ b/src/test/regress/expected/equivclass.out
@@ -438,15 +438,14 @@ set enable_mergejoin to off;
explain (costs off)
select * from ec0 m join ec0 n on m.ff = n.ff
join ec1 p on m.ff + n.ff = p.f1;
- QUERY PLAN
-----------------------------------------
+ QUERY PLAN
+---------------------------------------
Nested Loop
Join Filter: ((n.ff + n.ff) = p.f1)
- -> Seq Scan on ec1 p
+ -> Seq Scan on ec0 n
-> Materialize
- -> Seq Scan on ec0 n
- Filter: (ff IS NOT NULL)
-(6 rows)
+ -> Seq Scan on ec1 p
+(5 rows)
explain (costs off)
select * from ec0 m join ec0 n on m.ff = n.ff
@@ -455,11 +454,10 @@ explain (costs off)
---------------------------------------------------------------
Nested Loop
Join Filter: ((p.f1)::bigint = ((n.ff + n.ff))::int8alias1)
- -> Seq Scan on ec1 p
+ -> Seq Scan on ec0 n
-> Materialize
- -> Seq Scan on ec0 n
- Filter: (ff IS NOT NULL)
-(6 rows)
+ -> Seq Scan on ec1 p
+(5 rows)
reset enable_mergejoin;
-- this could be converted, but isn't at present
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 2c73270143..24d3004095 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -6357,14 +6357,14 @@ SELECT * FROM pg_am am WHERE am.amname IN (
JOIN pg_class c2
ON c1.oid=c2.oid AND c1.oid < 10
);
- QUERY PLAN
----------------------------------------------------------------------
+ QUERY PLAN
+----------------------------------------------------------------
Nested Loop Semi Join
Join Filter: (am.amname = c2.relname)
-> Seq Scan on pg_am am
-> Materialize
-> Index Scan using pg_class_oid_index on pg_class c2
- Index Cond: ((oid < '10'::oid) AND (oid IS NOT NULL))
+ Index Cond: (oid < '10'::oid)
(6 rows)
--
@@ -6619,14 +6619,14 @@ SELECT COUNT(*) FROM tab_with_flag
WHERE
(is_flag IS NULL OR is_flag = 0)
AND id IN (SELECT id FROM tab_with_flag WHERE id IN (2, 3));
- QUERY PLAN
-----------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------
Aggregate
-> Bitmap Heap Scan on tab_with_flag
- Recheck Cond: ((id = ANY ('{2,3}'::integer[])) AND (id IS NOT NULL))
+ Recheck Cond: (id = ANY ('{2,3}'::integer[]))
Filter: ((is_flag IS NULL) OR (is_flag = 0))
-> Bitmap Index Scan on tab_with_flag_pkey
- Index Cond: ((id = ANY ('{2,3}'::integer[])) AND (id IS NOT NULL))
+ Index Cond: (id = ANY ('{2,3}'::integer[]))
(6 rows)
DROP TABLE tab_with_flag;
@@ -6745,11 +6745,11 @@ reset enable_seqscan;
CREATE TABLE emp1 ( id SERIAL PRIMARY KEY NOT NULL, code int);
explain (verbose, costs off)
SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code;
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+------------------------------------------
Seq Scan on public.emp1 e2
Output: e2.id, e2.code, e2.id, e2.code
- Filter: ((e2.id IS NOT NULL) AND (e2.code <> e2.code))
+ Filter: (e2.code <> e2.code)
(3 rows)
-- Shuffle self-joined relations. Only in the case of iterative deletion
@@ -6758,31 +6758,31 @@ CREATE UNIQUE INDEX ON emp1((id*id));
explain (costs off)
SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
WHERE c1.id=c2.id AND c1.id*c2.id=c3.id*c3.id;
- QUERY PLAN
-----------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------
Aggregate
-> Seq Scan on emp1 c3
- Filter: ((id IS NOT NULL) AND ((id * id) IS NOT NULL))
+ Filter: ((id * id) IS NOT NULL)
(3 rows)
explain (costs off)
SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
WHERE c1.id=c3.id AND c1.id*c3.id=c2.id*c2.id;
- QUERY PLAN
-----------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------
Aggregate
-> Seq Scan on emp1 c3
- Filter: ((id IS NOT NULL) AND ((id * id) IS NOT NULL))
+ Filter: ((id * id) IS NOT NULL)
(3 rows)
explain (costs off)
SELECT count(*) FROM emp1 c1, emp1 c2, emp1 c3
WHERE c3.id=c2.id AND c3.id*c2.id=c1.id*c1.id;
- QUERY PLAN
-----------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------
Aggregate
-> Seq Scan on emp1 c3
- Filter: ((id IS NOT NULL) AND ((id * id) IS NOT NULL))
+ Filter: ((id * id) IS NOT NULL)
(3 rows)
-- Check the usage of a parse tree by the set operations (bug #18170)
@@ -6791,16 +6791,15 @@ SELECT c1.code FROM emp1 c1 LEFT JOIN emp1 c2 ON c1.id = c2.id
WHERE c2.id IS NOT NULL
EXCEPT ALL
SELECT c3.code FROM emp1 c3;
- QUERY PLAN
-----------------------------------------------
+ QUERY PLAN
+-------------------------------------------
HashSetOp Except All
-> Append
-> Subquery Scan on "*SELECT* 1"
-> Seq Scan on emp1 c2
- Filter: (id IS NOT NULL)
-> Subquery Scan on "*SELECT* 2"
-> Seq Scan on emp1 c3
-(7 rows)
+(6 rows)
-- Check that SJE removes references from PHVs correctly
explain (costs off)
@@ -6809,8 +6808,8 @@ select * from emp1 t1 left join
left join (emp1 t3 join emp1 t4 on t3.id = t4.id)
on true)
on true;
- QUERY PLAN
-----------------------------------------------------
+ QUERY PLAN
+---------------------------------------------
Nested Loop Left Join
-> Seq Scan on emp1 t1
-> Materialize
@@ -6818,8 +6817,7 @@ on true;
-> Seq Scan on emp1 t2
-> Materialize
-> Seq Scan on emp1 t4
- Filter: (id IS NOT NULL)
-(8 rows)
+(7 rows)
-- Check that SJE does not remove self joins if a PHV references the removed
-- rel laterally.
diff --git a/src/test/regress/expected/predicate.out b/src/test/regress/expected/predicate.out
new file mode 100644
index 0000000000..395ffb0fcd
--- /dev/null
+++ b/src/test/regress/expected/predicate.out
@@ -0,0 +1,244 @@
+--
+-- Tests for predicate handling
+--
+--
+-- test that restrictions that are always true are ignored, and that are always
+-- false are replaced with constant-FALSE
+--
+-- currently we only check for NullTest quals and OR clauses that include
+-- NullTest quals. We may extend it in the future.
+--
+CREATE TABLE pred_tab (a int NOT NULL, b int, c int NOT NULL);
+--
+-- Test restriction clauses
+--
+-- Ensure the IS_NOT_NULL qual is ignored when the column is non-nullable
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.a IS NOT NULL;
+ QUERY PLAN
+------------------------
+ Seq Scan on pred_tab t
+(1 row)
+
+-- Ensure the IS_NOT_NULL qual is not ignored on a nullable column
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.b IS NOT NULL;
+ QUERY PLAN
+---------------------------
+ Seq Scan on pred_tab t
+ Filter: (b IS NOT NULL)
+(2 rows)
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE for non-nullable
+-- columns
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.a IS NULL;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+-- Ensure the IS_NULL qual is not reduced to constant-FALSE on nullable
+-- columns
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.b IS NULL;
+ QUERY PLAN
+------------------------
+ Seq Scan on pred_tab t
+ Filter: (b IS NULL)
+(2 rows)
+
+--
+-- Tests for OR clauses in restriction clauses
+--
+-- Ensure the OR clause is ignored when an OR branch is always true
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.a IS NOT NULL OR t.b = 1;
+ QUERY PLAN
+------------------------
+ Seq Scan on pred_tab t
+(1 row)
+
+-- Ensure the OR clause is not ignored for NullTests that can't be proven
+-- always true
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.b IS NOT NULL OR t.a = 1;
+ QUERY PLAN
+----------------------------------------
+ Seq Scan on pred_tab t
+ Filter: ((b IS NOT NULL) OR (a = 1))
+(2 rows)
+
+-- Ensure the OR clause is reduced to constant-FALSE when all branches are
+-- provably false
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.a IS NULL OR t.c IS NULL;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+-- Ensure the OR clause is not reduced to constant-FALSE when not all branches
+-- are provably false
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.b IS NULL OR t.c IS NULL;
+ QUERY PLAN
+----------------------------------------
+ Seq Scan on pred_tab t
+ Filter: ((b IS NULL) OR (c IS NULL))
+(2 rows)
+
+--
+-- Test join clauses
+--
+-- Ensure the IS_NOT_NULL qual is ignored, since a) it's on a NOT NULL column,
+-- and b) its Var is not nullable by any outer joins
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
+ QUERY PLAN
+-------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on pred_tab t1
+ -> Materialize
+ -> Nested Loop Left Join
+ -> Seq Scan on pred_tab t2
+ -> Materialize
+ -> Seq Scan on pred_tab t3
+(7 rows)
+
+-- Ensure the IS_NOT_NULL qual is not ignored when columns are made nullable
+-- by an outer join
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON t1.a = 1
+ LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
+ QUERY PLAN
+-------------------------------------------
+ Nested Loop Left Join
+ Join Filter: (t2.a IS NOT NULL)
+ -> Nested Loop Left Join
+ Join Filter: (t1.a = 1)
+ -> Seq Scan on pred_tab t1
+ -> Materialize
+ -> Seq Scan on pred_tab t2
+ -> Materialize
+ -> Seq Scan on pred_tab t3
+(9 rows)
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE, since a) it's on a NOT
+-- NULL column, and b) its Var is not nullable by any outer joins
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab t3 ON t2.a IS NULL AND t2.b = 1;
+ QUERY PLAN
+---------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on pred_tab t1
+ -> Materialize
+ -> Nested Loop Left Join
+ Join Filter: (false AND (t2.b = 1))
+ -> Seq Scan on pred_tab t2
+ -> Result
+ One-Time Filter: false
+(8 rows)
+
+-- Ensure the IS_NULL qual is not reduced to constant-FALSE when the column is
+-- nullable by an outer join
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON t1.a = 1
+ LEFT JOIN pred_tab t3 ON t2.a IS NULL;
+ QUERY PLAN
+-------------------------------------------
+ Nested Loop Left Join
+ Join Filter: (t2.a IS NULL)
+ -> Nested Loop Left Join
+ Join Filter: (t1.a = 1)
+ -> Seq Scan on pred_tab t1
+ -> Materialize
+ -> Seq Scan on pred_tab t2
+ -> Materialize
+ -> Seq Scan on pred_tab t3
+(9 rows)
+
+--
+-- Tests for OR clauses in join clauses
+--
+-- Ensure the OR clause is ignored when an OR branch is provably always true
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
+ QUERY PLAN
+-------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on pred_tab t1
+ -> Materialize
+ -> Nested Loop Left Join
+ -> Seq Scan on pred_tab t2
+ -> Materialize
+ -> Seq Scan on pred_tab t3
+(7 rows)
+
+-- Ensure the NullTest is not ignored when the column is nullable by an outer
+-- join
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON t1.a = 1
+ LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
+ QUERY PLAN
+---------------------------------------------------
+ Nested Loop Left Join
+ Join Filter: ((t2.a IS NOT NULL) OR (t2.b = 1))
+ -> Nested Loop Left Join
+ Join Filter: (t1.a = 1)
+ -> Seq Scan on pred_tab t1
+ -> Materialize
+ -> Seq Scan on pred_tab t2
+ -> Materialize
+ -> Seq Scan on pred_tab t3
+(9 rows)
+
+-- Ensure the OR clause is reduced to constant-FALSE when all OR branches are
+-- provably false
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab t3 ON (t2.a IS NULL OR t2.c IS NULL) AND t2.b = 1;
+ QUERY PLAN
+---------------------------------------------------
+ Nested Loop Left Join
+ -> Seq Scan on pred_tab t1
+ -> Materialize
+ -> Nested Loop Left Join
+ Join Filter: (false AND (t2.b = 1))
+ -> Seq Scan on pred_tab t2
+ -> Result
+ One-Time Filter: false
+(8 rows)
+
+-- Ensure the OR clause is not reduced to constant-FALSE when a column is
+-- made nullable from an join join
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON t1.a = 1
+ LEFT JOIN pred_tab t3 ON t2.a IS NULL OR t2.c IS NULL;
+ QUERY PLAN
+---------------------------------------------------
+ Nested Loop Left Join
+ Join Filter: ((t2.a IS NULL) OR (t2.c IS NULL))
+ -> Nested Loop Left Join
+ Join Filter: (t1.a = 1)
+ -> Seq Scan on pred_tab t1
+ -> Materialize
+ -> Seq Scan on pred_tab t2
+ -> Materialize
+ -> Seq Scan on pred_tab t3
+(9 rows)
+
+DROP TABLE pred_tab;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index f0987ff537..6f5a33c234 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -119,7 +119,7 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion tr
# The stats test resets stats, so nothing else needing stats access can be in
# this group.
# ----------
-test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain compression memoize stats
+test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain compression memoize stats predicate
# event_trigger depends on create_am and cannot run concurrently with
# any test that runs DDL
diff --git a/src/test/regress/sql/predicate.sql b/src/test/regress/sql/predicate.sql
new file mode 100644
index 0000000000..338daf3255
--- /dev/null
+++ b/src/test/regress/sql/predicate.sql
@@ -0,0 +1,122 @@
+--
+-- Tests for predicate handling
+--
+
+--
+-- test that restrictions that are always true are ignored, and that are always
+-- false are replaced with constant-FALSE
+--
+-- currently we only check for NullTest quals and OR clauses that include
+-- NullTest quals. We may extend it in the future.
+--
+CREATE TABLE pred_tab (a int NOT NULL, b int, c int NOT NULL);
+
+--
+-- Test restriction clauses
+--
+
+-- Ensure the IS_NOT_NULL qual is ignored when the column is non-nullable
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.a IS NOT NULL;
+
+-- Ensure the IS_NOT_NULL qual is not ignored on a nullable column
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.b IS NOT NULL;
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE for non-nullable
+-- columns
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.a IS NULL;
+
+-- Ensure the IS_NULL qual is not reduced to constant-FALSE on nullable
+-- columns
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.b IS NULL;
+
+--
+-- Tests for OR clauses in restriction clauses
+--
+
+-- Ensure the OR clause is ignored when an OR branch is always true
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.a IS NOT NULL OR t.b = 1;
+
+-- Ensure the OR clause is not ignored for NullTests that can't be proven
+-- always true
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.b IS NOT NULL OR t.a = 1;
+
+-- Ensure the OR clause is reduced to constant-FALSE when all branches are
+-- provably false
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.a IS NULL OR t.c IS NULL;
+
+-- Ensure the OR clause is not reduced to constant-FALSE when not all branches
+-- are provably false
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t WHERE t.b IS NULL OR t.c IS NULL;
+
+--
+-- Test join clauses
+--
+
+-- Ensure the IS_NOT_NULL qual is ignored, since a) it's on a NOT NULL column,
+-- and b) its Var is not nullable by any outer joins
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
+
+-- Ensure the IS_NOT_NULL qual is not ignored when columns are made nullable
+-- by an outer join
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON t1.a = 1
+ LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL;
+
+-- Ensure the IS_NULL qual is reduced to constant-FALSE, since a) it's on a NOT
+-- NULL column, and b) its Var is not nullable by any outer joins
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab t3 ON t2.a IS NULL AND t2.b = 1;
+
+-- Ensure the IS_NULL qual is not reduced to constant-FALSE when the column is
+-- nullable by an outer join
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON t1.a = 1
+ LEFT JOIN pred_tab t3 ON t2.a IS NULL;
+
+--
+-- Tests for OR clauses in join clauses
+--
+
+-- Ensure the OR clause is ignored when an OR branch is provably always true
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
+
+-- Ensure the NullTest is not ignored when the column is nullable by an outer
+-- join
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON t1.a = 1
+ LEFT JOIN pred_tab t3 ON t2.a IS NOT NULL OR t2.b = 1;
+
+-- Ensure the OR clause is reduced to constant-FALSE when all OR branches are
+-- provably false
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON TRUE
+ LEFT JOIN pred_tab t3 ON (t2.a IS NULL OR t2.c IS NULL) AND t2.b = 1;
+
+-- Ensure the OR clause is not reduced to constant-FALSE when a column is
+-- made nullable from an join join
+EXPLAIN (COSTS OFF)
+SELECT * FROM pred_tab t1
+ LEFT JOIN pred_tab t2 ON t1.a = 1
+ LEFT JOIN pred_tab t3 ON t2.a IS NULL OR t2.c IS NULL;
+
+DROP TABLE pred_tab;
--
2.40.1
