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

From: Robert Haas <[email-masked]>
Date: Mon, 24 Jun 2024 11:28:13 -0400

On Fri, Jun 21, 2024 at 6:52 PM Alena Rybakina
<[email-masked]> wrote:
> It's hard to tell, but I think it might be one of the good places to apply transformation. Let me describe a brief conclusion on the two approaches.

This explanation is somewhat difficult for me to follow. For example:

> In the first approach, we definitely did not process the extra "OR" expressions in the first approach, since they were packaged as an Array. It could lead to the fact that less planning time would be spent on the optimizer.

I don't know what the "first approach" refers to, or what processing
the extra "OR" expressions means, or what it would mean to package OR
expressions as an array. If you made them into an SAOP then you'd have
an array *instead of* OR expressions, not OR expressions "packaged as
an array" but even then, they'd still be processed somewhere, unless
the patch was just wrong.

I think you should try writing this summary again and see if you can
make it a lot clearer and more precise.

I'm suspicious based that we should actually be postponing the
transformation even further. If, for example, the transformation is
advantageous for index scans and disadvantageous for bitmap scans, or
the other way around, then this approach can't help much: it either
does the transformation and all scan types are affected, or it doesn't
do it and no scan types are affected. But if you decided for each scan
whether to transform the quals, then you could handle that. Against
that, there might be increased planning cost. But, perhaps that could
be avoided somehow.

> What exactly is the strategy around OR-clauses with type differences?
> If I'm reading the code correctly, the first loop requires an exact
> opno match, which presumably implies that the constant-type elements
> are of the same type. But then why does the second loop need to use
> coerce_to_common_type?
>
> It needs to transform all similar constants to one type, because some constants of "OR" expressions can belong others, like the numeric and int types. Due to the fact that array structure demands that all types must be belonged to one type, so for this reason we applied this procedure.

The alternative that should be considered is not combining things if
the types don't match. If we're going to combine such things, we need
to be absolutely certain that type conversion cannot fail.

> I do not think this is acceptable. We should find a way to get the
> right operator into the ScalarArrayOpExpr without translating the OID
> back into a name and then back into an OID.
>
> I don’t really understand the reason why it’s better not to do this. Can you explain please?

One reason is that it is extra work to convert things to a name and
then back to an OID. It's got to be slower than using the OID you
already have.

The other reason is that it's error-prone. If somehow the second
lookup doesn't produce the same OID as the first lookup, bad things
will happen, possibly including security vulnerabilities. I see you've
taken steps to avoid that, like nailing down the schema, and that's
good, but it's not a good enough reason to do it like this. If we
don't have a function that can construct the node we need with the OID
rather than the name as an argument, we should invent one, not do this
sort of thing.

> Also, why is the array built with eval_const_expressions instead of
> something like makeArrayResult? There should be no need for general
> expression evaluation here if we are just dealing with constants.
>
> I'm not ready to answer this question right now, I need time to study the use of the makeArrayResult function in the optimizer.

OK. An important consideration here is that eval_const_expressions()
is prone to *fail* because it can call user-defined functions. We
really don't want this optimization to cause planner failure (or
queries to error out at any other stage, either). We also don't want
to end up with any security problems, which is another possible danger
when we call a function that can execute arbitrary code. It's better
to keep it simple and only do things that we know are simple and safe,
like assembling a bunch of datums that we already have into an array.

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

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 24 Jun 2024 12:08:59 -0400

> to be absolutely certain that type conversion cannot fail.

But what about cases like this:

SELECT * FROM mytable WHERE columna = 1_000_000_000 or columna =
5_000_000_000; -- columna is int4

This is using two types, of course. 1_000_000_000 is int4, while
5_000_000_000 is bigint. If the transformation suddenly failed to work
when a constant above INT_MAX was used for the first time, then I'd
say that that's pretty surprising. That's what happens currently if
you write the same query as "WHERE columna =
any('{1_000_000_000,5_000_000_000}')", due to the way the coercion
works. That seems less surprising to me, because the user is required
to construct their own array, and users expect arrays to always have
one element type.

It would probably be okay to make the optimization not combine
things/not apply when the user gratuitously mixes different syntaxes.
For example, if a numeric constant was used, rather than an integer
constant.

Maybe it would be practical to do something with the B-Tree operator
class for each of the types involved in the optimization. You could
probably find a way for a SAOP to work against a
"heterogeneously-typed array" while still getting B-Tree index scans
-- provided the types all came from the same operator family. I'm
assuming that the index has an index column whose input opclass was a
member of that same family. That would necessitate changing the
general definition of SAOP, and adding new code to nbtree that worked
with that. But that seems doable.

I was already thinking about doing something like this, to support
index scans for "IS NOT DISTINCT FROM", or on constructs like "columna
= 5 OR columna IS NULL". That is more or less a SAOP with two values,
except that one of the values in the value NULL. I've already
implemented "nbtree SAOPs where one of the elements is a NULL" for
skip scan, which could be generalized to support these other cases.

Admittedly I'm glossing over a lot of important details here. Does it
just work for the default opclass for the type, or can we expect it to
work with a non-default opclass when that's the salient opclass (the
one used by our index)? I don't know what you'd do about stuff like
that.

-- 
Peter Geoghegan

---

From: Robert Haas <[email-masked]>
Date: Mon, 24 Jun 2024 13:29:18 -0400

> to construct their own array, and users expect arrays to always have

I am not against handling this kind of case if we can do it, but it's
more important that the patch doesn't cause gratuitous failures than
that it handles more cases.

> with that. But that seems doable.

I agree that something based on operator families might be viable. Why
would that require changing the definition of an SAOP?

> one used by our index)? I don't know what you'd do about stuff like

It seems to me that it just depends on the opclasses in the query. If
the user says

WHERE column op1 const1 AND column op2 const2

...then if op1 and op2 are in the same operator family and if we can
convert one of const1 and const2 to the type of the other without risk
of failure, then we can rewrite this as an SAOP with whichever of the
two operators pertains to the target type, e.g.

column1 op1 ANY[const1,converted_const2]

I don't think the default opclass matters here, or the index properties either.

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 24 Jun 2024 13:46:42 -0400

> that it handles more cases.

I agree, with the proviso that "avoid gratuitous failures" should
include cases where a query that got the optimization suddenly fails
to get the optimization, due only to some very innocuous looking
change. Such as a change from using a constant 1_000_000_000 to a
constant 5_000_000_000 in the query text. That is a POLA violation.

> would that require changing the definition of an SAOP?

Maybe it doesn't. My point was only that the B-Tree code doesn't
necessarily need to use just one rhs type for the same column input
opclass. The definition of SOAP works (or could work) in basically the
same way, provided the "OR condition" were provably disjunct. We could
for example mix different operators for the same nbtree scan key (with
some work in nbtutils.c), just as we could support "where mycol =5 OR
mycol IS NULL" with much effort.

BTW, did you know MySQL has long supported the latter? It has a <=>
operator, which is basically a non-standard spelling of IS NOT
DISTINCT FROM. Importantly, it is indexable, whereas right now
Postgres doesn't support indexing IS NOT DISTINCT FROM. If you're
interested in working on this problem within the scope of this patch,
or some follow-up patch, I can take care of the nbtree side of things.

> I don't think the default opclass matters here, or the index properties either.

Okay, good.

The docs do say "Another requirement for a multiple-data-type family
is that any implicit or binary-coercion casts that are defined between
data types included in the operator family must not change the
associated sort ordering" [1]. There must be precedent for this sort
of thing. Probably for merge joins.

[1] https://www.postgresql.org/docs/devel/btree.html#BTREE-BEHAVIOR
-- 
Peter Geoghegan

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 24 Jun 2024 13:54:10 -0400

> or some follow-up patch, I can take care of the nbtree side of things.

To be clear, I meant that we could easily support "where mycol = 5 OR
mycol IS NULL" and have nbtree handle that efficiently, by making it a
SAOP internally. Separately, we could also make IS NOT DISTINCT FROM
indexable, though that probably wouldn't need any work in nbtree.

-- 
Peter Geoghegan

---

From: Robert Haas <[email-masked]>
Date: Mon, 24 Jun 2024 14:28:02 -0400

> constant 5_000_000_000 in the query text. That is a POLA violation.

Nope, I don't agree with that at all. If you imagine that we can
either have the optimization apply to one of those cases on the other,
or on the other hand we can have some cases that outright fail, I
think it's entirely clear that the former is better.

> or some follow-up patch, I can take care of the nbtree side of things.

I was assuming this patch shouldn't be changing the way indexes work
at all, just making use of the facilities that we have today. More
could be done, but that might make it harder to get anything
committed.

Before we get too deep into arguing about hypotheticals, I don't think
there's any problem here that we can't solve with the infrastructure
we already have. For instance, consider this:

robert.haas=# explain select * from foo where a in (1, 1000000000000000);
                        QUERY PLAN
-----------------------------------------------------------
 Seq Scan on foo1 foo  (cost=0.00..25.88 rows=13 width=36)
   Filter: (a = ANY ('{1,1000000000000000}'::bigint[]))
(2 rows)

I don't know exactly what's happening here, but it seems very similar
to what we need to have happen for this patch to work. pg_typeof(1) is
integer, and pg_typeof(1000000000000000) is bigint, and we're able to
figure out that it's OK to put both of those in an array of a single
type and without having any type conversion failures. If you replace
1000000000000000 with 2, then the array ends up being of type
integer[] rather than type bigint[], so. clearly the system is able to
reason its way through these kinds of scenarios already.

It's even possible, in my mind at least, that the patch is already
doing exactly the right things here. Even if it isn't, the problem
doesn't seem to be fundamental, because if this example can work (and
it does) then what the patch is trying to do should be workable, too.
We just have to make sure we're plugging all the pieces properly
together, and that we have comments adequately explain what is
happening and test cases that verify it. My feeling is that the patch
doesn't meet that standard today, but I think that just means it needs
some more work. I'm not arguing we have to throw the whole thing out,
or invent a lot of new infrastructure, or anything like that.

---

From: Peter Geoghegan <[email-masked]>
Date: Mon, 24 Jun 2024 15:02:47 -0400

> think it's entirely clear that the former is better.

I'm just saying that not having the optimization apply to a query very
similar to one where it does apply is a POLA violation. That's another
kind of failure, for all practical purposes. Weird performance cliffs
like that are bad. It's very easy to imagine code that generates a
query text, that at some point randomly and mysteriously gets a
sequential scan. Or a much less efficient index scan.

> could be done, but that might make it harder to get anything

I was just pointing out that there is currently no good way to make
nbtree efficiently execute a qual "WHERE a = 5 OR a IS NULL", which is
almost entirely (though not quite entirely) due to a lack of any way
of expressing that idea through SQL, in a way that'll get pushed down
to the index scan node. You can write "WHERE a = any('{5,NULL')", of
course, but that doesn't treat NULL as just another array element to
match against via an IS NULL qual (due to NULL semantics).

Yeah, this is nonessential. But it's quite a nice optimization, and
seems entirely doable within the framework of the patch. It would be a
natural follow-up.

All that I'd need on the nbtree side is to get an input scan key that
directly embodies "WHERE mycol = 5 OR mycol IS NULL". That would
probably just be a scan key with sk_flags "SK_SEARCHARRAY |
SK_SEARCHNULL", that was otherwise identical to the current
SK_SEARCHARRAY scan keys.

Adopting the nbtree array index scan code to work with this would be
straightforward. SK_SEARCHNULL scan keys basically already work like
regular equality scan keys at execution time, so all that this
optimization requires on the nbtree side is teaching
_bt_advance_array_keys to treat NULL as a distinct array condition
(evaluated as IS NULL, not as = NULL).

> or invent a lot of new infrastructure, or anything like that.

I feel like my point about the potential for POLA violations is pretty
much just common sense. I'm not particular about the exact mechanism
by which we avoid it; only that we avoid it.

--
Peter Geoghegan

---

From: Alena Rybakina <[email-masked]>
Date: Wed, 26 Jun 2024 02:13:53 +0300

On 24.06.2024 18:28, Robert Haas wrote:
> an array*instead of*  OR expressions, not OR expressions "packaged as

Sorry, you are right and I'll try to explain more precisely. The 
firstapproach isthefirstpartof the patch,wherewemade "Or" expressions 
into an SAOPatan earlystageof plangeneration[0],the secondonewasthe one 
proposedby A.Korotkov[1].

So, when we made "OR" expressions into an SAOPat the post-parsing stage 
of the plan generation [0], we definitely did not process the 
redundantexpressions"OR" expressions there (for example,duplicates), 
since they were transformed to SAOP expression. Furthermore, the list of 
OR expressions can be significantly reduced, since constants belonging 
to the same predicate will already be converted into an SAOP expression. 
I assume this may reduce planning time, as I know several places in the 
optimizer where these lists of "OR" expressions are scanned several times.
Also the selectivity for SAOP expressions is estimated better, which 
could lead to the generation of a more optimal plan, but, to be honest, 
this is just an observation from changes in regression tests and, in 
general, how the process of calculating the selectivity of a complex 
expression works. And I think it needs further consideration. SELECT * 
FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE 
(a = 1 OR a = 51) AND b = ''1'''); estimated | actual 
-----------+-------- - 99 | 100 + 100 | 100 (1 row) SELECT * FROM 
check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 
OR a = 51) AND (b = ''1'' OR b = ''2'')'); estimated | actual 
-----------+-------- - 99 | 100 + 100 | 100 (1 row) SELECT * FROM 
check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 
OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')'); estimated 
| actual -----------+-------- - 197 | 200 + 200 | 200 Speaking of the 
main disadvantages, we do not give the optimizer the opportunity to 
generate a plan using BitmapScan, which can lead to the generation of a 
suboptimal plan, but in the current approach the same thing happens [2]. 
And you mentioned about it before:

> that, there might be increased planning cost. But, perhaps that could

Andrei mentioned the problem, which might be caused by the 
transformation on the later stage of optimization is updating references 
to expressions in RestrictInfo [3] lists, because they can be used in 
different parts during the formation of the query plan. As the practice 
of Self-join removal [4] has shown, this can be expensive, but feasible. 
By applying the transformation at the analysis stage [0], because no 
links were created, so we did not encounter such problems, so this 
approach was more suitable than the others.

If some things were not clear enough, let me know.

[0] 
https://www.postgresql.org/message-id/attachment/156971/v21-0001-Transform-OR-clauses-to-ANY-expression.patch 
[1] 
https://www.postgresql.org/message-id/CAPpHfduah1PLzajBJFDmp7%2BMZuaWYpie2p%2BGsV0r03fcGghQ-g%40mail.gmail.com 
[2] 
https://www.postgresql.org/message-id/7d5aed92-d4cc-4b76-8ae0-051d182c9eec%40postgrespro.ru 
[3] 
https://www.postgresql.org/message-id/6850c306-4e9d-40b7-8096-1f3c7d29cd9e%40postgrespro.ru 
[4] https://commitfest.postgresql.org/48/5043/

> to be absolutely certain that type conversion cannot fail.

Peter,Robert,thanksforthe detaileddiscussion,I realizedthathereyou 
needto lookcarefullyatthe patch. In general, it comes out, I need to pay 
attention and highlight the cases where POLA violation occurs

> rather than the name as an argument, we should invent one, not do this

I understood. I'll try to fix it.

---

From: Robert Haas <[email-masked]>
Date: Wed, 26 Jun 2024 16:19:01 -0400

I think maybe replying to multiple emails with a single email is
something you'd be better off doing less often.

On Tue, Jun 25, 2024 at 7:14 PM Alena Rybakina
<[email-masked]> wrote:
> Sorry, you are right and I'll try to explain more precisely. The first approach is the first part of the patch, where we made "Or" expressions into an SAOP at an early stage of plan generation [0], the second one was the one proposed by A.Korotkov [1].

[0] isn't doing anything "at an early stage of plan generation".  It's
changing something in *the parser*. The parser and planner are VERY
different stages of query parsing, and it's really important to keep
them separate mentally and in discussions. We should not be changing
anything about the query in the parser, because that will, as
Alexander also pointed out, change what gets stored if the user does
something like CREATE VIEW whatever AS SELECT ...; and we should, for
the most part, be storing the query as the user entered it, not some
transformed version of it. Further, at the parser stage, we do not
know the cost of anything, so we can only transform things when the
transformed version is always - or practically always - going to be
cheaper than the untransformed version.

> Andrei mentioned the problem, which might be caused by the transformation on the later stage of optimization is updating references to expressions in RestrictInfo [3] lists, because they can be used in different parts during the formation of the query plan. As the practice of Self-join removal [4] has shown, this can be expensive, but feasible. By applying the transformation at the analysis stage [0], because no links were created, so we did not encounter such problems, so this approach was more suitable than the others.

The link you provided for [3] doesn't show me exactly what code you're
talking about, but I can see why mutating a RestrictInfo after
creating it could be problematic. However, I'm not proposing that, and
I don't think it's a good idea. Instead of mutating an existing data
structure after it's been created, we want to get each data structure
correct at the moment that it is created. What that means is that at
each stage of processing, whenever we create a new in-memory data
structure, we have an opportunity to make changes along the way.

For instance, let's say we have a RestrictInfo and we are creating a
Path, perhaps via create_index_path(). One argument to that function
is a list of indexclauses. The indexclauses are derived from the
RestrictInfo list associated with the RelOptInfo. We take some subset
of those quals that are deemed to be indexable and we reorder them and
maybe change a few things and we build this new list of indexclauses
that is then passed to create_index_path(). The RelOptInfo's list of
RestrictInfos is not changed -- only the new list of clauses derived
from it is being built up here, without any mutation of the original
structure.

This is the kind of thing that this patch can and probably should do.
Join removal is quite awkward, as you rightly point out, because we
end up modifying existing data structures after they've been created,
and that requires us to run around and fix up a bunch of stuff, and
that can have bugs. Whenever possible, we don't want to do it that
way. Instead, we want to pick points in the processing when we're
anyway constructing some new structure and use that as an opportunity
to do transformations when building the new structure that incorporate
optimizations that make sense.

---

From: Alena Rybakina <[email-masked]>
Date: Thu, 27 Jun 2024 22:56:56 +0300

> something you'd be better off doing less often.
Ok, I won't do this in the future. After thinkingit over,I 
realizedthatit turnedout to be somekindof messinthe end.
> them separate mentally and in discussions.

Thanks for the detailed explanation, I'm always glad to learn new things 
for myself)

To be honest, I had an intuitive feeling that the transformation was 
called in the analyzer stage, but I wasn't sure about it, so I tried to 
summarize it.

As for the fact that in general all this can be divided into two large 
stages, parsing and planner is a little new to me. I have reread the 
documentation [0] andI foundinformationaboutitthere.

Beforethat, Iwas guidedbyinformationfromthe 
CarnegieMellonUniversitylecture andthe BruceMamjian report[1],whichwas 
wrongonmypart.

By the way,it turnsout that the queryrewritingstagereferstoan 
independentstage,whichis locatedbetweenthe 
parserstageandtheplanner/optimizer. I found it from the documentation [2].

[0] https://www.postgresql.org/docs/current/planner-optimizer.html

[1] https://momjian.us/main/writings/pgsql/optimizer.pdf

[2] https://www.postgresql.org/docs/16/rule-system.html

> We should not be changing

Thank you, now it has become clear to me why it is so important to leave 
the transformation at the planner stage.

> optimizations that make sense.

Thanks for the idea! I hadn't thought in this direction before, but it 
really might just work and solve all our original problems.
By the way, I saw that the optimizer is smart enough to eliminate 
duplicates. Below I have conducted a couple of examples where he decides 
for himself which expression is more profitable for him to leave.
Wejustneedto addthistransformation,andthe optimizerwillchoosethe 
appropriateone)

alena@postgres=# explain select * from x where (a = 1 or a = 2) and a in 
(1,2);

                              QUERY PLAN
--------------------------------------------------------------------
  Index Only Scan using a_idx on x  (cost=0.28..8.61 rows=1 width=4)
    Index Cond: (a = ANY ('{1,2}'::integer[]))
(2 rows)

alena@postgres=# explain select * from x where a < 3 and (a = 1 or a = 
2) and a = ANY(ARRAY[1,2]);
                              QUERY PLAN
--------------------------------------------------------------------
  Index Only Scan using a_idx on x  (cost=0.28..8.60 rows=1 width=4)
    Index Cond: ((a < 3) AND (a = ANY ('{1,2}'::integer[])))
(2 rows)

ItworksforKorotkov's casetoo,asIseeit:

alena@postgres=# create table test as (select (random()*10)::int x, 
(random()*1000) y from generate_series(1,1000000) i); create index 
test_x_1_y on test (y) where x = 1; create index test_x_2_y on test (y) 
where x = 2; vacuum analyze test; SELECT 1000000 CREATE INDEX CREATE 
INDEX VACUUM alena@postgres=# explain select * from test where (x = 1 or 
x = 2) and y = 100 and x in (1,2); 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)

I noticed that the distribute_quals_to_rels function launches at the 
stage when it is necessary to generate RestrictInfo lists for relation - 
it might be a suitable place for applying transformation.
So, instead of completely replacing the list, we should form a complex 
BoolExpr structure with the "AND" operator, which should contain two 
expressions, where one of them is BoolExpr with the "OR" operator and 
the second is ScalarArrayOpExpr.

Tobe honest,I've alreadystartedwritingcodetodothis,butI'm facedwitha 
misunderstandingof howto correctlycreatea 
conditionfor"OR"expressionsthatare notsubjectto transformation.For 
example,the expressions b=1in the query below:

alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = 
ANY(ARRAY[5,4])) or (b=1); QUERY PLAN 
---------------------------------------------------------------------------------- 
Seq Scan on x (cost=0.00..123.00 rows=1 width=8) Filter: ((((a = 5) OR 
(a = 4)) AND (a = ANY ('{5,4}'::integer[]))) OR (b = 1)) (2 rows)

I see that two expressions have remained unchanged and it only works for 
"AND" binary operations.

But I think it might be worth applying this together, where does the 
optimizer generate indexes (build_paths_for_OR function)?

-- 
Regards,
Alena Rybakina
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company

---

From: Alena Rybakina <[email-masked]>
Date: Thu, 27 Jun 2024 23:06:49 +0300

Tobe honest,I've alreadystartedwritingcodetodothis,butI'm facedwitha 
misunderstandingof howto correctlycreatea 
conditionfor"OR"expressionsthatare notsubjectto transformation.
>
> For example,the expressions b=1in the query below:
>
> I see that two expressions have remained unchanged and it only works 
> for "AND" binary operations.
>

Sorry, it works) I needed to create one more index for b column.

Just in case, I gave an example of a complete case, otherwise it might 
not be entirely clear:

alena@postgres=# create table x (a int, b int);
CREATE TABLE
alena@postgres=# create index a_idx on x(a);
                         insert into x select id,id from 
generate_series(1, 5000) as id;
CREATE INDEX
INSERT 0 5000
alena@postgres=# analyze;
ANALYZE

alena@postgres=# create index b_idx on x(b);

CREATE INDEX

alena@postgres=# explain select * from x where ( (a =5 or a=4) and a = 
ANY(ARRAY[5,4]))  or (b=1);
                                 QUERY PLAN
--------------------------------------------------------------------------
  Bitmap Heap Scan on x  (cost=12.87..21.68 rows=1 width=8)
    Recheck Cond: ((a = ANY ('{5,4}'::integer[])) OR (b = 1))
    ->  BitmapOr  (cost=12.87..12.87 rows=3 width=0)
          ->  Bitmap Index Scan on a_idx  (cost=0.00..8.58 rows=2 width=0)
                Index Cond: (a = ANY ('{5,4}'::integer[]))
          ->  Bitmap Index Scan on b_idx  (cost=0.00..4.29 rows=1 width=0)
                Index Cond: (b = 1)
(7 rows)

---

From: Alena Rybakina <[email-masked]>
Date: Thu, 11 Jul 2024 02:30:22 +0300

On 27.06.2024 23:06, Alena Rybakina wrote:
>> alena@postgres=# explain select * from x where ( (a =5 or a=4) and a 
>> = ANY(ARRAY[5,4])) or (b=1); QUERY PLAN 
>> Seq Scan on x (cost=0.00..123.00 rows=1 width=8) Filter: ((((a = 5) 
>> OR (a = 4)) AND (a = ANY ('{5,4}'::integer[]))) OR (b = 1)) (2 rows)
>>
Iimplementedsuchcode,butatthe 
analysisstageinplanner,anditwasn'tfullyreadyyet,butIwas ableto 
drawsomeimportantconclusions.Firstof all,Ifacedtheproblemof the 
inequalityof the numberof columnsinthe expressionwiththe 
requiredone,atleastsomeextracolumnappeared,judgingby the 
crust.Ihaven'tfullyrealizedityet andhaven'tfixedit.

#0  __pthread_kill_implementation (no_tid=0, signo=6, 
threadid=134300960061248)
     at ./nptl/pthread_kill.c:44
#1  __pthread_kill_internal (signo=6, threadid=134300960061248) at 
./nptl/pthread_kill.c:78
#2  __GI___pthread_kill (threadid=134300960061248, signo=signo@entry=6) 
at ./nptl/pthread_kill.c:89
#3  0x00007a2560042476 in __GI_raise (sig=sig@entry=6) at 
../sysdeps/posix/raise.c:26
#4  0x00007a25600287f3 in __GI_abort () at ./stdlib/abort.c:79
#5  0x00005573f9df62a8 in ExceptionalCondition (
     conditionName=0x5573f9fec4c8 
"AttrNumberIsForUserDefinedAttr(list_attnums[i]) || 
!bms_is_member(attnum, clauses_attnums)", fileName=0x5573f9fec11c 
"dependencies.c", lineNumber=1525) at assert.c:66
#6  0x00005573f9b8b85f in dependencies_clauselist_selectivity 
(root=0x5573fad534e8,
     clauses=0x5573fad0b2d8, varRelid=0, jointype=JOIN_INNER, 
sjinfo=0x0, rel=0x5573fad54b38,
     estimatedclauses=0x7ffe2e43f178) at dependencies.c:1525
#7  0x00005573f9b8fed9 in statext_clauselist_selectivity 
(root=0x5573fad534e8, clauses=0x5573fad0b2d8,
     varRelid=0, jointype=JOIN_INNER, sjinfo=0x0, rel=0x5573fad54b38, 
estimatedclauses=0x7ffe2e43f178,
     is_or=false) at extended_stats.c:2035
--Type <RET> for more, q to quit, c to continue without paging--
#8  0x00005573f9a57f88 in clauselist_selectivity_ext 
(root=0x5573fad534e8, clauses=0x5573fad0b2d8,
     varRelid=0, jointype=JOIN_INNER, sjinfo=0x0, 
use_extended_stats=true) at clausesel.c:153
#9  0x00005573f9a57e30 in clauselist_selectivity (root=0x5573fad534e8, 
clauses=0x5573fad0b2d8,
     varRelid=0, jointype=JOIN_INNER, sjinfo=0x0) at clausesel.c:106
#10 0x00005573f9a62e03 in set_baserel_size_estimates 
(root=0x5573fad534e8, rel=0x5573fad54b38)
     at costsize.c:5247
#11 0x00005573f9a51aa5 in set_plain_rel_size (root=0x5573fad534e8, 
rel=0x5573fad54b38,
     rte=0x5573fad0ec58) at allpaths.c:581
#12 0x00005573f9a516ce in set_rel_size (root=0x5573fad534e8, 
rel=0x5573fad54b38, rti=1,
     rte=0x5573fad0ec58) at allpaths.c:411
#13 0x00005573f9a514c7 in set_base_rel_sizes (root=0x5573fad534e8) at 
allpaths.c:322
#14 0x00005573f9a5119d in make_one_rel (root=0x5573fad534e8, 
joinlist=0x5573fad0adf8) at allpaths.c:183
#15 0x00005573f9a94d45 in query_planner (root=0x5573fad534e8,
     qp_callback=0x5573f9a9b59e <standard_qp_callback>, 
qp_extra=0x7ffe2e43f540) at planmain.c:280
#16 0x00005573f9a977a8 in grouping_planner (root=0x5573fad534e8, 
tuple_fraction=0, setops=0x0)
     at planner.c:1520
#17 0x00005573f9a96e47 in subquery_planner (glob=0x5573fad533d8, 
parse=0x5573fad0ea48, parent_root=0x0,
     hasRecursion=false, tuple_fraction=0, setops=0x0) at planner.c:1089
#18 0x00005573f9a954aa in standard_planner (parse=0x5573fad0ea48,
     query_string=0x5573fad8b3b0 "explain analyze SELECT * FROM 
functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND 
upper(b) = '1'", cursorOptions=2048, boundParams=0x0) at planner.c:415
#19 0x00005573f9a951d4 in planner (parse=0x5573fad0ea48,
--Type <RET> for more, q to quit, c to continue without paging--
     query_string=0x5573fad8b3b0 "explain analyze SELECT * FROM 
functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND 
upper(b) = '1'", cursorOptions=2048, boundParams=0x0) at planner.c:282
#20 0x00005573f9bf4e2e in pg_plan_query (querytree=0x5573fad0ea48,
     query_string=0x5573fad8b3b0 "explain analyze SELECT * FROM 
functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND 
upper(b) = '1'", cursorOptions=2048, boundParams=0x0) at postgres.c:904
#21 0x00005573f98613e7 in standard_ExplainOneQuery 
(query=0x5573fad0ea48, cursorOptions=2048, into=0x0,
     es=0x5573fad57da0,
     queryString=0x5573fad8b3b0 "explain analyze SELECT * FROM 
functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND 
upper(b) = '1'", params=0x0, queryEnv=0x0) at explain.c:489
#22 0x00005573f9861205 in ExplainOneQuery (query=0x5573fad0ea48, 
cursorOptions=2048, into=0x0,
     es=0x5573fad57da0,
     queryString=0x5573fad8b3b0 "explain analyze SELECT * FROM 
functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND 
upper(b) = '1'", params=0x0, queryEnv=0x0) at explain.c:445
#23 0x00005573f9860e35 in ExplainQuery (pstate=0x5573fad57c90, 
stmt=0x5573fad8b5a0, params=0x0,
     dest=0x5573fad57c00) at explain.c:341
#24 0x00005573f9bff3a8 in standard_ProcessUtility (pstmt=0x5573fad8b490,
     queryString=0x5573fad8b3b0 "explain analyze SELECT * FROM 
functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND 
upper(b) = '1'", readOnlyTree=false, context=PROCESS_UTILITY_QUERY, 
params=0x0,
     queryEnv=0x0, dest=0x5573fad57c00, qc=0x7ffe2e43fcd0) at utility.c:863
#25 0x00005573f9bfe91a in ProcessUtility (pstmt=0x5573fad8b490,
     queryString=0x5573fad8b3b0 "explain analyze SELECT * FROM 
functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND 
upper(b) = '1'", readOnlyTree=false, context=PROCESS_UTILITY_QUERY, 
params=0x0,
--Type <RET> for more, q to quit, c to continue without paging--
     queryEnv=0x0, dest=0x5573fad57c00, qc=0x7ffe2e43fcd0) at utility.c:523
#26 0x00005573f9bfd195 in PortalRunUtility (portal=0x5573fac6bcf0, 
pstmt=0x5573fad8b490,
     isTopLevel=false, setHoldSnapshot=true, dest=0x5573fad57c00, 
qc=0x7ffe2e43fcd0) at pquery.c:1158
#27 0x00005573f9bfced2 in FillPortalStore (portal=0x5573fac6bcf0, 
isTopLevel=false) at pquery.c:1031
#28 0x00005573f9bfd778 in PortalRunFetch (portal=0x5573fac6bcf0, 
fdirection=FETCH_FORWARD, count=10,
     dest=0x5573fa1d6880 <spi_printtupDR>) at pquery.c:1442
#29 0x00005573f9992675 in _SPI_cursor_operation (portal=0x5573fac6bcf0, 
direction=FETCH_FORWARD,
     count=10, dest=0x5573fa1d6880 <spi_printtupDR>) at spi.c:3019
#30 0x00005573f9990849 in SPI_cursor_fetch (portal=0x5573fac6bcf0, 
forward=true, count=10) at spi.c:1805
#31 0x00007a25603e0aa5 in exec_for_query (estate=0x7ffe2e440200, 
stmt=0x5573fad067c8,
     portal=0x5573fac6bcf0, prefetch_ok=true) at pl_exec.c:5889
#32 0x00007a25603de728 in exec_stmt_dynfors (estate=0x7ffe2e440200, 
stmt=0x5573fad067c8)
     at pl_exec.c:4647
#33 0x00007a25603d8b1c in exec_stmts (estate=0x7ffe2e440200, 
stmts=0x5573fad06ec8) at pl_exec.c:2100
#34 0x00007a25603d8697 in exec_stmt_block (estate=0x7ffe2e440200, 
block=0x5573fad06f18) at pl_exec.c:1943
#35 0x00007a25603d7d9e in exec_toplevel_block (estate=0x7ffe2e440200, 
block=0x5573fad06f18)
     at pl_exec.c:1634
#36 0x00007a25603d5a2e in plpgsql_exec_function (func=0x5573fac2c1e0, 
fcinfo=0x5573fad2af60,
     simple_eval_estate=0x0, simple_eval_resowner=0x0, 
procedure_resowner=0x0, atomic=true)
     at pl_exec.c:623
#37 0x00007a25603f277f in plpgsql_call_handler (fcinfo=0x5573fad2af60) 
at pl_handler.c:277
#38 0x00005573f993589a in ExecMakeTableFunctionResult 
(setexpr=0x5573facfd8c8, econtext=0x5573facfd798,
--Type <RET> for more, q to quit, c to continue without paging--
     argContext=0x5573fad2ae60, expectedDesc=0x5573facfe130, 
randomAccess=false) at execSRF.c:234
#39 0x00005573f995299c in FunctionNext (node=0x5573facfd588) at 
nodeFunctionscan.c:94
#40 0x00005573f993735f in ExecScanFetch (node=0x5573facfd588, 
accessMtd=0x5573f99528e6 <FunctionNext>,
     recheckMtd=0x5573f9952ced <FunctionRecheck>) at execScan.c:131
#41 0x00005573f99373d8 in ExecScan (node=0x5573facfd588, 
accessMtd=0x5573f99528e6 <FunctionNext>,
     recheckMtd=0x5573f9952ced <FunctionRecheck>) at execScan.c:180
#42 0x00005573f9952d46 in ExecFunctionScan (pstate=0x5573facfd588) at 
nodeFunctionscan.c:269
#43 0x00005573f9932c7f in ExecProcNodeFirst (node=0x5573facfd588) at 
execProcnode.c:464
#44 0x00005573f9925df5 in ExecProcNode (node=0x5573facfd588)
     at ../../../src/include/executor/executor.h:274
#45 0x00005573f9928bf9 in ExecutePlan (estate=0x5573facfd360, 
planstate=0x5573facfd588,
     use_parallel_mode=false, operation=CMD_SELECT, sendTuples=true, 
numberTuples=0,
     direction=ForwardScanDirection, dest=0x5573fad8f6e0, 
execute_once=true) at execMain.c:1646
#46 0x00005573f992653d in standard_ExecutorRun (queryDesc=0x5573fad87f70,
     direction=ForwardScanDirection, count=0, execute_once=true) at 
execMain.c:363
#47 0x00005573f9926316 in ExecutorRun (queryDesc=0x5573fad87f70, 
direction=ForwardScanDirection,
     count=0, execute_once=true) at execMain.c:304
#48 0x00005573f9bfcb7d in PortalRunSelect (portal=0x5573fac6bbe0, 
forward=true, count=0,
     dest=0x5573fad8f6e0) at pquery.c:924
#49 0x00005573f9bfc7a5 in PortalRun (portal=0x5573fac6bbe0, 
count=9223372036854775807, isTopLevel=true,
     run_once=true, dest=0x5573fad8f6e0, altdest=0x5573fad8f6e0, 
qc=0x7ffe2e440a60) at pquery.c:768
#50 0x00005573f9bf5512 in exec_simple_query (
--Type <RET> for more, q to quit, c to continue without paging--
     query_string=0x5573fabea030 "SELECT * FROM 
check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 
2) = 2 OR (a * 2) = 102) AND upper(b) = ''1''');") at postgres.c:1274
#51 0x00005573f9bfa5b7 in PostgresMain (dbname=0x5573fab52240 "regression",
     username=0x5573fac27c98 "alena") at postgres.c:4680
#52 0x00005573f9bf137e in BackendMain (startup_data=0x7ffe2e440ce4 "", 
startup_data_len=4)
     at backend_startup.c:105
#53 0x00005573f9b06852 in postmaster_child_launch (child_type=B_BACKEND, 
startup_data=0x7ffe2e440ce4 "",
     startup_data_len=4, client_sock=0x7ffe2e440d30) at launch_backend.c:265
#54 0x00005573f9b0cd66 in BackendStartup (client_sock=0x7ffe2e440d30) at 
postmaster.c:3593
#55 0x00005573f9b09db1 in ServerLoop () at postmaster.c:1674
#56 0x00005573f9b09678 in PostmasterMain (argc=8, argv=0x5573fab500d0) 
at postmaster.c:1372
#57 0x00005573f99b5f79 in main (argc=8, argv=0x5573fab500d0) at main.c:197

Secondly,Isawdiffchangesinqueriesthatshowedcaseswherethe optimizerdid 
noteliminateoneofthe redundantexpressionsandprocessedbothof 
them.Thisindicatestheproblemthatthe optimizerhas notlearnedhow to 
handleitinallcases.IthinkI'll needtoaddsomecodetohandleit.

  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 ((thousand = ANY 
('{42,99}'::integer[])) OR (tenthous < 2))) OR (thousand = 41))
+         Recheck Cond: (((((thousand = 42) AND (thousand = ANY 
('{42,99}'::integer[]))) OR ((thousand = 99) AND (thousand = ANY 
('{42,99}'::integer[])))) OR (tenthous < 2)) OR (thousand = 41))
+         Filter: (((hundred = 42) AND ((((thousand = 42) OR (thousand = 
99)) AND (thousand = ANY ('{42,99}'::integer[]))) OR (tenthous < 2))) OR 
(thousand = 41))
           ->  BitmapOr
-               ->  BitmapAnd
-                     ->  Bitmap Index Scan on tenk1_hundred
-                           Index Cond: (hundred = 42)
+               ->  BitmapOr
                       ->  BitmapOr
                             ->  Bitmap Index Scan on tenk1_thous_tenthous
-                                 Index Cond: (thousand = ANY 
('{42,99}'::integer[]))
+                                 Index Cond: ((thousand = 42) AND 
(thousand = ANY ('{42,99}'::integer[])))
                             ->  Bitmap Index Scan on tenk1_thous_tenthous
-                                 Index Cond: (tenthous < 2)
+                                 Index Cond: ((thousand = 99) AND 
(thousand = ANY ('{42,99}'::integer[])))
+                     ->  Bitmap Index Scan on tenk1_thous_tenthous
+                           Index Cond: (tenthous < 2)
                 ->  Bitmap Index Scan on tenk1_thous_tenthous
                       Index Cond: (thousand = 41)
-(14 rows)
+(15 rows)

  SELECT count(*) FROM tenk1
    WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 
2) OR thousand = 41;
@@ -1986,20 +1987,21 @@
  EXPLAIN (COSTS OFF)
  SELECT count(*) FROM tenk1
    WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 
99 AND tenthous = 2);
-                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   Aggregate
     ->  Bitmap Heap Scan on tenk1
-         Recheck Cond: ((hundred = 42) AND ((thousand = ANY 
('{41,42}'::integer[])) OR ((thousand = 99) AND (tenthous = 2))))
-         ->  BitmapAnd
-               ->  Bitmap Index Scan on tenk1_hundred
-                     Index Cond: (hundred = 42)
+         Recheck Cond: ((((thousand = 42) AND (thousand = ANY 
('{41,42}'::integer[]))) OR ((thousand = 41) AND (thousand = ANY 
('{41,42}'::integer[])))) OR ((thousand = 99) AND (tenthous = 2)))
+         Filter: (hundred = 42)
+         ->  BitmapOr
                 ->  BitmapOr
                       ->  Bitmap Index Scan on tenk1_thous_tenthous
-                           Index Cond: (thousand = ANY 
('{41,42}'::integer[]))
+                           Index Cond: ((thousand = 42) AND (thousand = 
ANY ('{41,42}'::integer[])))
                       ->  Bitmap Index Scan on tenk1_thous_tenthous
-                           Index Cond: ((thousand = 99) AND (tenthous = 2))
-(11 rows)
+                           Index Cond: ((thousand = 41) AND (thousand = 
ANY ('{41,42}'::integer[])))
+               ->  Bitmap Index Scan on tenk1_thous_tenthous
+                     Index Cond: ((thousand = 99) AND (tenthous = 2))
+(12 rows)

  SELECT count(*) FROM tenk1
    WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 
99 AND tenthous = 2);
diff -U3 /home/alena/postgrespro5/src/test/regress/expected/inherit.out 
/home/alena/postgrespro5/src/test/regress/results/inherit.out
--- /home/alena/postgrespro5/src/test/regress/expected/inherit.out 
2024-06-20 12:28:52.324011724 +0300
+++ /home/alena/postgrespro5/src/test/regress/results/inherit.out 
2024-07-11 02:00:55.404006843 +0300
@@ -2126,7 +2126,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/postgrespro5/src/test/regress/expected/join.out 
/home/alena/postgrespro5/src/test/regress/results/join.out
--- /home/alena/postgrespro5/src/test/regress/expected/join.out 
2024-06-28 11:05:44.304135987 +0300
+++ /home/alena/postgrespro5/src/test/regress/results/join.out 
2024-07-11 02:00:58.152006921 +0300
@@ -4210,10 +4210,17 @@
  select * from tenk1 a join tenk1 b on
    (a.unique1 = 1 and b.unique1 = 2) or
    ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
-                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------
   Nested Loop
-   Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = 
ANY ('{3,7}'::integer[])) AND (b.hundred = 4)))
+   Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 
= 3) OR (a.unique2 = 7)) AND (a.unique2 = ANY ('{3,7}'::integer[])) AND 
(b.hundred = 4)))
+   ->  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[]))
     ->  Bitmap Heap Scan on tenk1 b
           Recheck Cond: ((unique1 = 2) OR (hundred = 4))
           ->  BitmapOr
@@ -4221,25 +4228,24 @@
                       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)
+(16 rows)

  SET enable_or_transformation = on;
  explain (costs off)
  select * from tenk1 a join tenk1 b on
    (a.unique1 = 1 and b.unique1 = 2) or
    ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
-                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------
   Nested Loop
-   Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = 
ANY ('{3,7}'::integer[])) AND (b.hundred = 4)))
+   Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 
= 3) OR (a.unique2 = 7)) AND (a.unique2 = ANY ('{3,7}'::integer[])) AND 
(b.hundred = 4)))
+   ->  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[]))
     ->  Bitmap Heap Scan on tenk1 b
           Recheck Cond: ((unique1 = 2) OR (hundred = 4))
           ->  BitmapOr
@@ -4247,37 +4253,29 @@
                       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)
+(16 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 = 3) OR ((a.unique1 = 
1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND 
(b.hundred = 4)))
+   Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 
= 3) OR (a.unique2 = 7)) AND (a.unique2 = ANY ('{3,7}'::integer[])) AND 
(b.hundred = 4)) OR (a.unique1 < 20) OR (a.unique1 = 3))
     ->  Seq Scan on tenk1 b
     ->  Materialize
           ->  Bitmap Heap Scan on tenk1 a
-               Recheck Cond: ((unique1 < 20) OR (unique1 = 3) OR 
(unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])))
+               Recheck Cond: ((unique1 = 1) OR (unique2 = ANY 
('{3,7}'::integer[])) OR (unique1 < 20) OR (unique1 = 3))
                 ->  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 < 20)
+                     ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 = 3)
  (15 rows)

Thirdly,Ihaveevidencethatthismayaffecttheunderestimationof power.I'll 
lookinto thisin detaillater.

diff -U3 
/home/alena/postgrespro5/src/test/regress/expected/stats_ext.out 
/home/alena/postgrespro5/src/test/regress/results/stats_ext.out
--- /home/alena/postgrespro5/src/test/regress/expected/stats_ext.out 
2024-06-28 11:05:44.304135987 +0300
+++ /home/alena/postgrespro5/src/test/regress/results/stats_ext.out 
2024-07-11 02:01:06.596007159 +0300
@@ -1156,19 +1156,19 @@
  SELECT * FROM check_estimated_rows('SELECT * FROM 
functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1''');
   estimated | actual
  -----------+--------
-         2 |    100
+         1 |    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
  -----------+--------
-         4 |    100
+         1 |    100
  (1 row)

  SELECT * FROM check_estimated_rows('SELECT * FROM 
functional_dependencies WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND 
(b = ''1'' OR b = ''2'')');
   estimated | actual
  -----------+--------
-         8 |    200
+         1 |    200
  (1 row)

  -- OR clauses referencing different attributes
@@ -1322,19 +1322,19 @@
  SELECT * FROM check_estimated_rows('SELECT * FROM 
functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1''');
   estimated | actual
  -----------+--------
-       100 |    100
+         2 |    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
  -----------+--------
-       100 |    100
+         2 |    100
  (1 row)

  SELECT * FROM check_estimated_rows('SELECT * FROM 
functional_dependencies WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND 
(b = ''1'' OR b = ''2'')');
   estimated | actual
  -----------+--------
-       200 |    200
+         8 |    200
  (1 row)

-- 
Regards,
Alena Rybakina
Postgres Professional:http://www.postgrespro.com
The Russian Postgres Company
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index 129fb447099..b2b457b614c 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -140,6 +140,33 @@ JumbleQuery(Query *query)
 	return jstate;
 }
 
+JumbleState *
+JumbleExpr(Expr *expr, uint64 *queryId)
+{
+	JumbleState *jstate = NULL;
+
+	Assert(queryId != NULL);
+
+	jstate = (JumbleState *) palloc(sizeof(JumbleState));
+
+	/* Set up workspace for query jumbling */
+	jstate->jumble = (unsigned char *) palloc(JUMBLE_SIZE);
+	jstate->jumble_len = 0;
+	jstate->clocations_buf_size = 32;
+	jstate->clocations = (LocationLen *)
+		palloc(jstate->clocations_buf_size * sizeof(LocationLen));
+	jstate->clocations_count = 0;
+	jstate->highest_extern_param_id = 0;
+
+	/* Compute query ID */
+	_jumbleNode(jstate, (Node *) expr);
+	*queryId = DatumGetUInt64(hash_any_extended(jstate->jumble,
+												jstate->jumble_len,
+												0));
+
+	return jstate;
+}
+
 /*
  * Enables query identifier computation.
  *
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 00cd7358ebb..0216d61b801 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -16,12 +16,15 @@
 #include "postgres.h"
 
 #include "catalog/pg_aggregate.h"
+#include "catalog/pg_operator.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_type.h"
 #include "commands/dbcommands.h"
+#include "common/hashfn.h"
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
+#include "nodes/queryjumble.h"
 #include "optimizer/optimizer.h"
 #include "parser/analyze.h"
 #include "parser/parse_agg.h"
@@ -41,6 +44,9 @@
 #include "utils/lsyscache.h"
 #include "utils/timestamp.h"
 #include "utils/xml.h"
+#include "utils/syscache.h"
+
+bool		enable_or_transformation = true;
 
 /* GUC parameters */
 bool		Transform_null_equals = false;
@@ -110,6 +116,415 @@ static Expr *make_distinct_op(ParseState *pstate, List *opname,
 static Node *make_nulltest_from_distinct(ParseState *pstate,
 										 A_Expr *distincta, Node *arg);
 
+typedef struct OrClauseGroupKey
+{
+	NodeTag	type;
+
+	Expr   *expr; /* Pointer to the expression tree which has been a source for
+					the hashkey value */
+	Oid		opno;
+	Oid		exprtype;
+} OrClauseGroupKey;
+
+typedef struct OrClauseGroupEntry
+{
+	OrClauseGroupKey	key;
+
+	List			   *consts;
+	List 			   *exprs;
+	FunctionCallInfo	fcinfo;
+} OrClauseGroupEntry;
+
+/*
+ * Hash function to find candidate clauses.
+ */
+static uint32
+orclause_hash(const void *data, Size keysize)
+{
+	OrClauseGroupKey   *key = (OrClauseGroupKey *) data;
+	uint64				exprHash;
+
+	Assert(keysize == sizeof(OrClauseGroupKey));
+	Assert(IsA(data, Invalid));
+
+	(void) JumbleExpr(key->expr, &exprHash);
+
+	return hash_combine((uint32) exprHash,
+						hash_combine((uint32) key->opno,
+									 (uint32) key->exprtype));
+}
+
+static void *
+orclause_keycopy(void *dest, const void *src, Size keysize)
+{
+	OrClauseGroupKey *src_key = (OrClauseGroupKey *) src;
+	OrClauseGroupKey *dst_key = (OrClauseGroupKey *) dest;
+
+	Assert(sizeof(OrClauseGroupKey) == keysize);
+	Assert(IsA(src, Invalid));
+
+	dst_key->type = T_Invalid;
+	dst_key->expr = src_key->expr;
+	dst_key->opno = src_key->opno;
+	dst_key->exprtype = src_key->exprtype;
+	return dst_key;
+}
+
+/*
+ * Dynahash match function to use in or_group_htab
+ */
+static int
+orclause_match(const void *data1, const void *data2, Size keysize)
+{
+	OrClauseGroupKey   *key1 = (OrClauseGroupKey *) data1;
+	OrClauseGroupKey   *key2 = (OrClauseGroupKey *) data2;
+
+	Assert(sizeof(OrClauseGroupKey) == keysize);
+	Assert(IsA(key1, Invalid));
+	Assert(IsA(key2, Invalid));
+
+	if (key1->opno == key2->opno &&
+		key1->exprtype == key2->exprtype &&
+		equal(key1->expr, key2->expr))
+		return 0;
+
+	return 1;
+}
+
+static FunctionCallInfo locfcinfo = NULL;
+
+static int
+saop_const_comparator(const ListCell *a, const ListCell *b)
+{
+	Node   *leftop = (Node *) lfirst(a);
+	Node   *rightop = (Node *) lfirst(b);
+
+	if (IsA(leftop, RelabelType))
+		leftop = (Node *) ((RelabelType *) leftop)->arg;
+
+	if (IsA(rightop, RelabelType))
+		rightop = (Node *) ((RelabelType *) rightop)->arg;
+
+	Assert(IsA(leftop, Const) && IsA(rightop, Const));
+
+	locfcinfo->args[0].value = ((Const *) leftop)->constvalue;
+	locfcinfo->args[0].isnull = false;
+	locfcinfo->args[1].value = ((Const *) rightop)->constvalue;
+	locfcinfo->args[1].isnull = false;
+	return DatumGetInt32(FunctionCallInvoke(locfcinfo));
+}
+
+static List *
+saop_delete_duplicates(ParseState *pstate, OrClauseGroupEntry *entry,
+					   Oid scalar_type)
+{
+	ListCell   *lc;
+	List	   *result;
+
+	locfcinfo = entry->fcinfo;
+	list_sort(entry->consts, saop_const_comparator);
+
+	result = list_make1(linitial(entry->consts));
+	for_each_from(lc, entry->consts, 1)
+	{
+		Node *node = (Node *) lfirst(lc);
+
+		if (equal(llast(result), node))
+			continue;
+
+		node = coerce_to_common_type(pstate, node, scalar_type, "IN");
+		result = lappend(result, node);
+	}
+
+	return result;
+}
+
+/*
+ * TransformOrExprToANY -
+ *	  Discover the args of an OR expression and try to group similar OR
+ *	  expressions to an ANY operation.
+ *	  Transformation must be already done on input args list before the call.
+ *	  Transformation groups two-sided equality operations. One side of such an
+ *	  operation must be plain constant or constant expression. The other side of
+ *	  the clause must be a variable expression without volatile functions.
+ *	  The grouping technique is based on an equivalence of variable sides of the
+ *	  expression: using queryId and equal() routine, it groups constant sides of
+ *	  similar clauses into an array. After the grouping procedure, each couple
+ *	  ('variable expression' and 'constant array') form a new SAOP operation,
+ *	  which is added to the args list of the returning expression.
+ *
+ *	  NOTE: function returns OR BoolExpr if more than one clause are detected in
+ *	  the final args list, or ScalarArrayOpExpr if all args were grouped into
+ *	  the single SAOP expression.
+ */
+static Node *
+TransformOrExprToANY(ParseState *pstate, List *args, int location)
+{
+	List				   *or_list = NIL;
+	List				   *groups = NIL;
+	List 				   *unconvinient_ors = NIL;
+	ListCell			   *lc;
+	HASHCTL					info;
+	HTAB 				   *or_group_htab = NULL;
+	int 					len_ors = list_length(args);
+	OrClauseGroupEntry	   *entry = NULL;
+
+	Assert(enable_or_transformation && len_ors > 1);
+
+	MemSet(&info, 0, sizeof(info));
+	info.keysize = sizeof(OrClauseGroupKey);
+	info.entrysize = sizeof(OrClauseGroupEntry);
+	info.hash = orclause_hash;
+	info.keycopy = orclause_keycopy;
+	info.match = orclause_match;
+	or_group_htab = hash_create("OR Groups",
+								len_ors,
+								&info,
+								HASH_ELEM | HASH_FUNCTION | HASH_COMPARE | HASH_KEYCOPY);
+
+	foreach(lc, args)
+	{
+		Node				   *orqual = lfirst(lc);
+		Node				   *const_expr;
+		Node				   *nconst_expr;
+		OrClauseGroupKey		hashkey;
+		bool					found;
+		Oid						opno;
+		Oid						exprtype;
+		Node				   *leftop, *rightop;
+		TypeCacheEntry		   *typentry;
+
+		if (!IsA(orqual, OpExpr))
+		{
+			unconvinient_ors = lappend(unconvinient_ors, orqual);
+			continue;
+		}
+
+		opno = ((OpExpr *) orqual)->opno;
+		if (get_op_rettype(opno) != BOOLOID)
+		{
+			/* Only operator returning boolean suits OR -> ANY transformation */
+			unconvinient_ors = lappend(unconvinient_ors, 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.
+		 */
+		leftop = get_leftop(orqual);
+		if (IsA(leftop, RelabelType))
+			leftop = (Node *) ((RelabelType *) leftop)->arg;
+		rightop = get_rightop(orqual);
+		if (IsA(rightop, RelabelType))
+			rightop = (Node *) ((RelabelType *) rightop)->arg;
+
+		if (IsA(leftop, Const))
+		{
+			opno = get_commutator(opno);
+
+			if (!OidIsValid(opno))
+			{
+				/* commutator doesn't exist, we can't reverse the order */
+				unconvinient_ors = lappend(unconvinient_ors, orqual);
+				continue;
+			}
+
+			nconst_expr = get_rightop(orqual);
+			const_expr = get_leftop(orqual);
+		}
+		else if (IsA(rightop, Const))
+		{
+			const_expr = get_rightop(orqual);
+			nconst_expr = get_leftop(orqual);
+		}
+		else
+		{
+			unconvinient_ors = lappend(unconvinient_ors, orqual);
+			continue;
+		}
+
+		/*
+		 * Transformation only works with both side type is not
+		 * { array | composite | domain | record }.
+		 * Also, forbid it for volatile expressions.
+		 */
+		exprtype = exprType(nconst_expr);
+		if (type_is_rowtype(exprType(const_expr)) ||
+			type_is_rowtype(exprtype) ||
+			contain_volatile_functions((Node *) nconst_expr))
+		{
+			unconvinient_ors = lappend(unconvinient_ors, orqual);
+			continue;
+		}
+
+		typentry = lookup_type_cache(exprtype, TYPECACHE_CMP_PROC_FINFO);
+
+		/*
+		 * Within this transformation we remove duplicates. To avoid
+		 * quadratic behavior we need to sort clauses after making a list of
+		 * elements. So, need sort operator to implement that.
+		 */
+		if (!OidIsValid(typentry->cmp_proc_finfo.fn_oid))
+		{
+			unconvinient_ors = lappend(unconvinient_ors, orqual);
+			continue;
+		}
+
+		/*
+		* At this point we definitely have a transformable clause.
+		* Classify it and add into specific group of clauses, or create new
+		* group.
+		*/
+		hashkey.type = T_Invalid;
+		hashkey.expr = (Expr *) nconst_expr;
+		hashkey.opno = opno;
+		hashkey.exprtype = exprtype;
+		entry = hash_search(or_group_htab, &hashkey, HASH_ENTER, &found);
+
+		if (unlikely(found))
+		{
+			entry->consts = lappend(entry->consts, const_expr);
+			entry->exprs = lappend(entry->exprs, orqual);
+		}
+		else
+		{
+			entry->consts = list_make1(const_expr);
+			entry->exprs = list_make1(orqual);
+
+			/* Prepare funcall for sort comparator */
+			entry->fcinfo =
+						(FunctionCallInfo) palloc(SizeForFunctionCallInfo(2));
+			InitFunctionCallInfoData(*(entry->fcinfo),
+									 &typentry->cmp_proc_finfo, 2,
+									 typentry->typcollation, NULL, NULL);
+
+			/*
+			 * Add the entry to the list. It is needed exclusively to manage the
+			 * problem with the order of transformed clauses in explain.
+			 * Hash value can depend on the platform and version. Hence,
+			 * sequental scan of the hash table would prone to change the order
+			 * of clauses in lists and, as a result, break regression tests
+			 * accidentially.
+			 */
+			groups = lappend(groups, entry);
+		}
+	}
+
+	/* 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, groups)
+	{
+		Oid				    scalar_type;
+		Oid					array_type;
+
+		if (!IsA(lfirst(lc), Invalid))
+		{
+			unconvinient_ors = lappend(unconvinient_ors, lfirst(lc));
+			continue;
+		}
+
+		entry = (OrClauseGroupEntry *) lfirst(lc);
+
+		Assert(list_length(entry->consts) > 0);
+		Assert(list_length(entry->exprs) == list_length(entry->consts));
+
+		if (list_length(entry->consts) == 1)
+		{
+			/*
+			 * Only one element returns origin expression into the BoolExpr args
+			 * list unchanged.
+			 */
+			list_free(entry->consts);
+			unconvinient_ors = list_concat(unconvinient_ors, entry->exprs);
+			continue;
+		}
+
+		/*
+		 * Do the transformation.
+		 */
+
+		scalar_type = entry->key.exprtype;
+		array_type = OidIsValid(scalar_type) ? get_array_type(scalar_type) :
+											   InvalidOid;
+
+		if (OidIsValid(array_type))
+		{
+			/*
+			 * OK: coerce all the right-hand non-Var inputs to the common
+			 * type and build an ArrayExpr for them.
+			 */
+			List			   *aexprs = NIL;
+			ArrayExpr		   *newa = NULL;
+			ScalarArrayOpExpr  *saopexpr = NULL;
+			HeapTuple			opertup;
+			Form_pg_operator	operform;
+			List			   *namelist = NIL;
+
+			aexprs = saop_delete_duplicates(pstate, entry, scalar_type);
+
+			newa = makeNode(ArrayExpr);
+			/* array_collid will be set by parse_collate.c */
+			newa->element_typeid = scalar_type;
+			newa->array_typeid = array_type;
+			newa->multidims = false;
+			newa->elements = aexprs;
+			newa->location = -1;
+
+			opertup = SearchSysCache1(OPEROID,
+									  ObjectIdGetDatum(entry->key.opno));
+			if (!HeapTupleIsValid(opertup))
+				elog(ERROR, "cache lookup failed for operator %u",
+					 entry->key.opno);
+
+			operform = (Form_pg_operator) GETSTRUCT(opertup);
+			if (!OperatorIsVisible(entry->key.opno))
+				namelist = lappend(namelist, makeString(get_namespace_name(operform->oprnamespace)));
+
+			namelist = lappend(namelist, makeString(pstrdup(NameStr(operform->oprname))));
+			ReleaseSysCache(opertup);
+
+			saopexpr =
+				(ScalarArrayOpExpr *)
+					make_scalar_array_op(pstate,
+										 namelist,
+										 true,
+										 (Node *) entry->key.expr,
+										 (Node *) newa,
+										 -1);
+
+			or_list = lappend(or_list, makeBoolExpr(AND_EXPR, list_make2(makeBoolExpr(OR_EXPR, entry->exprs, location), saopexpr), -1));
+		}
+		else
+		{
+			/*
+			 * If the const node (right side of operator expression) 's type
+			 *  don't have “true” array type, then we cannnot do the transformation.
+			 * We simply concatenate the expression node.
+			 *
+			 */
+			list_free(entry->consts);
+			unconvinient_ors = list_concat(unconvinient_ors, entry->exprs);
+		}
+	}
+	if(unconvinient_ors != NIL)
+		or_list = lappend(or_list, list_length(unconvinient_ors) > 1 ? makeBoolExpr(OR_EXPR, unconvinient_ors, location) : linitial(unconvinient_ors));
+	hash_destroy(or_group_htab);
+	list_free(groups);
+
+	/* One more trick: assemble correct clause */
+	return (Node *) ((list_length(or_list) > 1) ?
+					 makeBoolExpr(OR_EXPR, or_list, location) :
+					 linitial(or_list));
+}
 
 /*
  * transformExpr -
@@ -1432,6 +1847,11 @@ transformBoolExpr(ParseState *pstate, BoolExpr *a)
 		args = lappend(args, arg);
 	}
 
+	/* Make an attempt to group similar OR clauses into ANY operation */
+	if (enable_or_transformation && a->boolop == OR_EXPR &&
+		list_length(args) >0)
+		return TransformOrExprToANY(pstate, args, a->location);
+
 	return (Node *) makeBoolExpr(a->boolop, args, a->location);
 }
 
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index 46c258be282..f5da097fcdf 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1007,6 +1007,17 @@ struct config_bool ConfigureNamesBool[] =
 		true,
 		NULL, NULL, NULL
 	},
+	{
+		{"enable_or_transformation", PGC_USERSET, QUERY_TUNING_OTHER,
+			gettext_noop("Transform a sequence of OR clauses to an array expression."),
+			gettext_noop("The planner will replace expression like 'x=c1 OR x=c2 ..'"
+						 "to the expression 'x = ANY(ARRAY[c1,c2,..])'"),
+			GUC_EXPLAIN
+		},
+		&enable_or_transformation,
+		true,
+		NULL, NULL, NULL
+	},
 	{
 		/*
 		 * Not for general use --- used by SET SESSION AUTHORIZATION and SET
diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h
index f1c55c8067f..a9ae048af52 100644
--- a/src/include/nodes/queryjumble.h
+++ b/src/include/nodes/queryjumble.h
@@ -65,6 +65,7 @@ extern PGDLLIMPORT int compute_query_id;
 
 extern const char *CleanQuerytext(const char *query, int *location, int *len);
 extern JumbleState *JumbleQuery(Query *query);
+extern JumbleState *JumbleExpr(Expr *expr, uint64 *queryId);
 extern void EnableQueryId(void);
 
 extern PGDLLIMPORT bool query_id_enabled;
diff --git a/src/include/optimizer/optimizer.h b/src/include/optimizer/optimizer.h
index 7b63c5cf718..35ab5775019 100644
--- a/src/include/optimizer/optimizer.h
+++ b/src/include/optimizer/optimizer.h
@@ -50,6 +50,7 @@ struct PlannedStmt;
 struct ParamListInfoData;
 struct HeapTupleData;
 
+extern PGDLLIMPORT bool enable_or_transformation;
 
 /* in path/clausesel.c: */
 
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 6b16c3a6769..ccf280001a0 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4210,10 +4210,10 @@ explain (costs off)
 select * from tenk1 a join tenk1 b on
   (a.unique1 = 1 and b.unique1 = 2) or
   ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
-                                                      QUERY PLAN                                                      
-----------------------------------------------------------------------------------------------------------------------
+                                                       QUERY PLAN                                                       
+------------------------------------------------------------------------------------------------------------------------
  Nested Loop
-   Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR (a.unique2 = 7)) AND (b.hundred = 4)))
+   Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4)))
    ->  Bitmap Heap Scan on tenk1 b
          Recheck Cond: ((unique1 = 2) OR (hundred = 4))
          ->  BitmapOr
@@ -4223,16 +4223,64 @@ select * from tenk1 a join tenk1 b on
                      Index Cond: (hundred = 4)
    ->  Materialize
          ->  Bitmap Heap Scan on tenk1 a
-               Recheck Cond: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
+               Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])))
                ->  BitmapOr
                      ->  Bitmap Index Scan on tenk1_unique1
                            Index Cond: (unique1 = 1)
                      ->  Bitmap Index Scan on tenk1_unique2
-                           Index Cond: (unique2 = 3)
+                           Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+(17 rows)
+
+SET enable_or_transformation = on;
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+  (a.unique1 = 1 and b.unique1 = 2) or
+  ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+                                                       QUERY PLAN                                                       
+------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4)))
+   ->  Bitmap Heap Scan on tenk1 b
+         Recheck Cond: ((unique1 = 2) OR (hundred = 4))
+         ->  BitmapOr
+               ->  Bitmap Index Scan on tenk1_unique1
+                     Index Cond: (unique1 = 2)
+               ->  Bitmap Index Scan on tenk1_hundred
+                     Index Cond: (hundred = 4)
+   ->  Materialize
+         ->  Bitmap Heap Scan on tenk1 a
+               Recheck Cond: ((unique1 = 1) OR (unique2 = ANY ('{3,7}'::integer[])))
+               ->  BitmapOr
+                     ->  Bitmap Index Scan on tenk1_unique1
+                           Index Cond: (unique1 = 1)
                      ->  Bitmap Index Scan on tenk1_unique2
-                           Index Cond: (unique2 = 7)
-(19 rows)
+                           Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+(17 rows)
+
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+  (a.unique1 < 20 or a.unique1 = 3 or a.unique1 = 1 and b.unique1 = 2) or
+  ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+                                                                          QUERY PLAN                                                                           
+---------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+   Join Filter: ((a.unique1 < 20) OR (a.unique1 = 3) OR ((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY ('{3,7}'::integer[])) 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 = ANY ('{3,7}'::integer[])))
+               ->  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[]))
+(15 rows)
 
+RESET enable_or_transformation;
 --
 -- test placement of movable quals in a parameterized join tree
 --
diff --git a/src/test/regress/expected/partition_prune.out b/src/test/regress/expected/partition_prune.out
index 7ca98397aec..9c2a4d75619 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -82,25 +82,47 @@ explain (costs off) select * from lp where a is null;
 (2 rows)
 
 explain (costs off) select * from lp where a = 'a' or a = 'c';
-                        QUERY PLAN                        
-----------------------------------------------------------
+                  QUERY PLAN                   
+-----------------------------------------------
  Append
    ->  Seq Scan on lp_ad lp_1
-         Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+         Filter: (a = ANY ('{a,c}'::bpchar[]))
    ->  Seq Scan on lp_bc lp_2
-         Filter: ((a = 'a'::bpchar) OR (a = 'c'::bpchar))
+         Filter: (a = ANY ('{a,c}'::bpchar[]))
 (5 rows)
 
 explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
-                                   QUERY PLAN                                   
---------------------------------------------------------------------------------
+                             QUERY PLAN                              
+---------------------------------------------------------------------
  Append
    ->  Seq Scan on lp_ad lp_1
-         Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+         Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
    ->  Seq Scan on lp_bc lp_2
-         Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+         Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
 (5 rows)
 
+SET enable_or_transformation = on;
+explain (costs off) select * from lp where a = 'a' or a = 'c';
+                  QUERY PLAN                   
+-----------------------------------------------
+ Append
+   ->  Seq Scan on lp_ad lp_1
+         Filter: (a = ANY ('{a,c}'::bpchar[]))
+   ->  Seq Scan on lp_bc lp_2
+         Filter: (a = ANY ('{a,c}'::bpchar[]))
+(5 rows)
+
+explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
+                             QUERY PLAN                              
+---------------------------------------------------------------------
+ Append
+   ->  Seq Scan on lp_ad lp_1
+         Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
+   ->  Seq Scan on lp_bc lp_2
+         Filter: ((a IS NOT NULL) AND (a = ANY ('{a,c}'::bpchar[])))
+(5 rows)
+
+RESET enable_or_transformation;
 explain (costs off) select * from lp where a <> 'g';
              QUERY PLAN             
 ------------------------------------
@@ -515,10 +537,10 @@ explain (costs off) select * from rlp where a <= 31;
 (27 rows)
 
 explain (costs off) select * from rlp where a = 1 or a = 7;
-           QUERY PLAN           
---------------------------------
+                QUERY PLAN                
+------------------------------------------
  Seq Scan on rlp2 rlp
-   Filter: ((a = 1) OR (a = 7))
+   Filter: (a = ANY ('{1,7}'::integer[]))
 (2 rows)
 
 explain (costs off) select * from rlp where a = 1 or b = 'ab';
@@ -596,13 +618,13 @@ explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
 
 -- where clause contradicts sub-partition's constraint
 explain (costs off) select * from rlp where a = 20 or a = 40;
-               QUERY PLAN               
-----------------------------------------
+                    QUERY PLAN                    
+--------------------------------------------------
  Append
    ->  Seq Scan on rlp4_1 rlp_1
-         Filter: ((a = 20) OR (a = 40))
+         Filter: (a = ANY ('{20,40}'::integer[]))
    ->  Seq Scan on rlp5_default rlp_2
-         Filter: ((a = 20) OR (a = 40))
+         Filter: (a = ANY ('{20,40}'::integer[]))
 (5 rows)
 
 explain (costs off) select * from rlp3 where a = 20;   /* empty */
@@ -671,6 +693,163 @@ explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a =
          Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
 (11 rows)
 
+SET enable_or_transformation = on;
+explain (costs off) select * from rlp where a = 1 or a = 7;
+                QUERY PLAN                
+------------------------------------------
+ Seq Scan on rlp2 rlp
+   Filter: (a = ANY ('{1,7}'::integer[]))
+(2 rows)
+
+explain (costs off) select * from rlp where a = 1 or b = 'ab';
+                      QUERY PLAN                       
+-------------------------------------------------------
+ Append
+   ->  Seq Scan on rlp1 rlp_1
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp2 rlp_2
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp3abcd rlp_3
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp4_1 rlp_4
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp4_2 rlp_5
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp4_default rlp_6
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp5_1 rlp_7
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp5_default rlp_8
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp_default_10 rlp_9
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp_default_30 rlp_10
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp_default_null rlp_11
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+   ->  Seq Scan on rlp_default_default rlp_12
+         Filter: ((a = 1) OR ((b)::text = 'ab'::text))
+(25 rows)
+
+explain (costs off) select * from rlp where a > 20 and a < 27;
+               QUERY PLAN                
+-----------------------------------------
+ Append
+   ->  Seq Scan on rlp4_1 rlp_1
+         Filter: ((a > 20) AND (a < 27))
+   ->  Seq Scan on rlp4_2 rlp_2
+         Filter: ((a > 20) AND (a < 27))
+(5 rows)
+
+explain (costs off) select * from rlp where a = 29;
+          QUERY PLAN          
+------------------------------
+ Seq Scan on rlp4_default rlp
+   Filter: (a = 29)
+(2 rows)
+
+explain (costs off) select * from rlp where a >= 29;
+                 QUERY PLAN                  
+---------------------------------------------
+ Append
+   ->  Seq Scan on rlp4_default rlp_1
+         Filter: (a >= 29)
+   ->  Seq Scan on rlp5_1 rlp_2
+         Filter: (a >= 29)
+   ->  Seq Scan on rlp5_default rlp_3
+         Filter: (a >= 29)
+   ->  Seq Scan on rlp_default_30 rlp_4
+         Filter: (a >= 29)
+   ->  Seq Scan on rlp_default_default rlp_5
+         Filter: (a >= 29)
+(11 rows)
+
+explain (costs off) select * from rlp where a < 1 or (a > 20 and a < 25);
+                      QUERY PLAN                      
+------------------------------------------------------
+ Append
+   ->  Seq Scan on rlp1 rlp_1
+         Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
+   ->  Seq Scan on rlp4_1 rlp_2
+         Filter: ((a < 1) OR ((a > 20) AND (a < 25)))
+(5 rows)
+
+explain (costs off) select * from rlp where a = 20 or a = 40;
+                    QUERY PLAN                    
+--------------------------------------------------
+ Append
+   ->  Seq Scan on rlp4_1 rlp_1
+         Filter: (a = ANY ('{20,40}'::integer[]))
+   ->  Seq Scan on rlp5_default rlp_2
+         Filter: (a = ANY ('{20,40}'::integer[]))
+(5 rows)
+
+explain (costs off) select * from rlp3 where a = 20;   /* empty */
+        QUERY PLAN        
+--------------------------
+ Result
+   One-Time Filter: false
+(2 rows)
+
+explain (costs off) select * from rlp where a > 1 and a = 10;	/* only default */
+            QUERY PLAN            
+----------------------------------
+ Seq Scan on rlp_default_10 rlp
+   Filter: ((a > 1) AND (a = 10))
+(2 rows)
+
+explain (costs off) select * from rlp where a > 1 and a >=15;	/* rlp3 onwards, including default */
+                  QUERY PLAN                  
+----------------------------------------------
+ Append
+   ->  Seq Scan on rlp3abcd rlp_1
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp3efgh rlp_2
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp3nullxy rlp_3
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp3_default rlp_4
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp4_1 rlp_5
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp4_2 rlp_6
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp4_default rlp_7
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp5_1 rlp_8
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp5_default rlp_9
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp_default_30 rlp_10
+         Filter: ((a > 1) AND (a >= 15))
+   ->  Seq Scan on rlp_default_default rlp_11
+         Filter: ((a > 1) AND (a >= 15))
+(23 rows)
+
+explain (costs off) select * from rlp where a = 1 and a = 3;	/* empty */
+        QUERY PLAN        
+--------------------------
+ Result
+   One-Time Filter: false
+(2 rows)
+
+explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a = 15);
+                            QUERY PLAN                             
+-------------------------------------------------------------------
+ Append
+   ->  Seq Scan on rlp2 rlp_1
+         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+   ->  Seq Scan on rlp3abcd rlp_2
+         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+   ->  Seq Scan on rlp3efgh rlp_3
+         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+   ->  Seq Scan on rlp3nullxy rlp_4
+         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+   ->  Seq Scan on rlp3_default rlp_5
+         Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
+(11 rows)
+
+RESET enable_or_transformation;
 -- multi-column keys
 create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
 create table mc3p_default partition of mc3p default;
@@ -2072,10 +2251,10 @@ explain (costs off) select * from hp where a = 1 and b = 'abcde';
 
 explain (costs off) select * from hp where a = 1 and b = 'abcde' and
   (c = 2 or c = 3);
-                              QUERY PLAN                              
-----------------------------------------------------------------------
+                                   QUERY PLAN                                   
+--------------------------------------------------------------------------------
  Seq Scan on hp2 hp
-   Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3)))
+   Filter: ((c = ANY ('{2,3}'::integer[])) AND (a = 1) AND (b = 'abcde'::text))
 (2 rows)
 
 drop table hp2;
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index 8c4da955084..7678744181c 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -1322,19 +1322,19 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND b = ''1''');
  estimated | actual 
 -----------+--------
-        99 |    100
+       100 |    100
 (1 row)
 
 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 51) AND (b = ''1'' OR b = ''2'')');
  estimated | actual 
 -----------+--------
-        99 |    100
+       100 |    100
 (1 row)
 
 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')');
  estimated | actual 
 -----------+--------
-       197 |    200
+       200 |    200
 (1 row)
 
 -- OR clauses referencing different attributes are incompatible
@@ -1664,19 +1664,19 @@ SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE
 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = ''1''');
  estimated | actual 
 -----------+--------
-        99 |    100
+       100 |    100
 (1 row)
 
 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 102) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
  estimated | actual 
 -----------+--------
-        99 |    100
+       100 |    100
 (1 row)
 
 SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies WHERE ((a * 2) = 2 OR (a * 2) = 4 OR (a * 2) = 102 OR (a * 2) = 104) AND (upper(b) = ''1'' OR upper(b) = ''2'')');
  estimated | actual 
 -----------+--------
-       197 |    200
+       200 |    200
 (1 row)
 
 -- OR clauses referencing different attributes
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index dbfd0c13d46..6123351a9a8 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -147,6 +147,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_memoize                 | on
  enable_mergejoin               | on
  enable_nestloop                | on
+ enable_or_transformation       | on
  enable_parallel_append         | on
  enable_parallel_hash           | on
  enable_partition_pruning       | on
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index e296891cab8..057c6d2bda1 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -738,6 +738,41 @@ SELECT count(*) FROM tenk1
 SELECT count(*) FROM tenk1
   WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
 
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+  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 hundred = 42 AND (thousand < 42 OR thousand < 99 OR 43 > thousand OR 42 > thousand);
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+  WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+SELECT count(*) FROM tenk1
+  WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+  WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41;
+SELECT count(*) FROM tenk1
+  WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+  WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2);
+SELECT count(*) FROM tenk1
+  WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2);
+RESET enable_or_transformation;
+
 --
 -- Check behavior with duplicate index column contents
 --
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 8bfe3b7ba67..c77b5c50f01 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1408,6 +1408,16 @@ explain (costs off)
 select * from tenk1 a join tenk1 b on
   (a.unique1 = 1 and b.unique1 = 2) or
   ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+SET enable_or_transformation = on;
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+  (a.unique1 = 1 and b.unique1 = 2) or
+  ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+  (a.unique1 < 20 or a.unique1 = 3 or a.unique1 = 1 and b.unique1 = 2) or
+  ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+RESET enable_or_transformation;
 
 --
 -- test placement of movable quals in a parameterized join tree
diff --git a/src/test/regress/sql/partition_prune.sql b/src/test/regress/sql/partition_prune.sql
index a09b27d820c..9717c8c835c 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 enable_or_transformation = on;
+explain (costs off) select * from lp where a = 'a' or a = 'c';
+explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c');
+RESET enable_or_transformation;
+
 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 enable_or_transformation = on;
+explain (costs off) select * from rlp where a = 1 or a = 7;
+explain (costs off) select * from rlp where a = 1 or b = 'ab';
+explain (costs off) select * from rlp where a > 20 and a < 27;
+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 enable_or_transformation;
+
 -- multi-column keys
 create table mc3p (a int, b int, c int) partition by range (a, abs(b), c);
 create table mc3p_default partition of mc3p default;
diff --git a/src/test/regress/sql/tidscan.sql b/src/test/regress/sql/tidscan.sql
index 313e0fb9b67..0499bedb9eb 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 enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+RESET enable_or_transformation;
+
 -- ctid = ScalarArrayOp - implemented as tidscan
 EXPLAIN (COSTS OFF)
 SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 61ad417cde6..94450939331 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1714,6 +1714,8 @@ NumericVar
 OM_uint32
 OP
 OSAPerGroupState
+OrClauseGroupEntry
+OrClauseGroupKey
 OSAPerQueryState
 OSInfo
 OSSLCipher