Subject: Re: POC, WIP: OR-clause support for indexes

From: Alena Rybakina <[email-masked]>
Date: Mon, 6 Nov 2023 16:51:45 +0300

On 30.10.2023 17:06, Alexander Korotkov wrote:
> On Mon, Oct 30, 2023 at 3:40 PM Robert Haas <[email-masked]> wrote:
>> On Thu, Oct 26, 2023 at 5:05 PM Peter Geoghegan <[email-masked]> wrote:
>>> On Thu, Oct 26, 2023 at 12:59 PM Robert Haas <[email-masked]> wrote:
>>>> Alexander's example seems to show that it's not that simple. If I'm
>>>> reading his example correctly, with things like aid = 1, the
>>>> transformation usually wins even if the number of things in the OR
>>>> expression is large, but with things like aid + 1 * bid = 1, the
>>>> transformation seems to lose at least with larger numbers of items. So
>>>> it's not JUST the number of OR elements but also what they contain,
>>>> unless I'm misunderstanding his point.
>>> Alexander said "Generally, I don't see why ANY could be executed
>>> slower than the equivalent OR clause". I understood that this was his
>>> way of expressing the following idea:
>>>
>>> "In principle, there is no reason to expect execution of ANY() to be
>>> slower than execution of an equivalent OR clause (except for
>>> noise-level differences). While it might not actually look that way
>>> for every single type of plan you can imagine right now, that doesn't
>>> argue for making a cost-based decision. It actually argues for fixing
>>> the underlying issue, which can't possibly be due to some kind of
>>> fundamental advantage enjoyed by expression evaluation with ORs".
>>>
>>> This is also what I think of all this.
>> I agree with that, with some caveats, mainly that the reverse is to
>> some extent also true. Maybe not completely, because arguably the
>> ANY() formulation should just be straight-up easier to deal with, but
>> in principle, the two are equivalent and it shouldn't matter which
>> representation we pick.
>>
>> But practically, it may, and we need to be sure that we don't put in
>> place a translation that is theoretically a win but in practice leads
>> to large regressions. Avoiding regressions here is more important than
>> capturing all the possible gains. A patch that wins in some scenarios
>> and does nothing in others can be committed; a patch that wins in even
>> more scenarios but causes serious regressions in some cases probably
>> can't.
> +1
> Sure, I've identified two cases where patch shows regression [1].  The
> first one (quadratic complexity of expression processing) should be
> already addressed by usage of hash.  The second one (planning
> regression with Bitmap OR) is not yet addressed.
>
> Links
> 1. https://www.postgresql.org/message-id/CAPpHfduJtO0s9E%3DSHUTzrCD88BH0eik0UNog1_q3XBF2wLmH6g%40mail.gmail.com
>
I also support this approach. I have almost finished writing a patch 
that fixes the first problem related to the quadratic complexity of 
processing expressions by adding a hash table.

I also added a check: if the number of groups is equal to the number of 
OR expressions, we assume that no expressions need to be converted and 
interrupt further execution.

Now I am trying to fix the last problem in this patch: three tests have 
indicated a problem related to incorrect conversion. I don't think it 
can be serious, but I haven't figured out where the mistake is yet.

I added log like that: ERROR:  unrecognized node type: 0.

-- 
Regards,
Alena Rybakina
Postgres Professional
From 3062a2408af258b9e327219020221b75501e8530 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email-masked]>
Date: Mon, 6 Nov 2023 16:48:00 +0300
Subject: [PATCH] Replace OR clause to ANY expressions. Replace (X=N1) OR
 (X=N2) ... with X = ANY(N1, N2) on the stage of the optimiser when we are
 still working with a tree expression. Firstly, we do not try to make a
 transformation for "non-or" expressions or inequalities and the creation of a
 relation with "or" expressions occurs according to the same scenario.
 Secondly, we do not make transformations if there are less than set
 or_transform_limit. Thirdly, it is worth considering that we consider "or"
 expressions only at the current level.

Authors: Alena Rybakina <[email-masked]>, Andrey Lepikhov <[email-masked]>
Reviewed-by: Peter Geoghegan <[email-masked]>, Ranier Vilela <[email-masked]>
Reviewed-by: Alexander Korotkov <[email-masked]>, Robert Haas <[email-masked]>
---
 src/backend/parser/parse_expr.c               | 299 +++++++++++++++++-
 src/backend/utils/misc/guc_tables.c           |  10 +
 src/include/parser/parse_expr.h               |   1 +
 src/test/regress/expected/create_index.out    | 115 +++++++
 src/test/regress/expected/guc.out             |   3 +-
 src/test/regress/expected/join.out            |  50 +++
 src/test/regress/expected/partition_prune.out | 179 +++++++++++
 src/test/regress/expected/tidscan.out         |  17 +
 src/test/regress/sql/create_index.sql         |  32 ++
 src/test/regress/sql/join.sql                 |  10 +
 src/test/regress/sql/partition_prune.sql      |  22 ++
 src/test/regress/sql/tidscan.sql              |   6 +
 src/tools/pgindent/typedefs.list              |   1 +
 13 files changed, 743 insertions(+), 2 deletions(-)

diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 64c582c344c..25a4235dbd9 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -18,6 +18,7 @@
 #include "catalog/pg_aggregate.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_type.h"
+#include "common/hashfn.h"
 #include "commands/dbcommands.h"
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
@@ -43,6 +44,7 @@
 
 /* GUC parameters */
 bool		Transform_null_equals = false;
+bool		or_transform_limit = false;

 static Node *transformExprRecurse(ParseState *pstate, Node *expr);
@@ -98,7 +100,302 @@ static Expr *make_distinct_op(ParseState *pstate, List *opname,
 							  Node *ltree, Node *rtree, int location);
 static Node *make_nulltest_from_distinct(ParseState *pstate,
 										 A_Expr *distincta, Node *arg);
+typedef struct OrClauseGroupEntry
+{
+	char		   *hash_leftvar_key;
+
+	Node		   *node;
+	List		   *consts;
+	Oid				scalar_type;
+	Oid				opno;
+	Expr 		   *expr;
+} OrClauseGroupEntry;
+
+static int
+or_name_match(const void *key1, const void *key2, Size keysize)
+{
+	const char *name1 = *(const char *const *) key1;
+	const char *name2 = *(const char *const *) key2;
+
+	return strcmp(name1, name2);
+}
+
+static uint32
+or_name_hash(const void *key, Size keysize)
+{
+	const char *name = *(const char *const *) key;
+
+	return DatumGetInt32(hash_any((unsigned char *)name, strlen(name)));
+}
+
+static char *
+get_key_nconst_node(Node *nconst_node)
+{
+	if (IsA(nconst_node, OpExpr))
+	{
+		OpExpr 	   *clause = (OpExpr*) nconst_node;
+		OpExpr	   *temp = makeNode(OpExpr);
+
+		temp->opno = clause->opno;
+		temp->opfuncid = InvalidOid;
+		temp->opresulttype = clause->opresulttype;
+		temp->opretset = clause->opretset;
+		temp->opcollid = clause->opcollid;
+		temp->inputcollid = clause->inputcollid;
+		temp->location = -1;
+
+		temp->args = list_copy(clause->args);
+		return nodeToString(temp);
+	}
+	else if (IsA(nconst_node, Var))
+	{
+		Var	   *clause = (Var*) nconst_node;
+		Var	   *var = makeNode(Var);
+
+		var->varno = clause->varno;
+		var->varattno = clause->varattno;
+		var->vartype = clause->vartype;
+		var->vartypmod = clause->vartypmod;
+		var->varcollid = clause->varcollid;
+		var->varlevelsup = clause->varlevelsup;
+		var->varattnosyn = clause->varattno;
+		var->location = -1;
+
+		return nodeToString(var);
+	}
+	else
+	{
+		return NULL;
+	}
+ }
+
+static Node *
+transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
+{
+	List		   *or_list = NIL;
+	ListCell	   *lc;
+	HASHCTL			info;
+	HTAB 		   *or_group_htab = NULL;
+	int 			len_ors = list_length(expr_orig->args);
+
+	MemSet(&info, 0, sizeof(info));
+	info.keysize = sizeof(char *);
+	info.entrysize = sizeof(OrClauseGroupEntry);
+	info.hash = or_name_hash;
+	info.match = or_name_match;
+	or_group_htab = hash_create("OR Groups",
+									  len_ors,
+									  &info,
+									  HASH_ELEM | HASH_FUNCTION | HASH_COMPARE);
+
+	/* If this is not an 'OR' expression, skip the transformation */
+	if (expr_orig->boolop != OR_EXPR || !or_transform_limit || len_ors == 1 || !or_group_htab)
+		return transformBoolExpr(pstate, (BoolExpr *) expr_orig);
+
+	foreach(lc, expr_orig->args)
+	{
+		Node			   *arg = lfirst(lc);
+		Node			   *orqual;
+		Node			   *const_expr;
+		Node			   *nconst_expr;
+		OrClauseGroupEntry *gentry;
+		bool				found;
+		char		   	   *str;
+
+		/* At first, transform the arg and evaluate constant expressions. */
+		orqual = transformExprRecurse(pstate, (Node *) arg);
+		orqual = coerce_to_boolean(pstate, orqual, "OR");
+		orqual = eval_const_expressions(NULL, orqual);
+
+		if (!IsA(orqual, OpExpr))
+		{
+			or_list = lappend(or_list, orqual);
+			continue;
+		}
 
+		/*
+		 * 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.
+		 */
+		if (IsA(get_leftop(orqual), Const))
+		{
+			nconst_expr = get_rightop(orqual);
+			const_expr = get_leftop(orqual);
+		}
+		else if (IsA(get_rightop(orqual), Const))
+		{
+			const_expr = get_rightop(orqual);
+			nconst_expr = get_leftop(orqual);
+		}
+		else
+		{
+			or_list = lappend(or_list, orqual);
+			continue;
+		}
+
+		str = get_key_nconst_node(nconst_expr);
+
+		if (!op_mergejoinable(((OpExpr *) orqual)->opno, exprType(nconst_expr)) || str == NULL)
+		{
+			or_list = lappend(or_list, orqual);
+			continue;
+		}
+
+		/*
+		* At this point we definitely have a transformable clause.
+		* Classify it and add into specific group of clauses, or create new
+		* group.
+		* TODO: to manage complexity in the case of many different clauses
+		* (X1=C1) OR (X2=C2 OR) ... (XN = CN) we could invent something
+		* like a hash table. But also we believe, that the case of many
+		* different variable sides is very rare.
+		*/
+		gentry = hash_search(or_group_htab, &str, HASH_FIND, &found);
+
+		if (found)
+		{
+			gentry->consts = lappend(gentry->consts, const_expr);
+			/*
+				* The clause classified successfully and added into existed
+				* clause group.
+				*/
+			continue;
+		}
+
+		/* New clause group needed */
+		gentry = hash_search(or_group_htab, &str, HASH_ENTER, &found);
+		gentry->node = nconst_expr;
+		gentry->consts = list_make1(const_expr);
+		gentry->expr = (Expr *) orqual;
+		gentry->hash_leftvar_key = str;
+	}
+
+	if (or_group_htab && (hash_get_num_entries(or_group_htab) < 1 ||
+	                         hash_get_num_entries(or_group_htab) == len_ors))
+	{
+		/*
+		* No any transformations possible with this list of arguments. Here we
+		* already made all underlying transformations. Thus, just return the
+		* transformed bool expression.
+		*/
+		hash_destroy(or_group_htab);
+		list_free(or_list);
+		return (Node *) makeBoolExpr(OR_EXPR, or_list, expr_orig->location);
+	}
+	else
+	{
+		HASH_SEQ_STATUS		hash_seq;
+		OrClauseGroupEntry *gentry;
+
+		hash_seq_init(&hash_seq, or_group_htab);
+
+		/* Let's convert each group of clauses to an IN operation. */
+
+		/*
+		* Go through the list of groups and convert each, where number of
+		* consts more than 1. trivial groups move to OR-list again
+		*/
+
+		while ((gentry = (OrClauseGroupEntry *) hash_seq_search(&hash_seq)) != NULL)
+		{
+			List			   *allexprs;
+			Oid				    scalar_type;
+			Oid					array_type;
+
+			Assert(list_length(gentry->consts) > 0);
+
+			if (list_length(gentry->consts) == 1)
+			{
+				/*
+				 * Only one element in the class. Return rinfo into the BoolExpr
+				 * args list unchanged.
+				 */
+				list_free(gentry->consts);
+				or_list = lappend(or_list, gentry->expr);
+				continue;
+			}
+
+			/*
+			 * Do the transformation.
+			 *
+			 * First of all, try to select a common type for the array elements.
+			 * Note that since the LHS' type is first in the list, it will be
+			 * preferred when there is doubt (eg, when all the RHS items are
+			 * unknown literals).
+			 *
+			 * Note: use list_concat here not lcons, to avoid damaging rnonvars.
+			 *
+			 * As a source of insides, use make_scalar_array_op()
+			 */
+			allexprs = list_concat(list_make1(gentry->node), gentry->consts);
+			scalar_type = select_common_type(NULL, allexprs, NULL, NULL);
+
+			if (scalar_type != RECORDOID && OidIsValid(scalar_type))
+				array_type = get_array_type(scalar_type);
+			else
+				array_type = InvalidOid;
+
+			if (array_type != InvalidOid)
+			{
+				/*
+				 * OK: coerce all the right-hand non-Var inputs to the common
+				 * type and build an ArrayExpr for them.
+				 */
+				List	   *aexprs;
+				ArrayExpr  *newa;
+				ScalarArrayOpExpr *saopexpr;
+				ListCell *l;
+
+				aexprs = NIL;
+
+				foreach(l, gentry->consts)
+				{
+					Node	   *rexpr = (Node *) lfirst(l);
+
+					rexpr = coerce_to_common_type(pstate, rexpr,
+												scalar_type,
+												"IN");
+					aexprs = lappend(aexprs, rexpr);
+				}
+
+				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;
+
+				saopexpr =
+					(ScalarArrayOpExpr *)
+						make_scalar_array_op(pstate,
+											 list_make1(makeString((char *) "=")),
+											 true,
+											 gentry->node,
+											 (Node *) newa,
+											 -1);
+
+				or_list = lappend(or_list, (void *) saopexpr);
+			}
+			else
+			{
+				list_free(gentry->consts);
+				or_list = lappend(or_list, gentry->expr);
+			}
+			hash_search(or_group_htab, &gentry->hash_leftvar_key, HASH_REMOVE, NULL);
+		}
+		hash_destroy(or_group_htab);
+	}
+
+	/* One more trick: assemble correct clause */
+	return (Node *) ((list_length(or_list) > 1) ?
+						makeBoolExpr(OR_EXPR, or_list, expr_orig->location) :
+						linitial(or_list));
+}
 
 /*
  * transformExpr -
@@ -212,7 +509,7 @@ transformExprRecurse(ParseState *pstate, Node *expr)
 			}
 
 		case T_BoolExpr:
-			result = transformBoolExpr(pstate, (BoolExpr *) expr);
+			result = (Node *)transformBoolExprOr(pstate, (BoolExpr *) expr);
 			break;
 
 		case T_FuncCall:
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 7605eff9b9d..a8e8054e7e6 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1048,6 +1048,16 @@ struct config_bool ConfigureNamesBool[] =
 		true,
 		NULL, NULL, NULL
 	},
+	{
+		{"or_transform_limit", PGC_USERSET, QUERY_TUNING_OTHER,
+			gettext_noop("Transform a sequence of OR clauses to an IN expression."),
+			gettext_noop("The planner will replace clauses like 'x=c1 OR x=c2 .."
+						 "to the clause 'x IN (c1,c2,...)'")
+		},
+		&or_transform_limit,
+		false,
+		NULL, NULL, NULL
+	},
 	{
 		/*
 		 * Not for general use --- used by SET SESSION AUTHORIZATION and SET
diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h
index 7d38ca75f7b..7a6943c116c 100644
--- a/src/include/parser/parse_expr.h
+++ b/src/include/parser/parse_expr.h
@@ -17,6 +17,7 @@
 
 /* GUC parameters */
 extern PGDLLIMPORT bool Transform_null_equals;
+extern PGDLLIMPORT bool or_transform_limit;
 
 extern Node *transformExpr(ParseState *pstate, Node *expr, ParseExprKind exprKind);
 
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index acfd9d1f4f7..29c2bc6a2b2 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1883,6 +1883,121 @@ SELECT count(*) FROM tenk1
     10
 (1 row)
 
+SET or_transform_limit = on;
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+  WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+                                  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);
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+      42 |    5530 |   0 |    2 |   2 |      2 |      42 |       42 |          42 |        42 |       42 |  84 |   85 | QBAAAA   | SEIAAA   | OOOOxx
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+  WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+                                     QUERY PLAN                                     
+------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on tenk1
+         Recheck Cond: ((hundred = 42) AND (thousand = ANY ('{42,99}'::integer[])))
+         ->  BitmapAnd
+               ->  Bitmap Index Scan on tenk1_hundred
+                     Index Cond: (hundred = 42)
+               ->  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);
+ count 
+-------
+    10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+  WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+                                               QUERY PLAN                                               
+--------------------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on tenk1
+         Recheck Cond: (((thousand = 42) AND (tenthous = ANY ('{1,3}'::integer[]))) OR (thousand = 41))
+         ->  BitmapOr
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3}'::integer[])))
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: (thousand = 41)
+(8 rows)
+
+SELECT count(*) FROM tenk1
+  WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+ count 
+-------
+    10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+  WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41;
+                                                         QUERY PLAN                                                          
+-----------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on tenk1
+         Recheck Cond: (((hundred = 42) AND ((tenthous < 2) OR (thousand = ANY ('{42,99}'::integer[])))) OR (thousand = 41))
+         ->  BitmapOr
+               ->  BitmapAnd
+                     ->  Bitmap Index Scan on tenk1_hundred
+                           Index Cond: (hundred = 42)
+                     ->  BitmapOr
+                           ->  Bitmap Index Scan on tenk1_thous_tenthous
+                                 Index Cond: (tenthous < 2)
+                           ->  Bitmap Index Scan on tenk1_thous_tenthous
+                                 Index Cond: (thousand = ANY ('{42,99}'::integer[]))
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: (thousand = 41)
+(14 rows)
+
+SELECT count(*) FROM tenk1
+  WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41;
+ count 
+-------
+    20
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+  WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2);
+                                                          QUERY PLAN                                                          
+------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on tenk1
+         Recheck Cond: ((hundred = 42) AND (((thousand = 99) AND (tenthous = 2)) OR (thousand = ANY ('{42,41}'::integer[]))))
+         ->  BitmapAnd
+               ->  Bitmap Index Scan on tenk1_hundred
+                     Index Cond: (hundred = 42)
+               ->  BitmapOr
+                     ->  Bitmap Index Scan on tenk1_thous_tenthous
+                           Index Cond: ((thousand = 99) AND (tenthous = 2))
+                     ->  Bitmap Index Scan on tenk1_thous_tenthous
+                           Index Cond: (thousand = ANY ('{42,41}'::integer[]))
+(11 rows)
+
+SELECT count(*) FROM tenk1
+  WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2);
+ count 
+-------
+    10
+(1 row)
+
+RESET or_transform_limit;
 --
 -- Check behavior with duplicate index column contents
 --
diff --git a/src/test/regress/expected/guc.out b/src/test/regress/expected/guc.out
index 127c9532976..c052b113eea 100644
--- a/src/test/regress/expected/guc.out
+++ b/src/test/regress/expected/guc.out
@@ -861,7 +861,8 @@ SELECT name FROM tab_settings_flags
            name            
 ---------------------------
  default_statistics_target
-(1 row)
+ or_transform_limit
+(2 rows)
 
 -- Runtime-computed GUCs should be part of the preset category.
 SELECT name FROM tab_settings_flags
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 446959e3c5f..d8684e959e4 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4207,6 +4207,56 @@ select * from tenk1 a join tenk1 b on
                            Index Cond: (unique2 = 7)
 (19 rows)
 
+SET or_transform_limit = on;
+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);
+                                                       QUERY PLAN                                                       
+------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4)))
+   ->  Bitmap Heap Scan on tenk1 b
+         Recheck Cond: ((unique1 = 2) OR (hundred = 4))
+         ->  BitmapOr
+               ->  Bitmap Index Scan on tenk1_unique1
+                     Index Cond: (unique1 = 2)
+               ->  Bitmap Index Scan on tenk1_hundred
+                     Index Cond: (hundred = 4)
+   ->  Materialize
+         ->  Bitmap Heap Scan on tenk1 a
+               Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])))
+               ->  BitmapOr
+                     ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 = 1)
+                     ->  Bitmap Index Scan on tenk1_unique2
+                           Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+(17 rows)
+
+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);
+                                                                          QUERY PLAN                                                                           
+---------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((a.unique1 < 20) OR ((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4)) OR (a.unique1 = 3))
+   ->  Seq Scan on tenk1 b
+   ->  Materialize
+         ->  Bitmap Heap Scan on tenk1 a
+               Recheck Cond: ((unique1 < 20) OR (unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])) OR (unique1 = 3))
+               ->  BitmapOr
+                     ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 < 20)
+                     ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 = 1)
+                     ->  Bitmap Index Scan on tenk1_unique2
+                           Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+                     ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 = 3)
+(15 rows)
+
+RESET or_transform_limit;
 --
 -- test placement of movable quals in a parameterized join tree
 --
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 9a4c48c0556..1789d3c1fd7 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -101,6 +101,28 @@ explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'
          Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
 (5 rows)
 
+SET or_transform_limit = on;
+explain (costs off) select * from lp where a = 'a' or a = 'c';
+                  QUERY PLAN                   
+-----------------------------------------------
+ Append
+   ->  Seq Scan on lp_ad lp_1
+         Filter: (a = ANY ('{a,c}'::bpchar[]))
+   ->  Seq Scan on lp_bc lp_2
+         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                              
+---------------------------------------------------------------------
+ Append
+   ->  Seq Scan on lp_ad lp_1
+         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 = ANY ('{a,c}'::bpchar[])))
+(5 rows)
+
+RESET or_transform_limit;
 explain (costs off) select * from lp where a <> 'g';
              QUERY PLAN             
 ------------------------------------
@@ -671,6 +693,163 @@ explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a =
          Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
 (11 rows)
 
+SET or_transform_limit = on;
+explain (costs off) select * from rlp where a = 1 or a = 7;
+                QUERY PLAN                
+------------------------------------------
+ Seq Scan on rlp2 rlp
+   Filter: (a = ANY ('{1,7}'::integer[]))
+(2 rows)
+
+explain (costs off) select * from rlp where a = 1 or b = 'ab';
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Append
+   ->  Seq Scan on rlp1 rlp_1
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp2 rlp_2
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp3abcd rlp_3
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp4_1 rlp_4
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp4_2 rlp_5
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp4_default rlp_6
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp5_1 rlp_7
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp5_default rlp_8
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp_default_10 rlp_9
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp_default_30 rlp_10
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp_default_null rlp_11
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp_default_default rlp_12
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+(25 rows)
+
+explain (costs off) select * from rlp where a > 20 and a < 27;
+               QUERY PLAN                
+-----------------------------------------
+ Append
+   ->  Seq Scan on rlp4_1 rlp_1
+         Filter: ((a > 20) AND (a < 27))
+   ->  Seq Scan on rlp4_2 rlp_2
+         Filter: ((a > 20) AND (a < 27))
+(5 rows)
+
+explain (costs off) select * from rlp where a = 29;
+          QUERY PLAN          
+------------------------------
+ Seq Scan on rlp4_default rlp
+   Filter: (a = 29)
+(2 rows)
+
+explain (costs off) select * from rlp where a >= 29;
+                 QUERY PLAN                  
+---------------------------------------------
+ Append
+   ->  Seq Scan on rlp4_default rlp_1
+         Filter: (a >= 29)
+   ->  Seq Scan on rlp5_1 rlp_2
+         Filter: (a >= 29)
+   ->  Seq Scan on rlp5_default rlp_3
+         Filter: (a >= 29)
+   ->  Seq Scan on rlp_default_30 rlp_4
+         Filter: (a >= 29)
+   ->  Seq Scan on rlp_default_default rlp_5
+         Filter: (a >= 29)
+(11 rows)
+
+explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
+                      QUERY PLAN                      
+------------------------------------------------------
+ Append
+   ->  Seq Scan on rlp1 rlp_1
+         Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
+   ->  Seq Scan on rlp4_1 rlp_2
+         Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
+(5 rows)
+
+explain (costs off) select * from rlp where a = 20 or a = 40;
+                    QUERY PLAN                    
+--------------------------------------------------
+ Append
+   ->  Seq Scan on rlp4_1 rlp_1
+         Filter: (a = ANY ('{20,40}'::integer[]))
+   ->  Seq Scan on rlp5_default rlp_2
+         Filter: (a = ANY ('{20,40}'::integer[]))
+(5 rows)
+
+explain (costs off) select * from rlp3 where a = 20;   /* empty */
+        QUERY PLAN        
+--------------------------
+ Result
+   One-Time Filter: false
+(2 rows)
+
+explain (costs off) select * from rlp where a > 1 and a = 10;	/* only default */
+            QUERY PLAN            
+----------------------------------
+ Seq Scan on rlp_default_10 rlp
+   Filter: ((a > 1) AND (a = 10))
+(2 rows)
+
+explain (costs off) select * from rlp where a > 1 and a >=15;	/* rlp3 onwards, including default */
+                  QUERY PLAN                  
+----------------------------------------------
+ Append
+   ->  Seq Scan on rlp3abcd rlp_1
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp3efgh rlp_2
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp3nullxy rlp_3
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp3_default rlp_4
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp4_1 rlp_5
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp4_2 rlp_6
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp4_default rlp_7
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp5_1 rlp_8
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp5_default rlp_9
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp_default_30 rlp_10
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp_default_default rlp_11
+         Filter: ((a > 1) AND (a >= 15))
+(23 rows)
+
+explain (costs off) select * from rlp where a = 1 and a = 3;	/* empty */
+        QUERY PLAN        
+--------------------------
+ Result
+   One-Time Filter: false
+(2 rows)
+
+explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);
+                            QUERY PLAN                             
+-------------------------------------------------------------------
+ Append
+   ->  Seq Scan on rlp2 rlp_1
+         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+   ->  Seq Scan on rlp3abcd rlp_2
+         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+   ->  Seq Scan on rlp3efgh rlp_3
+         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+   ->  Seq Scan on rlp3nullxy rlp_4
+         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+   ->  Seq Scan on rlp3_default rlp_5
+         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+(11 rows)
+
+RESET or_transform_limit;
 -- multi-column keys
 create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
 create table mc3p_default partition of mc3p default;
diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out
index f133b5a4ac7..8a31e2e670d 100644
--- a/src/test/regress/expected/tidscan.out
+++ b/src/test/regress/expected/tidscan.out
@@ -56,6 +56,23 @@ SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
  (0,2) |  2
 (2 rows)
 
+SET or_transform_limit = on;
+EXPLAIN (COSTS OFF)
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Tid Scan on tidscan
+   TID Cond: (ctid = ANY ('{"(0,2)","(0,1)"}'::tid[]))
+(2 rows)
+
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+ ctid  | id 
+-------+----
+ (0,1) |  1
+ (0,2) |  2
+(2 rows)
+
+RESET or_transform_limit;
 -- ctid = ScalarArrayOp - implemented as tidscan
 EXPLAIN (COSTS OFF)
 SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index d49ce9f3007..a709b2c1abc 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -737,6 +737,38 @@ SELECT count(*) FROM tenk1
 SELECT count(*) FROM tenk1
   WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
 
+SET or_transform_limit = on;
+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 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 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);
+RESET or_transform_limit;
+
 --
 -- Check behavior with duplicate index column contents
 --
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 66dd03dd88b..f5dcf8b90a7 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1396,6 +1396,16 @@ 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);
+SET or_transform_limit = on;
+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);
+RESET or_transform_limit;
 
 --
 -- test placement of movable quals in a parameterized join tree
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 7bf3920827f..88709910592 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -21,6 +21,12 @@ explain (costs off) select * from lp where a is not null;
 explain (costs off) select * from lp where a is null;
 explain (costs off) select * from lp where a = 'a' or a = 'c';
 explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
+
+SET or_transform_limit = on;
+explain (costs off) select * from lp where a = 'a' or a = 'c';
+explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
+RESET or_transform_limit;
+
 explain (costs off) select * from lp where a <> 'g';
 explain (costs off) select * from lp where a <> 'a' and a <> 'd';
 explain (costs off) select * from lp where a not in ('a', 'd');
@@ -99,6 +105,22 @@ explain (costs off) select * from rlp where a > 1 and a >=15;	/* rlp3 onwards, i
 explain (costs off) select * from rlp where a = 1 and a = 3;	/* empty */
 explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);
 
+
+SET or_transform_limit = on;
+explain (costs off) select * from rlp where a = 1 or a = 7;
+explain (costs off) select * from rlp where a = 1 or b = 'ab';
+explain (costs off) select * from rlp where a > 20 and a < 27;
+explain (costs off) select * from rlp where a = 29;
+explain (costs off) select * from rlp where a >= 29;
+explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
+explain (costs off) select * from rlp where a = 20 or a = 40;
+explain (costs off) select * from rlp3 where a = 20;   /* empty */
+explain (costs off) select * from rlp where a > 1 and a = 10;	/* only default */
+explain (costs off) select * from rlp where a > 1 and a >=15;	/* rlp3 onwards, including default */
+explain (costs off) select * from rlp where a = 1 and a = 3;	/* empty */
+explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);
+RESET or_transform_limit;
+
 -- multi-column keys
 create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
 create table mc3p_default partition of mc3p default;
diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql
index 313e0fb9b67..c735e219589 100644
--- a/src/test/regress/sql/tidscan.sql
+++ b/src/test/regress/sql/tidscan.sql
@@ -22,6 +22,12 @@ EXPLAIN (COSTS OFF)
 SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
 SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
 
+SET or_transform_limit = on;
+EXPLAIN (COSTS OFF)
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+RESET or_transform_limit;
+
 -- ctid = ScalarArrayOp - implemented as tidscan
 EXPLAIN (COSTS OFF)
 SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 87c1aee379f..8cea6a6b851 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1638,6 +1638,7 @@ NumericVar
 OM_uint32
 OP
 OSAPerGroupState
+OrClauseGroupEntry
 OSAPerQueryState
 OSInfo
 OSSLCipher
-- 
2.34.1

---

From: Alena Rybakina <[email-masked]>
Date: Fri, 10 Nov 2023 12:20:57 +0300

On 06.11.2023 16:51, Alena Rybakina wrote:
> I also added a check: if the number of groups is equal to the number 
> of OR expressions, we assume that no expressions need to be converted 
> and interrupt further execution.
>
> Now I am trying to fix the last problem in this patch: three tests 
> have indicated a problem related to incorrect conversion. I don't 
> think it can be serious, but I haven't figured out where the mistake 
> is yet.
>

I fixed this issue and added some cosmetic refactoring.

The changes are presented in the or_patch_changes.diff file.

-- 
Regards,
Alena Rybakina
Postgres Professional
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 25a4235dbd9..46212a77c64 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -44,7 +44,7 @@
 
 /* GUC parameters */
 bool		Transform_null_equals = false;
-bool		or_transform_limit = false;
+bool		enable_or_transformation = false;

 static Node *transformExprRecurse(ParseState *pstate, Node *expr);
@@ -108,7 +108,7 @@ typedef struct OrClauseGroupEntry
 	List		   *consts;
 	Oid				scalar_type;
 	Oid				opno;
-	Expr 		   *expr;
+	Node 		   *expr;
 } OrClauseGroupEntry;
 
 static int
@@ -189,16 +189,16 @@ transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
 									  HASH_ELEM | HASH_FUNCTION | HASH_COMPARE);
 
 	/* If this is not an 'OR' expression, skip the transformation */
-	if (expr_orig->boolop != OR_EXPR || !or_transform_limit || len_ors == 1 || !or_group_htab)
+	if (expr_orig->boolop != OR_EXPR || !enable_or_transformation || len_ors == 1 || !or_group_htab)
 		return transformBoolExpr(pstate, (BoolExpr *) expr_orig);
 
 	foreach(lc, expr_orig->args)
 	{
 		Node			   *arg = lfirst(lc);
-		Node			   *orqual;
-		Node			   *const_expr;
-		Node			   *nconst_expr;
-		OrClauseGroupEntry *gentry;
+		Node			   *orqual = NULL;
+		Node			   *const_expr = NULL;
+		Node			   *nconst_expr = NULL;
+		OrClauseGroupEntry *gentry = NULL;
 		bool				found;
 		char		   	   *str;
 
@@ -270,7 +270,7 @@ transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
 		gentry = hash_search(or_group_htab, &str, HASH_ENTER, &found);
 		gentry->node = nconst_expr;
 		gentry->consts = list_make1(const_expr);
-		gentry->expr = (Expr *) orqual;
+		gentry->expr = orqual;
 		gentry->hash_leftvar_key = str;
 	}
 
@@ -283,7 +283,6 @@ transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
 		* transformed bool expression.
 		*/
 		hash_destroy(or_group_htab);
-		list_free(or_list);
 		return (Node *) makeBoolExpr(OR_EXPR, or_list, expr_orig->location);
 	}
 	else
@@ -345,9 +344,9 @@ transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
 				 * OK: coerce all the right-hand non-Var inputs to the common
 				 * type and build an ArrayExpr for them.
 				 */
-				List	   *aexprs;
-				ArrayExpr  *newa;
-				ScalarArrayOpExpr *saopexpr;
+				List	   *aexprs = NIL;
+				ArrayExpr  *newa = NULL;
+				ScalarArrayOpExpr *saopexpr = NULL;
 				ListCell *l;
 
 				aexprs = NIL;
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 54fd09abde7..3411f023df8 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1049,12 +1049,12 @@ struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 	{
-		{"or_transform_limit", PGC_USERSET, QUERY_TUNING_OTHER,
+		{"enable_or_transformation", PGC_USERSET, QUERY_TUNING_OTHER,
 			gettext_noop("Transform a sequence of OR clauses to an IN expression."),
 			gettext_noop("The planner will replace clauses like 'x=c1 OR x=c2 .."
 						 "to the clause 'x IN (c1,c2,...)'")
 		},
-		&or_transform_limit,
+		&enable_or_transformation,
 		false,
 		NULL, NULL, NULL
 	},
diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h
index 7a6943c116c..3a87de02859 100644
--- a/src/include/parser/parse_expr.h
+++ b/src/include/parser/parse_expr.h
@@ -17,7 +17,7 @@
 
 /* GUC parameters */
 extern PGDLLIMPORT bool Transform_null_equals;
-extern PGDLLIMPORT bool or_transform_limit;
+extern PGDLLIMPORT bool enable_or_transformation;

diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 29c2bc6a2b2..dbc8bc3bed0 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1883,7 +1883,7 @@ SELECT count(*) FROM tenk1
     10
 (1 row)
 
-SET or_transform_limit = on;
+SET enable_or_transformation = on;
 EXPLAIN (COSTS OFF)
 SELECT * FROM tenk1
   WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -1997,7 +1997,7 @@ SELECT count(*) FROM tenk1
     10
 (1 row)
 
-RESET or_transform_limit;
+RESET enable_or_transformation;
 --
 -- Check behavior with duplicate index column contents
 --
diff --git a/src/test/regress/expected/guc.out b/src/test/regress/expected/guc.out
index c052b113eea..0f2b1b16200 100644
--- a/src/test/regress/expected/guc.out
+++ b/src/test/regress/expected/guc.out
@@ -861,7 +861,7 @@ SELECT name FROM tab_settings_flags
            name            
 ---------------------------
  default_statistics_target
- or_transform_limit
+ enable_or_transformation
 (2 rows)
 
 -- Runtime-computed GUCs should be part of the preset category.
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 97b4964812e..d969b31e3e3 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4207,7 +4207,7 @@ select * from tenk1 a join tenk1 b on
                            Index Cond: (unique2 = 7)
 (19 rows)
 
-SET or_transform_limit = on;
+SET enable_or_transformation = on;
 explain (costs off)
 select * from tenk1 a join tenk1 b on
   (a.unique1 = 1 and b.unique1 = 2) or
@@ -4256,7 +4256,7 @@ select * from tenk1 a join tenk1 b on
                            Index Cond: (unique1 = 3)
 (15 rows)
 
-RESET or_transform_limit;
+RESET enable_or_transformation;
 --
 -- test placement of movable quals in a parameterized join tree
 --
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 1789d3c1fd7..fe815417c13 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -101,7 +101,7 @@ explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'
          Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
 (5 rows)
 
-SET or_transform_limit = on;
+SET enable_or_transformation = on;
 explain (costs off) select * from lp where a = 'a' or a = 'c';
                   QUERY PLAN                   
 -----------------------------------------------
@@ -122,7 +122,7 @@ explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'
          Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
 (5 rows)
 
-RESET or_transform_limit;
+RESET enable_or_transformation;
 explain (costs off) select * from lp where a <> 'g';
              QUERY PLAN             
 ------------------------------------
@@ -693,7 +693,7 @@ explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a =
          Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
 (11 rows)
 
-SET or_transform_limit = on;
+SET enable_or_transformation = on;
 explain (costs off) select * from rlp where a = 1 or a = 7;
                 QUERY PLAN                
 ------------------------------------------
@@ -706,29 +706,29 @@ explain (costs off) select * from rlp where a = 1 or b = 'ab';
 -------------------------------------------------------
  Append
    ->  Seq Scan on rlp1 rlp_1
-         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
    ->  Seq Scan on rlp2 rlp_2
-         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
    ->  Seq Scan on rlp3abcd rlp_3
-         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
    ->  Seq Scan on rlp4_1 rlp_4
-         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
    ->  Seq Scan on rlp4_2 rlp_5
-         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
    ->  Seq Scan on rlp4_default rlp_6
-         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
    ->  Seq Scan on rlp5_1 rlp_7
-         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
    ->  Seq Scan on rlp5_default rlp_8
-         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
    ->  Seq Scan on rlp_default_10 rlp_9
-         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
    ->  Seq Scan on rlp_default_30 rlp_10
-         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
    ->  Seq Scan on rlp_default_null rlp_11
-         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
    ->  Seq Scan on rlp_default_default rlp_12
-         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
 (25 rows)
 
 explain (costs off) select * from rlp where a > 20 and a < 27;
@@ -849,7 +849,7 @@ explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a =
          Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
 (11 rows)
 
-RESET or_transform_limit;
+RESET enable_or_transformation;
 -- multi-column keys
 create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
 create table mc3p_default partition of mc3p default;
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 271313ebf86..8a380c29a4c 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -123,6 +123,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_memoize                 | on
  enable_mergejoin               | on
  enable_nestloop                | on
+ enable_or_transformation       | off
  enable_parallel_append         | on
  enable_parallel_hash           | on
  enable_partition_pruning       | on
diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out
index 8a31e2e670d..be4d88200b6 100644
--- a/src/test/regress/expected/tidscan.out
+++ b/src/test/regress/expected/tidscan.out
@@ -56,7 +56,7 @@ SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
  (0,2) |  2
 (2 rows)
 
-SET or_transform_limit = on;
+SET enable_or_transformation = on;
 EXPLAIN (COSTS OFF)
 SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
                       QUERY PLAN                       
@@ -72,7 +72,7 @@ SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
  (0,2) |  2
 (2 rows)
 
-RESET or_transform_limit;
+RESET enable_or_transformation;
 -- ctid = ScalarArrayOp - implemented as tidscan
 EXPLAIN (COSTS OFF)
 SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index a709b2c1abc..48bb1bc0a0a 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -737,7 +737,7 @@ SELECT count(*) FROM tenk1
 SELECT count(*) FROM tenk1
   WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
 
-SET or_transform_limit = on;
+SET enable_or_transformation = on;
 EXPLAIN (COSTS OFF)
 SELECT * FROM tenk1
   WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -767,7 +767,7 @@ 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);
-RESET or_transform_limit;
+RESET enable_or_transformation;
 
 --
 -- Check behavior with duplicate index column contents
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index d95663b9c30..3d072cce498 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1396,7 +1396,7 @@ 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);
-SET or_transform_limit = on;
+SET enable_or_transformation = on;
 explain (costs off)
 select * from tenk1 a join tenk1 b on
   (a.unique1 = 1 and b.unique1 = 2) or
@@ -1405,7 +1405,7 @@ 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);
-RESET or_transform_limit;
+RESET enable_or_transformation;
 
 --
 -- test placement of movable quals in a parameterized join tree
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index 88709910592..1e270ae9c06 100644
--- a/src/test/regress/sql/partition_prune.sql
+++ b/src/test/regress/sql/partition_prune.sql
@@ -22,10 +22,10 @@ explain (costs off) select * from lp where a is null;
 explain (costs off) select * from lp where a = 'a' or a = 'c';
 explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
 
-SET or_transform_limit = on;
+SET enable_or_transformation = on;
 explain (costs off) select * from lp where a = 'a' or a = 'c';
 explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
-RESET or_transform_limit;
+RESET enable_or_transformation;
 
 explain (costs off) select * from lp where a <> 'g';
 explain (costs off) select * from lp where a <> 'a' and a <> 'd';
@@ -106,7 +106,7 @@ explain (costs off) select * from rlp where a = 1 and a = 3;	/* empty */
 explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);

-SET or_transform_limit = on;
+SET enable_or_transformation = on;
 explain (costs off) select * from rlp where a = 1 or a = 7;
 explain (costs off) select * from rlp where a = 1 or b = 'ab';
 explain (costs off) select * from rlp where a > 20 and a < 27;
@@ -119,7 +119,7 @@ explain (costs off) select * from rlp where a > 1 and a = 10;	/* only default */
 explain (costs off) select * from rlp where a > 1 and a >=15;	/* rlp3 onwards, including default */
 explain (costs off) select * from rlp where a = 1 and a = 3;	/* empty */
 explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);
-RESET or_transform_limit;
+RESET enable_or_transformation;
 
 -- multi-column keys
 create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql
index c735e219589..0499bedb9eb 100644
--- a/src/test/regress/sql/tidscan.sql
+++ b/src/test/regress/sql/tidscan.sql
@@ -22,11 +22,11 @@ EXPLAIN (COSTS OFF)
 SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
 SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
 
-SET or_transform_limit = on;
+SET enable_or_transformation = on;
 EXPLAIN (COSTS OFF)
 SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
 SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
-RESET or_transform_limit;
+RESET enable_or_transformation;
 
From 441448c9d8e0c5b63d0e9ea4d2da001b648311ce Mon Sep 17 00:00:00 2001

 src/backend/parser/parse_expr.c               | 298 +++++++++++++++++-
 src/test/regress/expected/sysviews.out        |   1 +
 14 files changed, 743 insertions(+), 2 deletions(-)

index 64c582c344c..46212a77c64 100644
 
 /* GUC parameters */
 bool		Transform_null_equals = false;
+bool		enable_or_transformation = false;

@@ -98,7 +100,301 @@ static Expr *make_distinct_op(ParseState *pstate, List *opname,
+	Node 		   *expr;
+	if (expr_orig->boolop != OR_EXPR || !enable_or_transformation || len_ors == 1 || !or_group_htab)
+		Node			   *orqual = NULL;
+		Node			   *const_expr = NULL;
+		Node			   *nconst_expr = NULL;
+		OrClauseGroupEntry *gentry = NULL;
 
+		/*
+		gentry->expr = orqual;
+				List	   *aexprs = NIL;
+				ArrayExpr  *newa = NULL;
+				ScalarArrayOpExpr *saopexpr = NULL;
 
 /*
  * transformExpr -
@@ -212,7 +508,7 @@ transformExprRecurse(ParseState *pstate, Node *expr)
 			}
 
 		case T_BoolExpr:
 
 		case T_FuncCall:
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index beed72abbd6..3411f023df8 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1048,6 +1048,16 @@ struct config_bool ConfigureNamesBool[] =
 		true,
 		NULL, NULL, NULL
 	},
+	{
+		{"enable_or_transformation", PGC_USERSET, QUERY_TUNING_OTHER,
+			gettext_noop("Transform a sequence of OR clauses to an IN expression."),
+			gettext_noop("The planner will replace clauses like 'x=c1 OR x=c2 .."
+						 "to the clause 'x IN (c1,c2,...)'")
+		},
+		&enable_or_transformation,
+		false,
+		NULL, NULL, NULL
+	},
 	{
 		/*
 		 * Not for general use --- used by SET SESSION AUTHORIZATION and SET
diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h
index 7d38ca75f7b..3a87de02859 100644
--- a/src/include/parser/parse_expr.h
+++ b/src/include/parser/parse_expr.h
@@ -17,6 +17,7 @@
 
 /* GUC parameters */
 extern PGDLLIMPORT bool Transform_null_equals;
+extern PGDLLIMPORT bool enable_or_transformation;

index acfd9d1f4f7..dbc8bc3bed0 100644
 
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+RESET enable_or_transformation;
index 127c9532976..0f2b1b16200 100644
+ enable_or_transformation
+(2 rows)
 
index ddc4e692329..d969b31e3e3 100644
 
+SET enable_or_transformation = on;
+explain (costs off)
+RESET enable_or_transformation;
index 9a4c48c0556..fe815417c13 100644
 
+SET enable_or_transformation = on;
+RESET enable_or_transformation;
 
+SET enable_or_transformation = on;
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
+         Filter: (((b)::text = 'ab'::text) OR (a = 1))
+RESET enable_or_transformation;
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 271313ebf86..8a380c29a4c 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -123,6 +123,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_memoize                 | on
  enable_mergejoin               | on
  enable_nestloop                | on
+ enable_or_transformation       | off
  enable_parallel_append         | on
  enable_parallel_hash           | on
  enable_partition_pruning       | on
index f133b5a4ac7..be4d88200b6 100644
 
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Tid Scan on tidscan
+   TID Cond: (ctid = ANY ('{"(0,2)","(0,1)"}'::tid[]))
+(2 rows)
+
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+ ctid  | id 
+-------+----
+ (0,1) |  1
+ (0,2) |  2
+(2 rows)
+
+RESET enable_or_transformation;
 -- ctid = ScalarArrayOp - implemented as tidscan
 EXPLAIN (COSTS OFF)
 SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index d49ce9f3007..48bb1bc0a0a 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -737,6 +737,38 @@ SELECT count(*) FROM tenk1
 SELECT count(*) FROM tenk1
   WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
 
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+RESET enable_or_transformation;
index a41787d1f1e..3d072cce498 100644
+SET enable_or_transformation = on;
+RESET enable_or_transformation;
 
index 7bf3920827f..1e270ae9c06 100644
+SET enable_or_transformation = on;
+RESET enable_or_transformation;
 
+
+SET enable_or_transformation = on;
+RESET enable_or_transformation;
index 313e0fb9b67..0499bedb9eb 100644
 
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+RESET enable_or_transformation;
+
 -- ctid = ScalarArrayOp - implemented as tidscan
 EXPLAIN (COSTS OFF)
 SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index bf50a321198..87f2967648b 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1640,6 +1640,7 @@ NumericVar
 OM_uint32
 OP
 OSAPerGroupState
+OrClauseGroupEntry
 OSAPerQueryState
 OSInfo
 OSSLCipher
-- 
2.34.1

---

From: "a.rybakina" <[email-masked]>
Date: Mon, 13 Nov 2023 22:48:23 +0300

Hi, all!

These days I was porting a patch for converting or expressions to ANY to 
the choose_bitmap_and function. Unfortunately, it is not possible to 
transfer the conversion there, since expressions are processed one by 
one, as far as I saw. Therefore, I tried to make the conversion earlier 
in the generate_bitmap_or_paths function, there is just a loop bypass. 
The patch turns out to be quite complicated, in my opinion, and to be 
honest, it does not work fully yet. Also, due to the fact that the index 
for the ScalarOpExpr expression is created earlier (approximately 344 
lines in the src/backend/optimizer/path/indxpath.c file), we had to call 
the generate_bitmap_or_paths function earlier. I haven't seen yet what 
problems this could potentially lead to. Patch in the attached diff file.

In the last letter, I had an incorrect numbering for the original patch, 
corrected, respectively, it is unclear whether the tests in CI were 
normal. Corrected it.

 
 /* GUC parameters */

+		/*
 
 /*
  * transformExpr -
 
 		case T_BoolExpr:
 
 		case T_FuncCall:
 
 /* GUC parameters */

 

 

 

+

diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 03a5fbdc6dc..754c83816f7 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -34,6 +34,11 @@
 #include "optimizer/restrictinfo.h"
 #include "utils/lsyscache.h"
 #include "utils/selfuncs.h"
+#include "common/hashfn.h"
+#include "utils/lsyscache.h"
+#include "parser/parse_expr.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_oper.h"

 /* XXX see PartCollMatchesExprColl */
@@ -73,6 +78,36 @@ typedef struct
 	int			indexcol;		/* index column we want to match to */
 } ec_member_matches_arg;
 
+	RestrictInfo	*ri;
+bool		enable_or_transformation = true;
 
 static void consider_index_join_clauses(PlannerInfo *root, RelOptInfo *rel,
 										IndexOptInfo *index,
@@ -193,6 +228,55 @@ static bool ec_member_matches_indexcol(PlannerInfo *root, RelOptInfo *rel,
 									   EquivalenceClass *ec, EquivalenceMember *em,
 									   void *arg);
 
+static char *
+/*
+ * Pass through baserestrictinfo clauses and try to convert OR clauses into IN
+ * Return a modified clause list or just the same baserestrictinfo, if no
+ * changes have made.
+ * XXX: do not change source list of clauses at all.
+ */
+
+
 
 /*
  * create_index_paths()
@@ -250,6 +334,13 @@ create_index_paths(PlannerInfo *root, RelOptInfo *rel)
 	/* Bitmap paths are collected and then dealt with at the end */
 	bitindexpaths = bitjoinpaths = joinorclauses = NIL;
 
+	/*
+	 * Generate BitmapOrPaths for any suitable OR-clauses present in the
+	 * restriction list.  Add these to bitindexpaths.
+	 */
+	indexpaths = generate_bitmap_or_paths(root, rel,
+										  rel->baserestrictinfo, NIL);
+
 	/* Examine each index in turn */
 	foreach(lc, rel->indexlist)
 	{
@@ -309,13 +400,7 @@ create_index_paths(PlannerInfo *root, RelOptInfo *rel)
 										&eclauseset,
 										&bitjoinpaths);
 	}
-
-	/*
-	 * Generate BitmapOrPaths for any suitable OR-clauses present in the
-	 * restriction list.  Add these to bitindexpaths.
-	 */
-	indexpaths = generate_bitmap_or_paths(root, rel,
-										  rel->baserestrictinfo, NIL);
+	
 	bitindexpaths = list_concat(bitindexpaths, indexpaths);
 
 	/*
@@ -1237,6 +1322,7 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
 	List	   *result = NIL;
 	List	   *all_clauses;
 	ListCell   *lc;
+	List	   *modified_rinfo = NIL;
 
 	/*
 	 * We can use both the current and other clauses as context for
@@ -1247,23 +1333,45 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
 	foreach(lc, clauses)
 	{
 		RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
-		List	   *pathlist;
+		List	   *pathlist = NIL;
 		Path	   *bitmapqual;
-		ListCell   *j;
+		ListCell   *lc_rargs, *lc_eargs;
+		HASHCTL		info;
+		HTAB 	   *or_group_htab = NULL;
+		int 		len_ors = 0;
+		List	   *or_list = NIL;
 
 		/* Ignore RestrictInfos that aren't ORs */
 		if (!restriction_is_or_clause(rinfo))
+		{
+			modified_rinfo = lappend(modified_rinfo, rinfo);
 			continue;
+		}
+
+		len_ors = list_length(((BoolExpr *) rinfo->orclause)->args);
+
+		MemSet(&info, 0, sizeof(info));
+		info.keysize = sizeof(char *);
+		info.entrysize = sizeof(OrClauseGroupEntry);
+		info.hash = or_name_hash;
+		info.match = or_name_match;
+		or_group_htab = hash_create("OR Groups",
+										len_ors,
+									  &info,
+									  HASH_ELEM | HASH_FUNCTION | HASH_COMPARE);
 
 		/*
 		 * We must be able to match at least one index to each of the arms of
 		 * the OR, else we can't use it.
 		 */
 		pathlist = NIL;
-		foreach(j, ((BoolExpr *) rinfo->orclause)->args)
+		forboth(lc_eargs, ((BoolExpr *) rinfo->clause)->args,
+				lc_rargs, ((BoolExpr *) rinfo->orclause)->args)
 		{
-			Node	   *orarg = (Node *) lfirst(j);
+			Node	   *orarg = (Node *) lfirst(lc_rargs);
 			List	   *indlist;
+			OrClauseGroupEntry *gentry;
+			Node	   *orqual = (Node *) lfirst(lc_eargs);
 
 			/* OR arguments should be ANDs or sub-RestrictInfos */
 			if (is_andclause(orarg))
@@ -1279,36 +1387,309 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
 									  generate_bitmap_or_paths(root, rel,
 															   andargs,
 															   all_clauses));
+				or_list = lappend(or_list, orqual);
 			}
 			else
 			{
 				RestrictInfo *ri = castNode(RestrictInfo, orarg);
-				List	   *orargs;
+				RestrictInfo *sub_rinfo = lfirst_node(RestrictInfo, lc_rargs);
+				Node *nconst_expr = NULL;
+				Node *const_expr = NULL;
+				char *str = NULL;
+				bool found;
 
 				Assert(!restriction_is_or_clause(ri));
-				orargs = list_make1(ri);
 
-				indlist = build_paths_for_OR(root, rel,
-											 orargs,
-											 all_clauses);
+				if (!IsA(lfirst(lc_rargs), RestrictInfo) || !enable_or_transformation ||
+				/* Check: it is an expr of the form 'F(x) oper ConstExpr' */
+					!IsA(sub_rinfo->clause, OpExpr) ||
+					!(bms_is_empty(sub_rinfo->left_relids) ^
+					bms_is_empty(sub_rinfo->right_relids)) ||
+					contain_volatile_functions((Node *) orarg))
+				{
+					List	   *orargs;
+					orargs = list_make1(ri);
+					indlist = build_paths_for_OR(root, rel,
+												orargs,
+												all_clauses);
+					/*
+					* If nothing matched this arm, we can't do anything with this OR
+					* clause.
+					*/
+					if (indlist == NIL)
+					{
+						pathlist = NIL;
+						break;
+					}
+
+					/*
+					* OK, pick the most promising AND combination, and add it to
+					* pathlist.
+					*/
+					bitmapqual = choose_bitmap_and(root, rel, indlist);
+					pathlist = lappend(pathlist, bitmapqual);
+					or_list = lappend(or_list, orqual);
+					continue;
+				}
+
+				/*
+				* 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.
+				*/
+				const_expr =bms_is_empty(sub_rinfo->left_relids) ?
+												get_leftop(sub_rinfo->clause) :
+												get_rightop(sub_rinfo->clause);
+				nconst_expr = bms_is_empty(sub_rinfo->left_relids) ?
+												get_rightop(sub_rinfo->clause) :
+												get_leftop(sub_rinfo->clause);
+
+				str = get_key_nconst_node(nconst_expr);
+
+				if (!op_mergejoinable(((OpExpr*)sub_rinfo->clause)->opno, exprType(nconst_expr)) || str == NULL)
+				{
+					List	   *orargs;
+					orargs = list_make1(ri);
+					indlist = build_paths_for_OR(root, rel,
+												orargs,
+												all_clauses);
+					/*
+					* If nothing matched this arm, we can't do anything with this OR
+					* clause.
+					*/
+					if (indlist == NIL)
+					{
+						pathlist = NIL;
+						break;
+					}
+
+					/*
+					* OK, pick the most promising AND combination, and add it to
+					* pathlist.
+					*/
+					bitmapqual = choose_bitmap_and(root, rel, indlist);
+					pathlist = lappend(pathlist, bitmapqual);
+					or_list = lappend(or_list, orqual);
+					continue;
+				}
+
+				/*
+				* At this point we definitely have a transformable clause.
+				* Classify it and add into specific group of clauses, or create new
+				* group.
+				* TODO: to manage complexity in the case of many different clauses
+				* (X1=C1) OR (X2=C2 OR) ... (XN = CN) we could invent something
+				* like a hash table. But also we believe, that the case of many
+				* different variable sides is very rare.
+				*/
+				gentry = hash_search(or_group_htab, &str, HASH_FIND, &found);
+
+				if (found)
+				{
+					gentry->consts = lappend(gentry->consts, const_expr);
+					/*
+						* The clause classified successfully and added into existed
+						* clause group.
+						*/
+					continue;
+				}
+
+				/* New clause group needed */
+				gentry = hash_search(or_group_htab, &str, HASH_ENTER, &found);
+				gentry->node = nconst_expr;
+				gentry->consts = list_make1(const_expr);
+				gentry->expr = orqual;
+				gentry->hash_leftvar_key = str;
+				gentry->ri = ri;
 			}
+		}
+
+		if (!(or_group_htab && (hash_get_num_entries(or_group_htab) < 1 ||
+	                         hash_get_num_entries(or_group_htab) == len_ors)))
+		{
+			HASH_SEQ_STATUS		hash_seq;
+			OrClauseGroupEntry *gentry;
+			bool success = false;
+
+			hash_seq_init(&hash_seq, or_group_htab);
+
+			/* Let's convert each group of clauses to an IN operation. */
 
 			/*
-			 * If nothing matched this arm, we can't do anything with this OR
-			 * clause.
-			 */
-			if (indlist == NIL)
-				pathlist = NIL;
-				break;
+				List *orargs;
+				List *indlist;
+					orargs = list_make1(gentry->ri);
+					indlist = build_paths_for_OR(root, rel,
+												orargs,
+												all_clauses);
+					* If nothing matched this arm, we can't do anything with this OR
+					* clause.
+					if (indlist == NIL)
+						pathlist = NIL;
+						break;
+					* OK, pick the most promising AND combination, and add it to
+					* pathlist.
+					bitmapqual = choose_bitmap_and(root, rel, indlist);
+					pathlist = lappend(pathlist, bitmapqual);
+						rexpr = coerce_to_common_type(NULL, rexpr,
+							make_scalar_array_op(NULL,
+					success = true;
+					orargs = list_make1(gentry->ri);
+					indlist = build_paths_for_OR(root, rel,
+												orargs,
+												all_clauses);
+					* If nothing matched this arm, we can't do anything with this OR
+					* clause.
+					if (indlist == NIL)
+						pathlist = NIL;
+						break;
+					* OK, pick the most promising AND combination, and add it to
+					* pathlist.
+					bitmapqual = choose_bitmap_and(root, rel, indlist);
+					pathlist = lappend(pathlist, bitmapqual);
 
-			/*
-			 * OK, pick the most promising AND combination, and add it to
-			 * pathlist.
-			 */
-			bitmapqual = choose_bitmap_and(root, rel, indlist);
-			pathlist = lappend(pathlist, bitmapqual);
+			if (!success)
+			{
+				/*
+				* Each group contains only one element - use rinfo as is.
+				*/
+				modified_rinfo = lappend(modified_rinfo, rinfo);
+				list_free(or_list);
+			}
+			else
+			{
+				RestrictInfo *ri;
+				List *orargs;
+				List *indlist;
+				ri = make_restrictinfo(root,
+				  list_length(or_list) > 1 ? make_orclause(or_list) :
+											 (Expr *) linitial(or_list),
+				  ri->is_pushed_down,
+				  ri->has_clone,
+				  ri->is_clone,
+				  ri->pseudoconstant,
+				  ri->security_level,
+				  ri->required_relids,
+				  ri->incompatible_relids,
+				  ri->outer_relids);
+				
+				ri->eval_cost=rinfo->eval_cost;
+				ri->norm_selec=rinfo->norm_selec;
+				ri->outer_selec=rinfo->outer_selec;
+				ri->left_bucketsize=rinfo->left_bucketsize;
+				ri->right_bucketsize=rinfo->right_bucketsize;
+				ri->left_mcvfreq=rinfo->left_mcvfreq;
+				ri->right_mcvfreq=rinfo->right_mcvfreq;
+				modified_rinfo = lappend(modified_rinfo, ri);
+			}
 		}
 
 		/*
@@ -1322,10 +1703,10 @@ generate_bitmap_or_paths(PlannerInfo *root, RelOptInfo *rel,
 		}
 	}
 
+	rel->baserestrictinfo = modified_rinfo;
 	return result;
 }
 
-
 /*
  * choose_bitmap_and
  *		Given a nonempty list of bitmap paths, AND them into one path.

---

From: Andrei Lepikhov <[email-masked]>
Date: Mon, 20 Nov 2023 15:52:08 +0700

> The changes are presented in the or_patch_changes.diff file.

Looking into the patch, I found some trivial improvements (see attachment).
Also, it is not obvious that using a string representation of the clause 
as a hash table key is needed here. Also, by making a copy of the node 
in the get_key_nconst_node(), you replace the location field, but in the 
case of complex expression, you don't do the same with other nodes.
I propose to generate expression hash instead + prove the equality of 
two expressions by calling equal().

-- 
regards,
Andrei Lepikhov
Postgres Professional
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 25a4235dbd..de27d2646c 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -178,6 +178,10 @@ transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
 	HTAB 		   *or_group_htab = NULL;
 	int 			len_ors = list_length(expr_orig->args);
 
+	/* If this is not an 'OR' expression, skip the transformation */
+	if (!or_transform_limit || expr_orig->boolop != OR_EXPR || len_ors < 2)
+		return transformBoolExpr(pstate, (BoolExpr *) expr_orig);
+
 	MemSet(&info, 0, sizeof(info));
 	info.keysize = sizeof(char *);
 	info.entrysize = sizeof(OrClauseGroupEntry);
@@ -188,10 +192,6 @@ transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
 									  &info,
 									  HASH_ELEM | HASH_FUNCTION | HASH_COMPARE);
 
-	/* If this is not an 'OR' expression, skip the transformation */
-	if (expr_orig->boolop != OR_EXPR || !or_transform_limit || len_ors == 1 || !or_group_htab)
-		return transformBoolExpr(pstate, (BoolExpr *) expr_orig);
-
 	foreach(lc, expr_orig->args)
 	{
 		Node			   *arg = lfirst(lc);

---

From: "a.rybakina" <[email-masked]>
Date: Mon, 20 Nov 2023 12:29:47 +0300

On 20.11.2023 11:52, Andrei Lepikhov wrote:
> Also, it is not obvious that using a string representation of the 
> clause as a hash table key is needed here. Also, by making a copy of 
> the node in the get_key_nconst_node(), you replace the location field, 
> but in the case of complex expression, you don't do the same with 
> other nodes.
Thank you! I agree with your changes.

---

From: Alena Rybakina <[email-masked]>
Date: Tue, 21 Nov 2023 03:50:15 +0300

> two expressions by calling equal().
I was thinking about your last email and a possible error where the 
location field may not be cleared in complex expressions. Unfortunately, 
I didn't come up with any examples either, but I think I was able to 
handle this with a special function that removes location-related 
patterns. The alternative to this is to bypass this expression, but I 
think it will be more invasive. In addition, I have added changes 
related to the hash table: now the key is of type int.

All changes are displayed in the attached 
v9-0001-Replace-OR-clause-to_ANY.diff.txt file.

I haven't measured it yet. But what do you think about these changes?

-- 
Regards,
Alena Rybakina
Postgres Professional
From 8e579b059ffd415fc477e0e8930e718084e58683 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email-masked]>
Date: Tue, 21 Nov 2023 03:22:13 +0300
Subject: [PATCH] [PATCH] Replace OR clause to ANY expressions. Replace (X=N1)
 OR (X=N2) ... with X = ANY(N1, N2) on the stage of the optimiser when we are
 still working with a tree expression. Firstly, we do not try to make a
 transformation for "non-or" expressions or inequalities and the creation of a
 relation with "or" expressions occurs according to the same scenario.
 Secondly, we do not make transformations if there are less than set
 or_transform_limit. Thirdly, it is worth considering that we consider "or"
 expressions only at the current level.

Authors: Alena Rybakina <[email-masked]>, Andrey Lepikhov <[email-masked]>
Reviewed-by: Peter Geoghegan <[email-masked]>, Ranier Vilela <[email-masked]>
Reviewed-by: Alexander Korotkov <[email-masked]>, Robert Haas <[email-masked]>
---
 src/backend/parser/parse_expr.c               | 280 +++++++++++++++++-
 src/backend/utils/misc/guc_tables.c           |  10 +
 src/include/parser/parse_expr.h               |   1 +
 src/test/regress/expected/create_index.out    | 115 +++++++
 src/test/regress/expected/guc.out             |   3 +-
 src/test/regress/expected/join.out            |  50 ++++
 src/test/regress/expected/partition_prune.out | 156 ++++++++++
 src/test/regress/expected/sysviews.out        |   3 +-
 src/test/regress/expected/tidscan.out         |  17 ++
 src/test/regress/sql/create_index.sql         |  32 ++
 src/test/regress/sql/join.sql                 |  10 +
 src/test/regress/sql/partition_prune.sql      |  22 ++
 src/test/regress/sql/tidscan.sql              |   6 +
 src/tools/pgindent/typedefs.list              |   1 +
 14 files changed, 703 insertions(+), 3 deletions(-)

index 64c582c344c..290422005a3 100644
 
 /* GUC parameters */

 static Node *transformExprRecurse(ParseState *pstate, Node *expr);
@@ -98,7 +100,283 @@ static Expr *make_distinct_op(ParseState *pstate, List *opname,
 							  Node *ltree, Node *rtree, int location);
 static Node *make_nulltest_from_distinct(ParseState *pstate,
 										 A_Expr *distincta, Node *arg);
+typedef struct OrClauseGroupEntry
+{
+	int32			hash_leftvar_key;
+
+	Node		   *node;
+	List		   *consts;
+	Oid				scalar_type;
+	Oid				opno;
+	Node 		   *expr;
+} OrClauseGroupEntry;
+
+/*
+ * TODO: consider different algorithm to manage complexity
+ * in the case of many different clauses,
+ * like Rabin-Karp or Boyer–Moore algorithms.
+ */
+static char *
+clear_patterns(const char *str, const char *start_pattern)
+{
+	int			i = 0;
+	int			j = 0;
+	int			start_pattern_len = strlen(start_pattern);
+	char	   *res = palloc0(sizeof(*res) * (strlen(str) + 1));
+
+	for (i = 0; str[i];)
+	{
+		if (i >= start_pattern_len && strncmp(&str[i - start_pattern_len],
+											  start_pattern,
+											  start_pattern_len) == 0)
+		{
+			while (str[i] && !(str[i] == '{' || str[i] == '}'))
+				i++;
+		}
+		if (str[i])
+			res[j++] = str[i++];
+	}
+
+	return res;
+}
+
+static int32
+get_key_nconst_node(Node *nconst_node)
+{
+	char *str = nodeToString(nconst_node);
+
+	str = clear_patterns(str, " :location");
+
+	if (str == NULL)
+		return 0;
+
+	return DatumGetInt32(hash_any((unsigned char *)str, strlen(str)));
+}
+
+static Node *
+transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
+{
+	List		   *or_list = NIL;
+	ListCell	   *lc;
+	HASHCTL			info;
+	HTAB 		   *or_group_htab = NULL;
+	int 			len_ors = list_length(expr_orig->args);
+
+	MemSet(&info, 0, sizeof(info));
+	info.keysize = sizeof(int);
+	info.entrysize = sizeof(OrClauseGroupEntry);
+	or_group_htab = hash_create("OR Groups",
+									  len_ors,
+									  &info,
+									  HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
+
+	/* If this is not an 'OR' expression, skip the transformation */
+	if (expr_orig->boolop != OR_EXPR || !enable_or_transformation || len_ors == 1 || !or_group_htab)
+		return transformBoolExpr(pstate, (BoolExpr *) expr_orig);
+
+	foreach(lc, expr_orig->args)
+	{
+		Node			   *arg = lfirst(lc);
+		Node			   *orqual = NULL;
+		Node			   *const_expr = NULL;
+		Node			   *nconst_expr = NULL;
+		OrClauseGroupEntry *gentry = NULL;
+		bool				found;
+		int32				hash;
+
+		/* At first, transform the arg and evaluate constant expressions. */
+		orqual = transformExprRecurse(pstate, (Node *) arg);
+		orqual = coerce_to_boolean(pstate, orqual, "OR");
+		orqual = eval_const_expressions(NULL, orqual);
+
+		if (!IsA(orqual, OpExpr))
+		{
+			or_list = lappend(or_list, orqual);
+			continue;
+		}
 
+		/*
+		hash = get_key_nconst_node(nconst_expr);
+		if (!op_mergejoinable(((OpExpr *) orqual)->opno, exprType(nconst_expr)) || hash == 0)
+		gentry = hash_search(or_group_htab, &hash, HASH_FIND, &found);
+		gentry = hash_search(or_group_htab, &hash, HASH_ENTER, &found);
+		gentry->hash_leftvar_key = hash;
 
 /*
  * transformExpr -
@@ -212,7 +490,7 @@ transformExprRecurse(ParseState *pstate, Node *expr)
 			}
 
 		case T_BoolExpr:
 
 		case T_FuncCall:
index b764ef69980..e2495529d29 100644
 
 /* GUC parameters */

 
 
 -- Runtime-computed GUCs should be part of the preset category.
 SELECT name FROM tab_settings_flags
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 2c73270143b..5af2cf09107 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4233,6 +4233,56 @@ select * from tenk1 a join tenk1 b on
                            Index Cond: (unique2 = 7)
 (19 rows)
 
index 9a4c48c0556..804fea9f34c 100644
 
@@ -671,6 +693,140 @@ explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a =
 
index 271313ebf86..566f51df428 100644
@@ -133,7 +134,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(22 rows)
+(23 rows)
 
 -- There are always wait event descriptions for various types.
 select type, count(*) > 0 as ok FROM pg_wait_events
diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out
index f133b5a4ac7..be4d88200b6 100644
--- a/src/test/regress/expected/tidscan.out
+++ b/src/test/regress/expected/tidscan.out
@@ -56,6 +56,23 @@ SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
  (0,2) |  2
 (2 rows)

index 8a8a63bd2f1..55d7e2ae7d6 100644
@@ -1408,6 +1408,16 @@ explain (costs off)

+
 
index dba3498a13e..c9004250a5f 100644
@@ -1641,6 +1641,7 @@ NumericVar
 OM_uint32
 OP
 OSAPerGroupState
+OrClauseGroupEntry
 OSAPerQueryState
 OSInfo
 OSSLCipher
-- 
2.34.1

diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 46212a77c64..290422005a3 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -102,7 +102,7 @@ static Node *make_nulltest_from_distinct(ParseState *pstate,
 										 A_Expr *distincta, Node *arg);
 typedef struct OrClauseGroupEntry
 {
-	char		   *hash_leftvar_key;
+	int32			hash_leftvar_key;
 
 	Node		   *node;
 	List		   *consts;
@@ -111,63 +111,47 @@ typedef struct OrClauseGroupEntry
 	Node 		   *expr;
 } OrClauseGroupEntry;
 
-static int
-or_name_match(const void *key1, const void *key2, Size keysize)
+/*
+ * TODO: consider different algorithm to manage complexity
+ * in the case of many different clauses,
+ * like Rabin-Karp or Boyer–Moore algorithms.
+ */
+static char *
+clear_patterns(const char *str, const char *start_pattern)
 {
-	const char *name1 = *(const char *const *) key1;
-	const char *name2 = *(const char *const *) key2;
+	int			i = 0;
+	int			j = 0;
+	int			start_pattern_len = strlen(start_pattern);
+	char	   *res = palloc0(sizeof(*res) * (strlen(str) + 1));
 
-	return strcmp(name1, name2);
-}
-
-static uint32
-or_name_hash(const void *key, Size keysize)
-{
-	const char *name = *(const char *const *) key;
+	for (i = 0; str[i];)
+	{
+		if (i >= start_pattern_len && strncmp(&str[i - start_pattern_len],
+											  start_pattern,
+											  start_pattern_len) == 0)
+		{
+			while (str[i] && !(str[i] == '{' || str[i] == '}'))
+				i++;
+		}
+		if (str[i])
+			res[j++] = str[i++];
+	}
 
-	return DatumGetInt32(hash_any((unsigned char *)name, strlen(name)));
+	return res;
 }
 
-static char *
+static int32
 get_key_nconst_node(Node *nconst_node)
 {
-	if (IsA(nconst_node, OpExpr))
-	{
-		OpExpr 	   *clause = (OpExpr*) nconst_node;
-		OpExpr	   *temp = makeNode(OpExpr);
-
-		temp->opno = clause->opno;
-		temp->opfuncid = InvalidOid;
-		temp->opresulttype = clause->opresulttype;
-		temp->opretset = clause->opretset;
-		temp->opcollid = clause->opcollid;
-		temp->inputcollid = clause->inputcollid;
-		temp->location = -1;
-
-		temp->args = list_copy(clause->args);
-		return nodeToString(temp);
-	}
-	else if (IsA(nconst_node, Var))
-	{
-		Var	   *clause = (Var*) nconst_node;
-		Var	   *var = makeNode(Var);
-
-		var->varno = clause->varno;
-		var->varattno = clause->varattno;
-		var->vartype = clause->vartype;
-		var->vartypmod = clause->vartypmod;
-		var->varcollid = clause->varcollid;
-		var->varlevelsup = clause->varlevelsup;
-		var->varattnosyn = clause->varattno;
-		var->location = -1;
-
-		return nodeToString(var);
-	}
-	else
-	{
-		return NULL;
-	}
- }
+	char *str = nodeToString(nconst_node);
+
+	str = clear_patterns(str, " :location");
+
+	if (str == NULL)
+		return 0;
+
+	return DatumGetInt32(hash_any((unsigned char *)str, strlen(str)));
+}
 
 static Node *
 transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
@@ -179,14 +163,12 @@ transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
 	int 			len_ors = list_length(expr_orig->args);
 
 	MemSet(&info, 0, sizeof(info));
-	info.keysize = sizeof(char *);
+	info.keysize = sizeof(int);
 	info.entrysize = sizeof(OrClauseGroupEntry);
-	info.hash = or_name_hash;
-	info.match = or_name_match;
 	or_group_htab = hash_create("OR Groups",
 									  len_ors,
 									  &info,
-									  HASH_ELEM | HASH_FUNCTION | HASH_COMPARE);
+									  HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
 
 	/* If this is not an 'OR' expression, skip the transformation */
 	if (expr_orig->boolop != OR_EXPR || !enable_or_transformation || len_ors == 1 || !or_group_htab)
@@ -200,7 +182,7 @@ transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
 		Node			   *nconst_expr = NULL;
 		OrClauseGroupEntry *gentry = NULL;
 		bool				found;
-		char		   	   *str;
+		int32				hash;
 
 		/* At first, transform the arg and evaluate constant expressions. */
 		orqual = transformExprRecurse(pstate, (Node *) arg);
@@ -237,9 +219,9 @@ transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
 			continue;
 		}
 
-		str = get_key_nconst_node(nconst_expr);
+		hash = get_key_nconst_node(nconst_expr);
 
-		if (!op_mergejoinable(((OpExpr *) orqual)->opno, exprType(nconst_expr)) || str == NULL)
+		if (!op_mergejoinable(((OpExpr *) orqual)->opno, exprType(nconst_expr)) || hash == 0)
 		{
 			or_list = lappend(or_list, orqual);
 			continue;
@@ -254,7 +236,7 @@ transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
 		* like a hash table. But also we believe, that the case of many
 		* different variable sides is very rare.
 		*/
-		gentry = hash_search(or_group_htab, &str, HASH_FIND, &found);
+		gentry = hash_search(or_group_htab, &hash, HASH_FIND, &found);
 
 		if (found)
 		{
@@ -267,11 +249,11 @@ transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
 		}
 
 		/* New clause group needed */
-		gentry = hash_search(or_group_htab, &str, HASH_ENTER, &found);
+		gentry = hash_search(or_group_htab, &hash, HASH_ENTER, &found);
 		gentry->node = nconst_expr;
 		gentry->consts = list_make1(const_expr);
 		gentry->expr = orqual;
-		gentry->hash_leftvar_key = str;
+		gentry->hash_leftvar_key = hash;
 	}
 
 	if (or_group_htab && (hash_get_num_entries(or_group_htab) < 1 ||
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index fe815417c13..804fea9f34c 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -702,34 +702,11 @@ explain (costs off) select * from rlp where a = 1 or a = 7;
 (2 rows)
 
 explain (costs off) select * from rlp where a = 1 or b = 'ab';
-                      QUERY PLAN                       
--------------------------------------------------------
- Append
-   ->  Seq Scan on rlp1 rlp_1
-         Filter: (((b)::text = 'ab'::text) OR (a = 1))
-   ->  Seq Scan on rlp2 rlp_2
-         Filter: (((b)::text = 'ab'::text) OR (a = 1))
-   ->  Seq Scan on rlp3abcd rlp_3
-         Filter: (((b)::text = 'ab'::text) OR (a = 1))
-   ->  Seq Scan on rlp4_1 rlp_4
-         Filter: (((b)::text = 'ab'::text) OR (a = 1))
-   ->  Seq Scan on rlp4_2 rlp_5
-         Filter: (((b)::text = 'ab'::text) OR (a = 1))
-   ->  Seq Scan on rlp4_default rlp_6
-         Filter: (((b)::text = 'ab'::text) OR (a = 1))
-   ->  Seq Scan on rlp5_1 rlp_7
-         Filter: (((b)::text = 'ab'::text) OR (a = 1))
-   ->  Seq Scan on rlp5_default rlp_8
-         Filter: (((b)::text = 'ab'::text) OR (a = 1))
-   ->  Seq Scan on rlp_default_10 rlp_9
-         Filter: (((b)::text = 'ab'::text) OR (a = 1))
-   ->  Seq Scan on rlp_default_30 rlp_10
-         Filter: (((b)::text = 'ab'::text) OR (a = 1))
-   ->  Seq Scan on rlp_default_null rlp_11
-         Filter: (((b)::text = 'ab'::text) OR (a = 1))
-   ->  Seq Scan on rlp_default_default rlp_12
-         Filter: (((b)::text = 'ab'::text) OR (a = 1))
-(25 rows)
+        QUERY PLAN        
+--------------------------
+ Result
+   One-Time Filter: false
+(2 rows)
 
 explain (costs off) select * from rlp where a > 20 and a < 27;
                QUERY PLAN                
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 8a380c29a4c..566f51df428 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -134,7 +134,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(22 rows)
+(23 rows)
 
 -- There are always wait event descriptions for various types.
 select type, count(*) > 0 as ok FROM pg_wait_events

---

From: Alena Rybakina <[email-masked]>
Date: Tue, 21 Nov 2023 14:31:11 +0300

On 21.11.2023 03:50, Alena Rybakina wrote:
>> the node in the get_key_nconst_node(), you replace the location 
>> field, but in the case of complex expression, you don't do the same 
>> with other nodes.
> location field may not be cleared in complex expressions. 
> Unfortunately, I didn't come up with any examples either, but I think 
> I was able to handle this with a special function that removes 
> location-related patterns. The alternative to this is to bypass this 
> expression, but I think it will be more invasive. In addition, I have 
> added changes related to the hash table: now the key is of type int.
>
Sorry, I lost your changes  during the revision process. I returned 
them. I raised the patch version just in case to run ci successfully.

-- 
Regards,
Alena Rybakina
Postgres Professional
From 66d1c479347d80ea45fc7aebaed873d45f9c4351 Mon Sep 17 00:00:00 2001
Date: Tue, 21 Nov 2023 14:20:56 +0300

---

index 64c582c344c..7b76c9f29a1 100644
 
 /* GUC parameters */

+		/*
 
 /*
  * transformExpr -
 
 		case T_BoolExpr:
 
 		case T_FuncCall:
 
 /* GUC parameters */

 

 

 

 
+

diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 290422005a3..7b76c9f29a1 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -162,6 +162,10 @@ transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
 	HTAB 		   *or_group_htab = NULL;
 	int 			len_ors = list_length(expr_orig->args);
 
+	/* If this is not an 'OR' expression, skip the transformation */
+	if (expr_orig->boolop != OR_EXPR || !enable_or_transformation || len_ors == 1 || !or_group_htab)
+		return transformBoolExpr(pstate, (BoolExpr *) expr_orig);
+
 	MemSet(&info, 0, sizeof(info));
 	info.keysize = sizeof(int);
 	info.entrysize = sizeof(OrClauseGroupEntry);
@@ -170,10 +174,6 @@ transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
 									  &info,
 									  HASH_ELEM | HASH_BLOBS | HASH_CONTEXT);
 
-	/* If this is not an 'OR' expression, skip the transformation */
-	if (expr_orig->boolop != OR_EXPR || !enable_or_transformation || len_ors == 1 || !or_group_htab)
-		return transformBoolExpr(pstate, (BoolExpr *) expr_orig);
-
 	foreach(lc, expr_orig->args)
 	{
 		Node			   *arg = lfirst(lc);

---

From: Andrei Lepikhov <[email-masked]>
Date: Thu, 23 Nov 2023 16:23:20 +0700

> them. I raised the patch version just in case to run ci successfully.

I think the usage of nodeToString for the generation of clause hash is 
too expensive and buggy.
Also, in the code, you didn't resolve hash collisions. So, I've 
rewritten the patch a bit (see the attachment).
One more thing: I propose to enable transformation by default at least 
for quick detection of possible issues.
This code changes tests in many places. But, as I see it, it mostly 
demonstrates the positive effect of the transformation.

-- 
regards,
Andrei Lepikhov
Postgres Professional
From 5071d02426ac3430f4dd61a8ad32c2847ba6f8a5 Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <[email-masked]>
Date: Thu, 23 Nov 2023 16:00:13 +0700
Subject: [PATCH] Transform OR clause to ANY expressions.

Replace (X=N1) OR (X=N2) ... with X = ANY(N1, N2) on the preliminary stage of
optimization when we are still working with a tree expression.
Sometimes it can lead to not optimal plan. But we think it is better to have
array of elements instead of a lot of OR clauses. Here is a room for further
optimizations on decomposing that array into more optimal parts.
---
 src/backend/nodes/queryjumblefuncs.c          |  30 ++
 src/backend/parser/parse_expr.c               | 285 +++++++++++++++++-
 src/backend/utils/misc/guc_tables.c           |  10 +
 src/backend/utils/misc/postgresql.conf.sample |   1 +
 src/include/nodes/queryjumble.h               |   1 +
 src/include/parser/parse_expr.h               |   1 +
 src/test/regress/expected/create_index.out    | 141 +++++++--
 src/test/regress/expected/create_view.out     |   2 +-
 src/test/regress/expected/guc.out             |   3 +-
 src/test/regress/expected/inherit.out         |   2 +-
 src/test/regress/expected/join.out            |  62 +++-
 src/test/regress/expected/partition_prune.out | 215 +++++++++++--
 src/test/regress/expected/rules.out           |  18 +-
 src/test/regress/expected/stats_ext.out       |  12 +-
 src/test/regress/expected/sysviews.out        |   3 +-
 src/test/regress/expected/tidscan.out         |  23 +-
 src/test/regress/sql/create_index.sql         |  32 ++
 src/test/regress/sql/join.sql                 |  10 +
 src/test/regress/sql/partition_prune.sql      |  22 ++
 src/test/regress/sql/tidscan.sql              |   6 +
 src/tools/pgindent/typedefs.list              |   2 +
 21 files changed, 815 insertions(+), 66 deletions(-)

diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index 281907a4d8..99207a8670 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -135,6 +135,36 @@ JumbleQuery(Query *query)
 	return jstate;
 }
 
+JumbleState *
+JumbleExpr(Expr *expr, uint64 *queryId)
+{
+	JumbleState *jstate = NULL;
+
+	Assert(queryId != NULL);
+
+	jstate = (JumbleState *) palloc(sizeof(JumbleState));
+
+	/* Set up workspace for query jumbling */
+	jstate->jumble = (unsigned char *) palloc(JUMBLE_SIZE);
+	jstate->jumble_len = 0;
+	jstate->clocations_buf_size = 32;
+	jstate->clocations = (LocationLen *)
+		palloc(jstate->clocations_buf_size * sizeof(LocationLen));
+	jstate->clocations_count = 0;
+	jstate->highest_extern_param_id = 0;
+
+	/* Compute query ID */
+	_jumbleNode(jstate, (Node *) expr);
+	*queryId = DatumGetUInt64(hash_any_extended(jstate->jumble,
+												jstate->jumble_len,
+												0));
+
+	if (*queryId == UINT64CONST(0))
+		*queryId = UINT64CONST(1);
+
+	return jstate;
+}
+
 /*
  * Enables query identifier computation.
  *
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 64c582c344..d782642771 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -22,6 +22,7 @@
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
+#include "nodes/queryjumble.h"
 #include "optimizer/optimizer.h"
 #include "parser/analyze.h"
 #include "parser/parse_agg.h"
@@ -43,6 +44,7 @@
 
 /* GUC parameters */

 static Node *transformExprRecurse(ParseState *pstate, Node *expr);
@@ -99,6 +101,287 @@ static Expr *make_distinct_op(ParseState *pstate, List *opname,
 static Node *make_nulltest_from_distinct(ParseState *pstate,
 										 A_Expr *distincta, Node *arg);
 
+typedef struct OrClauseGroupEntry
+{
+	Node		   *node;
+	List		   *consts;
+	Oid				scalar_type;
+	Oid				opno;
+	List 		   *exprs;
+} OrClauseGroupEntry;
+
+typedef struct OrClauseGroupEntries
+{
+	uint64	hashkey;
+	List   *list;
+} OrClauseGroupEntries;
+
+static Node *
+transformBoolExprOr(ParseState *pstate, BoolExpr *expr)
+{
+	List				   *or_list = NIL;
+	ListCell			   *lc;
+	HASHCTL					info;
+	HTAB 				   *or_group_htab = NULL;
+	int 					len_ors = list_length(expr->args);
+	HASH_SEQ_STATUS			hash_seq;
+	OrClauseGroupEntries   *entries;
+
+	/* If this is not an 'OR' expression, skip the transformation */
+	if (!enable_or_transformation || expr->boolop != OR_EXPR || len_ors < 2)
+		return transformBoolExpr(pstate, (BoolExpr *) expr);
+
+	MemSet(&info, 0, sizeof(info));
+	info.keysize = sizeof(uint64);
+	info.entrysize = sizeof(OrClauseGroupEntries);
+	or_group_htab = hash_create("OR Groups",
+								len_ors,
+								&info,
+								HASH_ELEM | HASH_BLOBS);
+
+	foreach(lc, expr->args)
+	{
+		Node				   *arg = lfirst(lc);
+		Node				   *orqual;
+		Node				   *const_expr;
+		Node				   *nconst_expr;
+		OrClauseGroupEntries   *entries;
+		OrClauseGroupEntry	   *entry = NULL;
+		bool					found;
+		uint64					hash;
+
+		/* At first, transform the arg and evaluate constant expressions. */
+		orqual = transformExprRecurse(pstate, (Node *) arg);
+		orqual = coerce_to_boolean(pstate, orqual, "OR");
+		orqual = eval_const_expressions(NULL, orqual);
+
+		if (!IsA(orqual, OpExpr))
+		{
+			or_list = lappend(or_list, orqual);
+			continue;
+		}
+
+		/*
+		 * 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.
+		 */
+		if (IsA(get_leftop(orqual), Const))
+		{
+			nconst_expr = get_rightop(orqual);
+			const_expr = get_leftop(orqual);
+		}
+		else if (IsA(get_rightop(orqual), Const))
+		{
+			const_expr = get_rightop(orqual);
+			nconst_expr = get_leftop(orqual);
+		}
+		else
+		{
+			or_list = lappend(or_list, orqual);
+			continue;
+		}
+
+		if (!op_mergejoinable(((OpExpr *) orqual)->opno, exprType(nconst_expr)))
+		{
+			or_list = lappend(or_list, orqual);
+			continue;
+		}
+
+		(void) JumbleExpr((Expr *) nconst_expr, &hash);
+
+		/*
+		* At this point we definitely have a transformable clause.
+		* Classify it and add into specific group of clauses, or create new
+		* group.
+		* TODO: to manage complexity in the case of many different clauses
+		* (X1=C1) OR (X2=C2 OR) ... (XN = CN) we could invent something
+		* like a hash table. But also we believe, that the case of many
+		* different variable sides is very rare.
+		*/
+		entries = hash_search(or_group_htab, &hash, HASH_ENTER, &found);
+
+		if (unlikely(found))
+		{
+			ListCell *lc1;
+
+			Assert(entries->list != NULL);
+
+			/*
+			 * Try to find the same expression in the list of expressions with
+			 * the same hash value
+			 */
+			foreach(lc1, entries->list)
+			{
+				OrClauseGroupEntry *elem = (OrClauseGroupEntry *) lfirst(lc1);
+
+				if (equal(nconst_expr, elem->node))
+				{
+					entry = elem;
+					entry->consts = lappend(entry->consts, const_expr);
+					entry->exprs = lappend(entry->exprs, orqual);
+					break;
+				}
+			}
+
+			if (entry != NULL)
+			{
+				/*
+				 * The clause classified successfully and added into existed
+				 * clause group.
+				 */
+				continue;
+			}
+
+			found = false;
+		}
+		else
+			/* Prepare for adding to the list the first element */
+			entries->list = NIL;
+
+		if (!found)
+		{
+			Assert(entry == NULL);
+
+			entry = palloc(sizeof(OrClauseGroupEntry));
+			entry->node = nconst_expr;
+			entry->consts = list_make1(const_expr);
+			entry->exprs = list_make1(orqual);
+			entries->list = lappend(entries->list, entry);
+		}
+	}
+
+	if (list_length(or_list) == len_ors)
+	{
+		/*
+		* No any transformations possible with this list of arguments. Here we
+		* already made all underlying transformations. Thus, just return the
+		* transformed bool expression.
+		*/
+		hash_destroy(or_group_htab);
+		return (Node *) makeBoolExpr(OR_EXPR, or_list, expr->location);
+	}
+
+	hash_seq_init(&hash_seq, or_group_htab);
+
+	/* Let's convert each group of clauses to an IN operation. */
+
+	/*
+	 * Go through the list of groups and convert each, where number of
+	* consts more than 1. trivial groups move to OR-list again
+	*/
+
+	while ((entries = (OrClauseGroupEntries *) hash_seq_search(&hash_seq)) != NULL)
+	{
+		ListCell *lc1;
+
+		foreach(lc1, entries->list)
+		{
+			OrClauseGroupEntry *gentry = (OrClauseGroupEntry *) lfirst(lc1);
+			List			   *allexprs;
+			Oid				    scalar_type;
+			Oid					array_type;
+
+			Assert(list_length(gentry->consts) > 0);
+			Assert(list_length(gentry->exprs) == list_length(gentry->consts));
+
+			if (list_length(gentry->consts) == 1)
+			{
+				/*
+				 * Only one element in the class. Return rinfo into the BoolExpr
+				 * args list unchanged.
+				 */
+				list_free(gentry->consts);
+				or_list = list_concat(or_list, gentry->exprs);
+				continue;
+			}
+
+			/*
+			 * Do the transformation.
+			 *
+			 * First of all, try to select a common type for the array elements.
+			 * Note that since the LHS' type is first in the list, it will be
+			 * preferred when there is doubt (eg, when all the RHS items are
+			 * unknown literals).
+			 *
+			 * Note: use list_concat here not lcons, to avoid damaging rnonvars.
+			 *
+			 * As a source of insides, use make_scalar_array_op()
+			 */
+			allexprs = list_concat(list_make1(gentry->node), gentry->consts);
+			scalar_type = select_common_type(NULL, allexprs, NULL, NULL);
+
+			if (scalar_type != RECORDOID && OidIsValid(scalar_type))
+				array_type = get_array_type(scalar_type);
+			else
+				array_type = InvalidOid;
+
+			if (array_type != InvalidOid)
+			{
+				/*
+				 * 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;
+				ListCell *l;
+
+				aexprs = NIL;
+
+				foreach(l, gentry->consts)
+				{
+					Node	   *rexpr = (Node *) lfirst(l);
+
+					rexpr = coerce_to_common_type(pstate, rexpr,
+												scalar_type,
+												"IN");
+					aexprs = lappend(aexprs, rexpr);
+				}
+
+				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;
+
+				saopexpr =
+					(ScalarArrayOpExpr *)
+						make_scalar_array_op(pstate,
+											 list_make1(makeString((char *) "=")),
+											 true,
+											 gentry->node,
+											 (Node *) newa,
+											 -1);
+
+				or_list = lappend(or_list, (void *) saopexpr);
+			}
+			else
+			{
+				/*
+				 * This part works on intarray test (OR there is made on
+				 * elements of a custom type)
+				 */
+				list_free(gentry->consts);
+				or_list = list_concat(or_list, gentry->exprs);
+			}
+		}
+		list_free(entries->list);
+		entries->list = NIL;
+		hash_search(or_group_htab, &entries->hashkey, HASH_REMOVE, NULL);
+	}
+	hash_destroy(or_group_htab);
+
+	/* One more trick: assemble correct clause */
+	return (Node *) ((list_length(or_list) > 1) ?
+					 makeBoolExpr(OR_EXPR, or_list, expr->location) :
+					 linitial(or_list));
+}
 
 /*
  * transformExpr -
@@ -212,7 +495,7 @@ transformExprRecurse(ParseState *pstate, Node *expr)
 			}
 
 		case T_BoolExpr:
-			result = transformBoolExpr(pstate, (BoolExpr *) expr);
+			result = transformBoolExprOr(pstate, (BoolExpr *) expr);
 			break;
 
 		case T_FuncCall:
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index b764ef6998..2ca8a21caf 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1048,6 +1048,16 @@ struct config_bool ConfigureNamesBool[] =
 		true,
 		NULL, NULL, NULL
 	},
+	{
+		{"enable_or_transformation", PGC_USERSET, QUERY_TUNING_OTHER,
+			gettext_noop("Transform a sequence of OR clauses to an IN expression."),
+			gettext_noop("The planner will replace clauses like 'x=c1 OR x=c2 .."
+						 "to the clause 'x IN (c1,c2,...)'")
+		},
+		&enable_or_transformation,
+		true,
+		NULL, NULL, NULL
+	},
 	{
 		/*
 		 * Not for general use --- used by SET SESSION AUTHORIZATION and SET
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index e48c066a5b..26bee30ad3 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -371,6 +371,7 @@
 # - Planner Method Configuration -
 
 #enable_async_append = on
+#enable_or_transformation = on
 #enable_bitmapscan = on
 #enable_gathermerge = on
 #enable_hashagg = on
diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h
index 0769081c7a..4aaa31aa80 100644
--- a/src/include/nodes/queryjumble.h
+++ b/src/include/nodes/queryjumble.h
@@ -65,6 +65,7 @@ extern PGDLLIMPORT int compute_query_id;
 
 extern const char *CleanQuerytext(const char *query, int *location, int *len);
 extern JumbleState *JumbleQuery(Query *query);
+extern JumbleState *JumbleExpr(Expr *expr, uint64 *queryId);
 extern void EnableQueryId(void);
 
 extern PGDLLIMPORT bool query_id_enabled;
diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h
index 7d38ca75f7..3a87de0285 100644
--- a/src/include/parser/parse_expr.h
+++ b/src/include/parser/parse_expr.h
@@ -17,6 +17,7 @@
 
 /* GUC parameters */

diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index acfd9d1f4f..3bb4bbca48 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1838,18 +1838,50 @@ 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);
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+      42 |    5530 |   0 |    2 |   2 |      2 |      42 |       42 |          42 |        42 |       42 |  84 |   85 | QBAAAA   | SEIAAA   | OOOOxx
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+  WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+                                     QUERY PLAN                                     
+------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on tenk1
+         Recheck Cond: ((hundred = 42) AND (thousand = ANY ('{42,99}'::integer[])))
+         ->  BitmapAnd
+               ->  Bitmap Index Scan on tenk1_hundred
+                     Index Cond: (hundred = 42)
+               ->  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);
+ count 
+-------
+    10
+(1 row)
+
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+  WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+                                  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);
@@ -1861,28 +1893,101 @@ 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)
+               ->  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);
+ count 
+-------
+    10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+  WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+                                               QUERY PLAN                                               
+--------------------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on tenk1
+         Recheck Cond: (((thousand = 42) AND (tenthous = ANY ('{1,3}'::integer[]))) OR (thousand = 41))
+         ->  BitmapOr
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3}'::integer[])))
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: (thousand = 41)
+(8 rows)
+
+SELECT count(*) FROM tenk1
+  WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+ count 
+-------
+    10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+  WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41;
+                                                         QUERY PLAN                                                          
+-----------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on tenk1
+         Recheck Cond: (((hundred = 42) AND ((tenthous < 2) OR (thousand = ANY ('{42,99}'::integer[])))) OR (thousand = 41))
+         ->  BitmapOr
+               ->  BitmapAnd
+                     ->  Bitmap Index Scan on tenk1_hundred
+                           Index Cond: (hundred = 42)
+                     ->  BitmapOr
+                           ->  Bitmap Index Scan on tenk1_thous_tenthous
+                                 Index Cond: (tenthous < 2)
+                           ->  Bitmap Index Scan on tenk1_thous_tenthous
+                                 Index Cond: (thousand = ANY ('{42,99}'::integer[]))
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: (thousand = 41)
+(14 rows)
+
+SELECT count(*) FROM tenk1
+  WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41;
+ count 
+-------
+    20
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+  WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2);
+                                                          QUERY PLAN                                                          
+------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on tenk1
+         Recheck Cond: ((hundred = 42) AND (((thousand = 99) AND (tenthous = 2)) OR (thousand = ANY ('{42,41}'::integer[]))))
          ->  BitmapAnd
                ->  Bitmap Index Scan on tenk1_hundred
                      Index Cond: (hundred = 42)
                ->  BitmapOr
                      ->  Bitmap Index Scan on tenk1_thous_tenthous
-                           Index Cond: (thousand = 42)
+                           Index Cond: ((thousand = 99) AND (tenthous = 2))
                      ->  Bitmap Index Scan on tenk1_thous_tenthous
-                           Index Cond: (thousand = 99)
+                           Index Cond: (thousand = ANY ('{42,41}'::integer[]))
 (11 rows)
 
 SELECT count(*) FROM tenk1
-  WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+  WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2);
  count 
 -------
     10
 (1 row)
 
+RESET enable_or_transformation;
 --
 -- Check behavior with duplicate index column contents
 --
diff --git a/src/test/regress/expected/create_view.out b/src/test/regress/expected/create_view.out
index 61825ef7d4..b3b1670fd9 100644
--- a/src/test/regress/expected/create_view.out
+++ b/src/test/regress/expected/create_view.out
@@ -2196,7 +2196,7 @@ select pg_get_viewdef('tt26v', true);
      x + (y + z) AS c6,                            +
      x + (y # z) AS c7,                            +
      x > y AND (y > z OR x > z) AS c8,             +
-     x > y OR y > z AND NOT x > z AS c9,           +
+     x > y OR y > z AND x <= z AS c9,              +
      ((x, y) <> ALL ( VALUES (1,2), (3,4))) AS c10,+
      ((x, y) <= ANY ( VALUES (1,2), (3,4))) AS c11 +
     FROM ( VALUES (1,2,3)) v(x, y, z);
diff --git a/src/test/regress/expected/guc.out b/src/test/regress/expected/guc.out
index 127c953297..0f2b1b1620 100644
--- a/src/test/regress/expected/guc.out
+++ b/src/test/regress/expected/guc.out
@@ -861,7 +861,8 @@ SELECT name FROM tab_settings_flags
            name            
 ---------------------------
  default_statistics_target
-(1 row)
+ enable_or_transformation
+(2 rows)
 
 -- Runtime-computed GUCs should be part of the preset category.
 SELECT name FROM tab_settings_flags
diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out
index 0f1aa831f6..1781250122 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2560,7 +2560,7 @@ explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'
                                    QUERY PLAN                                    
 ---------------------------------------------------------------------------------
  Seq Scan on part_ab_cd list_parted
-   Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
+   Filter: (((a)::text = ANY ('{NULL,cd}'::text[])) OR ((a)::text = 'ab'::text))
 (2 rows)
 
 explain (costs off) select * from list_parted where a = 'ab';
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 2c73270143..e952e5401f 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4210,10 +4210,10 @@ 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);
-                                                      QUERY PLAN                                                      
-----------------------------------------------------------------------------------------------------------------------
+                                                       QUERY PLAN                                                       
+------------------------------------------------------------------------------------------------------------------------
  Nested Loop
-   Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR (a.unique2 = 7)) AND (b.hundred = 4)))
+   Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4)))
    ->  Bitmap Heap Scan on tenk1 b
          Recheck Cond: ((unique1 = 2) OR (hundred = 4))
          ->  BitmapOr
@@ -4223,16 +4223,64 @@ select * from tenk1 a join tenk1 b on
                      Index Cond: (hundred = 4)
    ->  Materialize
          ->  Bitmap Heap Scan on tenk1 a
-               Recheck Cond: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
+               Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])))
                ->  BitmapOr
                      ->  Bitmap Index Scan on tenk1_unique1
                            Index Cond: (unique1 = 1)
                      ->  Bitmap Index Scan on tenk1_unique2
-                           Index Cond: (unique2 = 3)
+                           Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+(17 rows)
+
+SET enable_or_transformation = on;
+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);
+                                                       QUERY PLAN                                                       
+------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4)))
+   ->  Bitmap Heap Scan on tenk1 b
+         Recheck Cond: ((unique1 = 2) OR (hundred = 4))
+         ->  BitmapOr
+               ->  Bitmap Index Scan on tenk1_unique1
+                     Index Cond: (unique1 = 2)
+               ->  Bitmap Index Scan on tenk1_hundred
+                     Index Cond: (hundred = 4)
+   ->  Materialize
+         ->  Bitmap Heap Scan on tenk1 a
+               Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])))
+               ->  BitmapOr
+                     ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 = 1)
                      ->  Bitmap Index Scan on tenk1_unique2
-                           Index Cond: (unique2 = 7)
-(19 rows)
+                           Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+(17 rows)
+
+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);
+                                                                          QUERY PLAN                                                                           
+---------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((a.unique1 < 20) OR ((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4)) OR (a.unique1 = 3))
+   ->  Seq Scan on tenk1 b
+   ->  Materialize
+         ->  Bitmap Heap Scan on tenk1 a
+               Recheck Cond: ((unique1 < 20) OR (unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])) OR (unique1 = 3))
+               ->  BitmapOr
+                     ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 < 20)
+                     ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 = 1)
+                     ->  Bitmap Index Scan on tenk1_unique2
+                           Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+                     ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 = 3)
+(15 rows)
 
+RESET enable_or_transformation;
 --
 -- test placement of movable quals in a parameterized join tree
 --
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 9a4c48c055..14a254fba7 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -82,25 +82,47 @@ 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)
 
@@ -515,10 +537,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';
@@ -596,13 +618,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 */
@@ -671,6 +693,163 @@ explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a =
          Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
 (11 rows)
 
@@ -1933,10 +2112,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;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 1442c43d9c..e3ed533832 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2145,7 +2145,7 @@ pg_stat_sys_indexes| SELECT relid,
     idx_tup_read,
     idx_tup_fetch
    FROM pg_stat_all_indexes
-  WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
+  WHERE ((schemaname = ANY ('{pg_catalog,information_schema}'::name[])) OR (schemaname ~ '^pg_toast'::text));
 pg_stat_sys_tables| SELECT relid,
     schemaname,
     relname,
@@ -2173,7 +2173,7 @@ pg_stat_sys_tables| SELECT relid,
     analyze_count,
     autoanalyze_count
    FROM pg_stat_all_tables
-  WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
+  WHERE ((schemaname = ANY ('{pg_catalog,information_schema}'::name[])) OR (schemaname ~ '^pg_toast'::text));
 pg_stat_user_functions| SELECT p.oid AS funcid,
     n.nspname AS schemaname,
     p.proname AS funcname,
@@ -2279,7 +2279,7 @@ pg_stat_xact_sys_tables| SELECT relid,
     n_tup_hot_upd,
     n_tup_newpage_upd
    FROM pg_stat_xact_all_tables
-  WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
+  WHERE ((schemaname = ANY ('{pg_catalog,information_schema}'::name[])) OR (schemaname ~ '^pg_toast'::text));
 pg_stat_xact_user_functions| SELECT p.oid AS funcid,
     n.nspname AS schemaname,
     p.proname AS funcname,
@@ -2354,14 +2354,14 @@ pg_statio_sys_indexes| SELECT relid,
     idx_blks_read,
     idx_blks_hit
    FROM pg_statio_all_indexes
-  WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
+  WHERE ((schemaname = ANY ('{pg_catalog,information_schema}'::name[])) OR (schemaname ~ '^pg_toast'::text));
 pg_statio_sys_sequences| SELECT relid,
     schemaname,
     relname,
     blks_read,
     blks_hit
    FROM pg_statio_all_sequences
-  WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
+  WHERE ((schemaname = ANY ('{pg_catalog,information_schema}'::name[])) OR (schemaname ~ '^pg_toast'::text));
 pg_statio_sys_tables| SELECT relid,
     schemaname,
     relname,
@@ -2374,7 +2374,7 @@ pg_statio_sys_tables| SELECT relid,
     tidx_blks_read,
     tidx_blks_hit
    FROM pg_statio_all_tables
-  WHERE ((schemaname = ANY (ARRAY['pg_catalog'::name, 'information_schema'::name])) OR (schemaname ~ '^pg_toast'::text));
+  WHERE ((schemaname = ANY ('{pg_catalog,information_schema}'::name[])) OR (schemaname ~ '^pg_toast'::text));
 pg_statio_user_indexes| SELECT relid,
     indexrelid,
     schemaname,
@@ -2471,7 +2471,7 @@ pg_stats| SELECT n.nspname AS schemaname,
      JOIN pg_class c ON ((c.oid = s.starelid)))
      JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
      LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
-  WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
+  WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text) AND ((NOT c.relrowsecurity) OR (NOT row_security_active(c.oid))));
 pg_stats_ext| SELECT cn.nspname AS schemaname,
     c.relname AS tablename,
     sn.nspname AS statistics_schemaname,
@@ -2502,7 +2502,7 @@ pg_stats_ext| SELECT cn.nspname AS schemaname,
   WHERE ((NOT (EXISTS ( SELECT 1
            FROM (unnest(s.stxkeys) k(k)
              JOIN pg_attribute a ON (((a.attrelid = s.stxrelid) AND (a.attnum = k.k))))
-          WHERE (NOT has_column_privilege(c.oid, a.attnum, 'select'::text))))) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
+          WHERE (NOT has_column_privilege(c.oid, a.attnum, 'select'::text))))) AND ((NOT c.relrowsecurity) OR (NOT row_security_active(c.oid))));
 pg_stats_ext_exprs| SELECT cn.nspname AS schemaname,
     c.relname AS tablename,
     sn.nspname AS statistics_schemaname,
@@ -2616,7 +2616,7 @@ pg_user_mappings| SELECT u.oid AS umid,
             ELSE a.rolname
         END AS usename,
         CASE
-            WHEN (((u.umuser <> (0)::oid) AND (a.rolname = CURRENT_USER) AND (pg_has_role(s.srvowner, 'USAGE'::text) OR has_server_privilege(s.oid, 'USAGE'::text))) OR ((u.umuser = (0)::oid) AND pg_has_role(s.srvowner, 'USAGE'::text)) OR ( SELECT pg_authid.rolsuper
+            WHEN (((u.umuser <> '0'::oid) AND (a.rolname = CURRENT_USER) AND (pg_has_role(s.srvowner, 'USAGE'::text) OR has_server_privilege(s.oid, 'USAGE'::text))) OR ((u.umuser = '0'::oid) AND pg_has_role(s.srvowner, 'USAGE'::text)) OR ( SELECT pg_authid.rolsuper
                FROM pg_authid
               WHERE (pg_authid.rolname = CURRENT_USER))) THEN u.umoptions
             ELSE NULL::text[]
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index a430153b22..659d712f75 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/sysviews.out b/src/test/regress/expected/sysviews.out
index 271313ebf8..c6c6b9fb8d 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -123,6 +123,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_memoize                 | on
  enable_mergejoin               | on
  enable_nestloop                | on
+ enable_or_transformation       | on
  enable_parallel_append         | on
  enable_parallel_hash           | on
  enable_partition_pruning       | on
@@ -133,7 +134,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(22 rows)
+(23 rows)
 
 -- There are always wait event descriptions for various types.
 select type, count(*) > 0 as ok FROM pg_wait_events
diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out
index f133b5a4ac..2a079e996b 100644
--- a/src/test/regress/expected/tidscan.out
+++ b/src/test/regress/expected/tidscan.out
@@ -43,10 +43,26 @@ 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 = ANY ('{"(0,2)","(0,1)"}'::tid[]))
+(2 rows)
+
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+ ctid  | id 
+-------+----
+ (0,1) |  1
+ (0,2) |  2
+(2 rows)
+
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+                      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;
@@ -56,6 +72,7 @@ SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
  (0,2) |  2
 (2 rows)
 
index d49ce9f300..48bb1bc0a0 100644
 
index 8a8a63bd2f..55d7e2ae7d 100644
 
index 7bf3920827..1e270ae9c0 100644
 
+
index 313e0fb9b6..0499bedb9e 100644
 
index dba3498a13..b200363d4e 100644
@@ -1641,6 +1641,8 @@ NumericVar
 OM_uint32
 OP
 OSAPerGroupState
+OrClauseGroupEntries
+OrClauseGroupEntry
 OSAPerQueryState
 OSInfo
 OSSLCipher
-- 
2.43.0

---

From: Andrei Lepikhov <[email-masked]>
Date: Fri, 24 Nov 2023 10:30:39 +0700

> demonstrates the positive effect of the transformation.

I found out that the postgres_fdw tests were impacted by the feature. 
Fix it, because the patch is on the commitfest and passes buildfarm.
Taking advantage of this, I suppressed the expression evaluation 
procedure to make regression test changes more clear.

-- 
regards,
Andrei Lepikhov
Postgres Professional
From e29b35d3445d9852f3ecb9cdaa4f231c72334973 Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <[email-masked]>
Date: Thu, 23 Nov 2023 16:00:13 +0700
Subject: [PATCH] Transform OR clause to ANY expressions.

 .../postgres_fdw/expected/postgres_fdw.out    |   8 +-
 src/backend/parser/parse_expr.c               | 284 +++++++++++++++++-
 src/test/regress/expected/rules.out           |   4 +-
 21 files changed, 810 insertions(+), 62 deletions(-)

diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 64bcc66b8d..a8442f08aa 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8537,18 +8537,18 @@ insert into utrtest values (2, 'qux');
 -- Check case where the foreign partition is a subplan target rel
 explain (verbose, costs off)
 update utrtest set a = 1 where a = 1 or a = 2 returning *;
-                                             QUERY PLAN                                             
-----------------------------------------------------------------------------------------------------
+                                                  QUERY PLAN                                                  
+--------------------------------------------------------------------------------------------------------------
  Update on public.utrtest
    Output: utrtest_1.a, utrtest_1.b
    Foreign Update on public.remp utrtest_1
    Update on public.locp utrtest_2
    ->  Append
          ->  Foreign Update on public.remp utrtest_1
-               Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a = 2))) RETURNING a, b
+               Remote SQL: UPDATE public.loct SET a = 1 WHERE ((a = ANY ('{1,2}'::integer[]))) RETURNING a, b
          ->  Seq Scan on public.locp utrtest_2
                Output: 1, utrtest_2.tableoid, utrtest_2.ctid, NULL::record
-               Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
+               Filter: (utrtest_2.a = ANY ('{1,2}'::integer[]))
 (10 rows)
 
 -- The new values are concatenated with ' triggered !'
 
+JumbleState *
index 64c582c344..b69381cc45 100644
 
 /* GUC parameters */

 static Node *transformExprRecurse(ParseState *pstate, Node *expr);
@@ -99,6 +101,286 @@ static Expr *make_distinct_op(ParseState *pstate, List *opname,
 static Node *make_nulltest_from_distinct(ParseState *pstate,
 										 A_Expr *distincta, Node *arg);

 /*
  * transformExpr -
@@ -212,7 +494,7 @@ transformExprRecurse(ParseState *pstate, Node *expr)
 			}
 
 		case T_BoolExpr:
 
 		case T_FuncCall:

 
 
 /* GUC parameters */

 
 SELECT * FROM tenk1

 

 

 

 

 
 drop table hp2;
index 1442c43d9c..3bef969328 100644
+  WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text) AND ((NOT row_security_active(c.oid)) OR (c.relrowsecurity = false)));
+          WHERE (NOT has_column_privilege(c.oid, a.attnum, 'select'::text))))) AND ((NOT row_security_active(c.oid)) OR (c.relrowsecurity = false)));

 

 

 

 
+

---

From: Alena Rybakina <[email-masked]>
Date: Fri, 24 Nov 2023 08:05:14 +0300

> procedure to make regression test changes more clear.

Thank you for your work. You are right, the patch with the current 
changes looks better and works more correctly.

To be honest, I didn't think we could use JumbleExpr in this way.

-- 
Regards,
Alena Rybakina
Postgres Professional

---

From: Alexander Korotkov <[email-masked]>
Date: Sat, 25 Nov 2023 03:23:38 +0200

> To be honest, I didn't think we could use JumbleExpr in this way.

I think patch certainly gets better in this aspect.  One thing I can't
understand is why do we use home-grown code for resolving
hash-collisions.  You can just define custom hash and match functions
in HASHCTL.  Even if we need to avoid repeated JumbleExpr calls, we
still can save pre-calculated hash value into hash entry and use
custom hash and match.  This doesn't imply us to write our own
collision-resolving code.

------
Regards,
Alexander Korotkov

---

From: Andrei Lepikhov <[email-masked]>
Date: Mon, 27 Nov 2023 15:02:09 +0700

> collision-resolving code.

Thanks, it was an insightful suggestion.
I implemented it, and the code has become shorter (see attachment).

-- 
regards,
Andrei Lepikhov
Postgres Professional
From 8a43f5b50be6cb431046ab352fbcb9bdd3e4376c Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <[email-masked]>
Date: Thu, 23 Nov 2023 16:00:13 +0700
Subject: [PATCH] Transform OR clause to ANY expressions.

 src/backend/parser/parse_expr.c               | 283 +++++++++++++++++-
 src/test/regress/expected/partition_prune.out | 257 +++++++++++++---
 21 files changed, 830 insertions(+), 83 deletions(-)

index 22cae37a1e..fdfecd51c7 100644

+JumbleState *
index 64c582c344..0f2116a6f1 100644
 
 /* GUC parameters */

 static Node *transformExprRecurse(ParseState *pstate, Node *expr);
@@ -99,6 +101,285 @@ static Expr *make_distinct_op(ParseState *pstate, List *opname,
 static Node *make_nulltest_from_distinct(ParseState *pstate,
 										 A_Expr *distincta, Node *arg);
 
+typedef struct OrClauseGroupKey
+{
+	Expr   *expr; /* Pointer to the expression tree which has been a source for
+					the hashkey value */
+} OrClauseGroupKey;
+
+typedef struct OrClauseGroupEntry
+{
+	OrClauseGroupKey key;
+
+	Node		   *node;
+	List		   *consts;
+	Oid				scalar_type;
+	Oid				opno;
+	List 		   *exprs;
+} OrClauseGroupEntry;
+
+/*
+ * Hash function that's compatible with guc_name_compare
+ */
+static uint32
+orclause_hash(const void *data, Size keysize)
+{
+	OrClauseGroupKey   *key = (OrClauseGroupKey *) data;
+	uint64				hash;
+
+	(void) JumbleExpr(key->expr, &hash);
+	return hash;
+}
+
+static void *
+orclause_keycopy(void *dest, const void *src, Size keysize)
+{
+	OrClauseGroupKey *src_key = (OrClauseGroupKey *) src;
+	OrClauseGroupKey *dst_key = (OrClauseGroupKey *) dest;
+
+	Assert(sizeof(OrClauseGroupKey) == keysize);
+
+	dst_key->expr = src_key->expr;
+	return dst_key;
+}
+
+/*
+ * Dynahash match function to use in guc_hashtab
+ */
+static int
+orclause_match(const void *data1, const void *data2, Size keysize)
+{
+	OrClauseGroupKey   *key1 = (OrClauseGroupKey *) data1;
+	OrClauseGroupKey   *key2 = (OrClauseGroupKey *) data2;
+
+	Assert(sizeof(OrClauseGroupKey) == keysize);
+
+	if (equal(key1->expr, key2->expr))
+		return 0;
+
+	return 1;
+}
+
+static Node *
+transformBoolExprOr(ParseState *pstate, BoolExpr *expr)
+{
+	List				   *or_list = NIL;
+	ListCell			   *lc;
+	HASHCTL					info;
+	HTAB 				   *or_group_htab = NULL;
+	int 					len_ors = list_length(expr->args);
+	HASH_SEQ_STATUS			hash_seq;
+	OrClauseGroupEntry	   *entry = NULL;
+
+	/* If this is not an 'OR' expression, skip the transformation */
+	if (!enable_or_transformation || expr->boolop != OR_EXPR || len_ors < 2)
+		return transformBoolExpr(pstate, (BoolExpr *) expr);
+
+	MemSet(&info, 0, sizeof(info));
+	info.keysize = sizeof(OrClauseGroupKey);
+	info.entrysize = sizeof(OrClauseGroupEntry);
+	info.hash = orclause_hash;
+	info.keycopy = orclause_keycopy;
+	info.match = orclause_match;
+	or_group_htab = hash_create("OR Groups",
+								len_ors,
+								&info,
+								HASH_ELEM | HASH_FUNCTION | HASH_COMPARE | HASH_KEYCOPY);
+
+	foreach(lc, expr->args)
+	{
+		Node				   *arg = lfirst(lc);
+		Node				   *orqual;
+		Node				   *const_expr;
+		Node				   *nconst_expr;
+		OrClauseGroupKey		hashkey;
+		bool					found;
+
+		/* At first, transform the arg and evaluate constant expressions. */
+		orqual = transformExprRecurse(pstate, (Node *) arg);
+		orqual = coerce_to_boolean(pstate, orqual, "OR");
+
+		if (!IsA(orqual, OpExpr))
+		{
+			or_list = lappend(or_list, orqual);
+			continue;
+		}
+
+		/*
+		 * 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.
+		 */
+		if (IsA(get_leftop(orqual), Const))
+		{
+			nconst_expr = get_rightop(orqual);
+			const_expr = get_leftop(orqual);
+		}
+		else if (IsA(get_rightop(orqual), Const))
+		{
+			const_expr = get_rightop(orqual);
+			nconst_expr = get_leftop(orqual);
+		}
+		else
+		{
+			or_list = lappend(or_list, orqual);
+			continue;
+		}
+
+		if (!op_mergejoinable(((OpExpr *) orqual)->opno, exprType(nconst_expr)))
+		{
+			or_list = lappend(or_list, orqual);
+			continue;
+		}
+
+		/*
+		* At this point we definitely have a transformable clause.
+		* Classify it and add into specific group of clauses, or create new
+		* group.
+		* TODO: to manage complexity in the case of many different clauses
+		* (X1=C1) OR (X2=C2 OR) ... (XN = CN) we could invent something
+		* like a hash table. But also we believe, that the case of many
+		* different variable sides is very rare.
+		*/
+		hashkey.expr = (Expr *) nconst_expr;
+		entry = hash_search(or_group_htab, &hashkey, HASH_ENTER, &found);
+
+		if (unlikely(found))
+		{
+			entry->consts = lappend(entry->consts, const_expr);
+			entry->exprs = lappend(entry->exprs, orqual);
+		}
+		else
+		{
+			entry->node = nconst_expr;
+			entry->consts = list_make1(const_expr);
+			entry->exprs = list_make1(orqual);
+		}
+	}
+
+	if (list_length(or_list) == len_ors)
+	{
+		/*
+		* No any transformations possible with this list of arguments. Here we
+		* already made all underlying transformations. Thus, just return the
+		* transformed bool expression.
+		*/
+		hash_destroy(or_group_htab);
+		return (Node *) makeBoolExpr(OR_EXPR, or_list, expr->location);
+	}
+
+	hash_seq_init(&hash_seq, or_group_htab);
+
+	/* Let's convert each group of clauses to an IN operation. */
+
+	/*
+	 * Go through the list of groups and convert each, where number of
+	* consts more than 1. trivial groups move to OR-list again
+	*/
+
+	while ((entry = (OrClauseGroupEntry *) hash_seq_search(&hash_seq)) != NULL)
+	{
+		List			   *allexprs;
+		Oid				    scalar_type;
+		Oid					array_type;
+
+		Assert(list_length(entry->consts) > 0);
+		Assert(list_length(entry->exprs) == list_length(entry->consts));
+
+		if (list_length(entry->consts) == 1)
+		{
+			/*
+			 * Only one element in the class. Return rinfo into the BoolExpr
+			 * args list unchanged.
+			 */
+			list_free(entry->consts);
+			or_list = list_concat(or_list, entry->exprs);
+			continue;
+		}
+
+		/*
+		 * Do the transformation.
+		 *
+		 * First of all, try to select a common type for the array elements.
+		 * Note that since the LHS' type is first in the list, it will be
+		 * preferred when there is doubt (eg, when all the RHS items are
+		 * unknown literals).
+		 *
+		 * Note: use list_concat here not lcons, to avoid damaging rnonvars.
+		 *
+		 * As a source of insides, use make_scalar_array_op()
+		 */
+		allexprs = list_concat(list_make1(entry->node), entry->consts);
+		scalar_type = select_common_type(NULL, allexprs, NULL, NULL);
+
+		if (scalar_type != RECORDOID && OidIsValid(scalar_type))
+			array_type = get_array_type(scalar_type);
+		else
+			array_type = InvalidOid;
+
+		if (array_type != InvalidOid)
+		{
+			/*
+			 * 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;
+			ListCell		   *l;
+
+			aexprs = NIL;
+
+			foreach(l, entry->consts)
+			{
+				Node	   *rexpr = (Node *) lfirst(l);
+
+				rexpr = coerce_to_common_type(pstate, rexpr,
+											  scalar_type,
+											  "IN");
+				aexprs = lappend(aexprs, rexpr);
+			}
+
+			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;
+
+			saopexpr =
+				(ScalarArrayOpExpr *)
+					make_scalar_array_op(pstate,
+										 list_make1(makeString((char *) "=")),
+										 true,
+										 entry->node,
+										 (Node *) newa,
+										 -1);
+
+			or_list = lappend(or_list, (void *) saopexpr);
+		}
+		else
+		{
+			/*
+			 * This part works on intarray test (OR there is made on
+			 * elements of a custom type)
+			 */
+			list_free(entry->consts);
+			or_list = list_concat(or_list, entry->exprs);
+		}
+		hash_search(or_group_htab, &entry->key, HASH_REMOVE, NULL);
+	}
+	hash_destroy(or_group_htab);
+
+	/* One more trick: assemble correct clause */
+	return (Node *) ((list_length(or_list) > 1) ?
+					 makeBoolExpr(OR_EXPR, or_list, expr->location) :
+					 linitial(or_list));
+}
 
 /*
  * transformExpr -
@@ -212,7 +493,7 @@ transformExprRecurse(ParseState *pstate, Node *expr)
 			}
 
 		case T_BoolExpr:
 
 		case T_FuncCall:

 
 
 /* GUC parameters */

 
 SELECT * FROM tenk1

 

index 9a4c48c055..9b78cd9d74 100644

 

@@ -526,29 +548,29 @@ explain (costs off) select * from rlp where a = 1 or b = 'ab';

 
 
@@ -893,23 +1072,23 @@ explain (costs off) select * from mc3p where a = 1 or abs(b) = 1 or c = 1;
 ------------------------------------------------------
  Append
    ->  Seq Scan on mc3p0 mc3p_1
-         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+         Filter: ((c = 1) OR (a = 1) OR (abs(b) = 1))
    ->  Seq Scan on mc3p1 mc3p_2
-         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+         Filter: ((c = 1) OR (a = 1) OR (abs(b) = 1))
    ->  Seq Scan on mc3p2 mc3p_3
-         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+         Filter: ((c = 1) OR (a = 1) OR (abs(b) = 1))
    ->  Seq Scan on mc3p3 mc3p_4
-         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+         Filter: ((c = 1) OR (a = 1) OR (abs(b) = 1))
    ->  Seq Scan on mc3p4 mc3p_5
-         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+         Filter: ((c = 1) OR (a = 1) OR (abs(b) = 1))
    ->  Seq Scan on mc3p5 mc3p_6
-         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+         Filter: ((c = 1) OR (a = 1) OR (abs(b) = 1))
    ->  Seq Scan on mc3p6 mc3p_7
-         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+         Filter: ((c = 1) OR (a = 1) OR (abs(b) = 1))
    ->  Seq Scan on mc3p7 mc3p_8
-         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+         Filter: ((c = 1) OR (a = 1) OR (abs(b) = 1))
    ->  Seq Scan on mc3p_default mc3p_9
-         Filter: ((a = 1) OR (abs(b) = 1) OR (c = 1))
+         Filter: ((c = 1) OR (a = 1) OR (abs(b) = 1))
 (19 rows)
 
 explain (costs off) select * from mc3p where (a = 1 and abs(b) = 1) or (a = 10 and abs(b) = 10);
@@ -1933,10 +2112,10 @@ explain (costs off) select * from hp where a = 1 and b = 'abcde';

 drop table hp2;
index 05070393b9..c927c21dd4 100644
@@ -2495,7 +2495,7 @@ pg_stats| SELECT n.nspname AS schemaname,
@@ -2526,7 +2526,7 @@ pg_stats_ext| SELECT cn.nspname AS schemaname,

 

 

 

 
+
 
index 86a9886d4f..d9a7d9c284 100644
@@ -1642,6 +1642,8 @@ NumericVar
 OM_uint32
 OP
 OSAPerGroupState
+OrClauseGroupEntry
+OrClauseGroupKey
 OSAPerQueryState
 OSInfo
 OSSLCipher
-- 
2.43.0

---

From: Robert Haas <[email-masked]>
Date: Mon, 27 Nov 2023 16:03:50 -0500

> I implemented it, and the code has become shorter (see attachment).

Neither the code comments nor the commit message really explain the
design idea here. That's unfortunate, principally because it makes
review difficult.

I'm very skeptical about the idea of using JumbleExpr for any part of
this. It seems fairly expensive, and it might produce false matches.
If expensive is OK, then why not just use equal()? If it's not, then
this probably isn't really OK either. But in any case there should be
comments explaining why this strategy was chosen.

The use of op_mergejoinable() seems pretty random to me. Why should we
care about that? If somebody writes a<1 or a<2 or a<3 or a<4, you can
transform that to a<any(array[1,2,3,4]) if you want. It might not be a
good idea, but I think it's a legal transformation. The reader
shouldn't be left to guess whether a rule like this was made for
reasons of correctness or for reasons of efficiency or something else.
Looking further, I see that the reason for this is likely that the
operator for the transformation result is constructing using
list_make1(makeString((char *) "=")), but trying to choose an operator
based on the operator name is, I think, pretty clearly unacceptable.
Tom has fired more than one hacker out of an airlock for such
transgressions, and this violation seems less principled than some.
The = operator that got chosen could be entirely unrelated to any
operator in the original, untransformed query. It could be part of no
operator class that was involved in the original query, in a different
schema than the operator in the original query, and owned by a
different user than the one who owned any operator referenced by the
original query. I suspect that's not only incorrect but an exploitable
security vulnerability.

I am extremely dubious about the use of select_common_type() here. Why
not do this only when the types already match exactly? Maybe the
concern is unknown literals, but perhaps that should be handled in
some other way. If you do this kind of thing, you need to justify why
it can't fail or produce wrong answers.

Honestly, it seems very hard to avoid the conclusion that this
transformation is being done at too early a stage. Parse analysis is
not the time to try to do query optimization. I can't really believe
that there's a way to produce a committable patch along these lines.
Ideally, a transformation like this should be done after we know what
plan shape we're using (or considering using), so that we can make
cost-based decisions about whether to transform or not. But at the
very least it should happen somewhere in the planner. There's really
no justification for parse analysis rewriting the SQL that the user
entered.

-- 
Robert Haas
EDB: http://www.enterprisedb.com

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 27 Nov 2023 14:15:48 -0800

On Mon, Nov 27, 2023 at 1:04 PM Robert Haas <[email-masked]> wrote:
> good idea, but I think it's a legal transformation.

That kind of transformation is likely to be a very good idea, because
nbtree's _bt_preprocess_array_keys() function knows how to perform
preprocessing that makes the final index qual "a < 1". Obviously that
could be far more efficient.

Further suppose you have a machine generated query  "a<1 or a<2 or a<3
or a<4 AND a = 2" -- same as before, except that I added "AND a = 2"
to the end. Now _bt_preprocess_array_keys() will be able to do the
aforementioned inequality preprocessing, just as before. But this time
_bt_preprocess_keys() (a different function with a similar name) can
see that the quals are contradictory. That makes the entire index scan
end, before it ever really began.

Obviously, this is an example of a more general principle: a great
deal of the benefit of these transformations is indirect, in the sense
that they come from enabling further transformations/optimizations,
that apply in the context of some particular query. So you have to
think holistically.

It's perhaps a little unfortunate that all of this nbtree
preprocessing stuff is totally opaque to the planner. Tom has
expressed concerns about that in the past, FWIW:

https://www.postgresql.org/message-id/[email-masked]
(see the final paragraph for the reference)

There might be some bigger picture to doing all of these
transformations, in a way that maximizes opportunities to apply
further transformations/optimizations. You know much more about the
optimizer than I do, so maybe this is already very obvious. Just
pointing it out.

> no justification for parse analysis rewriting the SQL that the user

I am sure that there is a great deal of truth to this. The general
conclusion about parse analysis being the wrong place for this seems
very hard to argue with. But I'm much less sure that there needs to be
a conventional cost model.

The planner's cost model is supposed to have some basis in physical
runtime costs, which is not the case for any of these transformations.
Not in any general sense; they're just transformations that enable
finding a cheaper way to execute the query. While they have to pay for
themselves, in some sense, I think that that's purely a matter of
managing the added planner cycles. In principle they shouldn't have
any direct impact on the physical costs incurred by physical
operators. No?

As I keep pointing out, there is a sound theoretical basis to the idea
of normalizing to conjunctive normal form as its own standard step in
query processing. To some extent we do this already, but it's all
rather ad-hoc. Even if (say) the nbtree preprocessing transformations
that I described were something that the planner already knew about
directly, they still wouldn't really need to be costed. They're pretty
much strictly better at runtime (at most you only have to worry about
the fixed cost of determining if they apply at all).

-- 
Peter Geoghegan

---

From: Matthias van de Meent <[email-masked]>
Date: Tue, 28 Nov 2023 01:06:35 +0100

> could be far more efficient.

a < 4, you mean? The example mentioned ANY, not ALL

> end, before it ever really began.

With the given WHERE-clause I would hope it did *not* return before
scanning the index, given that any row with a < 3 is valid for that
constraint with current rules of operator precedence.

- Matthias

---

From: Robert Haas <[email-masked]>
Date: Mon, 27 Nov 2023 19:07:18 -0500

On Mon, Nov 27, 2023 at 5:16 PM Peter Geoghegan <[email-masked]> wrote:
> [ various observations ]

This all seems to make sense but I don't have anything particular to
say about it.

> a conventional cost model.

I'm not sure about that part, either. The big reason we shouldn't do
this in parse analysis is that parse analysis is supposed to produce
an internal representation which is basically just a direct
translation of what the user entered. The representation should be
able to be deparsed to produce more or less what the user entered
without significant transformations. References to objects like tables
and operators do get resolved to OIDs at this stage, so deparsing
results will vary if objects are renamed or the search_path changes
and more or less schema-qualification is required or things like that,
but the output of parse analysis is supposed to preserve the meaning
of the query as entered by the user. The right place to do
optimization is in the optimizer.

But where in the optimizer to do it is an open question in my mind.

Previous discussion suggests to me that we might not really have
enough information at the beginning, because it seems like the right
thing to do depends on which plan we ultimately choose to use, which

> any direct impact on the physical costs incurred by physical

Right. It's just that, as a practical matter, some of the operators
deal with one form better than the other. So if we waited until we
knew which operator we were using to decide on which form to pick,
that would let us be smart.

> the fixed cost of determining if they apply at all).

It's just a matter of figuring out where we can put the logic and have
the result make sense. We'd like to put it someplace where it's not
too expensive and gets the right answer.

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 27 Nov 2023 17:07:46 -0800

On Mon, Nov 27, 2023 at 4:07 PM Robert Haas <[email-masked]> wrote:
> of the query as entered by the user.

One of the reasons why we shouldn't do this during parse analysis is
because query rewriting might matter. But that doesn't mean that the
transformation/normalization process must fundamentally be the
responsibility of the optimizer, through process of elimination.

Maybe it should be the responsibility of some other phase of query
processing, invented solely to make life easier for the optimizer, but
not formally part of query planning per se.

> The right place to do

Then why doesn't the optimizer do query rewriting? Isn't that also a
kind of optimization, at least in part?

> that would let us be smart.

ISTM that the real problem is that this is true in the first place. If
the optimizer had only one representation for any two semantically
equivalent spellings of the same qual, then it would always use the
best available representation. That seems even smarter, because that
way the planner can be dumb and still look fairly smart at runtime.

I am trying to be pragmatic, too (at least I think so). If having only
one representation turns out to be very hard, then maybe they weren't
ever really equivalent -- meaning it really is an optimization
problem, and the responsibility of the planner. It seems like it would
be more useful to spend time on making the world simpler for the
optimizer, rather than spending time on making the optimizer smarter.
Especially if we're talking about teaching the optimizer about what
are actually fairly accidental differences that come from
implementation details.

I understand that it'll never be black and white. There are practical
constraints on how far you go with this. We throw around terms like
"semantically equivalent" as if everybody agreed on precisely what
that means, which isn't really true (users complain when their view
definition has "<>" instead of "!="). Even still, I bet that we could
bring things far closer to this theoretical ideal, to good effect.

> too expensive and gets the right answer.

Agreed.

--
Peter Geoghegan

---

From: Andrei Lepikhov <[email-masked]>
Date: Tue, 28 Nov 2023 18:46:51 +0700

> design idea here. That's unfortunate, principally because it makes

Yeah, it is still an issue. We will think about how to improve this; any 
suggestions are welcome.

> comments explaining why this strategy was chosen.

We used the equal() routine without hashing in earlier versions. Hashing 
resolves issues with many different OR clauses. Is it expensive? Maybe, 
but we assume this transformation should be applied to simple enough 
expressions.

> good idea, but I think it's a legal transformation.

You are right. The only reason was to obtain a working patch to 
benchmark and look for corner cases. We would rewrite that place but 
still live with the equivalence operator.

> The reader shouldn't be left to guess whether a rule like this was made for

Yes, it was a big mistake. It is fixed in the new version (I guess).

> it can't fail or produce wrong answers.

Perhaps. We implemented your approach in the next version. At least we 
could see consequences.

> no justification for parse analysis rewriting the SQL that the user

Here, we assume that array operation is generally better than many ORs.
As a result, it should be more effective to make OR->ANY transformation 
in the parser (it is a relatively lightweight operation here) and, as a 
second phase, decompose that in the optimizer.
We implemented earlier prototypes in different places of the optimizer, 
and I'm convinced that only this approach resolves the issues we found.
Does this approach look weird? Maybe. We can debate it in this thread.

-- 
regards,
Andrei Lepikhov
Postgres Professional
From a5f8eba522ff907f7a3fffb0635b61d38933e385 Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <[email-masked]>
Date: Thu, 23 Nov 2023 16:00:13 +0700
Subject: [PATCH] Transform OR clause to ANY expressions.

 src/backend/parser/parse_expr.c               | 289 +++++++++++++++++-
 src/test/regress/expected/partition_join.out  |   2 +-
 src/test/regress/expected/select.out          |   6 +-
 23 files changed, 819 insertions(+), 66 deletions(-)

 
+JumbleState *
index 64c582c344..d8c6096144 100644
@@ -16,12 +16,14 @@
 #include "postgres.h"
 
 #include "catalog/pg_aggregate.h"
+#include "catalog/pg_operator.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_type.h"
 #include "commands/dbcommands.h"
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
+#include "nodes/queryjumble.h"
 #include "optimizer/optimizer.h"
 #include "parser/analyze.h"
 #include "parser/parse_agg.h"
@@ -38,11 +40,13 @@
 #include "utils/date.h"
 #include "utils/fmgroids.h"
 #include "utils/lsyscache.h"
+#include "utils/syscache.h"
 #include "utils/timestamp.h"
 #include "utils/xml.h"
 
 /* GUC parameters */

 static Node *transformExprRecurse(ParseState *pstate, Node *expr);
@@ -99,6 +103,289 @@ static Expr *make_distinct_op(ParseState *pstate, List *opname,
 static Node *make_nulltest_from_distinct(ParseState *pstate,
 										 A_Expr *distincta, Node *arg);
 
+	Oid		exprtype;
+	hash += ((uint64) key->opno + (uint64) key->exprtype) % UINT64_MAX;
+	dst_key->opno = src_key->opno;
+	dst_key->exprtype = src_key->exprtype;
+	if (key1->opno == key2->opno && key1->exprtype == key2->exprtype &&
+		equal(key1->expr, key2->expr))
+		hashkey.opno = ((OpExpr *) orqual)->opno;
+		hashkey.exprtype = exprType(nconst_expr);
+		scalar_type = entry->key.exprtype;
+			HeapTuple			opertup;
+			Form_pg_operator	operform;
+			List			   *namelist = NIL;
+			aexprs = entry->consts;
+			opertup = SearchSysCache1(OPEROID,
+									  ObjectIdGetDatum(entry->key.opno));
+			if (!HeapTupleIsValid(opertup))
+				elog(ERROR, "cache lookup failed for operator %u",
+					 entry->key.opno);
+			operform = (Form_pg_operator) GETSTRUCT(opertup);
+			if (!OperatorIsVisible(entry->key.opno))
+				namelist = lappend(namelist, makeString(get_namespace_name(operform->oprnamespace)));
+			namelist = lappend(namelist, makeString(pstrdup(NameStr(operform->oprname))));
+			ReleaseSysCache(opertup);
+										 namelist,
 
 /*
  * transformExpr -
@@ -212,7 +499,7 @@ transformExprRecurse(ParseState *pstate, Node *expr)
 			}
 
 		case T_BoolExpr:
 
 		case T_FuncCall:

 
 
 /* GUC parameters */

 
 SELECT * FROM tenk1

 

+RESET enable_or_transformation;
 --
 -- test placement of movable quals in a parameterized join tree
 --
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index 6560fe2416..6e8d64655b 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -296,7 +296,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JO
    Sort Key: prt1.a, prt2.b
    ->  Hash Full Join
          Hash Cond: (prt1.a = prt2.b)
-         Filter: ((prt1.b = 0) OR (prt2.a = 0))
+         Filter: ((prt2.a = 0) OR (prt1.b = 0))
          ->  Append
                ->  Seq Scan on prt1_p1 prt1_1
                      Filter: (a < 450)
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 9a4c48c055..14a254fba7 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -82,25 +82,47 @@ explain (costs off) select * from lp where a is null;
 (2 rows)

 

 

 
 
 drop table hp2;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 05070393b9..c927c21dd4 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2495,7 +2495,7 @@ pg_stats| SELECT n.nspname AS schemaname,
      JOIN pg_class c ON ((c.oid = s.starelid)))
      JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum))))
      LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
-  WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
+  WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text) AND ((NOT row_security_active(c.oid)) OR (c.relrowsecurity = false)));
 pg_stats_ext| SELECT cn.nspname AS schemaname,
     c.relname AS tablename,
     sn.nspname AS statistics_schemaname,
@@ -2526,7 +2526,7 @@ pg_stats_ext| SELECT cn.nspname AS schemaname,
   WHERE ((NOT (EXISTS ( SELECT 1
            FROM (unnest(s.stxkeys) k(k)
              JOIN pg_attribute a ON (((a.attrelid = s.stxrelid) AND (a.attnum = k.k))))
-          WHERE (NOT has_column_privilege(c.oid, a.attnum, 'select'::text))))) AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
+          WHERE (NOT has_column_privilege(c.oid, a.attnum, 'select'::text))))) AND ((NOT row_security_active(c.oid)) OR (c.relrowsecurity = false)));
 pg_stats_ext_exprs| SELECT cn.nspname AS schemaname,
     c.relname AS tablename,
     sn.nspname AS statistics_schemaname,
diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out
index 33a6dceb0e..7a829c44c7 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -868,13 +868,13 @@ select unique1, unique2 from onek2
                                    QUERY PLAN                                   
 --------------------------------------------------------------------------------
  Bitmap Heap Scan on onek2
-   Recheck Cond: (((unique2 = 11) AND (stringu1 < 'B'::name)) OR (unique1 = 0))
+   Recheck Cond: ((unique1 = 0) OR ((unique2 = 11) AND (stringu1 < 'B'::name)))
    Filter: (stringu1 < 'B'::name)
    ->  BitmapOr
-         ->  Bitmap Index Scan on onek2_u2_prtl
-               Index Cond: (unique2 = 11)
          ->  Bitmap Index Scan on onek2_u1_prtl
                Index Cond: (unique1 = 0)
+         ->  Bitmap Index Scan on onek2_u2_prtl
+               Index Cond: (unique2 = 11)
 (8 rows)
 
 select unique1, unique2 from onek2

 

 

 

 
+

---

From: Robert Haas <[email-masked]>
Date: Tue, 28 Nov 2023 07:52:02 -0500

> not formally part of query planning per se.

I don't really see why that would be useful. Adding more stages to the
query pipeline adds cognitive burden for which there must be some
corresponding benefit. Even if this happened very early in query
planning as a completely separate pass over the query tree, that would
minimize the need for code changes outside the optimizer to need to
care about it. But I suspect that this shouldn't happen very early in
query planning as a completely separate pass, but someplace later
where it can be done together with other useful optimizations (e.g.
eval_const_expressions, or even path construction).

> kind of optimization, at least in part?

I mean, I think rewriting mostly means applying rules.

> way the planner can be dumb and still look fairly smart at runtime.

Sure, well, that's another way of attacking the problem, but the
in-array representation is more convenient to loop over than the
or-clause representation, so if you get to a point where looping over
all the values is a thing you want to do, you're going to want
something that looks like that. If I just care about the fact that the
values I'm looking for are 3, 4, and 6, I want someone to hand me 3,
4, and 6, not x = 3, x = 4, and x = 6, and then I have to skip over
the x = part each time.

---

From: Alena Rybakina <[email-masked]>
Date: Thu, 30 Nov 2023 11:00:28 +0300

Hi!

> As a result, it should be more effective to make OR->ANY 
> transformation in the parser (it is a relatively lightweight operation 
> here) and, as a second phase, decompose that in the optimizer.
> We implemented earlier prototypes in different places of the 
> optimizer, and I'm convinced that only this approach resolves the 
> issues we found.

I think this is incorrect, and the example of A. Korotkov confirms this. 
If we perform the conversion at the parsing stage, we will skip the more 
important conversion using OR expressions. I'll show you in the example 
below.

First of all, I will describe my idea to combine two approaches to 
obtaining plans with OR to ANY transformation and ANY to OR 
transformation. I think they are both good, and we can't work with just 
one of them, we should consider both the option of OR expressions, and 
with ANY.

I did this by creating a RelOptInfo with which has references from the 
original RelOptInfo, for which conversion is possible either from 
ANY->OR, or vice versa. After obtaining the necessary transformation, I 
started the procedure for obtaining the seq and index paths for both 
relations and then calculated their cost. The relation with the lowest 
cost is considered the best.

I'm not sure if this is the best approach, but it's less complicated.

I noticed that I got a lower cost for not the best plan, but I think 
this corresponds to another topic related to the wrong estimate calculation.

1. The first patch is a mixture of the original patch (when we perform 
the conversion of OR to ANY at the parsing stage), and when we perform 
the conversion at the index creation stage with the conversion to an OR 
expression. We can see that the query proposed by A.Korotkov did not 
have the best plan with ANY expression at all, and even despite 
receiving a query with OR expressions, we cannot get anything better 
than SeqScan, due to the lack of effective logical transformations that 
would have been performed if we had left the OR expressions.

So, I got query plans using enable_or_transformation if it is enabled:

postgres=# create table test as (select (random()*10)::int x, 
(random()*1000) y
from generate_series(1,1000000) i);
create index test_x_1_y on test (y) where x = 1;
create index test_x_2_y on test (y) where x = 2;
vacuum analyze test;
SELECT 1000000
CREATE INDEX
CREATE INDEX
VACUUM
postgres=# explain select * from test where (x = 1 or x = 2) and y = 100;
WARNING:  cost with original approach: - 20440.000000
WARNING:  cost with OR to ANY applied transfomation: - 15440.000000
                                 QUERY PLAN
--------------------------------------------------------------------------
  Gather  (cost=1000.00..12690.10 rows=1 width=12)
    Workers Planned: 2
    ->  Parallel Seq Scan on test  (cost=0.00..11690.00 rows=1 width=12)
          Filter: (((x = 1) OR (x = 2)) AND (y = '100'::double precision))
(4 rows)

and if it is off:

postgres=# set enable_or_transformation =off;
SET
postgres=# explain select * from test where (x = 1 or x = 2) and y = 100;
                                                   QUERY PLAN
--------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on test  (cost=8.60..12.62 rows=1 width=12)
    Recheck Cond: (((y = '100'::double precision) AND (x = 1)) OR ((y = 
'100'::double precision) AND (x = 2)))
    ->  BitmapOr  (cost=8.60..8.60 rows=1 width=0)
          ->  Bitmap Index Scan on test_x_1_y  (cost=0.00..4.30 rows=1 
width=0)
                Index Cond: (y = '100'::double precision)
          ->  Bitmap Index Scan on test_x_2_y  (cost=0.00..4.30 rows=1 
width=0)
                Index Cond: (y = '100'::double precision)
(7 rows)

2. The second patch is my patch version when I moved the OR 
transformation in the s index formation stage:

So, I got the best query plan despite the possible OR to ANY transformation:

postgres=# create table test as (select (random()*10)::int x, 
(random()*1000) y
from generate_series(1,1000000) i);
create index test_x_1_y on test (y) where x = 1;
create index test_x_2_y on test (y) where x = 2;
vacuum analyze test;
SELECT 1000000
CREATE INDEX
CREATE INDEX
VACUUM
postgres=# explain select * from test where (x = 1 or x = 2) and y = 100;
WARNING:  cost with original approach: - 12.618000
WARNING:  cost with OR to ANY applied transfomation: - 15440.000000
                                                   QUERY PLAN
--------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on test  (cost=8.60..12.62 rows=1 width=12)
    Recheck Cond: (((y = '100'::double precision) AND (x = 1)) OR ((y = 
'100'::double precision) AND (x = 2)))
    ->  BitmapOr  (cost=8.60..8.60 rows=1 width=0)
          ->  Bitmap Index Scan on test_x_1_y  (cost=0.00..4.30 rows=1 
width=0)
                Index Cond: (y = '100'::double precision)
          ->  Bitmap Index Scan on test_x_2_y  (cost=0.00..4.30 rows=1 
width=0)
                Index Cond: (y = '100'::double precision)
(7 rows)

-- 
Regards,
Alena Rybakina
Postgres Professional

---

From: Alena Rybakina <[email-masked]>
Date: Thu, 30 Nov 2023 11:05:31 +0300

Sorry, I forgot to apply my patches. For the first experiment was 
0001-OR-to-ANY-in-parser-and-ANY-to-OR-in-index.diff and for the second 
experiment was 0002-OR-to-ANY-in-index.diff.

On 30.11.2023 11:00, Alena Rybakina wrote:
>> transformation in the parser (it is a relatively lightweight 
>> operation here) and, as a second phase, decompose that in the optimizer.
> I think this is incorrect, and the example of A. Korotkov confirms 
> this. If we perform the conversion at the parsing stage, we will skip 
> the more important conversion using OR expressions. I'll show you in 
> the example below.
>
> transformation. I think they are both good, and we can't work with 
> just one of them, we should consider both the option of OR 
> expressions, and with ANY.
>
> ANY->OR, or vice versa. After obtaining the necessary transformation, 
> I started the procedure for obtaining the seq and index paths for both 
> the conversion at the index creation stage with the conversion to an 
> OR expression. We can see that the query proposed by A.Korotkov did 
> not have the best plan with ANY expression at all, and even despite 
> than SeqScan, due to the lack of effective logical transformations 
> that would have been performed if we had left the OR expressions.
>
index 0a5bdf8bcc0..9368ae1d052 100644
 
index 281907a4d83..99207a8670f 100644
 
+JumbleState *
+JumbleExpr(Expr *expr, uint64 *queryId)
+{
+	JumbleState *jstate = NULL;
+
+	Assert(queryId != NULL);
+
+	jstate = (JumbleState *) palloc(sizeof(JumbleState));
+
+	/* Set up workspace for query jumbling */
+	jstate->jumble = (unsigned char *) palloc(JUMBLE_SIZE);
+	jstate->jumble_len = 0;
+	jstate->clocations_buf_size = 32;
+	jstate->clocations = (LocationLen *)
+		palloc(jstate->clocations_buf_size * sizeof(LocationLen));
+	jstate->clocations_count = 0;
+	jstate->highest_extern_param_id = 0;
+
+	/* Compute query ID */
+	_jumbleNode(jstate, (Node *) expr);
+	*queryId = DatumGetUInt64(hash_any_extended(jstate->jumble,
+												jstate->jumble_len,
+												0));
+
+	if (*queryId == UINT64CONST(0))
+		*queryId = UINT64CONST(1);
+
+	return jstate;
+}
+
 /*
  * Enables query identifier computation.
  *
diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 67921a08262..2e97ff96036 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -50,6 +50,8 @@
 #include "port/pg_bitutils.h"
 #include "rewrite/rewriteManip.h"
 #include "utils/lsyscache.h"
+#include "parser/parse_expr.h"
+#include "utils/array.h"

 /* Bitmask flags for pushdown_safety_info.unsafeFlags */
@@ -759,6 +761,179 @@ set_rel_consider_parallel(PlannerInfo *root, RelOptInfo *rel,
 	rel->consider_parallel = true;
 }
 
+static List*
+research_or_list(Expr *qual, List *or_list, RestrictInfo *sub_rinfo)
+{
+	List *elem_exprs = NIL;
+
+	/* Check: it is an expr of the form 'F(x) oper ConstExpr' */
+	if (!IsA(qual, ScalarArrayOpExpr))
+	{
+		/* Again, it's not the expr we can transform */
+		or_list = lappend(or_list, qual);
+	}
+	else
+	{
+		ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) qual;
+		Expr	   *rightop = (Expr *) lsecond(saop->args);
+		ListCell   *lc1;
+
+		if (sub_rinfo && !op_mergejoinable(((OpExpr *) sub_rinfo->clause)->opno, exprType(get_leftop(sub_rinfo->clause))))
+			{
+				/* And again, filter out non-equality operators */
+				or_list = lappend(or_list, (void *) qual);
+			}
+		else if (rightop && IsA(rightop, Const))
+		{
+			ArrayType  *arrval;
+			int16		elemlen;
+			bool		elembyval;
+			char		elemalign;
+			Datum	   *elem_values;
+			bool	   *elem_nulls;
+			int			num_elems,
+						i;
+			Const	   *arr = (Const *) rightop;
+
+			arrval = DatumGetArrayTypeP(arr->constvalue);
+			get_typlenbyvalalign(ARR_ELEMTYPE(arrval),
+								&elemlen, &elembyval, &elemalign);
+			deconstruct_array(arrval,
+							ARR_ELEMTYPE(arrval),
+							elemlen, elembyval, elemalign,
+							&elem_values, &elem_nulls,
+							&num_elems);
+
+			for (i = 0; i < num_elems; i++)
+			{
+				Const	   *elem_expr;
+
+				/*
+				* A null array element must lead to a null comparison result,
+				* since saop_op is known strict.  We can ignore it in the
+				* useOr case, but otherwise it implies self-contradiction.
+				*/
+				if (elem_nulls[i])
+				{
+					or_list = lappend(or_list, (void *) qual);
+					elem_exprs = NIL;
+					break;
+				}
+
+				elem_expr = makeConst(ARR_ELEMTYPE(arrval), -1,
+									arr->constcollid, elemlen,
+									elem_values[i], false, elembyval);
+				elem_exprs = lappend(elem_exprs, elem_expr);
+			}
+		}
+		else if (rightop && IsA(rightop, ArrayExpr) && !((ArrayExpr *) rightop)->multidims)
+		{
+			ArrayExpr  *arrexpr = (ArrayExpr  *)get_rightop(rightop);
+			elem_exprs = arrexpr->elements;
+		}
+		else
+			or_list = lappend(or_list, qual);
+
+		if (elem_exprs)
+		foreach(lc1, elem_exprs)
+		{
+			Expr	   *elem_clause;
+
+			elem_clause = make_opclause(((ScalarArrayOpExpr*) qual)->opno, BOOLOID, false,
+										(Expr *) linitial(((ScalarArrayOpExpr*)qual)->args), lfirst(lc1),
+										InvalidOid, ((ScalarArrayOpExpr*)qual)->inputcollid);
+			or_list = lappend(or_list, (void*) elem_clause);
+		}
+	}
+	return or_list;
+}
+
+static List *
+get_baserestrictinfo(PlannerInfo *root, List *baserestrictinfo)
+{
+	ListCell   *lc;
+	List	   *modified_rinfo = NIL;
+	bool		or_transformation = false;
+
+	if (!enable_or_transformation)
+		return NULL;
+
+	foreach(lc, baserestrictinfo)
+	{
+		RestrictInfo   *rinfo_base = lfirst_node(RestrictInfo, lc);
+		RestrictInfo   *rinfo;
+		List		   *or_list = NIL;
+
+		ListCell	   *lc_eargs,
+					   *lc_rargs;
+
+		if (!IsA(rinfo_base->clause, BoolExpr) && !IsA(rinfo_base->clause, ScalarArrayOpExpr))
+		{
+			/* Add a clause without changes */
+			modified_rinfo = lappend(modified_rinfo, rinfo_base);
+			continue;
+		}
+
+		if (IsA(rinfo_base->clause, BoolExpr) && is_orclause(rinfo_base->clause))
+		forboth(lc_eargs, ((BoolExpr *) rinfo_base->clause)->args,
+				lc_rargs, ((BoolExpr *) rinfo_base->orclause)->args)
+		{
+			Expr			   *orqual = (Expr *) lfirst(lc_eargs);
+			RestrictInfo 	   *sub_rinfo = lfirst_node(RestrictInfo, lc_rargs);
+
+			if (!IsA(orqual, OpExpr) ||
+				!(bms_is_empty(sub_rinfo->left_relids) ^
+				bms_is_empty(sub_rinfo->right_relids)) ||
+				contain_volatile_functions((Node *) orqual))
+			{
+				/* Again, it's not the expr we can transform */
+				or_list = lappend(or_list, (void *) orqual);
+				continue;
+			}
+
+			or_list = research_or_list(orqual, or_list, sub_rinfo);
+		}
+		else if (IsA(rinfo_base->clause, ScalarArrayOpExpr))
+		{
+			Expr			   *orqual = rinfo_base->clause;
+
+			or_list = research_or_list(orqual, or_list, NULL);
+		}
+		else
+		{
+			or_list = lappend(or_list, (void*)rinfo_base->clause);
+		}
+
+
+		rinfo = make_restrictinfo(root,
+				  list_length(or_list) > 1 ? makeBoolExpr(OR_EXPR, or_list, -1):
+																				(Expr *) linitial(or_list),
+				  rinfo_base->is_pushed_down,
+				  rinfo_base->has_clone,
+				  rinfo_base->is_clone,
+				  rinfo_base->pseudoconstant,
+				  rinfo_base->security_level,
+				  rinfo_base->required_relids,
+				  rinfo_base->incompatible_relids,
+				  rinfo_base->outer_relids);
+		rinfo->eval_cost=rinfo_base->eval_cost;
+		rinfo->norm_selec=rinfo_base->norm_selec;
+		rinfo->outer_selec=rinfo_base->outer_selec;
+		rinfo->left_bucketsize=rinfo_base->left_bucketsize;
+		rinfo->right_bucketsize=rinfo_base->right_bucketsize;
+		rinfo->left_mcvfreq=rinfo_base->left_mcvfreq;
+		rinfo->right_mcvfreq=rinfo_base->right_mcvfreq;
+		modified_rinfo = lappend(modified_rinfo, rinfo);
+		or_transformation = true;
+	}
+
+	if(or_transformation)
+	return modified_rinfo;
+
+	return baserestrictinfo;
+}
+
+
 /*
  * set_plain_rel_pathlist
  *	  Build access paths for a plain relation (no subquery, no inheritance)
@@ -767,6 +942,12 @@ static void
 set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
 {
 	Relids		required_outer;
+	RelOptInfo *reL_alternative = makeNode(RelOptInfo);
+	//reL_alternative = copyObject(rel);
+	reL_alternative = copy_simple_rel(root, rel->relid, rel, reL_alternative);
+	reL_alternative->indexlist = NIL;
+	reL_alternative->baserestrictinfo = rel->baserestrictinfo;
+	reL_alternative->eclass_indexes = NULL;
 
 	/*
 	 * We don't support pushing join clauses into the quals of a seqscan, but
@@ -787,6 +968,39 @@ set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
 
 	/* Consider TID scans */
 	create_tidscan_paths(root, rel);
+
+	set_cheapest(rel);
+
+	if (rel->reloptkind == RELOPT_BASEREL && enable_or_transformation)
+	{
+		bool applied_transfomation=true;
+		//applied_transfomation =  (bool *) palloc(sizeof(bool));
+		reL_alternative->baserestrictinfo = get_baserestrictinfo(root, rel->baserestrictinfo);
+if (applied_transfomation)
+{
+	add_path(reL_alternative, create_seqscan_path(root, reL_alternative, required_outer, 0));
+
+	if (reL_alternative->consider_parallel && required_outer == NULL)
+		create_plain_partial_paths(root, reL_alternative);
+
+	create_index_paths(root, reL_alternative);
+
+	create_tidscan_paths(root, reL_alternative);
+
+	set_cheapest(reL_alternative);
+
+	elog(WARNING, "ors: - %f", rel->cheapest_total_path->total_cost);
+	elog(WARNING, "applied_transfomation: - %f", reL_alternative->cheapest_total_path->total_cost);
+
+	if (reL_alternative->cheapest_total_path->total_cost <= rel->cheapest_total_path->total_cost)
+	{
+		//copy_simple_rel(root, rel->relid, reL_alternative, rel);
+		rel->indexlist = reL_alternative->indexlist;
+		rel->baserestrictinfo = reL_alternative->baserestrictinfo;
+		rel->eclass_indexes = reL_alternative->eclass_indexes;
+	}
+	}
+	}
 }
 
 /*
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 5d83f60eb9a..189cee449bf 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -183,6 +183,97 @@ expand_planner_arrays(PlannerInfo *root, int add_size)
 	root->simple_rel_array_size = new_size;
 }
 
+RelOptInfo *
+copy_simple_rel(PlannerInfo *root, int relid, RelOptInfo *base_rel, RelOptInfo *rel)
+{
+	rel->reloptkind = base_rel->parent ? RELOPT_OTHER_MEMBER_REL : RELOPT_BASEREL;
+	rel->relids = base_rel->relids;
+	rel->rows = base_rel->rows;
+	/* cheap startup cost is interesting iff not all tuples to be retrieved */
+	rel->consider_startup = (root->tuple_fraction > 0);
+	rel->consider_param_startup = base_rel->consider_param_startup;	/* might get changed later */
+	rel->consider_parallel = base_rel->consider_parallel; /* might get changed later */
+	rel->reltarget = base_rel->reltarget;
+	rel->pathlist = base_rel->pathlist;
+	rel->ppilist = base_rel->ppilist;
+	rel->partial_pathlist = base_rel->partial_pathlist;
+	rel->cheapest_startup_path = base_rel->cheapest_startup_path;
+	rel->cheapest_total_path = base_rel->cheapest_total_path;
+	rel->cheapest_unique_path = base_rel->cheapest_unique_path;
+	rel->cheapest_parameterized_paths = base_rel->cheapest_parameterized_paths;
+	rel->relid = base_rel->relid;
+	/* min_attr, max_attr, attr_needed, attr_widths are set below */
+	rel->lateral_vars = base_rel->lateral_vars;
+	rel->statlist = base_rel->statlist;
+	rel->pages = base_rel->pages;
+	rel->tuples = base_rel->tuples;
+	rel->allvisfrac = base_rel->allvisfrac;
+	rel->subroot = base_rel->subroot;
+	rel->subplan_params = base_rel->subplan_params;
+	rel->rel_parallel_workers = base_rel->rel_parallel_workers; /* set up in get_relation_info */
+	rel->amflags = base_rel->amflags;
+	rel->parent = base_rel->parent;
+	rel->serverid = base_rel->serverid;
+	rel->userid = base_rel->userid;
+	rel->useridiscurrent = base_rel->useridiscurrent;
+	rel->fdwroutine = base_rel->fdwroutine;
+	rel->fdw_private = base_rel->fdw_private;
+	rel->unique_for_rels = base_rel->unique_for_rels;
+	rel->non_unique_for_rels = base_rel->non_unique_for_rels;
+	rel->baserestrictcost.startup = 0;
+	rel->baserestrictcost.per_tuple = 0;
+	rel->baserestrict_min_security = UINT_MAX;
+	rel->joininfo = base_rel->joininfo;
+	rel->has_eclass_joins = base_rel->has_eclass_joins;
+
+	/*
+	 * Pass assorted information down the inheritance hierarchy.
+	 */
+	if (base_rel->parent)
+	{
+		/* We keep back-links to immediate parent and topmost parent. */
+		rel->parent = base_rel->parent;
+		rel->top_parent = base_rel->parent->top_parent ? base_rel->parent->top_parent : base_rel->parent;
+		rel->top_parent_relids = base_rel->top_parent->relids;
+
+		/*
+		 * A child rel is below the same outer joins as its parent.  (We
+		 * presume this info was already calculated for the parent.)
+		 */
+		rel->nulling_relids = base_rel->parent->nulling_relids;
+
+		/*
+		 * Also propagate lateral-reference information from appendrel parent
+		 * rels to their child rels.  We intentionally give each child rel the
+		 * same minimum parameterization, even though it's quite possible that
+		 * some don't reference all the lateral rels.  This is because any
+		 * append path for the parent will have to have the same
+		 * parameterization for every child anyway, and there's no value in
+		 * forcing extra reparameterize_path() calls.  Similarly, a lateral
+		 * reference to the parent prevents use of otherwise-movable join rels
+		 * for each child.
+		 *
+		 * It's possible for child rels to have their own children, in which
+		 * case the topmost parent's lateral info propagates all the way down.
+		 */
+		rel->direct_lateral_relids = base_rel->parent->direct_lateral_relids;
+		rel->lateral_relids = base_rel->parent->lateral_relids;
+		rel->lateral_referencers = base_rel->parent->lateral_referencers;
+	}
+	else
+	{
+		rel->parent = base_rel->parent;
+		rel->top_parent = base_rel->top_parent;
+		rel->top_parent_relids = base_rel->top_parent_relids;
+		rel->nulling_relids = base_rel->nulling_relids;
+		rel->direct_lateral_relids = base_rel->direct_lateral_relids;
+		rel->lateral_relids = base_rel->lateral_relids;
+		rel->lateral_referencers = base_rel->lateral_referencers;
+	}
+
+	return rel;
+}
+
 /*
  * build_simple_rel
  *	  Construct a new RelOptInfo for a base relation or 'other' relation.
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 64c582c344c..d8c6096144c 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -16,12 +16,14 @@
 #include "postgres.h"

 /* GUC parameters */

 
 /*
  * transformExpr -
 
 		case T_BoolExpr:
 
 		case T_FuncCall:
index 6474e35ec04..c054b3e1658 100644
index cf9f283cfee..3d5b5fbc580 100644
@@ -374,6 +374,7 @@
 
index 0769081c7ab..4aaa31aa80e 100644

 extern PGDLLIMPORT bool query_id_enabled;
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 6e557bebc44..f50a0df5dd2 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -306,6 +306,8 @@ extern void setup_simple_rel_arrays(PlannerInfo *root);
 extern void expand_planner_arrays(PlannerInfo *root, int add_size);
 extern RelOptInfo *build_simple_rel(PlannerInfo *root, int relid,
 									RelOptInfo *parent);
+extern RelOptInfo *
+copy_simple_rel(PlannerInfo *root, int relid, RelOptInfo *base_rel, RelOptInfo *rel);
 extern RelOptInfo *find_base_rel(PlannerInfo *root, int relid);
 extern RelOptInfo *find_base_rel_ignore_join(PlannerInfo *root, int relid);
 extern RelOptInfo *find_join_rel(PlannerInfo *root, Relids relids);
diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h
index 7d38ca75f7b..3a87de02859 100644
--- a/src/include/parser/parse_expr.h
+++ b/src/include/parser/parse_expr.h
@@ -17,6 +17,7 @@
 
 /* GUC parameters */

index acfd9d1f4f7..3bb4bbca481 100644
 
 SELECT * FROM tenk1

 
index 0f1aa831f64..17812501227 100644
 
index 2c73270143b..e952e5401f5 100644
 
index 6560fe2416f..6e8d64655b2 100644
index 9a4c48c0556..14a254fba7b 100644

 

 

 
 
 drop table hp2;
index 05070393b99..c927c21dd4d 100644
index 33a6dceb0e3..7a829c44c7b 100644
 
index a430153b225..659d712f75e 100644

 

 
index 271313ebf86..c6c6b9fb8d9 100644
 
index f133b5a4ac7..2a079e996b2 100644

 

+
 
index d659adbfd6c..9591d0d084d 100644
@@ -1645,6 +1645,8 @@ NumericVar
 OM_uint32
 OP
 OSAPerGroupState
+OrClauseGroupEntry
+OrClauseGroupKey
 OSAPerQueryState
 OSInfo
 OSSLCipher
-- 
2.34.1

diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c
index 67921a08262..e0d83672756 100644
--- a/src/backend/optimizer/path/allpaths.c
+++ b/src/backend/optimizer/path/allpaths.c
@@ -50,6 +50,12 @@
 #include "port/pg_bitutils.h"
 #include "rewrite/rewriteManip.h"
 #include "utils/lsyscache.h"
+#include "parser/parse_coerce.h"
+#include "common/hashfn.h"
+#include "catalog/pg_operator.h"
+#include "nodes/queryjumble.h"
+#include "utils/syscache.h"
+#include "catalog/namespace.h"

 /* Bitmask flags for pushdown_safety_info.unsafeFlags */
@@ -165,6 +171,390 @@ static void remove_unused_subquery_outputs(Query *subquery, RelOptInfo *rel,
 										   Bitmapset *extra_used_attrs);

+bool		enable_or_transformation = true;
+typedef struct OrClauseGroupKey
+{
+	Expr   *expr; /* Pointer to the expression tree which has been a source for
+					the hashkey value */
+	Oid		opno;
+	Oid		exprtype;
+} OrClauseGroupKey;
+
+typedef struct OrClauseGroupEntry
+{
+	OrClauseGroupKey key;
+	Node		   *node;
+	List		   *consts;
+	Oid				collation;
+	RestrictInfo   *rinfo;
+	List 		   *exprs;
+} OrClauseGroupEntry;
+
+/*
+ * Hash function that's compatible with guc_name_compare
+ */
+static uint32
+orclause_hash(const void *data, Size keysize)
+{
+	OrClauseGroupKey   *key = (OrClauseGroupKey *) data;
+	uint64				hash;
+
+	(void) JumbleExpr(key->expr, &hash);
+	hash = ((uint64) key->opno + (uint64) key->exprtype) % UINT64_MAX;
+	return hash;
+}
+
+static void *
+orclause_keycopy(void *dest, const void *src, Size keysize)
+{
+	OrClauseGroupKey *src_key = (OrClauseGroupKey *) src;
+	OrClauseGroupKey *dst_key = (OrClauseGroupKey *) dest;
+
+	Assert(sizeof(OrClauseGroupKey) == keysize);
+
+	dst_key->expr = src_key->expr;
+	dst_key->opno = src_key->opno;
+	dst_key->exprtype = src_key->exprtype;
+	return dst_key;
+}
+
+/*
+ * Dynahash match function to use in guc_hashtab
+ */
+static int
+orclause_match(const void *data1, const void *data2, Size keysize)
+{
+	OrClauseGroupKey   *key1 = (OrClauseGroupKey *) data1;
+	OrClauseGroupKey   *key2 = (OrClauseGroupKey *) data2;
+
+	Assert(sizeof(OrClauseGroupKey) == keysize);
+
+	if (key1->opno == key2->opno && key1->exprtype == key2->exprtype &&
+		equal(key1->expr, key2->expr))
+		return 0;
+
+	return 1;
+}
+
+/*
+ * Pass through baserestrictinfo clauses and try to convert OR clauses into IN
+ * Return a modified clause list or just the same baserestrictinfo, if no
+ * changes have made.
+ * XXX: do not change source list of clauses at all.
+ */
+static List *
+transform_ors(PlannerInfo *root, List *baserestrictinfo, bool *something_changed)
+{
+	ListCell   *lc;
+	List	   *modified_rinfo = NIL;
+
+	/*
+	 * Complexity of a clause could be arbitrarily sophisticated. Here, we will
+	 * look up only on the top level of clause list.
+	 * XXX: It is substantiated? Could we change something here?
+	 */
+	foreach (lc, baserestrictinfo)
+	{
+		RestrictInfo   *rinfo = lfirst_node(RestrictInfo, lc);
+		RestrictInfo   *rinfo_base = copyObject(rinfo);
+		List		   *or_list = NIL;
+		ListCell	   *lc_eargs,
+					   *lc_rargs;
+		bool			change_apply = false;
+		HASHCTL			info;
+		HTAB 		   *or_group_htab = NULL;
+		int 			len_ors;
+		HASH_SEQ_STATUS		hash_seq;
+		OrClauseGroupEntry *gentry;
+
+		if (!enable_or_transformation || !restriction_is_or_clause(rinfo))
+		{
+			/* Add a clause without changes */
+			modified_rinfo = lappend(modified_rinfo, rinfo);
+			continue;
+		}
+
+		len_ors = ((BoolExpr *) rinfo->clause)->args? list_length(((BoolExpr *) rinfo->clause)->args) : 0;
+
+		if (len_ors < 2)
+		{
+			/* Add a clause without changes */
+			modified_rinfo = lappend(modified_rinfo, rinfo);
+			continue;
+		}
+
+		MemSet(&info, 0, sizeof(info));
+		info.keysize = sizeof(OrClauseGroupKey);
+		info.entrysize = sizeof(OrClauseGroupEntry);
+		info.hash = orclause_hash;
+		info.keycopy = orclause_keycopy;
+		info.match = orclause_match;
+		or_group_htab = hash_create("OR Groups",
+									len_ors,
+									&info,
+									HASH_ELEM | HASH_FUNCTION | HASH_COMPARE | HASH_KEYCOPY);
+
+		/*
+		 * NOTE:
+		 * It is an OR-clause. So, rinfo->orclause is a BoolExpr node, contains
+		 * a list of sub-restrictinfo args, and rinfo->clause - which is the
+		 * same expression, made from bare clauses. To not break selectivity
+		 * caches and other optimizations, use both:
+		 * - use rinfos from orclause if no transformation needed
+		 * - use  bare quals from rinfo->clause in the case of transformation,
+		 * to create new RestrictInfo: in this case we have no options to avoid
+		 * selectivity estimation procedure.
+		 */
+		forboth(lc_eargs, ((BoolExpr *) rinfo->clause)->args,
+				lc_rargs, ((BoolExpr *) rinfo->orclause)->args)
+		{
+			Expr			   *or_qual = (Expr *) lfirst(lc_eargs);
+			RestrictInfo	   *sub_rinfo;
+			Node			   *const_expr;
+			Node			   *nconst_expr;
+			OrClauseGroupKey		hashkey;
+			bool found = false;
+
+			/* It may be one more boolean expression, skip it for now */
+			if (!IsA(lfirst(lc_rargs), RestrictInfo))
+			{
+				or_list = lappend(or_list, (void *) or_qual);
+				continue;
+			}
+
+			sub_rinfo = lfirst_node(RestrictInfo, lc_rargs);
+
+			/* Check: it is an expr of the form 'F(x) oper ConstExpr' */
+			if (!IsA(or_qual, OpExpr) ||
+				!(bms_is_empty(sub_rinfo->left_relids) ^
+				bms_is_empty(sub_rinfo->right_relids)) ||
+				contain_volatile_functions((Node *) or_qual))
+			{
+				/* Again, it's not the expr we can transform */
+				or_list = lappend(or_list, (void *) or_qual);
+				continue;
+			}
+
+			/* Get pointers to constant and expression sides of the clause */
+			const_expr =bms_is_empty(sub_rinfo->left_relids) ?
+												get_leftop(sub_rinfo->clause) :
+												get_rightop(sub_rinfo->clause);
+			nconst_expr = bms_is_empty(sub_rinfo->left_relids) ?
+												get_rightop(sub_rinfo->clause) :
+												get_leftop(sub_rinfo->clause);
+
+			if (!op_mergejoinable(((OpExpr *) sub_rinfo->clause)->opno, exprType(nconst_expr)))
+			{
+				/* And again, filter out non-equality operators */
+				or_list = lappend(or_list, (void *) or_qual);
+				continue;
+			}
+
+			/*
+			 * At this point we definitely have a transformable clause.
+			 * Classify it and add into specific group of clauses, or create new
+			 * group.
+			 */
+			hashkey.expr = (Expr *) nconst_expr;
+			hashkey.opno = ((OpExpr *) or_qual)->opno;
+			hashkey.exprtype = exprType(nconst_expr);
+			gentry = hash_search(or_group_htab, &hashkey, HASH_ENTER, &found);
+
+			if (unlikely(found))
+			{
+				gentry->consts = lappend(gentry->consts, const_expr);
+				gentry->exprs = lappend(gentry->exprs, or_qual);
+			}
+			else
+			{
+				gentry->node = nconst_expr;
+				gentry->consts = list_make1(const_expr);
+				gentry->exprs = list_make1(or_qual);
+				gentry->rinfo = sub_rinfo;
+				gentry->collation = exprInputCollation((Node *)sub_rinfo->clause);
+			}
+		}
+
+		if (list_length(or_list) == len_ors)
+		{
+			/*
+			* No any transformations possible with this list of arguments. Here we
+			* already made all underlying transformations. Thus, just return the
+			* transformed bool expression.
+			*/
+			modified_rinfo = lappend(modified_rinfo, rinfo);
+			continue;
+		}
+
+		if (!or_group_htab && hash_get_num_entries(or_group_htab) < 1)
+		{
+			/*
+			 * No any transformations possible with this rinfo, just add itself
+			 * to the list and go further.
+			 */
+			modified_rinfo = lappend(modified_rinfo, rinfo);
+			continue;
+		}
+
+		hash_seq_init(&hash_seq, or_group_htab);
+
+		/* Let's convert each group of clauses to an IN operation. */
+
+		/*
+		* Go through the list of groups and convert each, where number of
+		* consts more than 1. trivial groups move to OR-list again
+		*/
+
+		while ((gentry = (OrClauseGroupEntry *) hash_seq_search(&hash_seq)) != NULL)
+		{
+			Oid				    scalar_type;
+			Oid					array_type;
+
+			Assert(list_length(gentry->consts) > 0);
+			Assert(list_length(gentry->exprs) == list_length(gentry->consts));
+
+			if (list_length(gentry->consts) == 1)
+			{
+				/*
+				* Only one element in the class. Return rinfo into the BoolExpr
+				* args list unchanged.
+				*/
+				list_free(gentry->consts);
+				or_list = list_concat(or_list, gentry->exprs);
+				continue;
+			}
+
+			/*
+			* Do the transformation.
+			*
+			* First of all, try to select a common type for the array elements.
+			* Note that since the LHS' type is first in the list, it will be
+			* preferred when there is doubt (eg, when all the RHS items are
+			* unknown literals).
+			*
+			* Note: use list_concat here not lcons, to avoid damaging rnonvars.
+			*
+			* As a source of insides, use make_scalar_array_op()
+			*/
+			scalar_type = gentry->key.exprtype;
+
+			if (scalar_type != RECORDOID && OidIsValid(scalar_type))
+				array_type = get_array_type(scalar_type);
+			else
+				array_type = InvalidOid;
+
+			if (array_type != InvalidOid && scalar_type != InvalidOid)
+			{
+				/*
+				* OK: coerce all the right-hand non-Var inputs to the common
+				* type and build an ArrayExpr for them.
+				*/
+				List	   *aexprs = NULL;
+				ArrayExpr  *newa = NULL;
+				ScalarArrayOpExpr *saopexpr = NULL;
+				HeapTuple			opertup;
+				Form_pg_operator	operform;
+				List			   *namelist = NIL;
+
+				aexprs = gentry->consts;
+
+				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;
+
+				opertup = SearchSysCache1(OPEROID,
+									  ObjectIdGetDatum(gentry->key.opno));
+
+				if (!HeapTupleIsValid(opertup))
+					elog(ERROR, "cache lookup failed for operator %u",
+						gentry->key.opno);
+
+				operform = (Form_pg_operator) GETSTRUCT(opertup);
+				if (!OperatorIsVisible(gentry->key.opno))
+					namelist = lappend(namelist, makeString(get_namespace_name(operform->oprnamespace)));
+
+				namelist = lappend(namelist, makeString(pstrdup(NameStr(operform->oprname))));
+				ReleaseSysCache(opertup);
+
+				saopexpr = makeNode(ScalarArrayOpExpr);
+				saopexpr->opno = ((OpExpr *) gentry->rinfo->clause)->opno;
+				saopexpr->useOr = true;
+				saopexpr->inputcollid = gentry->collation;
+				saopexpr->args = list_make2(gentry->node, newa);
+				saopexpr->location = -1;
+
+
+				or_list = lappend(or_list, (void *) saopexpr);
+
+				*something_changed = true;
+				change_apply = true;
+
+			}
+			else
+			{
+				list_free(gentry->consts);
+				or_list = lappend(or_list, (void *) gentry->rinfo->clause);
+			}
+			hash_search(or_group_htab, &gentry->key, HASH_REMOVE, NULL);
+		}
+
+		hash_destroy(or_group_htab);
+
+		if (!change_apply)
+		{
+			/*
+			 * Each group contains only one element - use rinfo as is.
+			 */
+			modified_rinfo = lappend(modified_rinfo, rinfo);
+			list_free(or_list);
+			continue;
+		}
+
+		/*
+		 * Make a new version of the restriction. Remember source restriction
+		 * can be used in another path (SeqScan, for example).
+		 */
+
+		/* One more trick: assemble correct clause */
+		rinfo = make_restrictinfo(root,
+				  list_length(or_list) > 1 ? makeBoolExpr(OR_EXPR, or_list, ((BoolExpr *) rinfo->clause)->location) :
+											 linitial(or_list),
+				  rinfo->is_pushed_down,
+				  rinfo->has_clone,
+				  rinfo->is_clone,
+				  rinfo->pseudoconstant,
+				  rinfo->security_level,
+				  rinfo->required_relids,
+				  rinfo->incompatible_relids,
+				  rinfo->outer_relids);
+		rinfo->eval_cost=rinfo_base->eval_cost;
+		rinfo->norm_selec=rinfo_base->norm_selec;
+		rinfo->outer_selec=rinfo_base->outer_selec;
+		rinfo->left_bucketsize=rinfo_base->left_bucketsize;
+		rinfo->right_bucketsize=rinfo_base->right_bucketsize;
+		rinfo->left_mcvfreq=rinfo_base->left_mcvfreq;
+		rinfo->right_mcvfreq=rinfo_base->right_mcvfreq;
+		modified_rinfo = lappend(modified_rinfo, rinfo);
+		*something_changed = true;
+	}
+
+	/*
+	 * Check if transformation has made. If nothing changed - return
+	 * baserestrictinfo as is.
+	 */
+	if (*something_changed)
+	{
+		return modified_rinfo;
+	}
+
+	list_free(modified_rinfo);
+	return baserestrictinfo;
+}
+
 /*
  * make_one_rel
  *	  Finds all possible access paths for executing a query, returning a
@@ -767,6 +1157,12 @@ static void
 set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
 {
 	Relids		required_outer;
+	RelOptInfo *reL_alternative = makeNode(RelOptInfo);
+	//reL_alternative = copyObject(rel);
+	reL_alternative = copy_simple_rel(root, rel->relid, rel, reL_alternative);
+	reL_alternative->indexlist = NIL;
+	reL_alternative->baserestrictinfo = rel->baserestrictinfo;
+	reL_alternative->eclass_indexes = NULL;
 
 	/*
 	 * We don't support pushing join clauses into the quals of a seqscan, but
@@ -787,6 +1183,39 @@ set_plain_rel_pathlist(PlannerInfo *root, RelOptInfo *rel, RangeTblEntry *rte)
 
 	/* Consider TID scans */
 	create_tidscan_paths(root, rel);
+
+	set_cheapest(rel);
+
+	if (rel->reloptkind == RELOPT_BASEREL && enable_or_transformation)
+	{
+		bool applied_transfomation;
+		applied_transfomation =  (bool *) palloc(sizeof(bool));
+		reL_alternative->baserestrictinfo = transform_ors(root, reL_alternative->baserestrictinfo, &applied_transfomation);
+if (applied_transfomation)
+{
+	add_path(reL_alternative, create_seqscan_path(root, reL_alternative, required_outer, 0));
+
+	if (reL_alternative->consider_parallel && required_outer == NULL)
+		create_plain_partial_paths(root, reL_alternative);
+
+	create_index_paths(root, reL_alternative);
+
+	create_tidscan_paths(root, reL_alternative);
+
+	set_cheapest(reL_alternative);
+
+	elog(WARNING, "ors: - %f", rel->cheapest_total_path->total_cost);
+	elog(WARNING, "applied_transfomation: - %f", reL_alternative->cheapest_total_path->total_cost);
+
+	if (reL_alternative->cheapest_total_path->total_cost <= rel->cheapest_total_path->total_cost)
+	{
+		//copy_simple_rel(root, rel->relid, reL_alternative, rel);
+		rel->indexlist = reL_alternative->indexlist;
+		rel->baserestrictinfo = reL_alternative->baserestrictinfo;
+		rel->eclass_indexes = reL_alternative->eclass_indexes;
+	}
+	}
+	}
 }
 
 /*
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index acf4937db01..a9cc9a585df 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -34,12 +34,6 @@
 #include "optimizer/restrictinfo.h"
 #include "utils/lsyscache.h"
 #include "utils/selfuncs.h"
-#include "parser/parse_coerce.h"
-#include "common/hashfn.h"
-#include "catalog/pg_operator.h"
-#include "nodes/queryjumble.h"
-#include "utils/syscache.h"
-#include "catalog/namespace.h"
 
 /* XXX see PartCollMatchesExprColl */
 #define IndexCollMatchesExprColl(idxcollation, exprcollation) \
@@ -198,391 +192,6 @@ static bool ec_member_matches_indexcol(PlannerInfo *root, RelOptInfo *rel,
 									   EquivalenceClass *ec, EquivalenceMember *em,
 									   void *arg);
 
-bool		enable_or_transformation = true;
-typedef struct OrClauseGroupKey
-{
-	Expr   *expr; /* Pointer to the expression tree which has been a source for
-					the hashkey value */
-	Oid		opno;
-	Oid		exprtype;
-} OrClauseGroupKey;
-
-typedef struct OrClauseGroupEntry
-{
-	OrClauseGroupKey key;
-	Node		   *node;
-	List		   *consts;
-	Oid				collation;
-	RestrictInfo   *rinfo;
-	List 		   *exprs;
-} OrClauseGroupEntry;
-
-/*
- * Hash function that's compatible with guc_name_compare
- */
-static uint32
-orclause_hash(const void *data, Size keysize)
-{
-	OrClauseGroupKey   *key = (OrClauseGroupKey *) data;
-	uint64				hash;
-
-	(void) JumbleExpr(key->expr, &hash);
-	hash = ((uint64) key->opno + (uint64) key->exprtype) % UINT64_MAX;
-	return hash;
-}
-
-static void *
-orclause_keycopy(void *dest, const void *src, Size keysize)
-{
-	OrClauseGroupKey *src_key = (OrClauseGroupKey *) src;
-	OrClauseGroupKey *dst_key = (OrClauseGroupKey *) dest;
-
-	Assert(sizeof(OrClauseGroupKey) == keysize);
-
-	dst_key->expr = src_key->expr;
-	dst_key->opno = src_key->opno;
-	dst_key->exprtype = src_key->exprtype;
-	return dst_key;
-}
-
-/*
- * Dynahash match function to use in guc_hashtab
- */
-static int
-orclause_match(const void *data1, const void *data2, Size keysize)
-{
-	OrClauseGroupKey   *key1 = (OrClauseGroupKey *) data1;
-	OrClauseGroupKey   *key2 = (OrClauseGroupKey *) data2;
-
-	Assert(sizeof(OrClauseGroupKey) == keysize);
-
-	if (key1->opno == key2->opno && key1->exprtype == key2->exprtype &&
-		equal(key1->expr, key2->expr))
-		return 0;
-
-	return 1;
-}
-
-/*
- * Pass through baserestrictinfo clauses and try to convert OR clauses into IN
- * Return a modified clause list or just the same baserestrictinfo, if no
- * changes have made.
- * XXX: do not change source list of clauses at all.
- */
-static List *
-transform_ors(PlannerInfo *root, List *baserestrictinfo)
-{
-	ListCell   *lc;
-	List	   *modified_rinfo = NIL;
-	bool		something_changed = false;
-
-	/*
-	 * Complexity of a clause could be arbitrarily sophisticated. Here, we will
-	 * look up only on the top level of clause list.
-	 * XXX: It is substantiated? Could we change something here?
-	 */
-	foreach (lc, baserestrictinfo)
-	{
-		RestrictInfo   *rinfo = lfirst_node(RestrictInfo, lc);
-		RestrictInfo   *rinfo_base = copyObject(rinfo);
-		List		   *or_list = NIL;
-		ListCell	   *lc_eargs,
-					   *lc_rargs;
-		bool			change_apply = false;
-		HASHCTL			info;
-		HTAB 		   *or_group_htab = NULL;
-		int 			len_ors;
-		HASH_SEQ_STATUS		hash_seq;
-		OrClauseGroupEntry *gentry;
-
-		if (!enable_or_transformation || !restriction_is_or_clause(rinfo))
-		{
-			/* Add a clause without changes */
-			modified_rinfo = lappend(modified_rinfo, rinfo);
-			continue;
-		}
-
-		len_ors = ((BoolExpr *) rinfo->clause)->args? list_length(((BoolExpr *) rinfo->clause)->args) : 0;
-
-		if (len_ors < 2)
-		{
-			/* Add a clause without changes */
-			modified_rinfo = lappend(modified_rinfo, rinfo);
-			continue;
-		}
-
-		MemSet(&info, 0, sizeof(info));
-		info.keysize = sizeof(OrClauseGroupKey);
-		info.entrysize = sizeof(OrClauseGroupEntry);
-		info.hash = orclause_hash;
-		info.keycopy = orclause_keycopy;
-		info.match = orclause_match;
-		or_group_htab = hash_create("OR Groups",
-									len_ors,
-									&info,
-									HASH_ELEM | HASH_FUNCTION | HASH_COMPARE | HASH_KEYCOPY);
-
-		/*
-		 * NOTE:
-		 * It is an OR-clause. So, rinfo->orclause is a BoolExpr node, contains
-		 * a list of sub-restrictinfo args, and rinfo->clause - which is the
-		 * same expression, made from bare clauses. To not break selectivity
-		 * caches and other optimizations, use both:
-		 * - use rinfos from orclause if no transformation needed
-		 * - use  bare quals from rinfo->clause in the case of transformation,
-		 * to create new RestrictInfo: in this case we have no options to avoid
-		 * selectivity estimation procedure.
-		 */
-		forboth(lc_eargs, ((BoolExpr *) rinfo->clause)->args,
-				lc_rargs, ((BoolExpr *) rinfo->orclause)->args)
-		{
-			Expr			   *or_qual = (Expr *) lfirst(lc_eargs);
-			RestrictInfo	   *sub_rinfo;
-			Node			   *const_expr;
-			Node			   *nconst_expr;
-			OrClauseGroupKey		hashkey;
-			bool found = false;
-
-			/* It may be one more boolean expression, skip it for now */
-			if (!IsA(lfirst(lc_rargs), RestrictInfo))
-			{
-				or_list = lappend(or_list, (void *) or_qual);
-				continue;
-			}
-
-			sub_rinfo = lfirst_node(RestrictInfo, lc_rargs);
-
-			/* Check: it is an expr of the form 'F(x) oper ConstExpr' */
-			if (!IsA(or_qual, OpExpr) ||
-				!(bms_is_empty(sub_rinfo->left_relids) ^
-				bms_is_empty(sub_rinfo->right_relids)) ||
-				contain_volatile_functions((Node *) or_qual))
-			{
-				/* Again, it's not the expr we can transform */
-				or_list = lappend(or_list, (void *) or_qual);
-				continue;
-			}
-
-			/* Get pointers to constant and expression sides of the clause */
-			const_expr =bms_is_empty(sub_rinfo->left_relids) ?
-												get_leftop(sub_rinfo->clause) :
-												get_rightop(sub_rinfo->clause);
-			nconst_expr = bms_is_empty(sub_rinfo->left_relids) ?
-												get_rightop(sub_rinfo->clause) :
-												get_leftop(sub_rinfo->clause);
-
-			if (!op_mergejoinable(((OpExpr *) sub_rinfo->clause)->opno, exprType(nconst_expr)))
-			{
-				/* And again, filter out non-equality operators */
-				or_list = lappend(or_list, (void *) or_qual);
-				continue;
-			}
-
-			/*
-			 * At this point we definitely have a transformable clause.
-			 * Classify it and add into specific group of clauses, or create new
-			 * group.
-			 */
-			hashkey.expr = (Expr *) nconst_expr;
-			hashkey.opno = ((OpExpr *) or_qual)->opno;
-			hashkey.exprtype = exprType(nconst_expr);
-			gentry = hash_search(or_group_htab, &hashkey, HASH_ENTER, &found);
-
-			if (unlikely(found))
-			{
-				gentry->consts = lappend(gentry->consts, const_expr);
-				gentry->exprs = lappend(gentry->exprs, or_qual);
-			}
-			else
-			{
-				gentry->node = nconst_expr;
-				gentry->consts = list_make1(const_expr);
-				gentry->exprs = list_make1(or_qual);
-				gentry->rinfo = sub_rinfo;
-				gentry->collation = exprInputCollation((Node *)sub_rinfo->clause);
-			}
-		}
-
-		if (list_length(or_list) == len_ors)
-		{
-			/*
-			* No any transformations possible with this list of arguments. Here we
-			* already made all underlying transformations. Thus, just return the
-			* transformed bool expression.
-			*/
-			modified_rinfo = lappend(modified_rinfo, rinfo);
-			continue;
-		}
-
-		if (!or_group_htab && hash_get_num_entries(or_group_htab) < 1)
-		{
-			/*
-			 * No any transformations possible with this rinfo, just add itself
-			 * to the list and go further.
-			 */
-			modified_rinfo = lappend(modified_rinfo, rinfo);
-			continue;
-		}
-
-		hash_seq_init(&hash_seq, or_group_htab);
-
-		/* Let's convert each group of clauses to an IN operation. */
-
-		/*
-		* Go through the list of groups and convert each, where number of
-		* consts more than 1. trivial groups move to OR-list again
-		*/
-
-		while ((gentry = (OrClauseGroupEntry *) hash_seq_search(&hash_seq)) != NULL)
-		{
-			Oid				    scalar_type;
-			Oid					array_type;
-
-			Assert(list_length(gentry->consts) > 0);
-			Assert(list_length(gentry->exprs) == list_length(gentry->consts));
-
-			if (list_length(gentry->consts) == 1)
-			{
-				/*
-				* Only one element in the class. Return rinfo into the BoolExpr
-				* args list unchanged.
-				*/
-				list_free(gentry->consts);
-				or_list = list_concat(or_list, gentry->exprs);
-				continue;
-			}
-
-			/*
-			* Do the transformation.
-			*
-			* First of all, try to select a common type for the array elements.
-			* Note that since the LHS' type is first in the list, it will be
-			* preferred when there is doubt (eg, when all the RHS items are
-			* unknown literals).
-			*
-			* Note: use list_concat here not lcons, to avoid damaging rnonvars.
-			*
-			* As a source of insides, use make_scalar_array_op()
-			*/
-			scalar_type = gentry->key.exprtype;
-
-			if (scalar_type != RECORDOID && OidIsValid(scalar_type))
-				array_type = get_array_type(scalar_type);
-			else
-				array_type = InvalidOid;
-
-			if (array_type != InvalidOid && scalar_type != InvalidOid)
-			{
-				/*
-				* OK: coerce all the right-hand non-Var inputs to the common
-				* type and build an ArrayExpr for them.
-				*/
-				List	   *aexprs = NULL;
-				ArrayExpr  *newa = NULL;
-				ScalarArrayOpExpr *saopexpr = NULL;
-				HeapTuple			opertup;
-				Form_pg_operator	operform;
-				List			   *namelist = NIL;
-
-				aexprs = gentry->consts;
-
-				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;
-
-				opertup = SearchSysCache1(OPEROID,
-									  ObjectIdGetDatum(gentry->key.opno));
-
-				if (!HeapTupleIsValid(opertup))
-					elog(ERROR, "cache lookup failed for operator %u",
-						gentry->key.opno);
-
-				operform = (Form_pg_operator) GETSTRUCT(opertup);
-				if (!OperatorIsVisible(gentry->key.opno))
-					namelist = lappend(namelist, makeString(get_namespace_name(operform->oprnamespace)));
-
-				namelist = lappend(namelist, makeString(pstrdup(NameStr(operform->oprname))));
-				ReleaseSysCache(opertup);
-
-				saopexpr = makeNode(ScalarArrayOpExpr);
-				saopexpr->opno = ((OpExpr *) gentry->rinfo->clause)->opno;
-				saopexpr->useOr = true;
-				saopexpr->inputcollid = gentry->collation;
-				saopexpr->args = list_make2(gentry->node, newa);
-				saopexpr->location = -1;
-
-
-				or_list = lappend(or_list, (void *) saopexpr);
-
-				something_changed = true;
-				change_apply = true;
-
-			}
-			else
-			{
-				list_free(gentry->consts);
-				or_list = lappend(or_list, (void *) gentry->rinfo->clause);
-			}
-			hash_search(or_group_htab, &gentry->key, HASH_REMOVE, NULL);
-		}
-
-		hash_destroy(or_group_htab);
-
-		if (!change_apply)
-		{
-			/*
-			 * Each group contains only one element - use rinfo as is.
-			 */
-			modified_rinfo = lappend(modified_rinfo, rinfo);
-			list_free(or_list);
-			continue;
-		}
-
-		/*
-		 * Make a new version of the restriction. Remember source restriction
-		 * can be used in another path (SeqScan, for example).
-		 */
-
-		/* One more trick: assemble correct clause */
-		rinfo = make_restrictinfo(root,
-				  list_length(or_list) > 1 ? makeBoolExpr(OR_EXPR, or_list, ((BoolExpr *) rinfo->clause)->location) :
-											 linitial(or_list),
-				  rinfo->is_pushed_down,
-				  rinfo->has_clone,
-				  rinfo->is_clone,
-				  rinfo->pseudoconstant,
-				  rinfo->security_level,
-				  rinfo->required_relids,
-				  rinfo->incompatible_relids,
-				  rinfo->outer_relids);
-		rinfo->eval_cost=rinfo_base->eval_cost;
-		rinfo->norm_selec=rinfo_base->norm_selec;
-		rinfo->outer_selec=rinfo_base->outer_selec;
-		rinfo->left_bucketsize=rinfo_base->left_bucketsize;
-		rinfo->right_bucketsize=rinfo_base->right_bucketsize;
-		rinfo->left_mcvfreq=rinfo_base->left_mcvfreq;
-		rinfo->right_mcvfreq=rinfo_base->right_mcvfreq;
-		modified_rinfo = lappend(modified_rinfo, rinfo);
-		something_changed = true;
-	}
-
-	/*
-	 * Check if transformation has made. If nothing changed - return
-	 * baserestrictinfo as is.
-	 */
-	if (something_changed)
-	{
-		return modified_rinfo;
-	}
-
-	list_free(modified_rinfo);
-	return baserestrictinfo;
-}
-
 /*
  * create_index_paths()
  *	  Generate all interesting index paths for the given relation.
@@ -1108,9 +717,6 @@ get_index_paths(PlannerInfo *root, RelOptInfo *rel,
 	bool		skip_lower_saop = false;
 	ListCell   *lc;
 
-	if (rel->reloptkind == RELOPT_BASEREL)
-		rel->baserestrictinfo = transform_ors(root, rel->baserestrictinfo);
-
 	/*
 	 * Build simple index paths using the clauses.  Allow ScalarArrayOpExpr
 	 * clauses only if the index AM supports them natively, and skip any such
diff --git a/src/backend/optimizer/util/relnode.c b/src/backend/optimizer/util/relnode.c
index 5d83f60eb9a..189cee449bf 100644
--- a/src/backend/optimizer/util/relnode.c
+++ b/src/backend/optimizer/util/relnode.c
@@ -183,6 +183,97 @@ expand_planner_arrays(PlannerInfo *root, int add_size)
 	root->simple_rel_array_size = new_size;
 }
 
+RelOptInfo *
+copy_simple_rel(PlannerInfo *root, int relid, RelOptInfo *base_rel, RelOptInfo *rel)
+{
+	rel->reloptkind = base_rel->parent ? RELOPT_OTHER_MEMBER_REL : RELOPT_BASEREL;
+	rel->relids = base_rel->relids;
+	rel->rows = base_rel->rows;
+	/* cheap startup cost is interesting iff not all tuples to be retrieved */
+	rel->consider_startup = (root->tuple_fraction > 0);
+	rel->consider_param_startup = base_rel->consider_param_startup;	/* might get changed later */
+	rel->consider_parallel = base_rel->consider_parallel; /* might get changed later */
+	rel->reltarget = base_rel->reltarget;
+	rel->pathlist = base_rel->pathlist;
+	rel->ppilist = base_rel->ppilist;
+	rel->partial_pathlist = base_rel->partial_pathlist;
+	rel->cheapest_startup_path = base_rel->cheapest_startup_path;
+	rel->cheapest_total_path = base_rel->cheapest_total_path;
+	rel->cheapest_unique_path = base_rel->cheapest_unique_path;
+	rel->cheapest_parameterized_paths = base_rel->cheapest_parameterized_paths;
+	rel->relid = base_rel->relid;
+	/* min_attr, max_attr, attr_needed, attr_widths are set below */
+	rel->lateral_vars = base_rel->lateral_vars;
+	rel->statlist = base_rel->statlist;
+	rel->pages = base_rel->pages;
+	rel->tuples = base_rel->tuples;
+	rel->allvisfrac = base_rel->allvisfrac;
+	rel->subroot = base_rel->subroot;
+	rel->subplan_params = base_rel->subplan_params;
+	rel->rel_parallel_workers = base_rel->rel_parallel_workers; /* set up in get_relation_info */
+	rel->amflags = base_rel->amflags;
+	rel->parent = base_rel->parent;
+	rel->serverid = base_rel->serverid;
+	rel->userid = base_rel->userid;
+	rel->useridiscurrent = base_rel->useridiscurrent;
+	rel->fdwroutine = base_rel->fdwroutine;
+	rel->fdw_private = base_rel->fdw_private;
+	rel->unique_for_rels = base_rel->unique_for_rels;
+	rel->non_unique_for_rels = base_rel->non_unique_for_rels;
+	rel->baserestrictcost.startup = 0;
+	rel->baserestrictcost.per_tuple = 0;
+	rel->baserestrict_min_security = UINT_MAX;
+	rel->joininfo = base_rel->joininfo;
+	rel->has_eclass_joins = base_rel->has_eclass_joins;
+
+	/*
+	 * Pass assorted information down the inheritance hierarchy.
+	 */
+	if (base_rel->parent)
+	{
+		/* We keep back-links to immediate parent and topmost parent. */
+		rel->parent = base_rel->parent;
+		rel->top_parent = base_rel->parent->top_parent ? base_rel->parent->top_parent : base_rel->parent;
+		rel->top_parent_relids = base_rel->top_parent->relids;
+
+		/*
+		 * A child rel is below the same outer joins as its parent.  (We
+		 * presume this info was already calculated for the parent.)
+		 */
+		rel->nulling_relids = base_rel->parent->nulling_relids;
+
+		/*
+		 * Also propagate lateral-reference information from appendrel parent
+		 * rels to their child rels.  We intentionally give each child rel the
+		 * same minimum parameterization, even though it's quite possible that
+		 * some don't reference all the lateral rels.  This is because any
+		 * append path for the parent will have to have the same
+		 * parameterization for every child anyway, and there's no value in
+		 * forcing extra reparameterize_path() calls.  Similarly, a lateral
+		 * reference to the parent prevents use of otherwise-movable join rels
+		 * for each child.
+		 *
+		 * It's possible for child rels to have their own children, in which
+		 * case the topmost parent's lateral info propagates all the way down.
+		 */
+		rel->direct_lateral_relids = base_rel->parent->direct_lateral_relids;
+		rel->lateral_relids = base_rel->parent->lateral_relids;
+		rel->lateral_referencers = base_rel->parent->lateral_referencers;
+	}
+	else
+	{
+		rel->parent = base_rel->parent;
+		rel->top_parent = base_rel->top_parent;
+		rel->top_parent_relids = base_rel->top_parent_relids;
+		rel->nulling_relids = base_rel->nulling_relids;
+		rel->direct_lateral_relids = base_rel->direct_lateral_relids;
+		rel->lateral_relids = base_rel->lateral_relids;
+		rel->lateral_referencers = base_rel->lateral_referencers;
+	}
+
+	return rel;
+}
+
 /*
  * build_simple_rel
  *	  Construct a new RelOptInfo for a base relation or 'other' relation.
diff --git a/src/include/optimizer/pathnode.h b/src/include/optimizer/pathnode.h
index 6e557bebc44..2b3825b6e52 100644
--- a/src/include/optimizer/pathnode.h
+++ b/src/include/optimizer/pathnode.h
@@ -304,6 +304,8 @@ extern Path *reparameterize_path_by_child(PlannerInfo *root, Path *path,
  */
 extern void setup_simple_rel_arrays(PlannerInfo *root);
 extern void expand_planner_arrays(PlannerInfo *root, int add_size);
+extern RelOptInfo *
+copy_simple_rel(PlannerInfo *root, int relid, RelOptInfo *base_rel, RelOptInfo *rel);
 extern RelOptInfo *build_simple_rel(PlannerInfo *root, int relid,
 									RelOptInfo *parent);
 extern RelOptInfo *find_base_rel(PlannerInfo *root, int relid);
-- 
2.34.1

---

From: Andrei Lepikhov <[email-masked]>
Date: Thu, 30 Nov 2023 15:30:28 +0700

> So, I got the best query plan despite the possible OR to ANY 

If the user uses a clause like "x IN (1,2) AND y=100", it will break 
your 'good' solution.
In my opinion, the general approach here is to stay with OR->ANY 
transformation at the parsing stage and invent one more way for picking 
an index by looking into the array and attempting to find a compound index.
Having a shorter list of expressions, where uniform ORs are grouped into 
arrays, the optimizer will do such work with less overhead.

-- 
regards,
Andrei Lepikhov
Postgres Professional

---

From: Alena Rybakina <[email-masked]>
Date: Thu, 30 Nov 2023 12:00:41 +0300

> If the user uses a clause like "x IN (1,2) AND y=100", it will break 

No, unfortunately I still see the plan with Seq scan node:

postgres=# explain analyze select * from test where x in (1,2) and y = 100;

                                                      QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
  Gather  (cost=1000.00..12690.10 rows=1 width=12) (actual 
time=72.985..74.832 rows=0 loops=1)
    Workers Planned: 2
    Workers Launched: 2
    ->  Parallel Seq Scan on test  (cost=0.00..11690.00 rows=1 width=12) 
(actual time=68.573..68.573 rows=0 loops=3)
          Filter: ((x = ANY ('{1,2}'::integer[])) AND (y = '100'::double 
precision))
          Rows Removed by Filter: 333333
  Planning Time: 0.264 ms
  Execution Time: 74.887 ms

(8 rows)

> transformation at the parsing stage and invent one more way for 
> picking an index by looking into the array and attempting to find a 
> compound index.
> Having a shorter list of expressions, where uniform ORs are grouped 
> into arrays, the optimizer will do such work with less overhead. 

Looking at the current index generation code, implementing this approach 
will require a lot of refactoring so that functions starting with 
get_indexes do not rely on the current baserestrictinfo, but use only 
the indexrestrictinfo, which is a copy of baserestrictinfo. And I think, 
potentially, there may be complexity also with the equivalences that we 
can get from OR expressions. All interesting transformations are 
available only for OR expressions, not for ANY, that is, it makes sense 
to try the last chance to find a suitable plan with the available OR 
expressions and if that plan turns out to be better, use it.

-- 
Regards,
Alena Rybakina
Postgres Professional