Hi, again!
I have finished patch and processed almost your suggestions (from [0],
[1], [2]). It remainsonlyto addtestswherethe conversionshouldwork,butI
willaddthis inthe nextversion.
[0]
https://www.postgresql.org/message-id/3381819.e9J7NaK4W3%40thinkpad-pgpro
[1]
https://www.postgresql.org/message-id/9736220.CDJkKcVGEf%40thinkpad-pgpro
[2]
https://www.postgresql.org/message-id/2193851.QkHrqEjB74%40thinkpad-pgpro
On 09.07.2024 04:57, Alena Rybakina wrote:
Hi! Thank you for your review! Sorryforthe delayin responding.
Irewrotethe patchasyourequested,butnowI'm facedwiththe problemof
processingthe elementsof the or_entries list.For somereason,
thepointerto thelistis cleared and I couldn't find the place where it
happened.MaybeI'mmissingsomethingsimpleinviewof the heavyworkloadright
now,butmaybeyou'll seea problem?Ihave displayedpart of stackbelow.
#5 0x00005b0f6d9f6a6a in ExceptionalCondition
(conditionName=0x5b0f6dbb74f7 "IsPointerList(list)",
fileName=0x5b0f6dbb7418 "list.c", lineNumber=341) at assert.c:66 #6
0x00005b0f6d5dc3ba in lappend (list=0x5b0f6eec5ca0,
datum=0x5b0f6eec0d90) at list.c:341 #7 0x00005b0f6d69230c in
transform_or_to_any (root=0x5b0f6eeb13c8, orlist=0x5b0f6eec57c0) at
initsplan.c:2818 #8 0x00005b0f6d692958 in add_base_clause_to_rel
(root=0x5b0f6eeb13c8, relid=1, restrictinfo=0x5b0f6eec5990) at
initsplan.c:2982 #9 0x00005b0f6d692e5f in
distribute_restrictinfo_to_rels (root=0x5b0f6eeb13c8,
restrictinfo=0x5b0f6eec5990) at initsplan.c:3175 #10
0x00005b0f6d691bf2 in distribute_qual_to_rels (root=0x5b0f6eeb13c8,
clause=0x5b0f6eec0fc0, jtitem=0x5b0f6eec4330, sjinfo=0x0,
security_level=0, qualscope=0x5b0f6eec4730, ojscope=0x0,
outerjoin_nonnullable=0x0, incompatible_relids=0x0,
allow_equivalence=true, has_clone=false, is_clone=false,
postponed_oj_qual_list=0x0) at initsplan.c:2576 #11 0x00005b0f6d69146f
in distribute_quals_to_rels (root=0x5b0f6eeb13c8,
clauses=0x5b0f6eec0bb0, jtitem=0x5b0f6eec4330, sjinfo=0x0,
security_level=0, qualscope=0x5b0f6eec4730, ojscope=0x0,
outerjoin_nonnullable=0x0, incompatible_relids=0x0,
allow_equivalence=true, has_clone=false, is_clone=false,
postponed_oj_qual_list=0x0) at initsplan.c:2144
Thisis stillthe firstiterationof the fixesyouhave proposed,soI have
attachedthe patchindiffformat.I rewroteit,asyousuggestedinthe
firstletter[0].Icreateda separatefunctionthattriesto forman
OrClauseGroup node,butifit failsinthis, it returnsfalse,otherwiseit
processesthe generatedelementaccordingtowhat it found-eitheraddsit to
thelistasnew,oraddsa constantto anexistingone.
Ialsodividedonegenerallistof
suitableforconversionandunsuitableintotwodifferentones:appropriate_entriesandor_entries.Nowweare
onlylookinginthe listof suitableelementstoformANYexpr.
Thishelpsusto get ridofrepetitionsinthe codeyoumentioned.
Pleasewriteifthisis notthelogicthatyouhave seenbefore.
[0]
https://www.postgresql.org/message-id/3381819.e9J7NaK4W3%40thinkpad-pgpro
The errorwascausedby the specificsof storingthe "OR"clausesinthe
RestrictInfostructure.Scanning the orclauses list of the RestrictInfo
variable, wecouldfacenotonlytheitem with RestrictInfo
type,butalsotheBoolExpr type.
For example, when we have both or clauses and "AND" clauses together,
like x = 1 and (y =1 or y=2 or y=3 and z = 1). The structure looks like:
RestrictInfo->orclauses = [RestrictInfo [x=1],
RestrictInfo->orclauses = [RestrictInfo[y=1],
RestrictInfo [y=2],
BoolExpr = [Restrictinfo [y=3], RestrictInfo [z=1]
]
]
It'sworkingfinenow.
--
Regards,
Alena Rybakina
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
From 26eca98229749b20ad0c82a9fa55f4f37fd34d29 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <a.rybak...@postgrespro.ru>
Date: Thu, 11 Jul 2024 19:01:10 +0300
Subject: [PATCH 1/2] Transform OR clauses to ANY expression
Replace (expr op C1) OR (expr op C2) ... with expr op ANY(ARRAY[C1, C2, ...])
during adding restrictinfo's to the base relation.
Here Cn is a n-th constant or parameters expression, 'expr' is non-constant
expression, 'op' is an operator which returns boolean result and has a commuter
(for the case of reverse order of constant and non-constant parts of the
expression, like 'Cn op expr').
Discussion: https://postgr.es/m/567ED6CA.2040504%40sigaev.ru
Author: Alena Rybakina <lena.riback...@yandex.ru>
Author: Andrey Lepikhov <a.lepik...@postgrespro.ru>
Reviewed-by: Peter Geoghegan <p...@bowt.ie>
Reviewed-by: Ranier Vilela <ranier...@gmail.com>
Reviewed-by: Alexander Korotkov <aekorot...@gmail.com>
Reviewed-by: Robert Haas <robertmh...@gmail.com>
Reviewed-by: Jian He <jian.universal...@gmail.com>
Reviewed-by: Tom Lane <t...@sss.pgh.pa.us>
Reviewed-by: Nikolay Shaplov <dh...@nataraj.su>
---
src/backend/optimizer/plan/initsplan.c | 335 ++++++++++++++++++
src/include/nodes/pathnodes.h | 31 ++
src/test/regress/expected/create_index.out | 32 +-
src/test/regress/expected/partition_prune.out | 46 +--
src/test/regress/expected/stats_ext.out | 12 +-
src/test/regress/expected/tidscan.out | 6 +-
src/tools/pgindent/typedefs.list | 1 +
7 files changed, 410 insertions(+), 53 deletions(-)
diff --git a/src/backend/optimizer/plan/initsplan.c b/src/backend/optimizer/plan/initsplan.c
index e2c68fe6f99..572312be748 100644
--- a/src/backend/optimizer/plan/initsplan.c
+++ b/src/backend/optimizer/plan/initsplan.c
@@ -14,9 +14,13 @@
*/
#include "postgres.h"
+#include "catalog/namespace.h"
+#include "catalog/pg_operator.h"
#include "catalog/pg_type.h"
+#include "common/hashfn.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/queryjumble.h"
#include "optimizer/clauses.h"
#include "optimizer/cost.h"
#include "optimizer/inherit.h"
@@ -29,8 +33,11 @@
#include "optimizer/planner.h"
#include "optimizer/restrictinfo.h"
#include "parser/analyze.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_oper.h"
#include "rewrite/rewriteManip.h"
#include "utils/lsyscache.h"
+#include "utils/syscache.h"
#include "utils/rel.h"
#include "utils/typcache.h"
@@ -2617,6 +2624,308 @@ check_redundant_nullability_qual(PlannerInfo *root, Node *clause)
return false;
}
+static bool
+try_prepare_single_or(RestrictInfo *rinfo, List **appropriate_group)
+{
+ OpExpr *orqual;
+ Node *const_expr;
+ Node *nconst_expr;
+ Oid opno;
+ Oid consttype;
+ Node *leftop,
+ *rightop;
+ ListCell *lc2;
+ bool found = false;
+ OrClauseGroup *or_clause_group;
+
+ if (!IsA(rinfo, RestrictInfo) || !IsA(rinfo->clause, OpExpr))
+ {
+ return false;
+ }
+
+ orqual = (OpExpr *) rinfo->clause;
+ opno = orqual->opno;
+ if (get_op_rettype(opno) != BOOLOID)
+ {
+ /* Only operator returning boolean suits OR -> ANY transformation */
+ return false;
+ }
+
+ /*
+ * Detect the constant side of the clause. Recall non-constant
+ * expression can be made not only with Vars, but also with Params,
+ * which is not bonded with any relation. Thus, we detect the const
+ * side - if another side is constant too, the orqual couldn't be an
+ * OpExpr. Get pointers to constant and expression sides of the qual.
+ */
+ leftop = get_leftop(orqual);
+ if (IsA(leftop, RelabelType))
+ leftop = (Node *) ((RelabelType *) leftop)->arg;
+
+ rightop = get_rightop(orqual);
+ if (IsA(rightop, RelabelType))
+ rightop = (Node *) ((RelabelType *) rightop)->arg;
+
+ if (IsA(leftop, Const) || IsA(leftop, Param))
+ {
+ opno = get_commutator(opno);
+
+ if (!OidIsValid(opno))
+ {/* commutator doesn't exist, we can't reverse the order */
+ return false;
+ }
+
+ nconst_expr = get_rightop(orqual);
+ const_expr = get_leftop(orqual);
+ }
+ else if (IsA(rightop, Const) || IsA(rightop, Param))
+ {
+ const_expr = get_rightop(orqual);
+ nconst_expr = get_leftop(orqual);
+ }
+ else
+ {
+ return false;
+ }
+
+ /*
+ * Forbid transformation for composite types, records, and volatile
+ * expressions.
+ */
+ consttype = exprType(const_expr);
+ if (type_is_rowtype(exprType(const_expr)) ||
+ type_is_rowtype(consttype) ||
+ contain_volatile_functions((Node *) nconst_expr))
+ {
+ return false;
+ }
+
+ or_clause_group = makeNode(OrClauseGroup);
+
+ foreach(lc2, *appropriate_group)
+ {
+
+ if (!IsA(lfirst(lc2), OrClauseGroup))
+ Assert(0);
+
+ or_clause_group = (OrClauseGroup *) lfirst(lc2);
+
+ if (or_clause_group->opno == opno &&
+ or_clause_group->consttype == consttype &&
+ or_clause_group->inputcollid == exprCollation(const_expr) &&
+ equal(or_clause_group->expr, nconst_expr))
+ {
+ found = true;
+ break;
+ }
+ }
+
+ if (!found)
+ {
+ or_clause_group->expr = (Expr *) nconst_expr;
+ or_clause_group->exprs = list_make1((void *) orqual);
+ or_clause_group->opno = opno;
+ or_clause_group->inputcollid = exprCollation(const_expr);
+ or_clause_group->consttype = consttype;
+ or_clause_group->consts = list_make1(const_expr);
+ Assert(list_length(or_clause_group->exprs) == list_length(or_clause_group->consts));
+ *appropriate_group = lappend(*appropriate_group, or_clause_group);
+ }
+ else
+ {
+ or_clause_group->consts = lappend(or_clause_group->consts, const_expr);
+ or_clause_group->exprs = lappend(or_clause_group->exprs, (void *) orqual);
+ Assert(list_length(or_clause_group->exprs) == list_length(or_clause_group->consts));
+ }
+
+ return true;
+}
+
+/*
+ * transform_or_to_any -
+ * Discover the args of an OR expression and try to group similar OR
+ * expressions to SAOP expressions.
+ *
+ * This transformation groups two-sided equality expression. One side of
+ * such an expression must be a plain constant or constant expression. The
+ * other side must be a variable expression without volatile functions.
+ * To group quals, opno, inputcollid of variable expression, and type of
+ * constant expression must be equal too.
+ *
+ * The grouping technique is based on the equivalence of variable sides of
+ * the expression: using exprId and equal() routine, it groups constant sides
+ * of similar clauses into an array. After the grouping procedure, each
+ * couple ('variable expression' and 'constant array') forms a new SAOP
+ * operation, which is added to the args list of the returning expression.
+ */
+static List *
+transform_or_to_any(PlannerInfo *root, RestrictInfo * rinfo)
+{
+ List *appropriate_entries = NIL;
+ List *or_entries = NIL;
+ int len_ors = ((BoolExpr *) rinfo->clause)->args ? list_length(((BoolExpr *) rinfo->clause)->args) : 0;
+ OrClauseGroup *restrict_info_entry = NULL;
+ ListCell *lc;
+ bool found;
+
+ if(len_ors < 2)
+ return NIL;
+
+ foreach(lc, ((BoolExpr *) rinfo->orclause)->args)
+ {
+ RestrictInfo *sub_rinfo;
+ Expr *or_qual = (Expr *) lfirst(lc);
+
+ if(!IsA(lfirst(lc), RestrictInfo))
+ or_entries = lappend(or_entries, (void *) or_qual);
+ else
+ {
+ sub_rinfo = (RestrictInfo *) lfirst(lc);
+
+ /*
+ * Add the restrict_info_entry to the list. It is needed exclusively to manage
+ * the problem with the order of transformed clauses in explain.
+ * Hash value can depend on the platform and version. Hence,
+ * sequental scan of the hash table would prone to change the
+ * order of clauses in lists and, as a result, break regression
+ * tests accidentially.
+ */
+ found = try_prepare_single_or(sub_rinfo, &appropriate_entries);
+
+ if (!found)
+ {
+ or_entries = lappend(or_entries, (void *) sub_rinfo->clause);
+ }
+ }
+ }
+
+ if (list_length(or_entries) == len_ors || (appropriate_entries == NIL && list_length(appropriate_entries) < 1))
+ return NIL;
+
+ found = false;
+
+ /* Let's convert each group of clauses to an ANY expression. */
+
+ /*
+ * Go through the list of groups and convert each, where number of consts
+ * more than 1. trivial groups move to OR-list again
+ */
+
+ foreach(lc, appropriate_entries)
+ {
+ Oid scalar_type;
+ Oid array_type;
+
+ if (!IsA(lfirst(lc), OrClauseGroup))
+ {
+ Assert(0);
+ }
+
+ restrict_info_entry = (OrClauseGroup *) lfirst(lc);
+
+ Assert(list_length(restrict_info_entry->exprs) == list_length(restrict_info_entry->consts));
+
+ if (list_length(restrict_info_entry->consts) == 1)
+ {
+ /*
+ * Only one element returns origin expression into the BoolExpr
+ * args list unchanged.
+ */
+ or_entries = list_concat(or_entries, (void *) restrict_info_entry->exprs);
+ continue;
+ }
+
+ /*
+ * Do the transformation.
+ */
+ scalar_type = restrict_info_entry->consttype;
+ array_type = OidIsValid(scalar_type) ? get_array_type(scalar_type) :
+ InvalidOid;
+
+ if (OidIsValid(array_type))
+ {
+ /*
+ * OK: coerce all the right-hand non-Var inputs to the common type
+ * and build an ArrayExpr for them.
+ */
+ List *aexprs = NIL;
+ ArrayExpr *newa = NULL;
+ ScalarArrayOpExpr *saopexpr = NULL;
+ HeapTuple opertup;
+ Form_pg_operator operform;
+ List *namelist = NIL;
+ ListCell *lc2;
+
+ foreach(lc2, restrict_info_entry->consts)
+ {
+ Node *node = (Node *) lfirst(lc2);
+
+ node = coerce_to_common_type(NULL, node, scalar_type,
+ "OR ANY Transformation");
+ aexprs = lappend(aexprs, node);
+ }
+
+ newa = makeNode(ArrayExpr);
+ /* array_collid will be set by parse_collate.c */
+ newa->element_typeid = scalar_type;
+ newa->array_typeid = array_type;
+ newa->multidims = false;
+ newa->elements = aexprs;
+ newa->location = -1;
+
+ /*
+ * Try to cast this expression to Const. Due to current strict
+ * transformation rules it should be done [almost] every time.
+ */
+ newa = (ArrayExpr *) eval_const_expressions(NULL, (Node *) newa);
+
+ opertup = SearchSysCache1(OPEROID,
+ ObjectIdGetDatum(restrict_info_entry->opno));
+ if (!HeapTupleIsValid(opertup))
+ elog(ERROR, "cache lookup failed for operator %u",
+ restrict_info_entry->opno);
+
+ operform = (Form_pg_operator) GETSTRUCT(opertup);
+ if (!OperatorIsVisible(restrict_info_entry->opno))
+ namelist = lappend(namelist, makeString(get_namespace_name(operform->oprnamespace)));
+
+ namelist = lappend(namelist, makeString(pstrdup(NameStr(operform->oprname))));
+ ReleaseSysCache(opertup);
+
+ saopexpr =
+ (ScalarArrayOpExpr *)
+ make_scalar_array_op(NULL,
+ namelist,
+ true,
+ (Node *) restrict_info_entry->expr,
+ (Node *) newa,
+ -1);
+ saopexpr->inputcollid = restrict_info_entry->inputcollid;
+
+ or_entries = lappend(or_entries, (Expr *) saopexpr);
+
+ found = true;
+ }
+ else
+ {
+ /*
+ * If the const node's (right side of operator expression) type
+ * don't have “true” array type, then we cannnot do the
+ * transformation. We simply concatenate the expression node.
+ */
+ list_free(restrict_info_entry->consts);
+ or_entries = list_concat(or_entries, (void *) restrict_info_entry->exprs);
+ }
+ }
+ list_free(appropriate_entries);
+
+ /* One more trick: assemble correct clause */
+ if (found)
+ return or_entries;
+ else
+ return NIL;
+}
+
/*
* add_base_clause_to_rel
* Add 'restrictinfo' as a baserestrictinfo to the base relation denoted
@@ -2677,6 +2986,32 @@ add_base_clause_to_rel(PlannerInfo *root, Index relid,
}
}
+ if (restriction_is_or_clause(restrictinfo))
+ {
+ List *or_list = transform_or_to_any(root, restrictinfo);
+
+ if (or_list != NIL)
+ {
+ Expr *clause = list_length(or_list) > 1 ?
+ makeBoolExpr(OR_EXPR, or_list,
+ ((BoolExpr *) restrictinfo->clause)->location) :
+ linitial(or_list);
+
+ RestrictInfo *rinfo;
+ rinfo = make_restrictinfo(root,
+ clause,
+ restrictinfo->is_pushed_down,
+ restrictinfo->has_clone,
+ restrictinfo->is_clone,
+ restrictinfo->pseudoconstant,
+ restrictinfo->security_level,
+ restrictinfo->required_relids,
+ restrictinfo->incompatible_relids,
+ restrictinfo->outer_relids);
+ restrictinfo = rinfo;
+ }
+ }
+
/* Add clause to rel's restriction list */
rel->baserestrictinfo = lappend(rel->baserestrictinfo, restrictinfo);
diff --git a/src/include/nodes/pathnodes.h b/src/include/nodes/pathnodes.h
index 14ccfc1ac1c..f48ddb8c385 100644
--- a/src/include/nodes/pathnodes.h
+++ b/src/include/nodes/pathnodes.h
@@ -2708,6 +2708,37 @@ typedef struct RestrictInfo
Oid right_hasheqoperator pg_node_attr(equal_ignore);
} RestrictInfo;
+/*
+ * The group of similar operator expressions in transform_or_to_any().
+ */
+typedef struct OrClauseGroup
+{
+ pg_node_attr(nodetag_only)
+
+ NodeTag type;
+
+ /* The expression of the variable side of operator */
+ Expr *expr;
+ /* The operator of the operator expression */
+ Oid opno;
+ /* The collation of the operator expression */
+ Oid inputcollid;
+ /* The type of constant side of operator */
+ Oid consttype;
+
+ /* The list of constant sides of operators */
+ List *consts;
+
+ /*
+ * List of source expressions. We need this for convenience in case we
+ * will give up on transformation.
+ */
+ List *exprs;
+
+ Node *const_expr;
+} OrClauseGroup;
+
+
/*
* This macro embodies the correct way to test whether a RestrictInfo is
* "pushed down" to a given outer join, that is, should be treated as a filter
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index cf6eac57349..bcae073143a 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1844,18 +1844,11 @@ DROP TABLE onek_with_null;
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on tenk1
- Recheck Cond: (((thousand = 42) AND (tenthous = 1)) OR ((thousand = 42) AND (tenthous = 3)) OR ((thousand = 42) AND (tenthous = 42)))
- -> BitmapOr
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 1))
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 3))
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 42))
-(9 rows)
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Index Scan using tenk1_thous_tenthous on tenk1
+ Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3,42}'::integer[])))
+(2 rows)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -1867,20 +1860,17 @@ SELECT * FROM tenk1
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
- QUERY PLAN
----------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------
Aggregate
-> Bitmap Heap Scan on tenk1
- Recheck Cond: ((hundred = 42) AND ((thousand = 42) OR (thousand = 99)))
+ Recheck Cond: ((hundred = 42) AND (thousand = ANY ('{42,99}'::integer[])))
-> BitmapAnd
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = 42)
- -> BitmapOr
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 42)
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 99)
-(11 rows)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = ANY ('{42,99}'::integer[]))
+(8 rows)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 7a03b4e3607..dc0876b4bb3 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -100,23 +100,23 @@ explain (costs off) select * from lp where a is null;
(2 rows)
explain (costs off) select * from lp where a = 'a' or a = 'c';
- QUERY PLAN
-----------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------
Append
-> Seq Scan on lp_ad lp_1
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
-> Seq Scan on lp_bc lp_2
- Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+ Filter: (a = ANY ('{a,c}'::bpchar[]))
(5 rows)
explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
- QUERY PLAN
---------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------
Append
-> Seq Scan on lp_ad lp_1
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
-> Seq Scan on lp_bc lp_2
- Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
(5 rows)
explain (costs off) select * from lp where a <> 'g';
@@ -533,10 +533,10 @@ explain (costs off) select * from rlp where a <= 31;
(27 rows)
explain (costs off) select * from rlp where a = 1 or a = 7;
- QUERY PLAN
---------------------------------
+ QUERY PLAN
+------------------------------------------
Seq Scan on rlp2 rlp
- Filter: ((a = 1) OR (a = 7))
+ Filter: (a = ANY ('{1,7}'::integer[]))
(2 rows)
explain (costs off) select * from rlp where a = 1 or b = 'ab';
@@ -614,13 +614,13 @@ explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
-- where clause contradicts sub-partition's constraint
explain (costs off) select * from rlp where a = 20 or a = 40;
- QUERY PLAN
-----------------------------------------
+ QUERY PLAN
+--------------------------------------------------
Append
-> Seq Scan on rlp4_1 rlp_1
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = ANY ('{20,40}'::integer[]))
-> Seq Scan on rlp5_default rlp_2
- Filter: ((a = 20) OR (a = 40))
+ Filter: (a = ANY ('{20,40}'::integer[]))
(5 rows)
explain (costs off) select * from rlp3 where a = 20; /* empty */
@@ -2090,10 +2090,10 @@ explain (costs off) select * from hp where a = 1 and b = 'abcde';
explain (costs off) select * from hp where a = 1 and b = 'abcde' and
(c = 2 or c = 3);
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
Seq Scan on hp2 hp
- Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3)))
+ Filter: ((c = ANY ('{2,3}'::integer[])) AND (a = 1) AND (b = 'abcde'::text))
(2 rows)
drop table hp2;
@@ -2429,8 +2429,8 @@ select explain_parallel_append('execute ab_q5 (33, 44, 55)');
-- Test Parallel Append with PARAM_EXEC Params
select explain_parallel_append('select count(*) from ab where (a = (select 1) or a = (select 3)) and b = 2');
- explain_parallel_append
-------------------------------------------------------------------------------------------------
+ explain_parallel_append
+---------------------------------------------------------------------------------------------------
Aggregate (actual rows=N loops=N)
InitPlan 1
-> Result (actual rows=N loops=N)
@@ -2441,11 +2441,11 @@ select explain_parallel_append('select count(*) from ab where (a = (select 1) or
Workers Launched: N
-> Parallel Append (actual rows=N loops=N)
-> Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N)
- Filter: ((b = 2) AND ((a = (InitPlan 1).col1) OR (a = (InitPlan 2).col1)))
+ Filter: ((a = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) AND (b = 2))
-> Parallel Seq Scan on ab_a2_b2 ab_2 (never executed)
- Filter: ((b = 2) AND ((a = (InitPlan 1).col1) OR (a = (InitPlan 2).col1)))
+ Filter: ((a = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) AND (b = 2))
-> Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N)
- Filter: ((b = 2) AND ((a = (InitPlan 1).col1) OR (a = (InitPlan 2).col1)))
+ Filter: ((a = ANY (ARRAY[(InitPlan 1).col1, (InitPlan 2).col1])) AND (b = 2))
(15 rows)
-- Test pruning during parallel nested loop query
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 8c4da955084..7678744181c 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -1322,19 +1322,19 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 197 | 200
+ 200 | 200
(1 row)
-- OR clauses referencing different attributes are incompatible
@@ -1664,19 +1664,19 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 4 OR (a * 2) = 102 OR (a * 2) = 104) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
estimated | actual
-----------+--------
- 197 | 200
+ 200 | 200
(1 row)
-- OR clauses referencing different attributes
diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out
index f133b5a4ac7..09553f19524 100644
--- a/src/test/regress/expected/tidscan.out
+++ b/src/test/regress/expected/tidscan.out
@@ -43,10 +43,10 @@ SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid;
-- OR'd clauses
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
- QUERY PLAN
---------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
Tid Scan on tidscan
- TID Cond: ((ctid = '(0,2)'::tid) OR ('(0,1)'::tid = ctid))
+ TID Cond: (ctid = ANY ('{"(0,2)","(0,1)"}'::tid[]))
(2 rows)
SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 9320e4d8080..4ec9e2ce32f 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1715,6 +1715,7 @@ NumericVar
OM_uint32
OP
OSAPerGroupState
+OrClauseGroup
OSAPerQueryState
OSInfo
OSSLCipher
--
2.34.1
From 8afa37e0843b2ce8cc8acb23e435a6bb1d92cc44 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <a.rybak...@postgrespro.ru>
Date: Thu, 11 Jul 2024 19:08:17 +0300
Subject: [PATCH 2/2] Add some new tests to check the functionality of
transformation of OR expressions to Any expression.
---
src/test/regress/sql/create_index.sql | 42 +++++++++++++++++++++++++++
src/test/regress/sql/join.sql | 9 ++++++
2 files changed, 51 insertions(+)
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index e296891cab8..f74ad415fbf 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -726,6 +726,24 @@ DROP TABLE onek_with_null;
-- Check bitmap index path planning
--
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = (SELECT 1 + 2) OR tenthous = 42);
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = (SELECT 1 + 2) OR tenthous = 42);
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -738,6 +756,30 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand < 42 OR thousand < 99 OR 43 > thousand OR 42 > thousand);
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand < 42 OR thousand < 99 OR 43 > thousand OR 42 > thousand);
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+SELECT count(*) FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41;
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2);
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2);
+
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index e3d26520832..cf02cc34ed0 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1409,6 +1409,15 @@ select * from tenk1 a join tenk1 b on
(a.unique1 = 1 and b.unique1 = 2) or
((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+ (a.unique1 = 1 and b.unique1 = 2) or
+ ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+ (a.unique1 < 20 or a.unique1 = 3 or a.unique1 = 1 and b.unique1 = 2) or
+ ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+
--
-- test placement of movable quals in a parameterized join tree
--
--
2.34.1
