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

From: Alena Rybakina <[email-masked]>
Date: Wed, 2 Aug 2023 18:58:37 +0300

I fixed an error that caused the current optimization not to work with 
prepared queries. I added a test to catch similar cases in the future.
I have attached a patch.

On 01.08.2023 22:42, Peter Geoghegan wrote:
> On Mon, Jul 31, 2023 at 9:38 AM Alena Rybakina <[email-masked]> wrote:
>> I noticed only one thing there: when we have unsorted array values in
>> SOAP, the query takes longer than
>> when it has a sorted array. I'll double-check it just in case and write
>> about the results later.
> I would expect the B-Tree preprocessing by _bt_preprocess_array_keys()
> to be very slightly faster when the query is written with presorted,
> duplicate-free constants. Sorting is faster when you don't really have
> to sort. However, I would not expect the effect to be significant
> enough to matter, except perhaps in very extreme cases.
> Although...some of the cases you care about are very extreme cases.
I tested an optimization to compare execution time and scheduling with 
sorting, shuffling, and reverse sorting constants in the simple case and
I didn't notice any significant changes (compare_sorted.png).
(I used a database with 100 million values generated by pgbench).
>> I am also testing some experience with multi-column indexes using SAOPs.
> Have you thought about a similar transformation for when the row
> constructor syntax happens to have been used?
>
> Consider a query like the following, against a table with a composite
> index on (a, b):
>
> select * from multi_test where ( a, b ) in (( 1, 1 ), ( 2, 1 ));
>
> This query will get a BitmapOr based plan that's similar to the plans
> that OR-based queries affected by your transformation patch get today,
> on HEAD.  However, this equivalent spelling has the potential to be
> significantly faster:
>
> select * from multi_test where a = any('{1,2}') and b = 1;
>
> (Of course, this is more likely to be true with my nbtree SAOP patch in place.)
No, I haven't thought about it yet. I studied the example and it would 
really be nice to add optimization here. I didn't notice any problems 
with its implementation. I also have an obvious example with the "or" 
operator, for example
, select * from multi_test, where (a, b ) = ( 1, 1 ) or (a, b ) = ( 2, 1 
) ...;

Although I think such a case will be used less often.

Thank you for the example, I think I understand better why our patches 
help each other, but I will review your patch again.

I tried another example to see the lack of optimization in the pgbench 
database, but I also created an additional index:

create index ind1 on pgbench_accounts(aid,bid);

test_db=# explain analyze select * from pgbench_accounts where (aid, 
bid) in ((2,1), (2,2), (2,3), (3,3));
                                                              QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on pgbench_accounts  (cost=17.73..33.66 rows=1 
width=97) (actual time=0.125..0.133 rows=1 loops=1)
    Recheck Cond: ((aid = 2) OR (aid = 2) OR (aid = 2) OR (aid = 3))
    Filter: (((aid = 2) AND (bid = 1)) OR ((aid = 2) AND (bid = 2)) OR 
((aid = 2) AND (bid = 3)) OR ((aid = 3) AND (bid = 3)))
    Rows Removed by Filter: 1
    Heap Blocks: exact=1
    ->  BitmapOr  (cost=17.73..17.73 rows=4 width=0) (actual 
time=0.100..0.102 rows=0 loops=1)
          ->  Bitmap Index Scan on pgbench_accounts_pkey 
(cost=0.00..4.43 rows=1 width=0) (actual time=0.036..0.037 rows=1 loops=1)
                Index Cond: (aid = 2)
          ->  Bitmap Index Scan on pgbench_accounts_pkey 
(cost=0.00..4.43 rows=1 width=0) (actual time=0.021..0.022 rows=1 loops=1)
                Index Cond: (aid = 2)
          ->  Bitmap Index Scan on pgbench_accounts_pkey 
(cost=0.00..4.43 rows=1 width=0) (actual time=0.021..0.021 rows=1 loops=1)
                Index Cond: (aid = 2)
          ->  Bitmap Index Scan on pgbench_accounts_pkey 
(cost=0.00..4.43 rows=1 width=0) (actual time=0.019..0.020 rows=1 loops=1)
                Index Cond: (aid = 3)
  Planning Time: 0.625 ms
  Execution Time: 0.227 ms
(16 rows)

I think such optimization would be useful here: aid =2 and bid in (1,2) 
or (aid,bid)=((3,3))

> Note that we currently won't use RowCompareExpr in many simple cases
> where the row constructor syntax has been used. For example, a query
> like this:
>
> select * from multi_test where ( a, b ) = (( 2, 1 ));
>
> This case already involves a transformation that is roughly comparable
> to the one you're working on now. We'll remove the RowCompareExpr
> during parsing. It'll be as if my example row constructor equality
> query was written this way instead:
>
> select * from multi_test where a = 2 and b = 1;
>
> This can be surprisingly important, when combined with other things,
> in more realistic examples.
>
> The nbtree code has special knowledge of RowCompareExpr that makes the
> rules for comparing index tuples different to those from other kinds
> of index scans. However, due to the RowCompareExpr transformation
> process I just described, we don't need to rely on that specialized
> nbtree code when the row constructor syntax is used with a simple
> equality clause -- which is what makes the normalization process have
> real value. If the nbtree code cannot see RowCompareExpr index quals
> then it cannot have this problem in the first place. In general it is
> useful to "normalize to conjunctive normal form" when it might allow
> scan key preprocessing in the nbtree code to come up with a much
> faster approach to executing the scan.
>
> It's easier to understand what I mean by showing a simple example. The
> nbtree preprocessing code is smart enough to recognize that the
> following query doesn't really need to do any work, due to having
> quals that it recognizes as contradictory (it can set so->qual_okay to
> false for unsatisfiable quals):
>
> select * from multi_test where ( a, b ) = (( 2, 1 )) and a = -1;
>
> However, it is not smart enough to perform the same trick if we change
> one small detail with the query:
>
> select * from multi_test where ( a, b ) >= (( 2, 1 )) and a = -1;

Yes, I have run the examples and I see it.

((ROW(aid, bid) >= ROW(2, 1)) AND (aid = '-1'::integer))

As I see it, we can implement such a transformation:

'( a, b ) >= (( 2, 1 )) and a = -1'     ->    'aid >= 2 and bid >= 1 and 
aid =-1'

It seems to me the most difficult thing is to notice problematic cases 
where the transformations are incorrect, but I think it can be implemented.

> Ideally, the optimizer would canonicalize/normalize everything in a
> way that made all of the nbtree preprocessing optimizations work just
> as well, without introducing any new special cases. Obviously, there
> is no reason why we can't perform the same trick with the second
> variant. (Note also that the nbtree preprocessing code can be smart
> about redundant quals, not just contradictory quals, so it matters
> more than it may appear from this simple, unrealistic example of
> mine.)
I agree with your position, but I still don't understand how to consider 
transformations to generalized cases without relying on special cases.

As I understand it, you assume that it is possible to apply 
transformations at the index creation stage, but there I came across the 
selectivity overestimation problem.

I still haven't found a solution for this problem.

> While these similar RowCompareExpr transformations are at least
> somewhat important, that's not really why I bring them up now. I am
> pointing them out now because I think that it might help you to
> develop a more complete mental model of these transformations.
> Ideally, your initial approach will generalize to other situations
> later on. So it's worth considering the relationship between this
> existing RowCompareExpr transformation, and the one that you're
> working on currently. Plus other, future transformations.
I will consider my case more broadly, but for this I will need some 
research work.
> This example might also give you some appreciation of why my SAOP
> patch is confused about when we need to do normalization/safety
> checks. Some things seem necessary when generating index paths in the
> optimizer. Other things seem necessary during preprocessing, in the
> nbtree code, at the start of the index scan. Unfortunately, it's not
> obvious to me where the right place is to deal with each aspect of
> setting up multi-column SAOP index quals. My mental model is very
> incomplete.
To be honest, I think that in your examples I understand better what you 
mean by normalization to the conjunctive norm, because I only had a 
theoretical idea from the logic course.

Hence, yes, normalization/security checks - now I understand why they 
are necessary.

-- 
Regards,
Alena Rybakina
Postgres Professional
From 36f731a4c7cda1581427b04ddb016c0bc961935a Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email-masked]>
Date: Wed, 2 Aug 2023 15:44:26 +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.

---
 src/backend/parser/parse_expr.c               | 230 +++++++++++++++++-
 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            |  67 +++++
 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                 |  19 ++
 src/test/regress/sql/partition_prune.sql      |  22 ++
 src/test/regress/sql/tidscan.sql              |   6 +
 src/tools/pgindent/typedefs.list              |   1 +
 13 files changed, 700 insertions(+), 2 deletions(-)

diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index fed8e4d0897..56e7503445a 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -43,6 +43,7 @@
 
 /* GUC parameters */
 bool		Transform_null_equals = false;
+int			or_transform_limit = 500;

 static Node *transformExprRecurse(ParseState *pstate, Node *expr);
@@ -99,6 +100,233 @@ 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;
+	Expr 		   *expr;
+} OrClauseGroupEntry;
+
+static Node *
+transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
+{
+	List		   *or_list = NIL;
+	List		   *groups_list = NIL;
+	ListCell	   *lc;
+
+	/* If this is not an 'OR' expression, skip the transformation */
+	if (expr_orig->boolop != OR_EXPR ||
+		list_length(expr_orig->args) < or_transform_limit)
+		return transformBoolExpr(pstate, (BoolExpr *) expr_orig);
+
+	foreach(lc, expr_orig->args)
+	{
+		Node			   *arg = lfirst(lc);
+		Node			   *orqual;
+		Node			   *const_expr;
+		Node			   *nconst_expr;
+		ListCell		   *lc_groups;
+		OrClauseGroupEntry *gentry;
+
+		/* 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) || IsA(get_leftop(orqual), Param))
+		{
+			nconst_expr = get_rightop(orqual);
+			const_expr = get_leftop(orqual);
+		}
+		else if (IsA(get_rightop(orqual), Const) || IsA(get_rightop(orqual), Param))
+		{
+			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.
+		*/
+		foreach(lc_groups, groups_list)
+		{
+			OrClauseGroupEntry *v = (OrClauseGroupEntry *) lfirst(lc_groups);
+
+			Assert(v->node != NULL);
+
+			if (equal(v->node, nconst_expr))
+			{
+				v->consts = lappend(v->consts, const_expr);
+				nconst_expr = NULL;
+				break;
+			}
+		}
+
+		if (nconst_expr == NULL)
+			/*
+				* The clause classified successfully and added into existed
+				* clause group.
+				*/
+			continue;
+
+		/* New clause group needed */
+		gentry = palloc(sizeof(OrClauseGroupEntry));
+		gentry->node = nconst_expr;
+		gentry->consts = list_make1(const_expr);
+		gentry->expr = (Expr *) orqual;
+		groups_list = lappend(groups_list,  (void *) gentry);
+	}
+
+	if (groups_list == NIL)
+	{
+		/*
+		* No any transformations possible with this list of arguments. Here we
+		* already made all underlying transformations. Thus, just return the
+		* transformed bool expression.
+		*/
+		return (Node *) makeBoolExpr(OR_EXPR, or_list, expr_orig->location);
+	}
+	else
+	{
+		ListCell	   *lc_args;
+
+		/* 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
+		*/
+
+		foreach(lc_args, groups_list)
+		{
+			OrClauseGroupEntry *gentry = (OrClauseGroupEntry *) lfirst(lc_args);
+			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);
+				continue;
+			}
+		}
+
+		list_free_deep(groups_list);
+	}
+
+	/* 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 +440,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 f9dba43b8c0..ddc27e2277c 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -2040,6 +2040,16 @@ struct config_int ConfigureNamesInt[] =
 		100, 1, MAX_STATISTICS_TARGET,
 		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,
+		500, 0, INT_MAX,
+		NULL, NULL, NULL
+	},
 	{
 		{"from_collapse_limit", PGC_USERSET, QUERY_TUNING_OTHER,
 			gettext_noop("Sets the FROM-list size beyond which subqueries "
diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h
index 7d38ca75f7b..891e6a462b9 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 int 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..cc229d4dcaf 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 = 0;
+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 9b8638f286a..b492ef1654f 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4207,6 +4207,73 @@ select * from tenk1 a join tenk1 b on
                            Index Cond: (unique2 = 7)
 (19 rows)
 
+SET or_transform_limit = 0;
+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)
+
+SELECT FORMAT('prepare prep1 %s AS SELECT * FROM tenk1 t WHERE %s',
+ '(' || string_agg('int', ',') || ')',
+ string_agg(FORMAT('t.unique1 = $%s', g.id), ' or ')
+ ) AS cmd
+ FROM generate_series(1, 10) AS g(id) \gexec
+prepare prep1 (int,int,int,int,int,int,int,int,int,int) AS SELECT * FROM tenk1 t WHERE t.unique1 = $1 or t.unique1 = $2 or t.unique1 = $3 or t.unique1 = $4 or t.unique1 = $5 or t.unique1 = $6 or t.unique1 = $7 or t.unique1 = $8 or t.unique1 = $9 or t.unique1 = $10
+SELECT FORMAT('explain (costs off) execute prep1 %s;', '(' || string_agg(g.id::text, ',') || ')') AS cmd
+ FROM generate_series(1, 10) AS g(id) \gexec
+explain (costs off) execute prep1 (1,2,3,4,5,6,7,8,9,10);
+                                QUERY PLAN                                 
+---------------------------------------------------------------------------
+ Bitmap Heap Scan on tenk1 t
+   Recheck Cond: (unique1 = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))
+   ->  Bitmap Index Scan on tenk1_unique1
+         Index Cond: (unique1 = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))
+(4 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 1eb347503aa..d1c5ce8be09 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 = 0;
+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 = 0;
+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..a2949d3d699 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 = 0;
+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..9c6baace0e2 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 = 0;
+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 3e5032b04dd..b6cc4644518 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1396,6 +1396,25 @@ 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 = 0;
+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);
+
+SELECT FORMAT('prepare prep1 %s AS SELECT * FROM tenk1 t WHERE %s',
+ '(' || string_agg('int', ',') || ')',
+ string_agg(FORMAT('t.unique1 = $%s', g.id), ' or ')
+ ) AS cmd
+ FROM generate_series(1, 10) AS g(id) \gexec
+SELECT FORMAT('explain (costs off) execute prep1 %s;', '(' || string_agg(g.id::text, ',') || ')') AS cmd
+ FROM generate_series(1, 10) AS g(id) \gexec
+
+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 d1c60b8fe9d..77f3e6c3b9b 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 = 0;
+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 = 0;
+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..634bf08e5fc 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 = 0;
+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 66823bc2a77..8bee8d85e25 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1633,6 +1633,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 1490aa07246..56e7503445a 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -149,12 +149,12 @@ transformBoolExprOr(ParseState *pstate, BoolExpr *expr_orig)
 		 * an OpExpr.
 		 * Get pointers to constant and expression sides of the qual.
 		 */
-		if (IsA(get_leftop(orqual), Const))
+		if (IsA(get_leftop(orqual), Const) || IsA(get_leftop(orqual), Param))
 		{
 			nconst_expr = get_rightop(orqual);
 			const_expr = get_leftop(orqual);
 		}
-		else if (IsA(get_rightop(orqual), Const))
+		else if (IsA(get_rightop(orqual), Const) || IsA(get_rightop(orqual), Param))
 		{
 			const_expr = get_rightop(orqual);
 			nconst_expr = get_leftop(orqual);
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 2314d92a6d4..b492ef1654f 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4256,6 +4256,23 @@ select * from tenk1 a join tenk1 b on
                            Index Cond: (unique1 = 3)
 (15 rows)
 
+SELECT FORMAT('prepare prep1 %s AS SELECT * FROM tenk1 t WHERE %s',
+ '(' || string_agg('int', ',') || ')',
+ string_agg(FORMAT('t.unique1 = $%s', g.id), ' or ')
+ ) AS cmd
+ FROM generate_series(1, 10) AS g(id) \gexec
+prepare prep1 (int,int,int,int,int,int,int,int,int,int) AS SELECT * FROM tenk1 t WHERE t.unique1 = $1 or t.unique1 = $2 or t.unique1 = $3 or t.unique1 = $4 or t.unique1 = $5 or t.unique1 = $6 or t.unique1 = $7 or t.unique1 = $8 or t.unique1 = $9 or t.unique1 = $10
+SELECT FORMAT('explain (costs off) execute prep1 %s;', '(' || string_agg(g.id::text, ',') || ')') AS cmd
+ FROM generate_series(1, 10) AS g(id) \gexec
+explain (costs off) execute prep1 (1,2,3,4,5,6,7,8,9,10);
+                                QUERY PLAN                                 
+---------------------------------------------------------------------------
+ Bitmap Heap Scan on tenk1 t
+   Recheck Cond: (unique1 = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))
+   ->  Bitmap Index Scan on tenk1_unique1
+         Index Cond: (unique1 = ANY ('{1,2,3,4,5,6,7,8,9,10}'::integer[]))
+(4 rows)
+
 RESET or_transform_limit;
 --
 -- test placement of movable quals in a parameterized join tree
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index d4d7d853a4a..b6cc4644518 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1405,6 +1405,15 @@ 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);
+
+SELECT FORMAT('prepare prep1 %s AS SELECT * FROM tenk1 t WHERE %s',
+ '(' || string_agg('int', ',') || ')',
+ string_agg(FORMAT('t.unique1 = $%s', g.id), ' or ')
+ ) AS cmd
+ FROM generate_series(1, 10) AS g(id) \gexec
+SELECT FORMAT('explain (costs off) execute prep1 %s;', '(' || string_agg(g.id::text, ',') || ')') AS cmd
+ FROM generate_series(1, 10) AS g(id) \gexec
+
 RESET or_transform_limit;
 
 --

---

From: Peter Geoghegan <[email-masked]>
Date: Wed, 2 Aug 2023 11:58:11 -0700

> Although I think such a case will be used less often.

Right. As I said, I don't particularly care about the row constructor
syntax -- it's not essential.

In my experience patches like this one that ultimately don't succeed
usually *don't* have specific problems that cannot be fixed. The real
problem tends to be ambiguity about the general high level design. So
more than anything else, ambiguity is the thing that you need to
minimize to be successful here. This is the #1 practical problem, by
far. This may be the only thing about your patch that I feel 100% sure
of.

In my experience it can actually be easier to expand the scope of a
project, and to come up with a more general solution:

https://en.wikipedia.org/wiki/Inventor%27s_paradox

I'm not trying to make your work more difficult by expanding its
scope. I'm actually trying to make your work *easier* by expanding its
scope. I don't claim to know what the specific scope of your patch
should be at all. Just that it might be possible to get a much clearer
picture of what the ideal scope really is by *trying* to generalize it
further -- that understanding is what we lack right now. Even if this
exercise fails in some way, it won't really have been a failure. The
reasons why it fails will still be interesting and practically
relevant.

> where the transformations are incorrect, but I think it can be implemented.

Right. To be clear, I am sure that it won't be practical to come up
with a 100% theoretically pure approach. If for no other reason than
this: normalizing to CNF in all cases will run into problems with very
complex predicates. It might even be computationally intractable
(could just be very slow). So there is a clear need to keep
theoretical purity in balance with practical considerations. There is
a need for some kind of negotiation between those two things. Probably
some set of heuristics will ultimately be required to keep costs and
benefits in balance.

> transformations to generalized cases without relying on special cases.

Me neither. I wish I could say something a bit less vague here.

I don't expect you to determine what set of heuristics will ultimately
be required to determine when and how to perform CNF conversions, in
the general case. But having at least some vague idea seems like it
might increase confidence in your design.

> I still haven't found a solution for this problem.

Do you think that this problem is just an accidental side-effect? It
isn't necessarily the responsibility of your patch to fix such things.
If it's even possible for selectivity estimates to change, then it's
already certain that sometimes they'll be worse than before -- if only
because of chance interactions. The optimizer is often right for the
wrong reasons, and wrong for the right reasons -- we cannot really
expect any patch to completely avoid problems like that.

> Hence, yes, normalization/security checks - now I understand why they

As I explained to Jim, I am trying to put things in this area on a
more rigorous footing. For example, I have said that the way that the
nbtree code executes SAOP quals is equivalent to DNF. That is
basically true, but it's also my own slightly optimistic
interpretation of history and of the design. That's a good start, but
it's not enough on its own.

My interpretation might still be wrong in some subtle way, that I have
yet to discover. That's really what I'm concerned about with your
patch, too. I'm currently trying to solve a problem that I don't yet
fully understand, so for me "getting a properly working flow of
information" seems like a good practical exercise. I'm trying to
generalize the design of my own patch as far as I can, to see what
breaks, and why it breaks. My intuition is that this will help me with
my own patch by forcing me to gain a truly rigorous understanding of
the problem.

My suggestion about generalizing your approach to cover RowCompareExpr
cases is what I would do, if I were you, and this was my patch. That's
almost exactly what I'm doing with my own patch already, in fact.

--
Peter Geoghegan

---

From: Alena Rybakina <[email-masked]>
Date: Thu, 3 Aug 2023 22:47:44 +0300

> almost exactly what I'm doing with my own patch already, in fact.
It's all right. I understand your position)

I also agree to try to find other optimization cases and generalize them.

I read the wiki article, and as I understand it, in such a situation we 
see a difficult problem with finding expressions that need to be 
converted into a logically correct expression and simplify execution for 
the executor. For example, this is a ROW type. It can have a simpler 
expression with AND and OR operations, besides we can exclude 
duplicates. But some of these transformations may be incorrect or they 
will have a more complex representation. We can try to find the 
problematic expressions and try to combine them into groups and finally 
find a solutions for each groups or, conversely, discover that the 
existing transformation is uncorrected. If I understand correctly, we 
should first start searching for "ROW" expressions (define a group for 
them) and think about a solution for the group.
> might increase confidence in your design.
I agree, but I think this will be the second step after solutions are 
found.
> expect any patch to completely avoid problems like that.
To be honest, I tried to fix it many times by calling the function to 
calculate selectivity, and each time the result of the estimate did not 
change. I didn't have any problems in this part after moving the 
transformation to the parsing stage. I even tried to perform this 
transformation at the planning stage (to the preprocess_qual_conditions 
function), but I ran into the same problem there as well.

To tell the truth, I think I'm ready to investigate this problem again 
(maybe I'll be able to see it differently or really find that I missed 
something in previous times).

-- 
Regards,
Alena Rybakina
Postgres Professional

---

From: Peter Geoghegan <[email-masked]>
Date: Sat, 5 Aug 2023 19:01:14 -0700

> It's all right. I understand your position)

Okay, good to know.  :-)

> I also agree to try to find other optimization cases and generalize them.

Good idea. Since the real goal is to "get a working flow of
information", the practical value of trying to get it working with
other clauses seems to be of secondary importance.

> something in previous times).

The optimizer will itself do a limited form of "normalizing to CNF".
Are you familiar with extract_restriction_or_clauses(), from
orclauses.c? Comments above the function have an example of how this
can work:

 * Although a join clause must reference multiple relations overall,
 * an OR of ANDs clause might contain sub-clauses that reference just one
 * relation and can be used to build a restriction clause for that rel.
 * For example consider
 *      WHERE ((a.x = 42 AND b.y = 43) OR (a.x = 44 AND b.z = 45));
 * We can transform this into
 *      WHERE ((a.x = 42 AND b.y = 43) OR (a.x = 44 AND b.z = 45))
 *          AND (a.x = 42 OR a.x = 44)
 *          AND (b.y = 43 OR b.z = 45);
 * which allows the latter clauses to be applied during the scans of a and b,
 * perhaps as index qualifications, and in any case reducing the number of
 * rows arriving at the join.  In essence this is a partial transformation to
 * CNF (AND of ORs format).  It is not complete, however, because we do not
 * unravel the original OR --- doing so would usually bloat the qualification
 * expression to little gain.

Of course this immediately makes me wonder: shouldn't your patch be
able to perform an additional transformation here? You know, by
transforming "a.x = 42 OR a.x = 44" into "a IN (42, 44)"? Although I
haven't checked for myself, I assume that this doesn't happen right
now, since your patch currently performs all of its transformations
during parsing.

I also noticed that the same comment block goes on to say something
about "clauselist_selectivity's inability to recognize redundant
conditions". Perhaps that is relevant to the problems you were having
with selectivity estimation, back when the code was in
preprocess_qual_conditions() instead? I have no reason to believe that
there should be any redundancy left behind by your transformation, so
this is just one possibility to consider.

Separately, the commit message of commit 25a9e54d2d says something
about how the planner builds RestrictInfos, which seems
possibly-relevant. That commit enhanced extended statistics for OR
clauses, so the relevant paragraph describes a limitation of extended
statistics with OR clauses specifically. I'm just guessing, but it
still seems like it might be relevant to the problem you ran into with
selectivity estimation. Another possibility to consider.

BTW, I sometimes use RR to help improve my understanding of the planner:

https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD#Recording_Postgres_using_rr_Record_and_Replay_Framework

The planner has particularly complicated control flow, which has
unique challenges -- just knowing where to begin can be difficult
(unlike most other areas). I find that setting watchpoints to see when
and where the planner modifies state using RR is far more useful than
it would be with regular GDB. Once I record a query, I find that I can
"map out" what happens in the planner relatively easily.

-- 
Peter Geoghegan

---

From: Peter Geoghegan <[email-masked]>
Date: Sun, 6 Aug 2023 14:43:08 -0700

> now, since your patch currently performs all of its transformations

Many interesting cases won't get SAOP transformation from the patch,
simply because of the or_transform_limit GUC's default of 500. I don't
think that that design makes too much sense. It made more sense back
when the focus was on expression evaluation overhead. But that's only
one of the benefits that we now expect from the patch, right? So it
seems like something that should be revisited soon.

I'm not suggesting that there is no need for some kind of limit. But
it seems like a set of heuristics might be a better approach. Although
I would like to get a better sense of the costs of the transformation
to be able to say too much more.

-- 
Peter Geoghegan

---

From: Alena Rybakina <[email-masked]>
Date: Wed, 9 Aug 2023 14:33:31 +0300

Hi! Thank you for your research, I'm sure it will help me to fix the 
problem of calculating selectivity faster)
I'm sorry I didn't answer right away, to be honest, I had a full diary 
of urgent matters at work. For this reason, I didn't have enough time to 
work on this patch properly.

>   * expression to little gain.
This is an interesting feature. I didn't notice this function before, I 
studied many times consider_new_or_cause, which were called there. As 
far as I know, there is a selectivity calculation going on there, but as 
far as I remember, I called it earlier after my conversion, and 
unfortunately it didn't solve my problem with calculating selectivity. 
I'll reconsider it again, maybe I can find something I missed.
> selectivity estimation. Another possibility to consider.

I understood what is said about AND clauses in this comment. It seems to 
me that AND clauses saved like (BoolExpr *) expr->args->(RestrictInfo *) 
clauseA->(RestrictInfo *)clauseB lists and OR clauses saved like 
(BoolExpr *) expr -> orclause->(RestrictInfo *)clause A->(RestrictInfo 
*)clause B.

As I understand it, selectivity is calculated for each expression. But 
I'll exploring it deeper, because I think this place may contain the 
answer to the question, what's wrong with selectivity calculation in my 
patch.

> "map out" what happens in the planner relatively easily.
Thank you for sharing this source! I didn't know about this before, and 
it will definitely make my life easier to understand the optimizer.

I understand what you mean, and I researched the optimizer in a similar 
way through gdb and looked at the comments and code in postgresql. This 
is a complicated way and I didn't always understand correctly what this 
variable was doing in this place, and this created some difficulties for me.

So, thank you for the link!

> to be able to say too much more.

Yes, this may be revised in the future after some transformations. 
Initially, I was solving the problem described here [0]. So, after 
testing [1], I come to the conclusion that 500 is the ideal value for 
or_transform_limit.

[0] 
https://www.postgresql.org/message-id/919bfbcb-f812-758d-d687-71f89f0d9a68%40postgrespro.ru

[1] 
https://www.postgresql.org/message-id/6b97b517-f36a-f0c6-3b3a-0cf8cfba220c%40yandex.ru

-- 
Regards,
Alena Rybakina
Postgres Professional

---

From: "a.rybakina" <[email-masked]>
Date: Thu, 17 Aug 2023 13:08:03 +0300

Hi, all!
> This is an interesting feature. I didn't notice this function before, 
> I studied many times consider_new_or_cause, which were called there. 
> As far as I know, there is a selectivity calculation going on there, 
> but as far as I remember, I called it earlier after my conversion, and 
> I understood what is said about AND clauses in this comment. It seems 
> to me that AND clauses saved like (BoolExpr *) 
> expr->args->(RestrictInfo *) clauseA->(RestrictInfo *)clauseB lists 
> and OR clauses saved like (BoolExpr *) expr -> orclause->(RestrictInfo 
> *)clause A->(RestrictInfo *)clause B.
>
> answer to the question, what's wrong with selectivity calculation in 
> my patch.

I could move transformation in there (extract_restriction_or_clauses) 
and didn't have any problem with selectivity calculation, besides it 
also works on the redundant or duplicates stage. So, it looks like:

CREATE TABLE tenk1 (unique1 int, unique2 int, ten int, hundred int); 
insert into tenk1 SELECT x,x,x,x FROM generate_series(1,50000) as x; 
CREATE INDEX a_idx1 ON tenk1(unique1); CREATE INDEX a_idx2 ON 
tenk1(unique2); CREATE INDEX a_hundred ON tenk1(hundred);

explain analyze select * from tenk1 a join tenk1 b on ((a.unique2 = 3 or 
a.unique2 = 7));

PLAN 
------------------------------------------------------------------------------------------------------------------------------ 
Nested Loop (cost=0.29..2033.62 rows=100000 width=32) (actual 
time=0.090..60.258 rows=100000 loops=1) -> Seq Scan on tenk1 b 
(cost=0.00..771.00 rows=50000 width=16) (actual time=0.016..9.747 
rows=50000 loops=1) -> Materialize (cost=0.29..12.62 rows=2 width=16) 
(actual time=0.000..0.000 rows=2 loops=50000) -> Index Scan using a_idx2 
on tenk1 a (cost=0.29..12.62 rows=2 width=16) (actual time=0.063..0.068 
rows=2 loops=1) Index Cond: (unique2 = ANY (ARRAY[3, 7])) Planning Time: 
8.257 ms Execution Time: 64.453 ms (7 rows)

Overall, this was due to incorrectly defined types of elements in the 
array, and if we had applied the transformation with the definition of 
the tup operator, we could have avoided such problems (I used 
make_scalar_array_op and have not yet found an alternative to this).

When I moved the transformation on the index creation stage, it couldn't 
work properly and as a result I faced the same problem of selectivity 
calculation. I supposed that the selectivity values are also used there, 
and not recalculated all over again. perhaps we can solve this by 
forcibly recalculating the selectivity values, but I foresee other 
problems there.

explain analyze select * from tenk1 a join tenk1 b on ((a.unique2 = 3 or 
a.unique2 = 7));

QUERY PLAN 
----------------------------------------------------------------------------------------------------------------------------------- 
Nested Loop (cost=12.58..312942.91 rows=24950000 width=32) (actual 
time=0.040..47.582 rows=100000 loops=1) -> Seq Scan on tenk1 b 
(cost=0.00..771.00 rows=50000 width=16) (actual time=0.009..7.039 
rows=50000 loops=1) -> Materialize (cost=12.58..298.16 rows=499 
width=16) (actual time=0.000..0.000 rows=2 loops=50000) -> Bitmap Heap 
Scan on tenk1 a (cost=12.58..295.66 rows=499 width=16) (actual 
time=0.025..0.028 rows=2 loops=1) Recheck Cond: ((unique2 = 3) OR 
(unique2 = 7)) Heap Blocks: exact=1 -> BitmapOr (cost=12.58..12.58 
rows=500 width=0) (actual time=0.023..0.024 rows=0 loops=1) -> Bitmap 
Index Scan on a_idx2 (cost=0.00..6.17 rows=250 width=0) (actual 
time=0.019..0.019 rows=1 loops=1) Index Cond: (unique2 = 3) -> Bitmap 
Index Scan on a_idx2 (cost=0.00..6.17 rows=250 width=0) (actual 
time=0.003..0.003 rows=1 loops=1) Index Cond: (unique2 = 7) Planning 
Time: 0.401 ms Execution Time: 51.350 ms (13 rows)

I have attached a diff file so far, but it is very raw and did not pass 
all regression tests (I attached regression.diff) and even had bad 
conversion cases (some of the cases did not work at all, in other cases 
there were no non-converted nodes). But now I see an interesting 
transformation, which was the most interesting for me.

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 (ARRAY[1, 3, 42]))) +(2 rows)
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/opr_sanity.out /home/alena/postgrespro7/src/test/regress/results/opr_sanity.out
--- /home/alena/postgrespro7/src/test/regress/expected/opr_sanity.out	2023-08-12 02:03:45.284074834 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/opr_sanity.out	2023-08-17 00:00:22.793043910 +0300
@@ -47,10 +47,8 @@
 SELECT p1.oid, p1.proname
 FROM pg_proc as p1
 WHERE (prosrc = '' OR prosrc = '-') AND prosqlbody IS NULL;
- oid | proname 
------+---------
-(0 rows)
-
+ERROR:  could not determine which collation to use for string comparison
+HINT:  Use the COLLATE clause to set the collation explicitly.
 -- proretset should only be set for normal functions
 SELECT p1.oid, p1.proname
 FROM pg_proc AS p1
@@ -81,10 +79,8 @@
 SELECT p1.oid, p1.proname
 FROM pg_proc as p1
 WHERE prolang = 13 AND (probin IS NULL OR probin = '' OR probin = '-');
- oid | proname 
------+---------
-(0 rows)
-
+ERROR:  could not determine which collation to use for string comparison
+HINT:  Use the COLLATE clause to set the collation explicitly.
 SELECT p1.oid, p1.proname
 FROM pg_proc as p1
 WHERE prolang != 13 AND probin IS NOT NULL;
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/create_index.out /home/alena/postgrespro7/src/test/regress/results/create_index.out
--- /home/alena/postgrespro7/src/test/regress/expected/create_index.out	2023-08-12 02:03:45.260074298 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/create_index.out	2023-08-17 00:00:25.309060540 +0300
@@ -1838,18 +1838,11 @@
 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 (ARRAY[1, 3, 42])))
+(2 rows)
 
 SELECT * FROM tenk1
   WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -1861,20 +1854,17 @@
 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 (ARRAY[42, 99])))
          ->  BitmapAnd
                ->  Bitmap Index Scan on tenk1_hundred
                      Index Cond: (hundred = 42)
-               ->  BitmapOr
-                     ->  Bitmap Index Scan on tenk1_thous_tenthous
-                           Index Cond: (thousand = 42)
-                     ->  Bitmap Index Scan on tenk1_thous_tenthous
-                           Index Cond: (thousand = 99)
-(11 rows)
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: (thousand = ANY (ARRAY[42, 99]))
+(8 rows)
 
 SELECT count(*) FROM tenk1
   WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/inherit.out /home/alena/postgrespro7/src/test/regress/results/inherit.out
--- /home/alena/postgrespro7/src/test/regress/expected/inherit.out	2023-08-12 02:03:29.411719453 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/inherit.out	2023-08-17 00:00:26.781070266 +0300
@@ -1929,7 +1929,7 @@
                                    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 -U3 /home/alena/postgrespro7/src/test/regress/expected/misc.out /home/alena/postgrespro7/src/test/regress/results/misc.out
--- /home/alena/postgrespro7/src/test/regress/expected/misc.out	2023-08-12 02:03:29.439720081 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/misc.out	2023-08-17 00:00:37.729142525 +0300
@@ -99,10 +99,14 @@
    SELECT 'posthacking', p.name
    FROM person* p
    WHERE p.name = 'mike' or p.name = 'jeff';
+ERROR:  could not determine which collation to use for string comparison
+HINT:  Use the COLLATE clause to set the collation explicitly.
 INSERT INTO hobbies_r (name, person)
    SELECT 'basketball', p.name
    FROM person p
    WHERE p.name = 'joe' or p.name = 'sally';
+ERROR:  could not determine which collation to use for string comparison
+HINT:  Use the COLLATE clause to set the collation explicitly.
 INSERT INTO hobbies_r (name) VALUES ('skywalking');
 INSERT INTO equipment_r (name, hobby) VALUES ('advil', 'posthacking');
 INSERT INTO equipment_r (name, hobby) VALUES ('peet''s coffee', 'posthacking');
@@ -163,24 +167,17 @@
 -- everyone else does nothing.
 --
 SELECT p.name, name(p.hobbies) FROM ONLY person p;
- name  |    name     
--------+-------------
- mike  | posthacking
- joe   | basketball
- sally | basketball
-(3 rows)
+ name | name 
+------+------
+(0 rows)
 
 --
 -- as above, but jeff also does post_hacking.
 --
 SELECT p.name, name(p.hobbies) FROM person* p;
- name  |    name     
--------+-------------
- mike  | posthacking
- joe   | basketball
- sally | basketball
- jeff  | posthacking
-(4 rows)
+ name | name 
+------+------
+(0 rows)
 
 --
 -- the next two queries demonstrate how functions generate bogus duplicates.
@@ -188,25 +185,16 @@
 --
 SELECT DISTINCT hobbies_r.name, name(hobbies_r.equipment) FROM hobbies_r
   ORDER BY 1,2;
-    name     |     name      
--------------+---------------
- basketball  | hightops
- posthacking | advil
- posthacking | peet's coffee
- skywalking  | guts
-(4 rows)
+    name    | name 
+------------+------
+ skywalking | guts
+(1 row)
 
 SELECT hobbies_r.name, (hobbies_r.equipment).name FROM hobbies_r;
-    name     |     name      
--------------+---------------
- posthacking | advil
- posthacking | peet's coffee
- posthacking | advil
- posthacking | peet's coffee
- basketball  | hightops
- basketball  | hightops
- skywalking  | guts
-(7 rows)
+    name    | name 
+------------+------
+ skywalking | guts
+(1 row)
 
 --
 -- mike needs advil and peet's coffee,
@@ -214,71 +202,41 @@
 -- everyone else is fine.
 --
 SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM ONLY person p;
- name  |    name     |     name      
--------+-------------+---------------
- mike  | posthacking | advil
- mike  | posthacking | peet's coffee
- joe   | basketball  | hightops
- sally | basketball  | hightops
-(4 rows)
+ name | name | name 
+------+------+------
+(0 rows)
 
 --
 -- as above, but jeff needs advil and peet's coffee as well.
 --
 SELECT p.name, name(p.hobbies), name(equipment(p.hobbies)) FROM person* p;
- name  |    name     |     name      
--------+-------------+---------------
- mike  | posthacking | advil
- mike  | posthacking | peet's coffee
- joe   | basketball  | hightops
- sally | basketball  | hightops
- jeff  | posthacking | advil
- jeff  | posthacking | peet's coffee
-(6 rows)
+ name | name | name 
+------+------+------
+(0 rows)
 
 --
 -- just like the last two, but make sure that the target list fixup and
 -- unflattening is being done correctly.
 --
 SELECT name(equipment(p.hobbies)), p.name, name(p.hobbies) FROM ONLY person p;
-     name      | name  |    name     
----------------+-------+-------------
- advil         | mike  | posthacking
- peet's coffee | mike  | posthacking
- hightops      | joe   | basketball
- hightops      | sally | basketball
-(4 rows)
+ name | name | name 
+------+------+------
+(0 rows)
 
 SELECT (p.hobbies).equipment.name, p.name, name(p.hobbies) FROM person* p;
-     name      | name  |    name     
----------------+-------+-------------
- advil         | mike  | posthacking
- peet's coffee | mike  | posthacking
- hightops      | joe   | basketball
- hightops      | sally | basketball
- advil         | jeff  | posthacking
- peet's coffee | jeff  | posthacking
-(6 rows)
+ name | name | name 
+------+------+------
+(0 rows)
 
 SELECT (p.hobbies).equipment.name, name(p.hobbies), p.name FROM ONLY person p;
-     name      |    name     | name  
----------------+-------------+-------
- advil         | posthacking | mike
- peet's coffee | posthacking | mike
- hightops      | basketball  | joe
- hightops      | basketball  | sally
-(4 rows)
+ name | name | name 
+------+------+------
+(0 rows)
 
 SELECT name(equipment(p.hobbies)), name(p.hobbies), p.name FROM person* p;
-     name      |    name     | name  
----------------+-------------+-------
- advil         | posthacking | mike
- peet's coffee | posthacking | mike
- hightops      | basketball  | joe
- hightops      | basketball  | sally
- advil         | posthacking | jeff
- peet's coffee | posthacking | jeff
-(6 rows)
+ name | name | name 
+------+------+------
+(0 rows)
 
 SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
  name 
@@ -334,7 +292,7 @@
 SELECT hobbies_by_name('basketball');
  hobbies_by_name 
 -----------------
- joe
+ 
 (1 row)
 
 SELECT name, overpaid(emp.*) FROM emp;
@@ -364,28 +322,16 @@
 (1 row)
 
 SELECT *, name(equipment(h.*)) FROM hobbies_r h;
-    name     | person |     name      
--------------+--------+---------------
- posthacking | mike   | advil
- posthacking | mike   | peet's coffee
- posthacking | jeff   | advil
- posthacking | jeff   | peet's coffee
- basketball  | joe    | hightops
- basketball  | sally  | hightops
- skywalking  |        | guts
-(7 rows)
+    name    | person | name 
+------------+--------+------
+ skywalking |        | guts
+(1 row)
 
 SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
-    name     | person |     name      
--------------+--------+---------------
- posthacking | mike   | advil
- posthacking | mike   | peet's coffee
- posthacking | jeff   | advil
- posthacking | jeff   | peet's coffee
- basketball  | joe    | hightops
- basketball  | sally  | hightops
- skywalking  |        | guts
-(7 rows)
+    name    | person | name 
+------------+--------+------
+ skywalking |        | guts
+(1 row)
 
 --
 -- functional joins
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/tidscan.out /home/alena/postgrespro7/src/test/regress/results/tidscan.out
--- /home/alena/postgrespro7/src/test/regress/expected/tidscan.out	2023-08-12 02:03:29.511721694 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/tidscan.out	2023-08-17 00:00:37.521141153 +0300
@@ -46,7 +46,7 @@
                           QUERY PLAN                          
 --------------------------------------------------------------
  Tid Scan on tidscan
-   TID Cond: ((ctid = '(0,2)'::tid) OR ('(0,1)'::tid = ctid))
+   TID Cond: (ctid = ANY (ARRAY['(0,2)'::tid, '(0,1)'::tid]))
 (2 rows)
 
 SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/stats_ext.out /home/alena/postgrespro7/src/test/regress/results/stats_ext.out
--- /home/alena/postgrespro7/src/test/regress/expected/stats_ext.out	2023-08-12 02:03:45.320075639 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/stats_ext.out	2023-08-17 00:00:41.165165174 +0300
@@ -1160,17 +1160,13 @@
 (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 
------------+--------
-         4 |    100
-(1 row)
-
+ERROR:  could not determine which collation to use for string comparison
+HINT:  Use the COLLATE clause to set the collation explicitly.
+CONTEXT:  PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
 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 
------------+--------
-         8 |    200
-(1 row)
-
+ERROR:  could not determine which collation to use for string comparison
+HINT:  Use the COLLATE clause to set the collation explicitly.
+CONTEXT:  PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
 -- OR clauses referencing different attributes
 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR b = ''1'') AND b = ''1''');
  estimated | actual 
@@ -1322,21 +1318,17 @@
 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
-(1 row)
-
+ERROR:  could not determine which collation to use for string comparison
+HINT:  Use the COLLATE clause to set the collation explicitly.
+CONTEXT:  PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
 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
-(1 row)
-
+ERROR:  could not determine which collation to use for string comparison
+HINT:  Use the COLLATE clause to set the collation explicitly.
+CONTEXT:  PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
 -- OR clauses referencing different attributes are incompatible
 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR b = ''1'') AND b = ''1''');
  estimated | actual 
@@ -1501,17 +1493,13 @@
 (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 
------------+--------
-         1 |    100
-(1 row)
-
+ERROR:  could not determine which collation to use for string comparison
+HINT:  Use the COLLATE clause to set the collation explicitly.
+CONTEXT:  PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
 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 
------------+--------
-         1 |    200
-(1 row)
-
+ERROR:  could not determine which collation to use for string comparison
+HINT:  Use the COLLATE clause to set the collation explicitly.
+CONTEXT:  PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
 -- OR clauses referencing different attributes
 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR upper(b) = ''1'') AND upper(b) = ''1''');
  estimated | actual 
@@ -1664,21 +1652,17 @@
 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
-(1 row)
-
+ERROR:  could not determine which collation to use for string comparison
+HINT:  Use the COLLATE clause to set the collation explicitly.
+CONTEXT:  PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
 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
-(1 row)
-
+ERROR:  could not determine which collation to use for string comparison
+HINT:  Use the COLLATE clause to set the collation explicitly.
+CONTEXT:  PL/pgSQL function check_estimated_rows(text) line 7 at FOR over EXECUTE statement
 -- OR clauses referencing different attributes
 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR upper(b) = ''1'') AND upper(b) = ''1''');
  estimated | actual 
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/partition_join.out /home/alena/postgrespro7/src/test/regress/results/partition_join.out
--- /home/alena/postgrespro7/src/test/regress/expected/partition_join.out	2023-08-12 02:03:45.288074924 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/partition_join.out	2023-08-17 00:00:51.173231068 +0300
@@ -290,13 +290,13 @@
 -- Currently we can't do partitioned join if nullable-side partitions are pruned
 EXPLAIN (COSTS OFF)
 SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1 WHERE a < 450) t1 FULL JOIN (SELECT * FROM prt2 WHERE b > 250) t2 ON t1.a = t2.b WHERE t1.b = 0 OR t2.a = 0 ORDER BY t1.a, t2.b;
-                     QUERY PLAN                     
-----------------------------------------------------
+                                     QUERY PLAN                                      
+-------------------------------------------------------------------------------------
  Sort
    Sort Key: prt1.a, prt2.b
    ->  Hash Full Join
          Hash Cond: (prt1.a = prt2.b)
-         Filter: ((prt1.b = 0) OR (prt2.a = 0))
+         Filter: (((prt1.b = 0) OR (prt2.a = 0)) AND ((prt1.b = 0) OR (prt2.a = 0)))
          ->  Append
                ->  Seq Scan on prt1_p1 prt1_1
                      Filter: (a < 450)
@@ -2270,10 +2270,11 @@
 where not exists (select 1 from prtx2
                   where prtx2.a=prtx1.a and (prtx2.b=prtx1.b+1 or prtx2.c=99))
   and a<20 and c=91;
-                           QUERY PLAN                            
------------------------------------------------------------------
+                                QUERY PLAN                                
+--------------------------------------------------------------------------
  Append
    ->  Nested Loop Anti Join
+         Join Filter: ((prtx2_1.b = (prtx1_1.b + 1)) OR (prtx2_1.c = 99))
          ->  Seq Scan on prtx1_1
                Filter: ((a < 20) AND (c = 91))
          ->  Bitmap Heap Scan on prtx2_1
@@ -2285,6 +2286,7 @@
                      ->  Bitmap Index Scan on prtx2_1_c_idx
                            Index Cond: (c = 99)
    ->  Nested Loop Anti Join
+         Join Filter: ((prtx2_2.b = (prtx1_2.b + 1)) OR (prtx2_2.c = 99))
          ->  Seq Scan on prtx1_2
                Filter: ((a < 20) AND (c = 91))
          ->  Bitmap Heap Scan on prtx2_2
@@ -2295,7 +2297,7 @@
                            Index Cond: (b = (prtx1_2.b + 1))
                      ->  Bitmap Index Scan on prtx2_2_c_idx
                            Index Cond: (c = 99)
-(23 rows)
+(25 rows)
 
 select * from prtx1
 where not exists (select 1 from prtx2
diff -U3 /home/alena/postgrespro7/src/test/regress/expected/partition_prune.out /home/alena/postgrespro7/src/test/regress/results/partition_prune.out
--- /home/alena/postgrespro7/src/test/regress/expected/partition_prune.out	2023-08-12 02:03:45.292075013 +0300
+++ /home/alena/postgrespro7/src/test/regress/results/partition_prune.out	2023-08-17 00:00:50.777228463 +0300
@@ -82,24 +82,38 @@
 (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))
-(5 rows)
+         Filter: (a = ANY ('{a,c}'::bpchar[]))
+   ->  Seq Scan on lp_ef lp_3
+         Filter: (a = ANY ('{a,c}'::bpchar[]))
+   ->  Seq Scan on lp_g lp_4
+         Filter: (a = ANY ('{a,c}'::bpchar[]))
+   ->  Seq Scan on lp_null lp_5
+         Filter: (a = ANY ('{a,c}'::bpchar[]))
+   ->  Seq Scan on lp_default lp_6
+         Filter: (a = ANY ('{a,c}'::bpchar[]))
+(13 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)))
-(5 rows)
+         Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
+   ->  Seq Scan on lp_ef lp_3
+         Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
+   ->  Seq Scan on lp_g lp_4
+         Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
+   ->  Seq Scan on lp_default lp_5
+         Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
+(11 rows)
 
@@ -515,11 +529,13 @@
 (27 rows)
 
 explain (costs off) select * from rlp where a = 1 or a = 7;
-           QUERY PLAN           
---------------------------------
- Seq Scan on rlp2 rlp
-   Filter: ((a = 1) OR (a = 7))
-(2 rows)
+                   QUERY PLAN                   
+------------------------------------------------
+ Append
+   Subplans Removed: 1
+   ->  Seq Scan on rlp2 rlp_1
+         Filter: (a = ANY ('{1,7}'::integer[]))
+(4 rows)
 
 explain (costs off) select * from rlp where a = 1 or b = 'ab';
                       QUERY PLAN                       
@@ -596,14 +612,15 @@
 
 -- where clause contradicts sub-partition's constraint
 explain (costs off) select * from rlp where a = 20 or a = 40;
-               QUERY PLAN               
-----------------------------------------
+                    QUERY PLAN                    
+--------------------------------------------------
  Append
+   Subplans Removed: 2
    ->  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))
-(5 rows)
+         Filter: (a = ANY ('{20,40}'::integer[]))
+(6 rows)
 
 explain (costs off) select * from rlp3 where a = 20;   /* empty */
         QUERY PLAN        
@@ -1933,10 +1950,10 @@
 
 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;
@@ -2265,11 +2282,11 @@
          Workers Launched: N
          ->  Parallel Append (actual rows=N loops=N)
                ->  Parallel Seq Scan on ab_a1_b2 ab_1 (actual rows=N loops=N)
-                     Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+                     Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
                ->  Parallel Seq Scan on ab_a2_b2 ab_2 (never executed)
-                     Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+                     Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
                ->  Parallel Seq Scan on ab_a3_b2 ab_3 (actual rows=N loops=N)
-                     Filter: ((b = 2) AND ((a = $0) OR (a = $1)))
+                     Filter: ((a = ANY (ARRAY[$0, $1])) AND (b = 2))
 (16 rows)
 
 -- Test pruning during parallel nested loop query
@@ -3408,14 +3425,11 @@
 (2 rows)
 
 explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}');
-                              QUERY PLAN                              
-----------------------------------------------------------------------
- Append
-   ->  Seq Scan on pp_arrpart1 pp_arrpart_1
-         Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
-   ->  Seq Scan on pp_arrpart2 pp_arrpart_2
-         Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
-(5 rows)
+             QUERY PLAN             
+------------------------------------
+ Seq Scan on pp_arrpart2 pp_arrpart
+   Filter: (a = '{4,5}'::integer[])
+(2 rows)
 
 explain (costs off) update pp_arrpart set a = a where a = '{1}';
                  QUERY PLAN                 
@@ -3464,14 +3478,11 @@
 (2 rows)
 
 explain (costs off) select * from pph_arrpart where a in ('{4, 5}', '{1}');
-                              QUERY PLAN                              
-----------------------------------------------------------------------
- Append
-   ->  Seq Scan on pph_arrpart1 pph_arrpart_1
-         Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
-   ->  Seq Scan on pph_arrpart2 pph_arrpart_2
-         Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[]))
-(5 rows)
+              QUERY PLAN              
+--------------------------------------
+ Seq Scan on pph_arrpart1 pph_arrpart
+   Filter: (a = '{4,5}'::integer[])
+(2 rows)
 
 drop table pph_arrpart;
 -- enum type list partition key
diff --git a/src/backend/optimizer/path/clausesel.c b/src/backend/optimizer/path/clausesel.c
index 435438a1735..9c82297e242 100644
--- a/src/backend/optimizer/path/clausesel.c
+++ b/src/backend/optimizer/path/clausesel.c
@@ -751,7 +751,8 @@ clause_selectivity_ext(PlannerInfo *root,
 		 * so that per-subclause selectivities can be cached.
 		 */
 		if (rinfo->orclause)
-			clause = (Node *) rinfo->orclause;
+			{clause = (Node *) rinfo->orclause;
+			}
 		else
 			clause = (Node *) rinfo->clause;
 	}
@@ -823,6 +824,7 @@ clause_selectivity_ext(PlannerInfo *root,
 		 * Almost the same thing as clauselist_selectivity, but with the
 		 * clauses connected by OR.
 		 */
+
 		s1 = clauselist_selectivity_or(root,
 									   ((BoolExpr *) clause)->args,
 									   varRelid,
diff --git a/src/backend/optimizer/util/orclauses.c b/src/backend/optimizer/util/orclauses.c
index 6ef9d14b902..81b11865203 100644
--- a/src/backend/optimizer/util/orclauses.c
+++ b/src/backend/optimizer/util/orclauses.c
@@ -22,6 +22,10 @@
 #include "optimizer/optimizer.h"
 #include "optimizer/orclauses.h"
 #include "optimizer/restrictinfo.h"
+#include "utils/lsyscache.h"
+#include "parser/parse_expr.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_oper.h"

 static bool is_safe_restriction_clause_for(RestrictInfo *rinfo, RelOptInfo *rel);
@@ -29,6 +33,300 @@ static Expr *extract_or_clause(RestrictInfo *or_rinfo, RelOptInfo *rel);
 static void consider_new_or_clause(PlannerInfo *root, RelOptInfo *rel,
 								   Expr *orclause, RestrictInfo *join_or_rinfo);
 
+typedef struct OrClauseGroupEntry
+{
+	Node		   *node;
+	List		   *consts;
+	Oid				collation;
+	Oid				opno;
+	RestrictInfo   *rinfo;
+	Expr *expr;
+} OrClauseGroupEntry;
+
+/*
+ * 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;
+	ListCell   *lc_cp;
+	List	   *modified_rinfo = NIL;
+	bool		something_changed = false;
+	List	   *baserestrictinfo_origin = list_copy(baserestrictinfo);
+
+	/*
+	 * 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?
+	 */
+	forboth (lc, baserestrictinfo, lc_cp, baserestrictinfo_origin)
+	{
+		RestrictInfo   *rinfo = lfirst_node(RestrictInfo, lc);
+		RestrictInfo   *rinfo_base = lfirst_node(RestrictInfo, lc_cp);
+		List		   *or_list = NIL;
+		ListCell	   *lc_eargs,
+					   *lc_rargs,
+					   *lc_args;
+		List		   *groups_list = NIL;
+
+		if (!restriction_is_or_clause(rinfo))
+		{
+			/* Add a clause without changes */
+			modified_rinfo = lappend(modified_rinfo, rinfo);
+			continue;
+		}
+
+		/*
+		 * 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			   *bare_orarg = (Expr *) lfirst(lc_eargs);
+			RestrictInfo	   *sub_rinfo;
+			Node			   *const_expr;
+			Node			   *non_const_expr;
+			ListCell		   *lc_groups;
+			OrClauseGroupEntry *gentry;
+			Oid					opno;
+
+			/* It may be one more boolean expression, skip it for now */
+			if (!IsA(lfirst(lc_rargs), RestrictInfo))
+			{
+				or_list = lappend(or_list, (void *) bare_orarg);
+				continue;
+			}
+
+			sub_rinfo = lfirst_node(RestrictInfo, lc_rargs);
+
+			/* Check: it is an expr of the form 'F(x) oper ConstExpr' */
+			if (!IsA(bare_orarg, OpExpr) ||
+				!(bms_is_empty(sub_rinfo->left_relids) ^
+				bms_is_empty(sub_rinfo->right_relids)) ||
+				contain_volatile_functions((Node *) bare_orarg))
+			{
+				/* Again, it's not the expr we can transform */
+				or_list = lappend(or_list, (void *) bare_orarg);
+				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);
+			non_const_expr = bms_is_empty(sub_rinfo->left_relids) ?
+												get_rightop(sub_rinfo->clause) :
+												get_leftop(sub_rinfo->clause);
+
+			opno = ((OpExpr *) sub_rinfo->clause)->opno;
+			if (!op_mergejoinable(opno, exprType(non_const_expr)))
+			{
+				/* And again, filter out non-equality operators */
+				or_list = lappend(or_list, (void *) bare_orarg);
+				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 (htab key ???).
+			 */
+			foreach(lc_groups, groups_list)
+			{
+				OrClauseGroupEntry *v = (OrClauseGroupEntry *) lfirst(lc_groups);
+
+				Assert(v->node != NULL);
+
+				if (equal(v->node, non_const_expr))
+				{
+					v->consts = lappend(v->consts, const_expr);
+					non_const_expr = NULL;
+					break;
+				}
+			}
+
+			if (non_const_expr == NULL)
+				/*
+				 * The clause classified successfully and added into existed
+				 * clause group.
+				 */
+				continue;
+
+			/* New clause group needed */
+			gentry = palloc(sizeof(OrClauseGroupEntry));
+			gentry->node = non_const_expr;
+			gentry->consts = list_make1(const_expr);
+			gentry->collation = exprInputCollation((Node *)sub_rinfo->clause);
+			gentry->opno = opno;
+			gentry->rinfo = sub_rinfo;
+			gentry->expr = bare_orarg;
+			groups_list = lappend(groups_list,  (void *) gentry);
+		}
+
+		if (groups_list == NIL)
+		{
+			/*
+			 * No any transformations possible with this rinfo, just add itself
+			 * to the list and go further.
+			 */
+			modified_rinfo = lappend(modified_rinfo, rinfo);
+			continue;
+		}
+
+		/* 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
+		 */
+
+		foreach(lc_args, groups_list)
+		{
+			OrClauseGroupEntry *gentry = (OrClauseGroupEntry *) lfirst(lc_args);
+			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(NULL, 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(NULL,
+											 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);
+				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 ? make_orclause(or_list) :
+											 (Expr *) 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);
+		list_free_deep(groups_list);
+		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;
+}
 
 /*
  * extract_restriction_or_clauses
@@ -93,6 +391,10 @@ extract_restriction_or_clauses(PlannerInfo *root)
 		if (rel->reloptkind != RELOPT_BASEREL)
 			continue;
 
+		if (rel->reloptkind == RELOPT_BASEREL)
+		rel->baserestrictinfo  = transform_ors(root, rel->baserestrictinfo);
+		rel->joininfo = transform_ors(root, rel->joininfo);
+
 		/*
 		 * Find potentially interesting OR joinclauses.  We can use any
 		 * joinclause that is considered safe to move to this rel by the

---

From: "a.rybakina" <[email-masked]>
Date: Thu, 17 Aug 2023 13:20:33 +0300

Sorry, I didn't write correctly enough, about the second second place in 
the code where the conversion works well enough is the removal of 
duplicate OR expressions.

I attached patch to learn it in more detail.

On 17.08.2023 13:08, a.rybakina wrote:
>> but as far as I remember, I called it earlier after my conversion, 
>> and unfortunately it didn't solve my problem with calculating 
>> selectivity. I'll reconsider it again, maybe I can find something I 
>> missed.
>> and OR clauses saved like (BoolExpr *) expr -> 
>> orclause->(RestrictInfo *)clause A->(RestrictInfo *)clause B.
>>
>> As I understand it, selectivity is calculated for each expression. 
>> But I'll exploring it deeper, because I think this place may contain 
>> the answer to the question, what's wrong with selectivity calculation 
>> in my patch.
>
> explain analyze select * from tenk1 a join tenk1 b on ((a.unique2 = 3 
> or a.unique2 = 7));
>
> PLAN 
> (actual time=0.000..0.000 rows=2 loops=50000) -> Index Scan using 
> a_idx2 on tenk1 a (cost=0.29..12.62 rows=2 width=16) (actual 
> time=0.063..0.068 rows=2 loops=1) Index Cond: (unique2 = ANY (ARRAY[3, 
> 7])) Planning Time: 8.257 ms Execution Time: 64.453 ms (7 rows)
>
> When I moved the transformation on the index creation stage, it 
> couldn't work properly and as a result I faced the same problem of 
> selectivity calculation. I supposed that the selectivity values are 
> also used there, and not recalculated all over again. perhaps we can 
> solve this by forcibly recalculating the selectivity values, but I 
> foresee other problems there.
>
> explain analyze select * from tenk1 a join tenk1 b on ((a.unique2 = 3 
> or a.unique2 = 7));
>
> QUERY PLAN 
> I have attached a diff file so far, but it is very raw and did not 
> pass all regression tests (I attached regression.diff) and even had 
> bad conversion cases (some of the cases did not work at all, in other 
> cases there were no non-converted nodes). But now I see an interesting 
> (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 

diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 44efb1f4ebc..80935cec7aa 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -67,6 +67,7 @@
 #include "utils/rel.h"
 #include "utils/selfuncs.h"
 #include "utils/syscache.h"
+#include "optimizer/orclauses.h"
 
 /* GUC parameters */
 double		cursor_tuple_fraction = DEFAULT_CURSOR_TUPLE_FRACTION;
@@ -1169,7 +1170,7 @@ preprocess_expression(PlannerInfo *root, Node *expr, int kind)
 	if (kind == EXPRKIND_QUAL)
 	{
 		expr = (Node *) canonicalize_qual((Expr *) expr, false);
-
+expr = transform_ors(root, (Expr *) expr);
 #ifdef OPTIMIZER_DEBUG
 		printf("After canonicalize_qual()\n");
 		pprint(expr);
diff --git a/src/backend/optimizer/util/orclauses.c b/src/backend/optimizer/util/orclauses.c
index 6ef9d14b902..2e30f2bf88a 100644
--- a/src/backend/optimizer/util/orclauses.c
+++ b/src/backend/optimizer/util/orclauses.c
@@ -22,6 +22,10 @@
 #include "optimizer/optimizer.h"
 #include "optimizer/orclauses.h"
 #include "optimizer/restrictinfo.h"
+#include "utils/lsyscache.h"
+#include "parser/parse_expr.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_oper.h"

 static bool is_safe_restriction_clause_for(RestrictInfo *rinfo, RelOptInfo *rel);
@@ -29,7 +33,255 @@ static Expr *extract_or_clause(RestrictInfo *or_rinfo, RelOptInfo *rel);
 static void consider_new_or_clause(PlannerInfo *root, RelOptInfo *rel,
 								   Expr *orclause, RestrictInfo *join_or_rinfo);
 
+static Expr *
+transform_ors_for_rel(Expr *qual)
+	List	       *modified_clause = NIL;
+	bool			change_apply = false;
+	if (!(is_orclause(qual)))
+		return qual;
+	foreach(lc_eargs, ((BoolExpr *) qual)->args)
+		if (!bare_orarg  ||
+		const_expr = get_rightop(bare_orarg);
+		non_const_expr = get_leftop(bare_orarg);
+		opno = ((OpExpr *)bare_orarg)->opno;
+		//if (!op_mergejoinable(opno, exprType(non_const_expr)))
+		//{
+		//	or_list = lappend(or_list, (void *) bare_orarg);
+		//	continue;
+		//}
+		gentry->collation = exprInputCollation((Node *) bare_orarg);
+		modified_clause = lappend(modified_clause, qual);
+	qual = list_length(or_list) > 1 ? make_orclause(or_list) : linitial(or_list);
 
+	//modified_clause = lappend(modified_clause, qual);
+	list_free_deep(groups_list);
+	something_changed = true;
+
+	/*
+	 * Check if transformation has made. If nothing changed - return
+	 * baserestrictinfo as is.
+	 */
+	/* if (something_changed)
+	{
+		return modified_clause;
+	} */
+
+	list_free(modified_clause);
+	return qual;
+}
+Node *
+transform_ors(PlannerInfo *root, Expr *jtnode)
+{
+	return (Node *) transform_ors_for_rel(jtnode);
+}
 /*
  * extract_restriction_or_clauses
  *	  Examine join OR-of-AND clauses to see if any useful restriction OR
diff --git a/src/include/optimizer/orclauses.h b/src/include/optimizer/orclauses.h
index f9dbe6a2972..6a232aeb3ed 100644
--- a/src/include/optimizer/orclauses.h
+++ b/src/include/optimizer/orclauses.h
@@ -17,5 +17,5 @@
 #include "nodes/pathnodes.h"
 
 extern void extract_restriction_or_clauses(PlannerInfo *root);
-
+extern Node * transform_ors(PlannerInfo *root, Expr *jtnode);
 #endif							/* ORCLAUSES_H */

---

From: Peter Geoghegan <[email-masked]>
Date: Sun, 20 Aug 2023 15:11:24 -0700

On Thu, Aug 17, 2023 at 3:08 AM a.rybakina <[email-masked]> wrote:
> This is an interesting feature. I didn't notice this function before, I studied many times consider_new_or_cause, which were called there. As far as I know, there is a selectivity calculation going on there, but as far as I remember, I called it earlier after my conversion, and unfortunately it didn't solve my problem with calculating selectivity. I'll reconsider it again, maybe I can find something I missed.

Back in 2003, commit 9888192f removed (or at least simplified) what
were then called "CNF/DNF CONVERSION ROUTINES". Prior to that point
the optimizer README had something about leaving clause lists
un-normalized leading to selectivity estimation problems. Bear in mind
that this is a couple of years before ScalarArrayOpExpr was first
invented. Apparently even back then "The OR-of-ANDs format is useful
for indexscan implementation". It's possible that that old work will
offer some hints on what to do now.

In a way it's not surprising that work in this area would have some
impact on selectivies. The surprising part is the extent of the
problem, I suppose.

I see that a lot of the things in this area are just used by BitmapOr
clauses, such as build_paths_for_OR() -- but you're not necessarily
able to use any of that stuff. Also, choose_bitmap_and() has some
stuff about how it compensates to avoid "too-small selectivity that
makes a redundant AND step look like it reduces the total cost". It
also mentions some problems with match_join_clauses_to_index() +
extract_restriction_or_clauses(). Again, this might be a good place to
look for more clues.

-- 
Peter Geoghegan

---

From: Peter Geoghegan <[email-masked]>
Date: Sun, 20 Aug 2023 15:26:32 -0700

> offer some hints on what to do now.

There was actually support for OR lists in index AMs prior to
ScalarArrayOpExpr. Even though ScalarArrayOpExpr don't really seem all
that related to bitmap scans these days (since at least nbtree knows
how to execute them "natively"), that wasn't always the case.
ScalarArrayOpExpr were invented the same year that bitmap index scans
were first added (2005), and seem more or less related to that work.
See commits bc843d39, 5b051852, 1e9a6ba5, and 290166f9 (all from
2005). Particularly the last one, which has a commit message that
heavily suggests that my interpretation is correct.

I think that we currently over-rely on BitmapOr for OR clauses. It's
useful that they're so general, of course, but ISTM that we shouldn't
even try to use a BitmapOr in simple cases. Things like the "WHERE
thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42)"
tenk1 query that you brought up probably shouldn't even have a
BitmapOr path (which I guess they don't with you patch). Note that I
recently discussed the same query at length with Tomas Vondra on the
ongoing thread for his index filter patch (you probably knew that
already).

-- 
Peter Geoghegan

---

From: Peter Geoghegan <[email-masked]>
Date: Sun, 20 Aug 2023 15:42:39 -0700

On Thu, Aug 17, 2023 at 3:08 AM a.rybakina <[email-masked]> wrote:
> But now I see an interesting transformation, which was the most interesting for me.
>
> EXPLAIN (COSTS OFF) SELECT * FROM tenk1 WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);

It would be even more interesting if it could be an index-only scan as
a result of the transformation. For example, we could use an
index-only scan with this query (once your patch was in place):

"SELECT thousand, tenthous FROM tenk1 WHERE thousand = 42 AND
(tenthous = 1 OR tenthous = 3 OR tenthous = 42)"

Index-only scans were the original motivation for adding native
ScalarArrayExprOp support to nbtree (in 2011 commit 9e8da0f7), in
fact.

As I suggested earlier, I suspect that there is too much planner logic
that targets BitmapOrs specifically -- maybe even selectivity
estimation/restrictinfo stuff.

PS I wonder if the correctness issues that you saw could be related to
eval_const_expressions(), since "the planner assumes that this
[eval_const_expressions] will always flatten nested AND and OR clauses
into N-argument form". See its subroutines simplify_or_arguments() and
simplify_and_arguments().

-- 
Peter Geoghegan

---

From: "a.rybakina" <[email-masked]>
Date: Tue, 29 Aug 2023 06:37:51 +0300

Thank you for your interest in this problem and help, and I'm sorry that 
I didn't respond to this email for a long time. To be honest, I wanted 
to investigate the problems in more detail and already answer more 
clearly, but unfortunately I have not found anything more significant yet.

> extract_restriction_or_clauses(). Again, this might be a good place to
I agree with your assumption about looking at the source of the error 
related to selectivity in these places. But honestly, no matter how many 
times I looked, until enough sensible thoughts appeared, which could 
cause a problem. I keep looking, maybe I'll find something.
> (tenthous = 1 OR tenthous = 3 OR tenthous = 42); - QUERY PLAN 
I think so too, but it's still quite difficult to find a stable enough 
optimization to implement this, in my opinion. But I will finish the 
current optimization with OR->ANY, given that something interesting has 
appeared.

---

From: Peter Eisentraut <[email-masked]>
Date: Wed, 20 Sep 2023 11:37:05 +0200

> clearly, but unfortunately I have not found anything more significant yet.

What is the status of this patch?  It is registered in the commitfest. 
It looks like a stalled research project?  The last posted patch doesn't 
contain any description or tests, so it doesn't look very ready.

---

From: "a.rybakina" <[email-masked]>
Date: Wed, 20 Sep 2023 15:06:35 +0300

Hi!

When I sent the patch version to commitfest, I thought that the work on 
this topic was completed. Patch version and test results in [0].

But in the process of discussing this patch, we found out that there is 
another place where you can make a transformation, specifically, during 
the calculation of selectivity. I implemented the raw version [1], but 
unfortunately it didn't work in regression tests.

I'm sorry that I didn't write about the status earlier, I was very 
overwhelmed with tasks at work due to releases and preparations for the 
conference. I returned to the work of this patch, today or tomorrow I'll 
drop the version.

[0]

https://www.postgresql.org/message-id/4bac271d-1700-db24-74ac-8414f2baf9fd%40postgrespro.ru

https://www.postgresql.org/message-id/11403645-b342-c400-859e-47d0f41ec22a%40postgrespro.ru

[1] 
https://www.postgresql.org/message-id/b301dce1-09fd-72b1-834a-527ca428db5e%40yandex.ru

On 20.09.2023 12:37, Peter Eisentraut wrote:
>> Thank you for your interest in this problem and help, and I'm sorry 
>> that I didn't respond to this email for a long time. To be honest, I 
>> wanted to investigate the problems in more detail and already answer 
>> more clearly, but unfortunately I have not found anything more 
>> significant yet.
>
> It looks like a stalled research project?  The last posted patch 
> doesn't contain any description or tests, so it doesn't look very ready.
>

---

From: "a.rybakina" <[email-masked]>
Date: Tue, 26 Sep 2023 12:08:52 +0300

I'm sorry I didn't write for a long time, but I really had a very 
difficult month, now I'm fully back to work.

*I was able to implement the patches to the end and moved the 
transformation of "OR" expressions to ANY.* I haven't seen a big 
difference between them yet, one has a conversion before calculating 
selectivity (v7-v1-Replace-OR-clause-to-ANY.patch), the other after 
(v7-v2-Replace-OR-clause-to-ANY.patch). Regression tests are passing, I 
don't see any problems with selectivity, nothing has fallen into the 
coredump, but I found some incorrect transformations. What is the reason 
for these inaccuracies, I have not found, but, to be honest, they look 
unusual). Gave the error below.
In the patch, I don't like that I had to drag three libraries from 
parsing until I found a way around it.The advantage of this approach 
compared to the other (v7-v0-Replace-OR-clause-to-ANY.patch) is that at 
this stage all possible or transformations are performed, compared to 
the patch, where the transformation was done at the parsing stage. That 
is, here, for example, there are such optimizations in the transformation:

I took the common element out of the bracket and the rest is converted 
to ANY, while, as noted by Peter Geoghegan, we did not have several 
bitmapscans, but only one scan through the array.

postgres=# explain analyze SELECT p1.oid, p1.proname
FROM pg_proc as p1
WHERE prolang = 13 AND prolang=1 OR prolang = 13 AND prolang = 2 OR 
prolang = 13 AND prolang = 3;
                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------
  Seq Scan on pg_proc p1  (cost=0.00..151.66 rows=1 width=68) (actual 
time=1.167..1.168 rows=0 loops=1)
    Filter: ((prolang = '13'::oid) AND (prolang = ANY (ARRAY['1'::oid, 
'2'::oid, '3'::oid])))
    Rows Removed by Filter: 3302
  Planning Time: 0.146 ms
  Execution Time: 1.191 ms
(5 rows)

*While I was testing, I found some transformations that don't work, 
although in my opinion, they should:**
**
**1. First case:*
explain analyze SELECT p1.oid, p1.proname
FROM pg_proc as p1
WHERE prolang = 13 AND prolang=1 OR prolang = 2 AND prolang = 2 OR 
prolang = 13 AND prolang = 13;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Seq Scan on pg_proc p1  (cost=0.00..180.55 rows=2 width=68) (actual 
time=2.959..3.335 rows=89 loops=1)
    Filter: (((prolang = '13'::oid) AND (prolang = '1'::oid)) OR 
((prolang = '2'::oid) AND (prolang = '2'::oid)) OR ((prolang = 
'13'::oid) AND (prolang = '13'::oid)))
    Rows Removed by Filter: 3213
  Planning Time: 1.278 ms
  Execution Time: 3.486 ms
(5 rows)

Should have left only prolang = '13'::oid:

                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------
  Seq Scan on pg_proc p1  (cost=0.00..139.28 rows=1 width=68) (actual 
time=2.034..2.034 rows=0 loops=1)
    Filter: ((prolang = '13'::oid ))
    Rows Removed by Filter: 3302
  Planning Time: 0.181 ms
  Execution Time: 2.079 ms
(5 rows)

*2. Also does not work:*
postgres=# explain analyze SELECT p1.oid, p1.proname
FROM pg_proc as p1
WHERE prolang = 13 OR prolang = 2 AND prolang = 2 OR prolang = 13;
                                                   QUERY PLAN
---------------------------------------------------------------------------------------------------------------
  Seq Scan on pg_proc p1  (cost=0.00..164.04 rows=176 width=68) (actual 
time=2.422..2.686 rows=89 loops=1)
    Filter: ((prolang = '13'::oid) OR ((prolang = '2'::oid) AND (prolang 
= '2'::oid)) OR (prolang = '13'::oid))
    Rows Removed by Filter: 3213
  Planning Time: 1.370 ms
  Execution Time: 2.799 ms
(5 rows)

Should have left:
Filter: ((prolang = '13'::oid) OR (prolang = '2'::oid))

*3. Or another:*

explain analyze SELECT p1.oid, p1.proname
FROM pg_proc as p1
WHERE prolang = 13 OR prolang=13 OR prolang = 2 AND prolang = 2;
                                                   QUERY PLAN
---------------------------------------------------------------------------------------------------------------
  Seq Scan on pg_proc p1  (cost=0.00..164.04 rows=176 width=68) (actual 
time=2.350..2.566 rows=89 loops=1)
    Filter: ((prolang = '13'::oid) OR (prolang = '13'::oid) OR ((prolang 
= '2'::oid) AND (prolang = '2'::oid)))
    Rows Removed by Filter: 3213
  Planning Time: 0.215 ms
  Execution Time: 2.624 ms
(5 rows)

Should have left:
Filter: ((prolang = '13'::oid) OR (prolang = '2'::oid))

*Falls into coredump at me:*
explain analyze SELECT p1.oid, p1.proname
FROM pg_proc as p1
WHERE prolang = 13 OR prolang = 2 AND prolang = 2 OR prolang = 13;

explain analyze SELECT p1.oid, p1.proname
FROM pg_proc as p1
WHERE prolang = 13 OR prolang=13 OR prolang = 2 AND prolang = 2;
                                                   QUERY PLAN
---------------------------------------------------------------------------------------------------------------
  Seq Scan on pg_proc p1  (cost=0.00..164.04 rows=176 width=68) (actual 
time=2.350..2.566 rows=89 loops=1)
    Filter: ((prolang = '13'::oid) OR (prolang = '13'::oid) OR ((prolang 
= '2'::oid) AND (prolang = '2'::oid)))
    Rows Removed by Filter: 3213
  Planning Time: 0.215 ms
  Execution Time: 2.624 ms

(5 rows)

I remind that initially the task was to find an opportunity to optimize 
the case of processing a large number of "or" expressions to optimize 
memory consumption. The FlameGraph for executing 50,000 "or" 
expressionshas grown 1.4Gb and remains in this state until exiting the 
psql session (flamegraph1.png) and it sagged a lot in execution time. If 
this case is converted to ANY, the query is executed much faster and 
memory is optimized (flamegraph2.png). It may be necessary to use this 
approach if there is no support for the framework to process ANY, IN 
expressions.

Peter Geoghegan also noticed some development of this patch in terms of 
preparing some transformations to optimize the query at the stage of its 
execution [0].

[0] 
https://www.postgresql.org/message-id/CAH2-Wz%3D9N_4%2BEyhtyFqYQRx4OgVbP%2B1aoYU2JQPVogCir61ZEQ%40mail.gmail.com
From 087125cc413429bda05f22ebbd51115c23819285 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email-masked]>
Date: Tue, 18 Jul 2023 17:19:53 +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]>
---
 src/backend/parser/parse_expr.c               | 230 +++++++++++++++++-
 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, 674 insertions(+), 2 deletions(-)

diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 5a05caa8744..b2294af0f43 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -43,6 +43,7 @@
 
 /* GUC parameters */

 static Node *transformExprRecurse(ParseState *pstate, Node *expr);
@@ -95,6 +96,233 @@ static Expr *make_distinct_op(ParseState *pstate, List *opname,
 static Node *make_nulltest_from_distinct(ParseState *pstate,
 										 A_Expr *distincta, Node *arg);
 
+		if (IsA(get_leftop(orqual), Const))
+		else if (IsA(get_rightop(orqual), Const))
 
 /*
  * transformExpr -
@@ -208,7 +436,7 @@ transformExprRecurse(ParseState *pstate, Node *expr)
 			}
 
 		case T_BoolExpr:
 
 		case T_FuncCall:
 
 /* 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 9b8638f286a..2314d92a6d4 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)

 

index 3e5032b04dd..d4d7d853a4a 100644
@@ -1396,6 +1396,16 @@ explain (costs off)

+
 
index e941fb6c82f..c3abb725c8c 100644
@@ -1631,6 +1631,7 @@ NumericVar
 OM_uint32
 OP
 OSAPerGroupState
+OrClauseGroupEntry
 OSAPerQueryState
 OSInfo
 OSSLCipher
-- 
6.0.1

From 9e0a0200525e7e72f1a91f658b4674fbf78ea18d Mon Sep 17 00:00:00 2001
Date: Thu, 21 Sep 2023 19:15:42 +0300

Authors: Alena Rybakina <[email-masked]>, Andrey Lepikhov <[email-masked]>
Reviewed-by: Peter Geoghegan <[email-masked]>, Ranier Vilela <[email-masked]>
---
 src/backend/optimizer/plan/planner.c   |   3 +-
 src/backend/optimizer/util/orclauses.c | 232 +++++++++++++++++++++++++
 src/include/optimizer/orclauses.h      |   2 +-
 3 files changed, 235 insertions(+), 2 deletions(-)

 /* GUC parameters */
index 6ef9d14b902..805f4b7294a 100644

 static bool is_safe_restriction_clause_for(RestrictInfo *rinfo, RelOptInfo *rel);
@@ -29,7 +33,235 @@ static Expr *extract_or_clause(RestrictInfo *or_rinfo, RelOptInfo *rel);
 static void consider_new_or_clause(PlannerInfo *root, RelOptInfo *rel,
 								   Expr *orclause, RestrictInfo *join_or_rinfo);
 
+transform_ors_for_rel(BoolExpr *expr_orig)
+		list_length(expr_orig->args) < 1)
+		return (Node*) expr_orig;
+		Node			   *orqual = lfirst(lc);
+		if (IsA(get_leftop(orqual), Const))
+		else if (IsA(get_rightop(orqual), Const))
 
+		return (Node *) expr_orig;
+ 			saopexpr->inputcollid = exprInputCollation((Node *)gentry->expr);;
+	if (IsA(jtnode, BoolExpr))
+		return transform_ors_for_rel((BoolExpr *) jtnode);
+	return (Node *) jtnode;

From 84ba19a988447bd5e19132080375101e1ae2e63b Mon Sep 17 00:00:00 2001
Date: Tue, 26 Sep 2023 09:23:44 +0300

Authors: Alena Rybakina <[email-masked]>, Andrey Lepikhov <[email-masked]>
Reviewed-by: Peter Geoghegan <[email-masked]>, Ranier Vilela <[email-masked]>
---
 src/backend/optimizer/util/orclauses.c | 295 +++++++++++++++++++++++++
 1 file changed, 295 insertions(+)

index 6ef9d14b902..b4ac9370461 100644

 static bool is_safe_restriction_clause_for(RestrictInfo *rinfo, RelOptInfo *rel);
@@ -30,6 +34,292 @@ static void consider_new_or_clause(PlannerInfo *root, RelOptInfo *rel,
 								   Expr *orclause, RestrictInfo *join_or_rinfo);

+int			or_transform_limit = 2;
+
+	ListCell	   *lc_clause, *lc_or;
+	foreach (lc_clause, baserestrictinfo)
+			RestrictInfo   	   *rinfo = lfirst_node(RestrictInfo, lc_clause);
+			RestrictInfo	   *rinfo_base = copyObject(rinfo);
+			if (!restriction_is_or_clause(rinfo) ||
+				list_length(((BoolExpr *) rinfo->clause)->args) < or_transform_limit)
+				modified_rinfo = lappend(modified_rinfo, copyObject(rinfo));
+			foreach (lc_or, ((BoolExpr *) rinfo->clause)->args)
+				Node			   *orqual = lfirst(lc_or);
+			if (IsA(get_leftop(orqual), Const))
+			else if (IsA(get_rightop(orqual), Const))
+			modified_rinfo = lappend(modified_rinfo, copyObject(rinfo));
+				gentry = (OrClauseGroupEntry *) lfirst(lc_args);
+					saopexpr->inputcollid = exprInputCollation((Node *)gentry->expr);;
+					change_apply = true;
+				if (!change_apply)
+					* Each group contains only one element - use rinfo as is.
+						rinfo->outer_relids,
+		list_free(or_list);
@@ -93,6 +383,9 @@ extract_restriction_or_clauses(PlannerInfo *root)
 
+		rel->baserestrictinfo  = transform_ors(root, rel->baserestrictinfo);
+		rel->joininfo = transform_ors(root, rel->joininfo);
+
 		/*
 		 * Find potentially interesting OR joinclauses.  We can use any
 		 * joinclause that is considered safe to move to this rel by the
@@ -114,7 +407,9 @@ extract_restriction_or_clauses(PlannerInfo *root)
 				 * and insert it into the rel's restrictinfo list if so.
 				 */
 				if (orclause)
+				{
 					consider_new_or_clause(root, rel, orclause, rinfo);
+				}
 			}
 		}
 	}
-- 
2.34.1

---

From: "a.rybakina" <[email-masked]>
Date: Tue, 26 Sep 2023 12:13:29 +0300

*I was able to implement the patches to the end and moved the 
transformation of "OR" expressions to ANY.* I haven't seen a big 
difference between them yet, one has a transformation before calculating 
selectivity (v7.1-Replace-OR-clause-to-ANY.patch), the other after 
(v7.2-Replace-OR-clause-to-ANY.patch). Regression tests are passing, I 
don't see any problems with selectivity, nothing has fallen into the 
coredump, but I found some incorrect transformations. What is the reason 
for these inaccuracies, I have not found, but, to be honest, they look 
unusual). Gave the error below.

compared to the other (v7.0-Replace-OR-clause-to-ANY.patch) is that at 

                                               QUERY PLAN

*2. Also does not work:*

Should have left:

*3. Or another:*

Should have left:

(5 rows)

[0] 

 
 /* GUC parameters */

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

 

 

 

+

 
 /* GUC parameters */

---

From: "a.rybakina" <[email-masked]>
Date: Tue, 26 Sep 2023 12:21:10 +0300

In the patch, I don't like that I had to drag three libraries from 
parsing until I found a way around it.The advantage of this approach 
compared to the other ([1]) is that at this stage all possible or 
transformations are performed, compared to the patch, where the 
transformation was done at the parsing stage. That is, here, for 
example, there are such optimizations in the transformation:

                                               QUERY PLAN

*2. Also does not work:*

Should have left:

*3. Or another:*

Should have left:

(5 rows)

[0] 

[1] 
https://www.postgresql.org/message-id/attachment/149105/v7-Replace-OR-clause-to-ANY-expressions.patch

 
 /* GUC parameters */

---

From: "a.rybakina" <[email-masked]>
Date: Tue, 26 Sep 2023 12:39:02 +0300

Sorry for the duplicates, I received a letter that my letter did not 
reach the addressee, I thought the design was incorrect.

On 26.09.2023 12:21, a.rybakina wrote:
> difference between them yet, one has a transformation before 
> calculating selectivity (v7.1-Replace-OR-clause-to-ANY.patch), the 
> other after (v7.2-Replace-OR-clause-to-ANY.patch). Regression tests 
> are passing, I don't see any problems with selectivity, nothing has 
> fallen into the coredump, but I found some incorrect transformations. 
> What is the reason for these inaccuracies, I have not found, but, to 
> be honest, they look unusual). Gave the error below.
>
>    Filter: ((prolang = '13'::oid) OR ((prolang = '2'::oid) AND 
> (prolang = '2'::oid)) OR (prolang = '13'::oid))
>    Filter: ((prolang = '13'::oid) OR (prolang = '13'::oid) OR 
> ((prolang = '2'::oid) AND (prolang = '2'::oid)))
>    Filter: ((prolang = '13'::oid) OR (prolang = '13'::oid) OR 
> ((prolang = '2'::oid) AND (prolang = '2'::oid)))
> I remind that initially the task was to find an opportunity to 
> optimize the case of processing a large number of "or" expressions to 
> optimize memory consumption. The FlameGraph for executing 50,000 "or" 
> psql session (flamegraph1.png) and it sagged a lot in execution time. 
> If this case is converted to ANY, the query is executed much faster 
> and memory is optimized (flamegraph2.png). It may be necessary to use 
> this approach if there is no support for the framework to process ANY, 
> IN expressions.
>
>
> Peter Geoghegan also noticed some development of this patch in terms 
> of preparing some transformations to optimize the query at the stage 
> of its execution [0].
>
> [0]

---

From: "a.rybakina" <[email-masked]>
Date: Fri, 29 Sep 2023 20:35:13 +0300

> prolang = 13 AND prolang = 3;
I continue to try to move transformations of "OR" expressions at the 
optimization stage, unfortunately I have not been able to figure out 
coredump yet, but I saw an important thing that it is already necessary 
to process RestrictInfo expressions here. I corrected it.

To be honest, despite some significant advantages in the fact that we 
are already processing pre-converted "or" expressions (logical 
transformations have been performed and duplicates have been removed), I 
have big doubts about this approach. We already have quite a lot of 
objects at this stage that can refer to the RestrictInfo variable in 
ReplOptInfo, and updating these links can be costly for us. By the way, 
right now I suspect that the current coredump appeared precisely because 
there is a link somewhere that refers to an un-updated RestrictInfo, but 
so far I can't find this place. coredump occurs at the request execution 
stage, looks like this:

Core was generated by `postgres: alena regression [local] 
SELECT                                     '.
--Type <RET> for more, q to quit, c to continue without paging--
Program terminated with signal SIGSEGV, Segmentation fault.
#0  0x00005565f3ec4947 in ExecInitExprRec (node=0x5565f530b290, 
state=0x5565f53383d8, resv=0x5565f53383e0, resnull=0x5565f53383dd) at 
execExpr.c:1331
1331                                            Expr       *arg = (Expr 
*) lfirst(lc);
(gdb) bt
#0  0x00005565f3ec4947 in ExecInitExprRec (node=0x5565f530b290, 
state=0x5565f53383d8, resv=0x5565f53383e0, resnull=0x5565f53383dd) at 
execExpr.c:1331
#1  0x00005565f3ec2708 in ExecInitQual (qual=0x5565f531d950, 
parent=0x5565f5337948) at execExpr.c:258
#2  0x00005565f3f2f080 in ExecInitSeqScan (node=0x5565f5309700, 
estate=0x5565f5337700, eflags=32) at nodeSeqscan.c:172
#3  0x00005565f3ee70c9 in ExecInitNode (node=0x5565f5309700, 
estate=0x5565f5337700, eflags=32) at execProcnode.c:210
#4  0x00005565f3edbe3a in InitPlan (queryDesc=0x5565f53372f0, eflags=32) 
at execMain.c:968
#5  0x00005565f3edabe3 in standard_ExecutorStart 
(queryDesc=0x5565f53372f0, eflags=32) at execMain.c:266
#6  0x00005565f3eda927 in ExecutorStart (queryDesc=0x5565f53372f0, 
eflags=0) at execMain.c:145
#7  0x00005565f419921e in PortalStart (portal=0x5565f52ace90, 
params=0x0, eflags=0, snapshot=0x0) at pquery.c:517
#8  0x00005565f4192635 in exec_simple_query (
     query_string=0x5565f5233af0 "SELECT p1.oid, p1.proname\nFROM 
pg_proc as p1\nWHERE prolang = 13 AND (probin IS NULL OR probin = '' OR 
probin = '-');") at postgres.c:1233
#9  0x00005565f41976ef in PostgresMain (dbname=0x5565f526ad10 
"regression", username=0x5565f526acf8 "alena") at postgres.c:4652
#10 0x00005565f40b8417 in BackendRun (port=0x5565f525f830) at 
postmaster.c:4439
#11 0x00005565f40b7ca3 in BackendStartup (port=0x5565f525f830) at 
postmaster.c:4167
#12 0x00005565f40b40f1 in ServerLoop () at postmaster.c:1781
#13 0x00005565f40b399b in PostmasterMain (argc=8, argv=0x5565f522c110) 
at postmaster.c:1465
#14 0x00005565f3f6560e in main (argc=8, argv=0x5565f522c110) at main.c:198

I have saved my experimental version of the "or" transfer in the diff 
file, I am attaching the main patch in the ".patch" format so that the 
tests are checked against this version. Let me remind you that the main 
patch contains the code for converting "OR" expressions to "ANY" at the 
parsing stage.
diff --git a/src/backend/optimizer/util/orclauses.c b/src/backend/optimizer/util/orclauses.c
index 6ef9d14b902..d01c09f28e2 100644
--- a/src/backend/optimizer/util/orclauses.c
+++ b/src/backend/optimizer/util/orclauses.c
@@ -22,6 +22,10 @@
 #include "optimizer/optimizer.h"
 #include "optimizer/orclauses.h"
 #include "optimizer/restrictinfo.h"
+#include "utils/lsyscache.h"
+#include "parser/parse_expr.h"
+#include "parser/parse_coerce.h"
+#include "parser/parse_oper.h"

+	ListCell	   *lc_clause;
+					   	   *lc_rargs;
+			Expr			   *orqual = (Expr *) lfirst(lc_eargs);
+			if (!IsA(orqual, OpExpr) ||
+				contain_volatile_functions((Node *) orqual))
+				or_list = lappend(or_list, (void *) orqual);
+			nconst_expr = bms_is_empty(sub_rinfo->left_relids) ?
+			if (!op_mergejoinable(((OpExpr *) sub_rinfo->clause)->opno, exprType(nconst_expr)))
+				or_list = lappend(or_list, (void *) orqual);
+					or_list = lappend(or_list, gentry->rinfo->clause);
+				if (array_type != InvalidOid && scalar_type != InvalidOid)
+					saopexpr->inputcollid = exprInputCollation((Node *)gentry->rinfo->clause);
 
+		//rel->joininfo = transform_ors(root, rel->joininfo);

 
 /* GUC parameters */

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

 

 

 

+

---

From: "a.rybakina" <[email-masked]>
Date: Wed, 4 Oct 2023 22:19:59 +0300

On 29.09.2023 20:35, a.rybakina wrote:
>> I took the common element out of the bracket and the rest is 
>> converted to ANY, while, as noted by Peter Geoghegan, we did not have 
>> several bitmapscans, but only one scan through the array.
>>
Hi, all!

I fixed the kernel dump issue and all the regression tests were 
successful, but I discovered another problem when I added my own 
regression tests.
Some queries that contain "or" expressions do not convert to "ANY". I 
have described this in more detail using diff as expected and real results:

diff -U3 
/home/alena/postgrespro__copy6/src/test/regress/expected/create_index.out 
/home/alena/postgrespro__copy6/src/test/regress/results/create_index.out
--- 
/home/alena/postgrespro__copy6/src/test/regress/expected/create_index.out 
2023-10-04 21:54:12.496282667 +0300
+++ 
/home/alena/postgrespro__copy6/src/test/regress/results/create_index.out 
2023-10-04 21:55:41.665422459 +0300
@@ -1925,17 +1925,20 @@
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM tenk1
    WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
-                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
+                                                        QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
   Aggregate
     ->  Bitmap Heap Scan on tenk1
-         Recheck Cond: (((thousand = 42) AND (tenthous = ANY 
('{1,3}'::integer[]))) OR (thousand = 41))
+         Recheck Cond: ((((thousand = 42) AND (tenthous = 1)) OR 
((thousand = 42) AND (tenthous = 3))) OR (thousand = 41))
           ->  BitmapOr
-               ->  Bitmap Index Scan on tenk1_thous_tenthous
-                     Index Cond: ((thousand = 42) AND (tenthous = ANY 
('{1,3}'::integer[])))
+               ->  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 = 41)
-(8 rows)
+(11 rows)
@@ -1946,24 +1949,50 @@
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM tenk1
+  WHERE thousand = 42 OR tenthous = 1 AND thousand = 42 OR tenthous = 1;
+                                            QUERY PLAN
+---------------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on tenk1
+         Recheck Cond: ((thousand = 42) OR ((thousand = 42) AND 
(tenthous = 1)) OR (tenthous = 1))
+         ->  BitmapOr
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: (thousand = 42)
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: ((thousand = 42) AND (tenthous = 1))
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: (tenthous = 1)
+(10 rows)
+
+SELECT count(*) FROM tenk1
+  WHERE thousand = 42 OR tenthous = 1 AND thousand = 42 OR tenthous = 1;
+ count
+-------
+    11
+(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
------------------------------------------------------------------------------------------------------------------------------
+                                                       QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
   Aggregate
     ->  Bitmap Heap Scan on tenk1
-         Recheck Cond: (((hundred = 42) AND ((tenthous < 2) OR 
(thousand = ANY ('{42,99}'::integer[])))) OR (thousand = 41))
+         Recheck Cond: (((hundred = 42) AND ((thousand = 42) OR 
(thousand = 99) OR (tenthous < 2))) 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)
+                                 Index Cond: (thousand = 42)
                             ->  Bitmap Index Scan on tenk1_thous_tenthous
-                                 Index Cond: (thousand = ANY 
('{42,99}'::integer[]))
+                                 Index Cond: (thousand = 99)
+                           ->  Bitmap Index Scan on tenk1_thous_tenthous
+                                 Index Cond: (tenthous < 2)
                 ->  Bitmap Index Scan on tenk1_thous_tenthous
                       Index Cond: (thousand = 41)
-(14 rows)
+(16 rows)

diff -U3 
/home/alena/postgrespro__copy6/src/test/regress/expected/join.out 
/home/alena/postgrespro__copy6/src/test/regress/results/join.out
--- /home/alena/postgrespro__copy6/src/test/regress/expected/join.out 
2023-10-04 21:53:55.632069079 +0300
+++ /home/alena/postgrespro__copy6/src/test/regress/results/join.out 
2023-10-04 21:55:46.597485979 +0300
  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
----------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 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))
+   Join Filter: ((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)))
     ->  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))
+               Recheck Cond: ((unique1 < 20) OR (unique1 = 3) OR 
(unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
                 ->  BitmapOr
                       ->  Bitmap Index Scan on tenk1_unique1
                             Index Cond: (unique1 < 20)
                       ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 = 3)
+                     ->  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)
+                           Index Cond: (unique2 = 3)
+                     ->  Bitmap Index Scan on tenk1_unique2
+                           Index Cond: (unique2 = 7)
+(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
----------------------------------------------------------------------------------------------------------------------------------------------------------------
+ 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))
+   Join Filter: ((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)))
     ->  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))
+               Recheck Cond: ((unique1 < 20) OR (unique1 = 3) OR 
(unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
                 ->  BitmapOr
                       ->  Bitmap Index Scan on tenk1_unique1
                             Index Cond: (unique1 < 20)
                       ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 = 3)
+                     ->  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)
+                           Index Cond: (unique2 = 3)
+                     ->  Bitmap Index Scan on tenk1_unique2
+                           Index Cond: (unique2 = 7)
+(17 rows)

I haven't been able to fully deal with this problem yet

I have attached my experimental patch with the code.
index 6ef9d14b902..cbb187229fe 100644

 static bool is_safe_restriction_clause_for(RestrictInfo *rinfo, RelOptInfo *rel);
@@ -29,6 +33,307 @@ static Expr *extract_or_clause(RestrictInfo *or_rinfo, RelOptInfo *rel);
 static void consider_new_or_clause(PlannerInfo *root, RelOptInfo *rel,
 								   Expr *orclause, RestrictInfo *join_or_rinfo);
 
+			if (!op_mergejoinable(((OpExpr *) sub_rinfo->clause)->opno, exprType(non_const_expr)))
+				or_list = lappend(or_list, (void *) gentry->rinfo->clause);
+					//saopexpr->inputcollid = exprInputCollation((Node *)gentry->rinfo->clause);
 
 /*
  * extract_restriction_or_clauses
@@ -93,6 +398,8 @@ extract_restriction_or_clauses(PlannerInfo *root)
 		if (rel->reloptkind != RELOPT_BASEREL)
 			continue;
 
index 16ec6c5ef02..a4f04d84021 100644
@@ -2052,6 +2052,16 @@ struct config_int ConfigureNamesInt[] =
 
 /* GUC parameters */

diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index acfd9d1f4f7..a397137ecb6 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1883,6 +1883,150 @@ SELECT count(*) FROM tenk1
     10
 (1 row)
 
+SET or_transform_limit = 0;
+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 (ARRAY[1, 3, 42])))
+(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 (ARRAY[42, 99])))
+         ->  BitmapAnd
+               ->  Bitmap Index Scan on tenk1_hundred
+                     Index Cond: (hundred = 42)
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: (thousand = ANY (ARRAY[42, 99]))
+(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 = 1)) OR ((thousand = 42) AND (tenthous = 3))) OR (thousand = 41))
+         ->  BitmapOr
+               ->  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 = 41)
+(11 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 thousand = 42 OR tenthous = 1 AND thousand = 42 OR tenthous = 1;
+                                            QUERY PLAN                                             
+---------------------------------------------------------------------------------------------------
+ Aggregate
+   ->  Bitmap Heap Scan on tenk1
+         Recheck Cond: ((thousand = 42) OR ((thousand = 42) AND (tenthous = 1)) OR (tenthous = 1))
+         ->  BitmapOr
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: (thousand = 42)
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: ((thousand = 42) AND (tenthous = 1))
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: (tenthous = 1)
+(10 rows)
+
+SELECT count(*) FROM tenk1
+  WHERE thousand = 42 OR tenthous = 1 AND thousand = 42 OR tenthous = 1;
+ count 
+-------
+    11
+(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 ((thousand = 42) OR (thousand = 99) OR (tenthous < 2))) 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: (thousand = 42)
+                           ->  Bitmap Index Scan on tenk1_thous_tenthous
+                                 Index Cond: (thousand = 99)
+                           ->  Bitmap Index Scan on tenk1_thous_tenthous
+                                 Index Cond: (tenthous < 2)
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: (thousand = 41)
+(16 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 (ARRAY[42, 41]))))
+         ->  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 (ARRAY[42, 41]))
+(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 9b8638f286a..f22e4524099 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4207,6 +4207,60 @@ select * from tenk1 a join tenk1 b on
                            Index Cond: (unique2 = 7)
 (19 rows)
 
+SET or_transform_limit = 0;
+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 = 3) OR (a.unique2 = 7)) 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 = 3) OR (unique2 = 7))
+               ->  BitmapOr
+                     ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 = 1)
+                     ->  Bitmap Index Scan on tenk1_unique2
+                           Index Cond: (unique2 = 3)
+                     ->  Bitmap Index Scan on tenk1_unique2
+                           Index Cond: (unique2 = 7)
+(19 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 = 3) OR ((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR (a.unique2 = 7)) AND (b.hundred = 4)))
+   ->  Seq Scan on tenk1 b
+   ->  Materialize
+         ->  Bitmap Heap Scan on tenk1 a
+               Recheck Cond: ((unique1 < 20) OR (unique1 = 3) OR (unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
+               ->  BitmapOr
+                     ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 < 20)
+                     ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 = 3)
+                     ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 = 1)
+                     ->  Bitmap Index Scan on tenk1_unique2
+                           Index Cond: (unique2 = 3)
+                     ->  Bitmap Index Scan on tenk1_unique2
+                           Index Cond: (unique2 = 7)
+(17 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 bb1223e2b13..ad4c4dae81e 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -101,6 +101,42 @@ 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)
 
@@ -671,6 +707,166 @@ explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a =
 
index f133b5a4ac7..e462212aa54 100644
 
+SET or_transform_limit = 0;
+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 (ARRAY['(0,2)'::tid, '(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..514e4f0da48 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -737,6 +737,44 @@ SELECT count(*) FROM tenk1
 SELECT count(*) FROM tenk1
   WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
 
+  WHERE thousand = 42 OR tenthous = 1 AND thousand = 42 OR tenthous = 1;
+  WHERE thousand = 42 OR tenthous = 1 AND thousand = 42 OR tenthous = 1;
index 3e5032b04dd..272ff7c5d90 100644
@@ -1397,6 +1397,17 @@ select * from tenk1 a join tenk1 b on
 
index 83fed54b8c6..068eed3499c 100644
@@ -99,6 +105,21 @@ explain (costs off) select * from rlp where a > 1 and a >=15;	/* rlp3 onwards, i

index 8de90c49585..b40b58124b4 100644
@@ -1635,6 +1635,7 @@ NumericVar
 OM_uint32
 OP
 OSAPerGroupState
+OrClauseGroupEntry
 OSAPerQueryState
 OSInfo
 OSSLCipher

---

From: Alexander Korotkov <[email-masked]>
Date: Sun, 15 Oct 2023 01:34:51 +0300

Hi, Alena!

Thank you for your work on the subject.

On Wed, Oct 4, 2023 at 10:21 PM a.rybakina <[email-masked]> wrote:
> I fixed the kernel dump issue and all the regression tests were successful, but I discovered another problem when I added my own regression tests.
> Some queries that contain "or" expressions do not convert to "ANY". I have described this in more detail using diff as expected and real results:
>
> diff -U3 /home/alena/postgrespro__copy6/src/test/regress/expected/create_index.out /home/alena/postgrespro__copy6/src/test/regress/results/create_index.out
> --- /home/alena/postgrespro__copy6/src/test/regress/expected/create_index.out 2023-10-04 21:54:12.496282667 +0300
> +++ /home/alena/postgrespro__copy6/src/test/regress/results/create_index.out  2023-10-04 21:55:41.665422459 +0300
> @@ -1925,17 +1925,20 @@
>  EXPLAIN (COSTS OFF)
> -         Recheck Cond: (((thousand = 42) AND (tenthous = ANY ('{1,3}'::integer[]))) OR (thousand = 41))
> -                     Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3}'::integer[])))
> +               ->  BitmapOr

I think this query is not converted, because you only convert
top-level ORs in the transform_ors() function.  But in the example
given, the target OR lays under AND, which in turn lays under another
OR.  I think you need to make transform_ors() recursive to handle
cases like this.

I wonder about the default value of the parameter or_transform_limit
of 500. In [1] and [2] you show the execution time degradation from 0
to ~500 OR clauses.  I made a simple SQL script with the query "SELECT
* FROM pgbench_accounts a WHERE  aid = 1 OR aid = 2 OR ... OR aid =
100;". The pgbench results for a single connection in prepared mode
are the following.
master: 936 tps
patched (or_transform_limit == 0) :1414 tps
So, transformation to ANY obviously accelerates the execution.

I think it's important to identify the cases where this patch causes
the degradation.  Generally, I don't see why ANY could be executed
slower than the equivalent OR clause.  So, the possible degradation
cases are slower plan generation and worse plans.  I managed to find
both.

As you stated before, currently the OR transformation has a quadratic
complexity depending on the number of or-clause-groups.  I made a
simple test to evaluate this. containing 10000 or-clause-groups.
SELECT * FROM pgbench_accounts a WHERE aid + 1 * bid = 1 OR aid + 2 *
bid = 1 OR ... OR aid + 10000 * bid = 1;
master: 316ms
patched: 7142ms
Note, that the current or_transform_limit GUC parameter is not capable
of cutting such cases, because it cuts cases lower than the limit not
higher than the limit.  In the comment, you mention that we could
invent something like hash to handle this.  Hash should be nice, but
the problem is that we currently don't have a generic facility to hash
nodes (or even order them).  It would be nice to add this facility,
that would be quite a piece of work.  I would propose to limit this
patch for now to handle just a single Var node as a non-const side of
the clause and implement a simple hash for Vars.

Another problem is the possible generation of worse plans.  I made an
example table with two partial indexes.
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;

Without the transformation of ORs to ANY, our planner manages to use
both indexes with a Bitmap scan.
# 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)

With transformation, the planner can't use indexes.
# explain select * from test where (x = 1 or x = 2) and y = 100;
                                 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 = ANY (ARRAY[1, 2])) AND (y = '100'::double precision))
(4 rows)

The solution I see would be to tech Bitmap scan to handle ANY clause
in the same way as the OR clause.  I think the entry point for the
relevant logic is the choose_bitmap_and() function.

Regarding the GUC parameter, I don't see we need a limit.  It's not
yet clear whether a small number or a large number of OR clauses are
more favorable for transformation.  I propose to have just a boolean
enable_or_transformation GUC.

Links
1. https://www.postgresql.org/message-id/6b97b517-f36a-f0c6-3b3a-0cf8cfba220c%40yandex.ru
2. https://www.postgresql.org/message-id/938d82e1-98df-6553-334c-9db7c4e288ae%40yandex.ru

------
Regards,
Alexander Korotkov

---

From: "a.rybakina" <[email-masked]>
Date: Mon, 16 Oct 2023 02:21:39 +0300

Hi! Thank you for your review!

On 15.10.2023 01:34, Alexander Korotkov wrote:
> On Wed, Oct 4, 2023 at 10:21 PM a.rybakina<[email-masked]>  wrote:
Yes, you are right, it seems that a recursive method is needed here.
> the clause and implement a simple hash for Vars.
I ran the query and saw that you were right, this place in the patch 
turns out to be very expensive. In addition to the hash, I saw a second 
solution to this problem - parameterize constants and store them in the 
list, but this will not be such a universal solution as hashing. If the 
variable, not the constant, changes, parameterization will not help.

I agree with your suggestion to try adding hashing. I'll take a closer 
look at this.

> # explain select * from test where (x = 1 or x = 2) and y = 100;
It's a good idea, I'll try.
But to be honest, I'm afraid that problems with selectivity may come up 
again and in order to solve them, additional processing of RestrictInfo 
may be required, which will be unnecessarily expensive. As far as I 
understand, at this stage we are creating indexes for AND expressions 
and there is a risk that its transformation may cause the need to change 
references in all possible places where it was referenced.
> 1.https://www.postgresql.org/message-id/6b97b517-f36a-f0c6-3b3a-0cf8cfba220c%40yandex.ru
> 2.https://www.postgresql.org/message-id/938d82e1-98df-6553-334c-9db7c4e288ae%40yandex.ru

I tend to agree with you and I see that in some cases it really doesn't 
help.

---

From: "a.rybakina" <[email-masked]>
Date: Wed, 25 Oct 2023 14:04:24 +0300

Hi!

> # explain select * from test where (x = 1 or x = 2) and y = 100;
I removed the limit from the hook, left the option to enable it or not.

I replaced the data structure so that the groups were formed not in a 
list, but in a hash table. It seems to work fine, but I haven't figured 
out yet why in some cases the regression test results are different and 
the function doesn't work.

So far, I have formed a patch for the version where the conversion takes 
place in parsing, since so far this patch looks the most reliable for me

For convenience, I have formed a patch for the very first version so far.

I have a suspicion that the problem is in the part where we form a hash 
from a string. I'm still figuring it out.
From 35b4cd3ee48a5c5893a731439f5099c2736a2a66 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <[email-masked]>
Date: Wed, 25 Oct 2023 13:52:55 +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]>, Alexander Korotkov <[email-masked]>
---
 src/backend/parser/parse_expr.c               | 254 +++++++++++++++++-
 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, 698 insertions(+), 2 deletions(-)

diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 64c582c344c..93ae5d2dbc9 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,257 @@ 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 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;
+		}
+
+		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.
+		*/
+		str = nodeToString(nconst_expr);
+		gentry = hash_search(or_group_htab, &str, HASH_FIND, &found);
+
+		if (found)
+		{
+			elog(WARNING, "find anything");
+			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)
+	{
+		/*
+		* No any transformations possible with this list of arguments. Here we
+		* already made all underlying transformations. Thus, just return the
+		* transformed bool expression.
+		*/
+		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);
 
+			hash_search(or_group_htab, &gentry->hash_leftvar_key, HASH_REMOVE, NULL);
 
 /*
  * transformExpr -
@@ -212,7 +464,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 4c585741661..634be59e538 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1038,6 +1038,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;

index acfd9d1f4f7..29c2bc6a2b2 100644
 
+SET or_transform_limit = on;
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
 
index b95d30f6586..a3ef1afd1fd 100644
 
+SET or_transform_limit = on;
+explain (costs off)
index 9a4c48c0556..1789d3c1fd7 100644
 
+SET or_transform_limit = on;
 
+SET or_transform_limit = on;
index f133b5a4ac7..8a31e2e670d 100644
 
+SET or_transform_limit = on;
+EXPLAIN (COSTS OFF)
index d49ce9f3007..a709b2c1abc 100644
 
+SET or_transform_limit = on;
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
index 3e5032b04dd..481898c2987 100644
+SET or_transform_limit = on;
 
index 7bf3920827f..88709910592 100644
+SET or_transform_limit = on;
 
+
+SET or_transform_limit = on;
index 313e0fb9b67..c735e219589 100644
 
+SET or_transform_limit = on;
+EXPLAIN (COSTS OFF)
index 06b25617bc9..701b1075ffc 100644

---

From: Robert Haas <[email-masked]>
Date: Wed, 25 Oct 2023 15:54:12 -0400

> enable_or_transformation GUC.

That's a poor solution. So is the GUC patch currently has
(or_transform_limit). What you need is a heuristic that figures out
fairly reliably whether the transformation is going to be better or
worse. Or else, do the whole thing in some other way that is always
same-or-better.

In general, adding GUCs makes sense when the user knows something that
we can't know. For example, shared_buffers makes some sense because,
even if we discovered how much memory the machine has, we can't know
how much of it the user wants to devote to PostgreSQL as opposed to
anything else. And track_io_timing makes sense because we can't know
whether the user wants to pay the price of gathering that additional
data. But GUCs are a poor way of handling cases where the real problem
is that we don't know what code to write. In this case, some queries
will be better with enable_or_transformation=on, and some will be
better with enable_or_transformation=off. Since we don't know which
will work out better, we make the user figure it out and set the GUC,
possibly differently for each query. That's terrible. It's the query
optimizer's whole job to figure out which transformations will speed
up the query. It shouldn't turn around and punt the decision back to
the user.

Notice that superficially-similar GUCs like enable_seqscan aren't
really the same thing at all. That's just for developer testing and
debugging. Nobody expects that you have to adjust that GUC on a
production system - ever.

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

---

From: Alena Rybakina <[email-masked]>
Date: Thu, 26 Oct 2023 22:47:20 +0300

Hi! Thank you for your feedback!

On 25.10.2023 22:54, Robert Haas wrote:
> On Sat, Oct 14, 2023 at 6:37 PM Alexander Korotkov<[email-masked]>  wrote:

I noticed that the costs of expressions are different and it can help to 
assess when it is worth leaving the conversion, when not.

With small amounts of "OR" elements, the cost of orexpr is lower than 
with "ANY", on the contrary, higher.

postgres=# SET or_transform_limit = 500;
EXPLAIN (analyze)
SELECT oid,relname FROM pg_class
WHERE
   oid = 13779 AND (oid = 2 OR oid = 4 OR oid = 5)
;
SET
                                                           QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
  Index Scan using pg_class_oid_index on pg_class  (*cost=0.27..8.30* 
rows=1 width=68) (actual time=0.105..0.106 rows=0 loops=1)
    Index Cond: (oid = '13779'::oid)
    Filter: ((oid = '2'::oid) OR (oid = '4'::oid) OR (oid = '5'::oid))
  Planning Time: 0.323 ms
  Execution Time: 0.160 ms

(5 rows)

postgres=# SET or_transform_limit = 0;
EXPLAIN (analyze)
SELECT oid,relname FROM pg_class
WHERE
   oid = 13779 AND (oid = 2 OR oid = 4 OR oid = 5)
;
SET
                                                           QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
  Index Scan using pg_class_oid_index on pg_class  (*cost=0.27..16.86* 
rows=1 width=68) (actual time=0.160..0.161 rows=0 loops=1)
    Index Cond: ((oid = ANY (ARRAY['2'::oid, '4'::oid, '5'::oid])) AND 
(oid = '13779'::oid))
  Planning Time: 4.515 ms
  Execution Time: 0.313 ms
(4 rows)

Index Scan using pg_class_oid_index on pg_class  (*cost=0.27..2859.42* 
rows=414 width=68) (actual time=1.504..34.183 rows=260 loops=1)
    Index Cond: (oid = ANY (ARRAY['1'::oid, '2'::oid, '3'::oid, 
'4'::oid, '5'::oid, '6'::oid, '7'::oid,

Bitmap Heap Scan on pg_class  (*cost=43835.00..54202.14* rows=414 
width=68) (actual time=39.958..41.293 rows=260 loops=1)
    Recheck Cond: ((oid = '1'::oid) OR (oid = '2'::oid) OR (oid = 
'3'::oid) OR (oid = '4'::oid) OR (oid =

I think we could see which value is lower, and if lower with expressions 
converted to ANY, then work with it further, otherwise work with the 
original "OR" expressions. But we still need to make this conversion to 
find out its cost.

In addition, I will definitely have to postpone the transformation of 
"OR" to "ANY" at the stage of creating indexes (?) or maybe a little 
earlier so that I have to count only the cost of the transformed 
expression.

---

From: Robert Haas <[email-masked]>
Date: Thu, 26 Oct 2023 15:58:47 -0400

On Thu, Oct 26, 2023 at 3:47 PM Alena Rybakina
<[email-masked]> wrote:
> With small amounts of "OR" elements, the cost of orexpr is lower than with "ANY", on the contrary, higher.

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.

> Index Scan using pg_class_oid_index on pg_class  (cost=0.27..2859.42 rows=414 width=68) (actual time=1.504..34.183 rows=260 loops=1)
>    Index Cond: (oid = ANY (ARRAY['1'::oid, '2'::oid, '3'::oid, '4'::oid, '5'::oid, '6'::oid, '7'::oid,
>
> Bitmap Heap Scan on pg_class  (cost=43835.00..54202.14 rows=414 width=68) (actual time=39.958..41.293 rows=260 loops=1)
>    Recheck Cond: ((oid = '1'::oid) OR (oid = '2'::oid) OR (oid = '3'::oid) OR (oid = '4'::oid) OR (oid =
>
> I think we could see which value is lower, and if lower with expressions converted to ANY, then work with it further, otherwise work with the original "OR" expressions. But we still need to make this conversion to find out its cost.

To me, this sort of output suggests that perhaps the transformation is
being done in the wrong place. I expect that we have to decide whether
to convert from OR to = ANY(...) at a very early stage of the planner,
before we have any idea what the selected path will ultimately be. But
this output suggests that we want the answer to depend on which kind
of path is going to be faster, which would seem to argue for doing
this sort of transformation as part of path generation for only those
paths that will benefit from it, rather than during earlier phases of
expression processing/simplification.

I'm not sure I have the full picture here, though, so I might have
this all wrong.

---

From: Alena Rybakina <[email-masked]>
Date: Thu, 26 Oct 2023 23:41:54 +0300

> unless I'm misunderstanding his point.
Yes, I agree, with Alexander's example, this option will not help and 
here I need to look inside Expr itself. But I noticed that such a 
complex non-constant expression is always an OpExpr type, otherwise if 
the non-constant part contains only one variable, then it is a Var type. 
We can add a constraint that we will transform expressions with the 
simple variables like x=1 or x=2 or x=3, etc., but expressions like 
x*1+y=1 or x*2+y=2... we ignore.

But then, we do not consider expressions when the nonconstant part is 
always the same for expressions. For example, we could transform x*1+y=1 
or x*1+y=2... to x*1+y = ANY([1,2,...]). But I think it's not so 
critical, because such cases are rare.
> I'm not sure I have the full picture here, though, so I might have
This would be the most ideal option, and to be honest, I like the 
conversion at an early stage also because there are no problems with 
selectivity or link updates if we changed the structure of RestrictInfo 
of relation.

But in terms of calculating which option is better to use transformed or 
original, I think this solution might be complicated, since we need not 
only to highlight the cases in which the transformation wins in 
principle, but also with which types of data it will work best and there 
is a risk of missing some cases and we may need the own evaluation 
model. Now it's hard for me to come up with something simple.

The cost option seems simpler and clearer to me, but yes, it is 
difficult to decide when it is better to do the conversion for the most 
correct estimate.

-- 
Regards,
Alena Rybakina

---

From: Peter Geoghegan <[email-masked]>
Date: Thu, 26 Oct 2023 14:04:44 -0700

> 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.

Alexander's partial index example had this quality to it. Obviously,
the planner *could* be taught to do the right thing with such a case,
with a little more work. The fact that it doesn't right now is
definitely a problem, and should probably be treated as a blocker for
this patch. But that doesn't really argue against the general idea
behind the patch -- it just argues for fixing that one problem.

There may also be a separate problem that comes from the added planner
cycles required to do the transformation -- particularly in extreme or
adversarial cases. We should worry about that, too. But, again, it
doesn't change the basic fact, which is that having a
standard/normalized representation of OR lists/DNF transformation is
extremely useful in general, and *shouldn't* result in any real
slowdowns at execution time if done well.

> expression processing/simplification.

I don't think that that's the right direction. They're semantically
equivalent things. But a SAOP-based plan can be fundamentally better,
since SAOPs enable passing down useful context to index AMs (at least
nbtree). And because we can use a hash table for SAOP expression
evaluation. It's a higher level, standardized, well optimized way of
expressing exactly the same concept.

I can come up with a case that'll be orders of magnitude more
efficient with this patch, despite the transformation process only
affecting a small OR list of 3 or 5 elements -- a 100x reduction in
heap page accesses is quite possible. This is particularly likely to
come up if you assume that the nbtree patch that I'm currently working
on is also available. In general, I think that we totally over-rely on
bitmap index scans, especially BitmapOrs.

-- 
Peter Geoghegan

---

From: Alena Rybakina <[email-masked]>
Date: Sun, 29 Oct 2023 19:41:06 +0300

Hi!

On 27.10.2023 00:04, Peter Geoghegan wrote:
> On Thu, Oct 26, 2023 at 12:59 PM Robert Haas<[email-masked]>  wrote:
I think it would be more correct to finalize the current approach to 
converting "OR" expressions to "ANY", since quite a few problems related 
to this patch have already been found here, I think you can solve them 
first, and then you can move on.

> bitmap index scans, especially BitmapOrs.
Regarding the application of the transformation at an early stage, the 
patch is almost ready, except for solving cases related to queries that 
work slower. I haven't figured out how to exclude such requests without 
comparing the cost or parameter by the number of OR elements yet. The 
simplest option is not to process Expr types (already mentioned earlier) 
in the queries that Alexander gave as an example, but as I already said, 
I don't like this approach very much.

-- 
Regards,
Alena Rybakina
Postgres Professional

---

From: Robert Haas <[email-masked]>
Date: Mon, 30 Oct 2023 09:40:11 -0400

> 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.

---

From: Alexander Korotkov <[email-masked]>
Date: Mon, 30 Oct 2023 16:06:41 +0200

> more scenarios but causes serious regressions in some cases probably

+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

------
Regards,
Alexander Korotkov

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 30 Oct 2023 09:01:29 -0700

> in principle, the two are equivalent and it shouldn't matter which

I recently looked into MySQL's handling of these issues, which is more
mature and better documented than what we can do. EXPLAIN ANALYZE will
show an IN() list as if the query had been written as a list of ORs,
even though it can efficiently execute an index scan that uses
IN()/"OR var = constant" lists. So I agree with what you said here. It
is perhaps just as accident of history that we're talking about
converting to a ScalarArrayOpExpr, rather than talking about
converting to some other clause type that we associate with OR lists.

The essential point is that there ought to be one clause type that is
easier to deal with.

> more scenarios but causes serious regressions in some cases probably

I agree. Most of the really big wins here will come from simple
transformations. I see no reason why we can't take an incremental
approach. In fact I think we almost have to do so, since as I
understand it the transformations are just infeasible in certain
extreme cases.

-- 
Peter Geoghegan