On 28/11/2023 04:03, Robert Haas wrote:
On Mon, Nov 27, 2023 at 3:02 AM Andrei Lepikhov
<a.lepik...@postgrespro.ru> wrote:
On 25/11/2023 08:23, Alexander Korotkov wrote:
I think patch certainly gets better in this aspect. One thing I can't
understand is why do we use home-grown code for resolving
hash-collisions. You can just define custom hash and match functions
in HASHCTL. Even if we need to avoid repeated JumbleExpr calls, we
still can save pre-calculated hash value into hash entry and use
custom hash and match. This doesn't imply us to write our own
collision-resolving code.
Thanks, it was an insightful suggestion.
I implemented it, and the code has become shorter (see attachment).
Neither the code comments nor the commit message really explain the
design idea here. That's unfortunate, principally because it makes
review difficult.
Yeah, it is still an issue. We will think about how to improve this; any
suggestions are welcome.
I'm very skeptical about the idea of using JumbleExpr for any part of
this. It seems fairly expensive, and it might produce false matches.
If expensive is OK, then why not just use equal()? If it's not, then
this probably isn't really OK either. But in any case there should be
comments explaining why this strategy was chosen.
We used the equal() routine without hashing in earlier versions. Hashing
resolves issues with many different OR clauses. Is it expensive? Maybe,
but we assume this transformation should be applied to simple enough
expressions.
The use of op_mergejoinable() seems pretty random to me. Why should we
care about that? If somebody writes a<1 or a<2 or a<3 or a<4, you can
transform that to a<any(array[1,2,3,4]) if you want. It might not be a
good idea, but I think it's a legal transformation.
You are right. The only reason was to obtain a working patch to
benchmark and look for corner cases. We would rewrite that place but
still live with the equivalence operator.
The reader shouldn't be left to guess whether a rule like this was made for
reasons of correctness or for reasons of efficiency or something else.
Looking further, I see that the reason for this is likely that the
operator for the transformation result is constructing using
list_make1(makeString((char *) "=")), but trying to choose an operator
based on the operator name is, I think, pretty clearly unacceptable.
Yes, it was a big mistake. It is fixed in the new version (I guess).
I am extremely dubious about the use of select_common_type() here. Why
not do this only when the types already match exactly? Maybe the
concern is unknown literals, but perhaps that should be handled in
some other way. If you do this kind of thing, you need to justify why
it can't fail or produce wrong answers.
Perhaps. We implemented your approach in the next version. At least we
could see consequences.
Honestly, it seems very hard to avoid the conclusion that this
transformation is being done at too early a stage. Parse analysis is
not the time to try to do query optimization. I can't really believe
that there's a way to produce a committable patch along these lines.
Ideally, a transformation like this should be done after we know what
plan shape we're using (or considering using), so that we can make
cost-based decisions about whether to transform or not. But at the
very least it should happen somewhere in the planner. There's really
no justification for parse analysis rewriting the SQL that the user
entered.
Here, we assume that array operation is generally better than many ORs.
As a result, it should be more effective to make OR->ANY transformation
in the parser (it is a relatively lightweight operation here) and, as a
second phase, decompose that in the optimizer.
We implemented earlier prototypes in different places of the optimizer,
and I'm convinced that only this approach resolves the issues we found.
Does this approach look weird? Maybe. We can debate it in this thread.
--
regards,
Andrei Lepikhov
Postgres Professional
From a5f8eba522ff907f7a3fffb0635b61d38933e385 Mon Sep 17 00:00:00 2001
From: "Andrey V. Lepikhov" <a.lepik...@postgrespro.ru>
Date: Thu, 23 Nov 2023 16:00:13 +0700
Subject: [PATCH] Transform OR clause to ANY expressions.
Replace (X=N1) OR (X=N2) ... with X = ANY(N1, N2) on the preliminary stage of
optimization when we are still working with a tree expression.
Sometimes it can lead to not optimal plan. But we think it is better to have
array of elements instead of a lot of OR clauses. Here is a room for further
optimizations on decomposing that array into more optimal parts.
---
.../postgres_fdw/expected/postgres_fdw.out | 8 +-
src/backend/nodes/queryjumblefuncs.c | 30 ++
src/backend/parser/parse_expr.c | 289 +++++++++++++++++-
src/backend/utils/misc/guc_tables.c | 10 +
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/nodes/queryjumble.h | 1 +
src/include/parser/parse_expr.h | 1 +
src/test/regress/expected/create_index.out | 141 +++++++--
src/test/regress/expected/guc.out | 3 +-
src/test/regress/expected/inherit.out | 2 +-
src/test/regress/expected/join.out | 62 +++-
src/test/regress/expected/partition_join.out | 2 +-
src/test/regress/expected/partition_prune.out | 215 +++++++++++--
src/test/regress/expected/rules.out | 4 +-
src/test/regress/expected/select.out | 6 +-
src/test/regress/expected/stats_ext.out | 12 +-
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/expected/tidscan.out | 23 +-
src/test/regress/sql/create_index.sql | 32 ++
src/test/regress/sql/join.sql | 10 +
src/test/regress/sql/partition_prune.sql | 22 ++
src/test/regress/sql/tidscan.sql | 6 +
src/tools/pgindent/typedefs.list | 2 +
23 files changed, 819 insertions(+), 66 deletions(-)
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out
b/contrib/postgres_fdw/expected/postgres_fdw.out
index 22cae37a1e..fdfecd51c7 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -8537,18 +8537,18 @@ insert into utrtest values (2, 'qux');
-- Check case where the foreign partition is a subplan target rel
explain (verbose, costs off)
update utrtest set a = 1 where a = 1 or a = 2 returning *;
- QUERY PLAN
-----------------------------------------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------
Update on public.utrtest
Output: utrtest_1.a, utrtest_1.b
Foreign Update on public.remp utrtest_1
Update on public.locp utrtest_2
-> Append
-> Foreign Update on public.remp utrtest_1
- Remote SQL: UPDATE public.loct SET a = 1 WHERE (((a = 1) OR (a
= 2))) RETURNING a, b
+ Remote SQL: UPDATE public.loct SET a = 1 WHERE ((a = ANY
('{1,2}'::integer[]))) RETURNING a, b
-> Seq Scan on public.locp utrtest_2
Output: 1, utrtest_2.tableoid, utrtest_2.ctid, NULL::record
- Filter: ((utrtest_2.a = 1) OR (utrtest_2.a = 2))
+ Filter: (utrtest_2.a = ANY ('{1,2}'::integer[]))
(10 rows)
-- The new values are concatenated with ' triggered !'
diff --git a/src/backend/nodes/queryjumblefuncs.c
b/src/backend/nodes/queryjumblefuncs.c
index 281907a4d8..99207a8670 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -135,6 +135,36 @@ JumbleQuery(Query *query)
return jstate;
}
+JumbleState *
+JumbleExpr(Expr *expr, uint64 *queryId)
+{
+ JumbleState *jstate = NULL;
+
+ Assert(queryId != NULL);
+
+ jstate = (JumbleState *) palloc(sizeof(JumbleState));
+
+ /* Set up workspace for query jumbling */
+ jstate->jumble = (unsigned char *) palloc(JUMBLE_SIZE);
+ jstate->jumble_len = 0;
+ jstate->clocations_buf_size = 32;
+ jstate->clocations = (LocationLen *)
+ palloc(jstate->clocations_buf_size * sizeof(LocationLen));
+ jstate->clocations_count = 0;
+ jstate->highest_extern_param_id = 0;
+
+ /* Compute query ID */
+ _jumbleNode(jstate, (Node *) expr);
+ *queryId = DatumGetUInt64(hash_any_extended(jstate->jumble,
+
jstate->jumble_len,
+
0));
+
+ if (*queryId == UINT64CONST(0))
+ *queryId = UINT64CONST(1);
+
+ return jstate;
+}
+
/*
* Enables query identifier computation.
*
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 64c582c344..d8c6096144 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -16,12 +16,14 @@
#include "postgres.h"
#include "catalog/pg_aggregate.h"
+#include "catalog/pg_operator.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"
#include "commands/dbcommands.h"
#include "miscadmin.h"
#include "nodes/makefuncs.h"
#include "nodes/nodeFuncs.h"
+#include "nodes/queryjumble.h"
#include "optimizer/optimizer.h"
#include "parser/analyze.h"
#include "parser/parse_agg.h"
@@ -38,11 +40,13 @@
#include "utils/date.h"
#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
+#include "utils/syscache.h"
#include "utils/timestamp.h"
#include "utils/xml.h"
/* GUC parameters */
bool Transform_null_equals = false;
+bool enable_or_transformation = true;
static Node *transformExprRecurse(ParseState *pstate, Node *expr);
@@ -99,6 +103,289 @@ static Expr *make_distinct_op(ParseState *pstate, List
*opname,
static Node *make_nulltest_from_distinct(ParseState *pstate,
A_Expr *distincta, Node *arg);
+typedef struct OrClauseGroupKey
+{
+ Expr *expr; /* Pointer to the expression tree which has been a source
for
+ the hashkey value */
+ Oid opno;
+ Oid exprtype;
+} OrClauseGroupKey;
+
+typedef struct OrClauseGroupEntry
+{
+ OrClauseGroupKey key;
+
+ Node *node;
+ List *consts;
+ Oid scalar_type;
+ List *exprs;
+} OrClauseGroupEntry;
+
+/*
+ * Hash function that's compatible with guc_name_compare
+ */
+static uint32
+orclause_hash(const void *data, Size keysize)
+{
+ OrClauseGroupKey *key = (OrClauseGroupKey *) data;
+ uint64 hash;
+
+ (void) JumbleExpr(key->expr, &hash);
+ hash += ((uint64) key->opno + (uint64) key->exprtype) % UINT64_MAX;
+ return hash;
+}
+
+static void *
+orclause_keycopy(void *dest, const void *src, Size keysize)
+{
+ OrClauseGroupKey *src_key = (OrClauseGroupKey *) src;
+ OrClauseGroupKey *dst_key = (OrClauseGroupKey *) dest;
+
+ Assert(sizeof(OrClauseGroupKey) == keysize);
+
+ dst_key->expr = src_key->expr;
+ dst_key->opno = src_key->opno;
+ dst_key->exprtype = src_key->exprtype;
+ return dst_key;
+}
+
+/*
+ * Dynahash match function to use in guc_hashtab
+ */
+static int
+orclause_match(const void *data1, const void *data2, Size keysize)
+{
+ OrClauseGroupKey *key1 = (OrClauseGroupKey *) data1;
+ OrClauseGroupKey *key2 = (OrClauseGroupKey *) data2;
+
+ Assert(sizeof(OrClauseGroupKey) == keysize);
+
+ if (key1->opno == key2->opno && key1->exprtype == key2->exprtype &&
+ equal(key1->expr, key2->expr))
+ return 0;
+
+ return 1;
+}
+
+static Node *
+transformBoolExprOr(ParseState *pstate, BoolExpr *expr)
+{
+ List *or_list = NIL;
+ ListCell *lc;
+ HASHCTL info;
+ HTAB *or_group_htab = NULL;
+ int len_ors =
list_length(expr->args);
+ HASH_SEQ_STATUS hash_seq;
+ OrClauseGroupEntry *entry = NULL;
+
+ /* If this is not an 'OR' expression, skip the transformation */
+ if (!enable_or_transformation || expr->boolop != OR_EXPR || len_ors < 2)
+ return transformBoolExpr(pstate, (BoolExpr *) expr);
+
+ MemSet(&info, 0, sizeof(info));
+ info.keysize = sizeof(OrClauseGroupKey);
+ info.entrysize = sizeof(OrClauseGroupEntry);
+ info.hash = orclause_hash;
+ info.keycopy = orclause_keycopy;
+ info.match = orclause_match;
+ or_group_htab = hash_create("OR Groups",
+ len_ors,
+ &info,
+ HASH_ELEM |
HASH_FUNCTION | HASH_COMPARE | HASH_KEYCOPY);
+
+ foreach(lc, expr->args)
+ {
+ Node *arg = lfirst(lc);
+ Node *orqual;
+ Node *const_expr;
+ Node *nconst_expr;
+ OrClauseGroupKey hashkey;
+ bool found;
+
+ /* At first, transform the arg and evaluate constant
expressions. */
+ orqual = transformExprRecurse(pstate, (Node *) arg);
+ orqual = coerce_to_boolean(pstate, orqual, "OR");
+
+ if (!IsA(orqual, OpExpr))
+ {
+ or_list = lappend(or_list, orqual);
+ continue;
+ }
+
+ /*
+ * Detect the constant side of the clause. Recall non-constant
+ * expression can be made not only with Vars, but also with
Params,
+ * which is not bonded with any relation. Thus, we detect the
const
+ * side - if another side is constant too, the orqual couldn't
be
+ * an OpExpr.
+ * Get pointers to constant and expression sides of the qual.
+ */
+ if (IsA(get_leftop(orqual), Const))
+ {
+ nconst_expr = get_rightop(orqual);
+ const_expr = get_leftop(orqual);
+ }
+ else if (IsA(get_rightop(orqual), Const))
+ {
+ const_expr = get_rightop(orqual);
+ nconst_expr = get_leftop(orqual);
+ }
+ else
+ {
+ or_list = lappend(or_list, orqual);
+ continue;
+ }
+
+ if (!op_mergejoinable(((OpExpr *) orqual)->opno,
exprType(nconst_expr)))
+ {
+ or_list = lappend(or_list, orqual);
+ continue;
+ }
+
+ /*
+ * At this point we definitely have a transformable clause.
+ * Classify it and add into specific group of clauses, or create
new
+ * group.
+ * TODO: to manage complexity in the case of many different
clauses
+ * (X1=C1) OR (X2=C2 OR) ... (XN = CN) we could invent something
+ * like a hash table. But also we believe, that the case of many
+ * different variable sides is very rare.
+ */
+ hashkey.expr = (Expr *) nconst_expr;
+ hashkey.opno = ((OpExpr *) orqual)->opno;
+ hashkey.exprtype = exprType(nconst_expr);
+ entry = hash_search(or_group_htab, &hashkey, HASH_ENTER,
&found);
+
+ if (unlikely(found))
+ {
+ entry->consts = lappend(entry->consts, const_expr);
+ entry->exprs = lappend(entry->exprs, orqual);
+ }
+ else
+ {
+ entry->node = nconst_expr;
+ entry->consts = list_make1(const_expr);
+ entry->exprs = list_make1(orqual);
+ }
+ }
+
+ if (list_length(or_list) == len_ors)
+ {
+ /*
+ * No any transformations possible with this list of arguments.
Here we
+ * already made all underlying transformations. Thus, just
return the
+ * transformed bool expression.
+ */
+ hash_destroy(or_group_htab);
+ return (Node *) makeBoolExpr(OR_EXPR, or_list, expr->location);
+ }
+
+ hash_seq_init(&hash_seq, or_group_htab);
+
+ /* Let's convert each group of clauses to an IN operation. */
+
+ /*
+ * Go through the list of groups and convert each, where number of
+ * consts more than 1. trivial groups move to OR-list again
+ */
+
+ while ((entry = (OrClauseGroupEntry *) hash_seq_search(&hash_seq)) !=
NULL)
+ {
+ List *allexprs;
+ Oid scalar_type;
+ Oid array_type;
+
+ Assert(list_length(entry->consts) > 0);
+ Assert(list_length(entry->exprs) == list_length(entry->consts));
+
+ if (list_length(entry->consts) == 1)
+ {
+ /*
+ * Only one element in the class. Return rinfo into the
BoolExpr
+ * args list unchanged.
+ */
+ list_free(entry->consts);
+ or_list = list_concat(or_list, entry->exprs);
+ continue;
+ }
+
+ /*
+ * Do the transformation.
+ */
+ allexprs = list_concat(list_make1(entry->node), entry->consts);
+ scalar_type = entry->key.exprtype;
+
+ if (scalar_type != RECORDOID && OidIsValid(scalar_type))
+ array_type = get_array_type(scalar_type);
+ else
+ array_type = InvalidOid;
+
+ if (array_type != InvalidOid)
+ {
+ /*
+ * OK: coerce all the right-hand non-Var inputs to the
common
+ * type and build an ArrayExpr for them.
+ */
+ List *aexprs = NIL;
+ ArrayExpr *newa = NULL;
+ ScalarArrayOpExpr *saopexpr = NULL;
+ ListCell *l;
+ HeapTuple opertup;
+ Form_pg_operator operform;
+ List *namelist = NIL;
+
+ aexprs = entry->consts;
+
+ newa = makeNode(ArrayExpr);
+ /* array_collid will be set by parse_collate.c */
+ newa->element_typeid = scalar_type;
+ newa->array_typeid = array_type;
+ newa->multidims = false;
+ newa->elements = aexprs;
+ newa->location = -1;
+
+ opertup = SearchSysCache1(OPEROID,
+
ObjectIdGetDatum(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,
+
entry->node,
+
(Node *) newa,
+
-1);
+
+ or_list = lappend(or_list, (void *) saopexpr);
+ }
+ else
+ {
+ /*
+ * This part works on intarray test (OR there is made on
+ * elements of a custom type)
+ */
+ list_free(entry->consts);
+ or_list = list_concat(or_list, entry->exprs);
+ }
+ hash_search(or_group_htab, &entry->key, HASH_REMOVE, NULL);
+ }
+ hash_destroy(or_group_htab);
+
+ /* One more trick: assemble correct clause */
+ return (Node *) ((list_length(or_list) > 1) ?
+ makeBoolExpr(OR_EXPR, or_list,
expr->location) :
+ linitial(or_list));
+}
/*
* transformExpr -
@@ -212,7 +499,7 @@ transformExprRecurse(ParseState *pstate, Node *expr)
}
case T_BoolExpr:
- result = transformBoolExpr(pstate, (BoolExpr *) expr);
+ result = transformBoolExprOr(pstate, (BoolExpr *) expr);
break;
case T_FuncCall:
diff --git a/src/backend/utils/misc/guc_tables.c
b/src/backend/utils/misc/guc_tables.c
index b764ef6998..2ca8a21caf 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -1048,6 +1048,16 @@ struct config_bool ConfigureNamesBool[] =
true,
NULL, NULL, NULL
},
+ {
+ {"enable_or_transformation", PGC_USERSET, QUERY_TUNING_OTHER,
+ gettext_noop("Transform a sequence of OR clauses to an
IN expression."),
+ gettext_noop("The planner will replace clauses like
'x=c1 OR x=c2 .."
+ "to the clause 'x IN
(c1,c2,...)'")
+ },
+ &enable_or_transformation,
+ true,
+ NULL, NULL, NULL
+ },
{
/*
* Not for general use --- used by SET SESSION AUTHORIZATION
and SET
diff --git a/src/backend/utils/misc/postgresql.conf.sample
b/src/backend/utils/misc/postgresql.conf.sample
index e48c066a5b..26bee30ad3 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -371,6 +371,7 @@
# - Planner Method Configuration -
#enable_async_append = on
+#enable_or_transformation = on
#enable_bitmapscan = on
#enable_gathermerge = on
#enable_hashagg = on
diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h
index 0769081c7a..4aaa31aa80 100644
--- a/src/include/nodes/queryjumble.h
+++ b/src/include/nodes/queryjumble.h
@@ -65,6 +65,7 @@ extern PGDLLIMPORT int compute_query_id;
extern const char *CleanQuerytext(const char *query, int *location, int *len);
extern JumbleState *JumbleQuery(Query *query);
+extern JumbleState *JumbleExpr(Expr *expr, uint64 *queryId);
extern void EnableQueryId(void);
extern PGDLLIMPORT bool query_id_enabled;
diff --git a/src/include/parser/parse_expr.h b/src/include/parser/parse_expr.h
index 7d38ca75f7..3a87de0285 100644
--- a/src/include/parser/parse_expr.h
+++ b/src/include/parser/parse_expr.h
@@ -17,6 +17,7 @@
/* GUC parameters */
extern PGDLLIMPORT bool Transform_null_equals;
+extern PGDLLIMPORT bool enable_or_transformation;
extern Node *transformExpr(ParseState *pstate, Node *expr, ParseExprKind
exprKind);
diff --git a/src/test/regress/expected/create_index.out
b/src/test/regress/expected/create_index.out
index acfd9d1f4f..3bb4bbca48 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1838,18 +1838,50 @@ DROP TABLE onek_with_null;
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
- Bitmap Heap Scan on tenk1
- Recheck Cond: (((thousand = 42) AND (tenthous = 1)) OR ((thousand = 42) AND
(tenthous = 3)) OR ((thousand = 42) AND (tenthous = 42)))
- -> BitmapOr
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 1))
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 3))
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: ((thousand = 42) AND (tenthous = 42))
-(9 rows)
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Index Scan using tenk1_thous_tenthous on tenk1
+ Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3,42}'::integer[])))
+(2 rows)
+
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand |
twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+ 42 | 5530 | 0 | 2 | 2 | 2 | 42 | 42 |
42 | 42 | 42 | 84 | 85 | QBAAAA | SEIAAA | OOOOxx
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+ QUERY PLAN
+------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: ((hundred = 42) AND (thousand = ANY
('{42,99}'::integer[])))
+ -> BitmapAnd
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 42)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = ANY ('{42,99}'::integer[]))
+(8 rows)
+
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+ count
+-------
+ 10
+(1 row)
+
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+ QUERY PLAN
+------------------------------------------------------------------------------
+ Index Scan using tenk1_thous_tenthous on tenk1
+ Index Cond: ((thousand = 42) AND (tenthous = ANY ('{1,3,42}'::integer[])))
+(2 rows)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -1861,28 +1893,101 @@ SELECT * FROM tenk1
EXPLAIN (COSTS OFF)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
- QUERY PLAN
----------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------
Aggregate
-> Bitmap Heap Scan on tenk1
- Recheck Cond: ((hundred = 42) AND ((thousand = 42) OR (thousand =
99)))
+ Recheck Cond: ((hundred = 42) AND (thousand = ANY
('{42,99}'::integer[])))
+ -> BitmapAnd
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 42)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = ANY ('{42,99}'::integer[]))
+(8 rows)
+
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+ count
+-------
+ 10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: (((thousand = 42) AND (tenthous = ANY
('{1,3}'::integer[]))) OR (thousand = 41))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: ((thousand = 42) AND (tenthous = ANY
('{1,3}'::integer[])))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 41)
+(8 rows)
+
+SELECT count(*) FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+ count
+-------
+ 10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR
thousand = 41;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: (((hundred = 42) AND ((tenthous < 2) OR (thousand = ANY
('{42,99}'::integer[])))) OR (thousand = 41))
+ -> BitmapOr
+ -> BitmapAnd
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 42)
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (tenthous < 2)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = ANY
('{42,99}'::integer[]))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 41)
+(14 rows)
+
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR
thousand = 41;
+ count
+-------
+ 20
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND
tenthous = 2);
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: ((hundred = 42) AND (((thousand = 99) AND (tenthous =
2)) OR (thousand = ANY ('{42,41}'::integer[]))))
-> BitmapAnd
-> Bitmap Index Scan on tenk1_hundred
Index Cond: (hundred = 42)
-> BitmapOr
-> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 42)
+ Index Cond: ((thousand = 99) AND (tenthous = 2))
-> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 99)
+ Index Cond: (thousand = ANY ('{42,41}'::integer[]))
(11 rows)
SELECT count(*) FROM tenk1
- WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND
tenthous = 2);
count
-------
10
(1 row)
+RESET enable_or_transformation;
--
-- Check behavior with duplicate index column contents
--
diff --git a/src/test/regress/expected/guc.out
b/src/test/regress/expected/guc.out
index 127c953297..0f2b1b1620 100644
--- a/src/test/regress/expected/guc.out
+++ b/src/test/regress/expected/guc.out
@@ -861,7 +861,8 @@ SELECT name FROM tab_settings_flags
name
---------------------------
default_statistics_target
-(1 row)
+ enable_or_transformation
+(2 rows)
-- Runtime-computed GUCs should be part of the preset category.
SELECT name FROM tab_settings_flags
diff --git a/src/test/regress/expected/inherit.out
b/src/test/regress/expected/inherit.out
index 0f1aa831f6..1781250122 100644
--- a/src/test/regress/expected/inherit.out
+++ b/src/test/regress/expected/inherit.out
@@ -2560,7 +2560,7 @@ explain (costs off) select * from list_parted where a =
'ab' or a in (null, 'cd'
QUERY PLAN
---------------------------------------------------------------------------------
Seq Scan on part_ab_cd list_parted
- Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY
('{NULL,cd}'::text[])))
+ Filter: (((a)::text = ANY ('{NULL,cd}'::text[])) OR ((a)::text =
'ab'::text))
(2 rows)
explain (costs off) select * from list_parted where a = 'ab';
diff --git a/src/test/regress/expected/join.out
b/src/test/regress/expected/join.out
index 2c73270143..e952e5401f 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4210,10 +4210,10 @@ explain (costs off)
select * from tenk1 a join tenk1 b on
(a.unique1 = 1 and b.unique1 = 2) or
((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
- QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
Nested Loop
- Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR (((a.unique2 = 3) OR
(a.unique2 = 7)) AND (b.hundred = 4)))
+ Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY
('{3,7}'::integer[])) AND (b.hundred = 4)))
-> Bitmap Heap Scan on tenk1 b
Recheck Cond: ((unique1 = 2) OR (hundred = 4))
-> BitmapOr
@@ -4223,16 +4223,64 @@ select * from tenk1 a join tenk1 b on
Index Cond: (hundred = 4)
-> Materialize
-> Bitmap Heap Scan on tenk1 a
- Recheck Cond: ((unique1 = 1) OR (unique2 = 3) OR (unique2 = 7))
+ Recheck Cond: ((unique1 = 1) OR (unique2 = ANY
('{3,7}'::integer[])))
-> BitmapOr
-> Bitmap Index Scan on tenk1_unique1
Index Cond: (unique1 = 1)
-> Bitmap Index Scan on tenk1_unique2
- Index Cond: (unique2 = 3)
+ Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+(17 rows)
+
+SET enable_or_transformation = on;
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+ (a.unique1 = 1 and b.unique1 = 2) or
+ ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Join Filter: (((a.unique1 = 1) AND (b.unique1 = 2)) OR ((a.unique2 = ANY
('{3,7}'::integer[])) AND (b.hundred = 4)))
+ -> Bitmap Heap Scan on tenk1 b
+ Recheck Cond: ((unique1 = 2) OR (hundred = 4))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 2)
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 4)
+ -> Materialize
+ -> Bitmap Heap Scan on tenk1 a
+ Recheck Cond: ((unique1 = 1) OR (unique2 = ANY
('{3,7}'::integer[])))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 1)
-> Bitmap Index Scan on tenk1_unique2
- Index Cond: (unique2 = 7)
-(19 rows)
+ Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+(17 rows)
+
+explain (costs off)
+select * from tenk1 a join tenk1 b on
+ (a.unique1 < 20 or a.unique1 = 3 or a.unique1 = 1 and b.unique1 = 2) or
+ ((a.unique2 = 3 or a.unique2 = 7) and b.hundred = 4);
+
QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------------------------------------------
+ Nested Loop
+ Join Filter: ((a.unique1 < 20) OR ((a.unique1 = 1) AND (b.unique1 = 2)) OR
((a.unique2 = ANY ('{3,7}'::integer[])) AND (b.hundred = 4)) OR (a.unique1 = 3))
+ -> Seq Scan on tenk1 b
+ -> Materialize
+ -> Bitmap Heap Scan on tenk1 a
+ Recheck Cond: ((unique1 < 20) OR (unique1 = 1) OR (unique2 =
ANY ('{3,7}'::integer[])) OR (unique1 = 3))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 < 20)
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 1)
+ -> Bitmap Index Scan on tenk1_unique2
+ Index Cond: (unique2 = ANY ('{3,7}'::integer[]))
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 3)
+(15 rows)
+RESET enable_or_transformation;
--
-- test placement of movable quals in a parameterized join tree
--
diff --git a/src/test/regress/expected/partition_join.out
b/src/test/regress/expected/partition_join.out
index 6560fe2416..6e8d64655b 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -296,7 +296,7 @@ SELECT t1.a, t1.c, t2.b, t2.c FROM (SELECT * FROM prt1
WHERE a < 450) t1 FULL JO
Sort Key: prt1.a, prt2.b
-> Hash Full Join
Hash Cond: (prt1.a = prt2.b)
- Filter: ((prt1.b = 0) OR (prt2.a = 0))
+ Filter: ((prt2.a = 0) OR (prt1.b = 0))
-> Append
-> Seq Scan on prt1_p1 prt1_1
Filter: (a < 450)
diff --git a/src/test/regress/expected/partition_prune.out
b/src/test/regress/expected/partition_prune.out
index 9a4c48c055..14a254fba7 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -82,25 +82,47 @@ explain (costs off) select * from lp where a is null;
(2 rows)
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;
@@ -1933,10 +2112,10 @@ explain (costs off) select * from hp where a = 1 and b
= 'abcde';
explain (costs off) select * from hp where a = 1 and b = 'abcde' and
(c = 2 or c = 3);
- QUERY PLAN
-----------------------------------------------------------------------
+ QUERY PLAN
+--------------------------------------------------------------------------------
Seq Scan on hp2 hp
- Filter: ((a = 1) AND (b = 'abcde'::text) AND ((c = 2) OR (c = 3)))
+ Filter: ((c = ANY ('{2,3}'::integer[])) AND (a = 1) AND (b = 'abcde'::text))
(2 rows)
drop table hp2;
diff --git a/src/test/regress/expected/rules.out
b/src/test/regress/expected/rules.out
index 05070393b9..c927c21dd4 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -2495,7 +2495,7 @@ pg_stats| SELECT n.nspname AS schemaname,
JOIN pg_class c ON ((c.oid = s.starelid)))
JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum =
s.staattnum))))
LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))
- WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum,
'select'::text) AND ((c.relrowsecurity = false) OR (NOT
row_security_active(c.oid))));
+ WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum,
'select'::text) AND ((NOT row_security_active(c.oid)) OR (c.relrowsecurity =
false)));
pg_stats_ext| SELECT cn.nspname AS schemaname,
c.relname AS tablename,
sn.nspname AS statistics_schemaname,
@@ -2526,7 +2526,7 @@ pg_stats_ext| SELECT cn.nspname AS schemaname,
WHERE ((NOT (EXISTS ( SELECT 1
FROM (unnest(s.stxkeys) k(k)
JOIN pg_attribute a ON (((a.attrelid = s.stxrelid) AND (a.attnum
= k.k))))
- WHERE (NOT has_column_privilege(c.oid, a.attnum, 'select'::text)))))
AND ((c.relrowsecurity = false) OR (NOT row_security_active(c.oid))));
+ WHERE (NOT has_column_privilege(c.oid, a.attnum, 'select'::text)))))
AND ((NOT row_security_active(c.oid)) OR (c.relrowsecurity = false)));
pg_stats_ext_exprs| SELECT cn.nspname AS schemaname,
c.relname AS tablename,
sn.nspname AS statistics_schemaname,
diff --git a/src/test/regress/expected/select.out
b/src/test/regress/expected/select.out
index 33a6dceb0e..7a829c44c7 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -868,13 +868,13 @@ select unique1, unique2 from onek2
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on onek2
- Recheck Cond: (((unique2 = 11) AND (stringu1 < 'B'::name)) OR (unique1 = 0))
+ Recheck Cond: ((unique1 = 0) OR ((unique2 = 11) AND (stringu1 < 'B'::name)))
Filter: (stringu1 < 'B'::name)
-> BitmapOr
- -> Bitmap Index Scan on onek2_u2_prtl
- Index Cond: (unique2 = 11)
-> Bitmap Index Scan on onek2_u1_prtl
Index Cond: (unique1 = 0)
+ -> Bitmap Index Scan on onek2_u2_prtl
+ Index Cond: (unique2 = 11)
(8 rows)
select unique1, unique2 from onek2
diff --git a/src/test/regress/expected/stats_ext.out
b/src/test/regress/expected/stats_ext.out
index a430153b22..659d712f75 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -1322,19 +1322,19 @@ SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies WHERE
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE (a = 1 OR a = 51) AND b = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE (a = 1 OR a = 51) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE (a = 1 OR a = 2 OR a = 51 OR a = 52) AND (b = ''1'' OR b = ''2'')');
estimated | actual
-----------+--------
- 197 | 200
+ 200 | 200
(1 row)
-- OR clauses referencing different attributes are incompatible
@@ -1664,19 +1664,19 @@ SELECT * FROM check_estimated_rows('SELECT * FROM
functional_dependencies WHERE
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE ((a * 2) = 2 OR (a * 2) = 102) AND upper(b) = ''1''');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE ((a * 2) = 2 OR (a * 2) = 102) AND (upper(b) = ''1'' OR upper(b) =
''2'')');
estimated | actual
-----------+--------
- 99 | 100
+ 100 | 100
(1 row)
SELECT * FROM check_estimated_rows('SELECT * FROM functional_dependencies
WHERE ((a * 2) = 2 OR (a * 2) = 4 OR (a * 2) = 102 OR (a * 2) = 104) AND
(upper(b) = ''1'' OR upper(b) = ''2'')');
estimated | actual
-----------+--------
- 197 | 200
+ 200 | 200
(1 row)
-- OR clauses referencing different attributes
diff --git a/src/test/regress/expected/sysviews.out
b/src/test/regress/expected/sysviews.out
index 271313ebf8..c6c6b9fb8d 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -123,6 +123,7 @@ select name, setting from pg_settings where name like
'enable%';
enable_memoize | on
enable_mergejoin | on
enable_nestloop | on
+ enable_or_transformation | on
enable_parallel_append | on
enable_parallel_hash | on
enable_partition_pruning | on
@@ -133,7 +134,7 @@ select name, setting from pg_settings where name like
'enable%';
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(22 rows)
+(23 rows)
-- There are always wait event descriptions for various types.
select type, count(*) > 0 as ok FROM pg_wait_events
diff --git a/src/test/regress/expected/tidscan.out
b/src/test/regress/expected/tidscan.out
index f133b5a4ac..2a079e996b 100644
--- a/src/test/regress/expected/tidscan.out
+++ b/src/test/regress/expected/tidscan.out
@@ -43,10 +43,26 @@ SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid;
-- OR'd clauses
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
- QUERY PLAN
---------------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------
+ Tid Scan on tidscan
+ TID Cond: (ctid = ANY ('{"(0,2)","(0,1)"}'::tid[]))
+(2 rows)
+
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+ ctid | id
+-------+----
+ (0,1) | 1
+ (0,2) | 2
+(2 rows)
+
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+ QUERY PLAN
+-------------------------------------------------------
Tid Scan on tidscan
- TID Cond: ((ctid = '(0,2)'::tid) OR ('(0,1)'::tid = ctid))
+ TID Cond: (ctid = ANY ('{"(0,2)","(0,1)"}'::tid[]))
(2 rows)
SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
@@ -56,6 +72,7 @@ SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' =
ctid;
(0,2) | 2
(2 rows)
+RESET enable_or_transformation;
-- ctid = ScalarArrayOp - implemented as tidscan
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
diff --git a/src/test/regress/sql/create_index.sql
b/src/test/regress/sql/create_index.sql
index d49ce9f300..48bb1bc0a0 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -737,6 +737,38 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+SELECT count(*) FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR
thousand = 41;
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR
thousand = 41;
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND
tenthous = 2);
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND
tenthous = 2);
+RESET 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 8a8a63bd2f..55d7e2ae7d 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 7bf3920827..1e270ae9c0 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 313e0fb9b6..0499bedb9e 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 86a9886d4f..d9a7d9c284 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1642,6 +1642,8 @@ NumericVar
OM_uint32
OP
OSAPerGroupState
+OrClauseGroupEntry
+OrClauseGroupKey
OSAPerQueryState
OSInfo
OSSLCipher
--
2.43.0
