From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
To: Richard Guo <guofenglinux@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: william.duclot@gmail.com, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17540: Prepared statement: PG switches to a generic query plan which is consistently much slower
Date: Tue, 24 Oct 2023 11:25:12 +0700
Message-ID: <74a806ed-3b6c-4c45-a67a-27b5f3496ae5@postgrespro.ru> (raw)
In-Reply-To: <CAMbWs4-eNVNTNc94eF+O_UwHYKv43vyMurhcdqMV=Ht5fehcOg@mail.gmail.com>
References: <17540-7aa1855ad5ec18b4@postgresql.org>
<CAApHDvqRR81Dvur_zcrY+RsKxv_e8uDpJ2xxAPMmnfgDY7VNew@mail.gmail.com>
<2732256.1657163219@sss.pgh.pa.us>
<CAApHDvqhr1eU9v42w5gt_gL=G15pB9RBpAH_Jmg7eDU6iZ_2zg@mail.gmail.com>
<2740652.1657165848@sss.pgh.pa.us>
<CAApHDvqHCNKJi9CrQZG-reQDXTfRWnT5rhzNtDQhnrBzAAusfA@mail.gmail.com>
<CAMbWs48fiio5RcsBHztjNXXYvU4QKTekbszECs0ydpY2faqo9A@mail.gmail.com>
<CAMbWs49E2Ps7xdPNTAQOuDg6B9RAidcgQ2S4_nEdveSg_GqiDg@mail.gmail.com>
<CAApHDvr+70jEZpraMZ8sj=UH77dBzCaeUc9EcJHB3YaxX9syNg@mail.gmail.com>
<CAMbWs4-cWwe0whACTuKTLT+4jNcSJ+RLEcSp4us1vBAt0k7GAA@mail.gmail.com>
<CAApHDvpkfS1hY3P4DWbOw6WCgRrja=yDLoEz+5g+E2z19Upsrg@mail.gmail.com>
<CAMbWs48krRDsB6PbG1DVjQLoYbmFSotMt-HnR3Cphsn78+yY9g@mail.gmail.com>
<CAApHDvpsgqopTiEFv5HoMkevAmxKapwxm6188U=KthZWWfAQ=w@mail.gmail.com>
<CAMbWs4-eNVNTNc94eF+O_UwHYKv43vyMurhcdqMV=Ht5fehcOg@mail.gmail.com>
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%40ma...
--
regards,
Andrei Lepikhov
Postgres Professional
Attachments:v6-0001-Reduce-NullTest-quals-to-constant-TRUE-or-FALSE.patch (text/plain, 27.4K) download
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.outindex 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.sqlindex 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.cindex 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.cindex 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.cindex 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.cindex 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.hindex 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.hindex 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.outnew file mode 100644index 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_scheduleindex 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.sqlnew file mode 100644index 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: a.lepikhov@postgrespro.ru, guofenglinux@gmail.com, dgrowleyml@gmail.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17540: Prepared statement: PG switches to a generic query plan which is consistently much slower
In-Reply-To: <74a806ed-3b6c-4c45-a67a-27b5f3496ae5@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox