[prev in list] [next in list] [prev in thread] [next in thread]
List: pgsql-bugs
Subject: Re: BUG #17540: Prepared statement: PG switches to a generic query plan which is consistently much s
From: Andrei Lepikhov <a.lepikhov () postgrespro ! ru>
Date: 2023-10-24 4:25:12
Message-ID: 74a806ed-3b6c-4c45-a67a-27b5f3496ae5 () postgrespro ! ru
[Download RAW message or body]
On 8/10/2023 15:26, Richard Guo wrote:
Hi,
> On Thu, Sep 28, 2023 at 11:51 AM David Rowley <dgrowleyml@gmail.com
> <mailto:dgrowleyml@gmail.com>> wrote:
>
> On Thu, 28 Sept 2023 at 16:22, Richard Guo <guofenglinux@gmail.com
> <mailto:guofenglinux@gmail.com>> wrote:
> > It seems that optimizing IS NULL quals is more complex than
> optimizing
> > IS NOT NULL quals. I also wonder if it's worth the trouble to
> optimize
> > IS NULL quals.
>
> I'm happy to reduce the scope of this patch. As for what to cut, I
> think if we're doing a subset then we should try to do that subset in
> a way that best leaves things open for phase 2 at some later date.
>
>
> I had a go at supporting IS NULL quals and ended up with the attached.
> The patch generates a new constant-FALSE RestrictInfo that is marked
> with the same required_relids etc as the original one if it is an IS
> NULL qual that can be reduced to FALSE. Note that the original
> rinfo_serial is also copied to the new RestrictInfo.
>
> One thing that is not great is that we may have 'FALSE and otherquals'
> in the final plan, as shown by the plan below which is from the new
> added test case.
Setting aside the thread's subject, I am interested in this feature
because of its connection with the SJE feature and the same issue raised
[1] during the discussion.
In the attachment - rebased version of your patch (because of the
5d8aa8bced).
Although the patch is already in a good state, some improvements can be
made. Look:
explain (costs off)
SELECT oid,relname FROM pg_class
WHERE oid < 5 OR (oid = 1 AND oid IS NULL);
Bitmap Heap Scan on pg_class
Recheck Cond: ((oid < '5'::oid) OR ((oid = '1'::oid) AND (oid IS NULL)))
-> BitmapOr
-> Bitmap Index Scan on pg_class_oid_index
Index Cond: (oid < '5'::oid)
-> Bitmap Index Scan on pg_class_oid_index
Index Cond: ((oid = '1'::oid) AND (oid IS NULL))
If we go deeply through the filter, I guess we could replace such buried
clauses.
[1] Removing unneeded self joins
https://www.postgresql.org/message-id/CAPpHfdt-0kVV7O%3D%3DaJEbjY2iGYBu%2BXBzTHEbPv_6sVNeC7fffQ%40mail.gmail.com
--
regards,
Andrei Lepikhov
Postgres Professional
["v6-0001-Reduce-NullTest-quals-to-constant-TRUE-or-FALSE.patch" (text/plain)]
From 0a08ccbf376c1003c45ba8a555a39ae9c2f9c207 Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Sat, 7 Oct 2023 17:04:07 +0800
Subject: [PATCH] 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 | 243 ++++++++++++++----
src/backend/optimizer/util/joininfo.c | 25 ++
src/backend/optimizer/util/plancat.c | 9 +
src/backend/optimizer/util/relnode.c | 2 +
src/include/nodes/pathnodes.h | 2 +
src/include/optimizer/planmain.h | 4 +
src/test/regress/expected/predicate.out | 204 +++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/sql/predicate.sql | 80 ++++++
11 files changed, 533 insertions(+), 58 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 144c114d0f..4d7c5b9140 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 a303bfb322..8542a9e9de 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 b31d892121..06e73f4169 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -2618,6 +2618,174 @@ check_redundant_nullability_qual(PlannerInfo *root, Node \
*clause) return false;
}
+/*
+ * add_baserestrictinfo_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_baserestrictinfo_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 constant-FALSE for the origin qual if it is always false */
+ 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,
+ false, /* 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));
+ 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));
+ 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,58 +2800,39 @@ distribute_restrictinfo_to_rels(PlannerInfo *root,
RestrictInfo *restrictinfo)
{
Relids relids = restrictinfo->required_relids;
- RelOptInfo *rel;
+ int relid;
- switch (bms_membership(relids))
+ if (relids == NULL)
{
- case BMS_SINGLETON:
-
- /*
- * There is only one relation participating in the clause, so it
- * is a restriction clause for that relation.
- */
- rel = find_base_rel(root, bms_singleton_member(relids));
-
- /* 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);
- break;
- case BMS_MULTIPLE:
-
- /*
- * The clause is a join clause, since there is more than one rel
- * in its relid set.
- */
-
- /*
- * Check for hashjoinable operators. (We don't bother setting the
- * hashjoin info except in true join clauses.)
- */
- check_hashjoinable(restrictinfo);
+ /*
+ * clause references no rels, and therefore we have no place to
+ * attach it. Shouldn't get here if callers are working properly.
+ */
+ elog(ERROR, "cannot cope with variable-free clause");
+ }
+ else if (bms_get_singleton_member(relids, &relid))
+ add_baserestrictinfo_to_rel(root, relid, restrictinfo);
+ else
+ {
+ /*
+ * The clause is a join clause, since there is more than one rel in
+ * its relid set.
+ */
- /*
- * Likewise, check if the clause is suitable to be used with a
- * Memoize node to cache inner tuples during a parameterized
- * nested loop.
- */
- check_memoizable(restrictinfo);
+ /*
+ * Check for hashjoinable operators. (We don't bother setting the
+ * hashjoin info except in true join clauses.)
+ */
+ check_hashjoinable(restrictinfo);
- /*
- * Add clause to the join lists of all the relevant relations.
- */
- add_join_clause_to_rels(root, restrictinfo, relids);
- break;
- default:
+ /*
+ * Likewise, check if the clause is suitable to be used with a Memoize
+ * node to cache inner tuples during a parameterized nested loop.
+ */
+ check_memoizable(restrictinfo);
- /*
- * clause references no rels, and therefore we have no place to
- * attach it. Shouldn't get here if callers are working properly.
- */
- elog(ERROR, "cannot cope with variable-free clause");
- break;
+ /* Add clause to the join lists of all the relevant relations. */
+ add_join_clause_to_rels(root, restrictinfo, relids);
}
}
diff --git a/src/backend/optimizer/util/joininfo.c \
b/src/backend/optimizer/util/joininfo.c index 968a5a488e..b0b960a001 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,28 @@ 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 */
+ 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,
+ false, /* 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..0024b27edc 100644
--- a/src/backend/optimizer/util/plancat.c
+++ b/src/backend/optimizer/util/plancat.c
@@ -163,6 +163,15 @@ 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..6bb0df6535 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;
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 5702fbba60..d4f6bbcd2c 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 */
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index 31c188176b..60f2597cbe 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -75,6 +75,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/predicate.out \
b/src/test/regress/expected/predicate.out new file mode 100644
index 0000000000..47c8c1d2bd
--- /dev/null
+++ b/src/test/regress/expected/predicate.out
@@ -0,0 +1,204 @@
+--
+-- 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);
+-- An IS_NOT_NULL qual in restriction clauses can be ignored if it's on a NOT
+-- NULL column
+explain (costs off)
+select * from pred_tab t where t.a is not null;
+ QUERY PLAN
+------------------------
+ Seq Scan on pred_tab t
+(1 row)
+
+-- On the contrary, an IS_NOT_NULL qual in restriction clauses can not be
+-- ignored if it's not on a NOT NULL 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)
+
+-- An IS_NULL qual in restriction clauses can be reduced to constant-FALSE if
+-- it's on a NOT NULL column
+explain (costs off)
+select * from pred_tab t where t.a is null;
+ QUERY PLAN
+--------------------------
+ Result
+ One-Time Filter: false
+(2 rows)
+
+-- On the contrary, an IS_NULL qual in restriction clauses can not be reduced
+-- to constant-FALSE if it's not on a NOT NULL column
+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
+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)
+
+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)
+
+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)
+
+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)
+
+-- An IS_NOT_NULL qual in join clauses can be ignored if
+-- a) it's on a NOT NULL column, and
+-- b) its Var is not nulled 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)
+
+-- Otherwise the IS_NOT_NULL qual in join clauses cannot be ignored
+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)
+
+-- An IS_NULL qual in join clauses can be reduced to constant-FALSE if
+-- a) it's on a NOT NULL column, and
+-- b) its Var is not nulled 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)
+
+-- Otherwise the IS_NULL qual in join clauses cannot be reduced to constant-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; + QUERY PLAN
+-------------------------------------------
+ Nested Loop Left Join
+ Join Filter: (t2.a IS NULL)
+ -> Nested Loop Left Join
+ -> Seq Scan on pred_tab t1
+ -> Materialize
+ -> Seq Scan on pred_tab t2
+ -> Materialize
+ -> Seq Scan on pred_tab t3
+(8 rows)
+
+-- Tests for OR clauses in join clauses
+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)
+
+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)
+
+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)
+
+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; + QUERY PLAN
+---------------------------------------------------
+ Nested Loop Left Join
+ Join Filter: ((t2.a IS NULL) OR (t2.c IS NULL))
+ -> Nested Loop Left Join
+ -> Seq Scan on pred_tab t1
+ -> Materialize
+ -> Seq Scan on pred_tab t2
+ -> Materialize
+ -> Seq Scan on pred_tab t3
+(8 rows)
+
+drop table pred_tab;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 4df9d8503b..3816efc7b3 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..9d4336861f
--- /dev/null
+++ b/src/test/regress/sql/predicate.sql
@@ -0,0 +1,80 @@
+--
+-- 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);
+
+-- An IS_NOT_NULL qual in restriction clauses can be ignored if it's on a NOT
+-- NULL column
+explain (costs off)
+select * from pred_tab t where t.a is not null;
+
+-- On the contrary, an IS_NOT_NULL qual in restriction clauses can not be
+-- ignored if it's not on a NOT NULL column
+explain (costs off)
+select * from pred_tab t where t.b is not null;
+
+-- An IS_NULL qual in restriction clauses can be reduced to constant-FALSE if
+-- it's on a NOT NULL column
+explain (costs off)
+select * from pred_tab t where t.a is null;
+
+-- On the contrary, an IS_NULL qual in restriction clauses can not be reduced
+-- to constant-FALSE if it's not on a NOT NULL column
+explain (costs off)
+select * from pred_tab t where t.b is null;
+
+-- Tests for OR clauses in restriction clauses
+explain (costs off)
+select * from pred_tab t where t.a is not null or t.b = 1;
+
+explain (costs off)
+select * from pred_tab t where t.b is not null or t.a = 1;
+
+explain (costs off)
+select * from pred_tab t where t.a is null or t.c is null;
+
+explain (costs off)
+select * from pred_tab t where t.b is null or t.c is null;
+
+-- An IS_NOT_NULL qual in join clauses can be ignored if
+-- a) it's on a NOT NULL column, and
+-- b) its Var is not nulled 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; +
+-- Otherwise the IS_NOT_NULL qual in join clauses cannot be ignored
+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; +
+-- An IS_NULL qual in join clauses can be reduced to constant-FALSE if
+-- a) it's on a NOT NULL column, and
+-- b) its Var is not nulled 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; +
+-- Otherwise the IS_NULL qual in join clauses cannot be reduced to constant-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; +
+-- Tests for OR clauses in join clauses
+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; +
+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; +
+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; +
+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; +
+drop table pred_tab;
--
2.42.0
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic