On Mon, 22 Jan 2024 at 17:32, Peter Smith <smithpb2...@gmail.com> wrote:
> Hi, This patch has a CF status of "Needs Review" [1], but it seems
> there were CFbot test failures last time it was run [2].
I've attached v11 which updates the expected results in some newly
added regression tests.
No other changes.
David
From 095744f5583ab5446c1cdb75bfd3b40c7ab493d8 Mon Sep 17 00:00:00 2001
From: David Rowley <dgrow...@gmail.com>
Date: Thu, 7 Dec 2023 22:52:34 +1300
Subject: [PATCH v11] 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 | 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, 663 insertions(+), 73 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 d83f6ae8cb..b5a38aeb21 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 90c8fa4b70..f410c3db4e 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 a2c2df39da..30e8434287 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 585e9e06af..5fb0c17630 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 9fab52c58f..b933eefa64 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -163,6 +163,25 @@ 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);
+
+ /*
+ * Per RemoveAttributeById(), dropped columns will have
their
+ * attnotnull unset, so we needn't check for dropped
columns in
+ * the above condition.
+ */
+ Assert(!attr->attisdropped);
+ }
+ }
+
/*
* 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 22d01cef5b..e5f4062bfb 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -222,6 +222,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;
@@ -719,6 +720,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;
@@ -917,6 +919,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 137da178dc..534692bee1 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 */
@@ -2598,7 +2600,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 e0c578b996..f2e3fa4c2e 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 a2fad81d7a..9c08d0134c 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 removes the whole PHVs correctly
explain (verbose, costs off)
@@ -6828,8 +6826,8 @@ select 1 from emp1 t1 left join
(select * from emp1 t3) s2 on s1.id = s2.id)
on true
where s1.x = 1;
- QUERY PLAN
----------------------------------------------------------
+ QUERY PLAN
+----------------------------------------
Nested Loop
Output: 1
-> Seq Scan on public.emp1 t1
@@ -6838,7 +6836,7 @@ where s1.x = 1;
Output: t3.id
-> Seq Scan on public.emp1 t3
Output: t3.id
- Filter: ((t3.id IS NOT NULL) AND (1 = 1))
+ Filter: (1 = 1)
(9 rows)
-- Check that PHVs do not impose any constraints on removing self joins
@@ -6851,11 +6849,10 @@ select * from emp1 t1 join emp1 t2 on t1.id = t2.id
left join
Output: t2.id, t2.code, t2.id, t2.code, (t2.id), t3.t3
-> Seq Scan on public.emp1 t2
Output: t2.id, t2.code
- Filter: (t2.id IS NOT NULL)
-> Function Scan on pg_catalog.generate_series t3
Output: t3.t3, t2.id
Function Call: generate_series(1, 1)
-(8 rows)
+(7 rows)
explain (verbose, costs off)
select * from generate_series(1,10) t1(id) left join
@@ -6870,8 +6867,7 @@ on true;
Function Call: generate_series(1, 10)
-> Seq Scan on public.emp1 t3
Output: t3.id, t1.id
- Filter: (t3.id IS NOT NULL)
-(8 rows)
+(7 rows)
-- Check that SJE replaces join clauses involving the removed rel correctly
explain (costs off)
@@ -6883,10 +6879,9 @@ select * from emp1 t1
Nested Loop Left Join
Join Filter: ((t2.id > 1) AND (t2.id < 2))
-> Seq Scan on emp1 t2
- Filter: (id IS NOT NULL)
-> Materialize
-> Seq Scan on emp1 t3
-(6 rows)
+(5 rows)
-- Check that SJE doesn't replace the target relation
explain (costs off)
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
