On 30.11.2023 11:00, Alena Rybakina wrote:
Hi!
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.
I think this is incorrect, and the example of A. Korotkov confirms
this. If we perform the conversion at the parsing stage, we will skip
the more important conversion using OR expressions. I'll show you in
the example below.
First of all, I will describe my idea to combine two approaches to
obtaining plans with OR to ANY transformation and ANY to OR
transformation. I think they are both good, and we can't work with
just one of them, we should consider both the option of OR
expressions, and with ANY.
Just in case, I have attached a patch or->any transformation where this
happens at the index creation stage.
I get diff file during make check, but judging by the changes, it shows
that the transformation is going well. I also attached it.
--
Regards,
Alena Rybakina
Postgres Professional
From 9f75b58525b4892db2c360401771f69599ed21c8 Mon Sep 17 00:00:00 2001
From: Alena Rybakina <a.rybak...@postgrespro.ru>
Date: Wed, 29 Nov 2023 18:58:55 +0300
Subject: [PATCH] Transform OR clause to ANY expressions.
Replace (X=N1) OR (X=N2) ... with X = ANY(N1, N2) on the preliminary stage of
optimization when we are still working with a tree expression.
Sometimes it can lead to not optimal plan. But we think it is better to have
array of elements instead of a lot of OR clauses. Here is a room for further
optimizations on decomposing that array into more optimal parts.
---
src/backend/nodes/queryjumblefuncs.c | 30 ++
src/backend/optimizer/path/indxpath.c | 394 +++++++++++++++++-
src/backend/utils/misc/guc_tables.c | 10 +
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/include/nodes/queryjumble.h | 1 +
src/include/optimizer/paths.h | 1 +
src/test/regress/expected/create_index.out | 120 ++++++
src/test/regress/expected/guc.out | 3 +-
src/test/regress/expected/join.out | 48 +++
src/test/regress/expected/partition_prune.out | 179 ++++++++
src/test/regress/expected/sysviews.out | 3 +-
src/test/regress/expected/tidscan.out | 17 +
src/test/regress/sql/create_index.sql | 32 ++
src/test/regress/sql/join.sql | 10 +
src/test/regress/sql/partition_prune.sql | 22 +
src/test/regress/sql/tidscan.sql | 6 +
src/tools/pgindent/typedefs.list | 1 +
17 files changed, 875 insertions(+), 3 deletions(-)
diff --git a/src/backend/nodes/queryjumblefuncs.c b/src/backend/nodes/queryjumblefuncs.c
index 281907a4d83..c98fda73d17 100644
--- a/src/backend/nodes/queryjumblefuncs.c
+++ b/src/backend/nodes/queryjumblefuncs.c
@@ -283,6 +283,36 @@ _jumbleNode(JumbleState *jstate, Node *node)
}
}
+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;
+}
+
static void
_jumbleList(JumbleState *jstate, Node *node)
{
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 03a5fbdc6dc..acf4937db01 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -34,7 +34,12 @@
#include "optimizer/restrictinfo.h"
#include "utils/lsyscache.h"
#include "utils/selfuncs.h"
-
+#include "parser/parse_coerce.h"
+#include "common/hashfn.h"
+#include "catalog/pg_operator.h"
+#include "nodes/queryjumble.h"
+#include "utils/syscache.h"
+#include "catalog/namespace.h"
/* XXX see PartCollMatchesExprColl */
#define IndexCollMatchesExprColl(idxcollation, exprcollation) \
@@ -193,6 +198,390 @@ static bool ec_member_matches_indexcol(PlannerInfo *root, RelOptInfo *rel,
EquivalenceClass *ec, EquivalenceMember *em,
void *arg);
+bool enable_or_transformation = true;
+typedef struct OrClauseGroupKey
+{
+ Expr *expr; /* Pointer to the expression tree which has been a source for
+ the hashkey value */
+ Oid opno;
+ Oid exprtype;
+} OrClauseGroupKey;
+
+typedef struct OrClauseGroupEntry
+{
+ OrClauseGroupKey key;
+ Node *node;
+ List *consts;
+ Oid collation;
+ RestrictInfo *rinfo;
+ List *exprs;
+} OrClauseGroupEntry;
+
+/*
+ * Hash function that's compatible with guc_name_compare
+ */
+static uint32
+orclause_hash(const void *data, Size keysize)
+{
+ OrClauseGroupKey *key = (OrClauseGroupKey *) data;
+ uint64 hash;
+
+ (void) JumbleExpr(key->expr, &hash);
+ hash = ((uint64) key->opno + (uint64) key->exprtype) % UINT64_MAX;
+ return hash;
+}
+
+static void *
+orclause_keycopy(void *dest, const void *src, Size keysize)
+{
+ OrClauseGroupKey *src_key = (OrClauseGroupKey *) src;
+ OrClauseGroupKey *dst_key = (OrClauseGroupKey *) dest;
+
+ Assert(sizeof(OrClauseGroupKey) == keysize);
+
+ dst_key->expr = src_key->expr;
+ dst_key->opno = src_key->opno;
+ dst_key->exprtype = src_key->exprtype;
+ return dst_key;
+}
+
+/*
+ * Dynahash match function to use in guc_hashtab
+ */
+static int
+orclause_match(const void *data1, const void *data2, Size keysize)
+{
+ OrClauseGroupKey *key1 = (OrClauseGroupKey *) data1;
+ OrClauseGroupKey *key2 = (OrClauseGroupKey *) data2;
+
+ Assert(sizeof(OrClauseGroupKey) == keysize);
+
+ if (key1->opno == key2->opno && key1->exprtype == key2->exprtype &&
+ equal(key1->expr, key2->expr))
+ return 0;
+
+ return 1;
+}
+
+/*
+ * Pass through baserestrictinfo clauses and try to convert OR clauses into IN
+ * Return a modified clause list or just the same baserestrictinfo, if no
+ * changes have made.
+ * XXX: do not change source list of clauses at all.
+ */
+static List *
+transform_ors(PlannerInfo *root, List *baserestrictinfo)
+{
+ ListCell *lc;
+ List *modified_rinfo = NIL;
+ bool something_changed = false;
+
+ /*
+ * Complexity of a clause could be arbitrarily sophisticated. Here, we will
+ * look up only on the top level of clause list.
+ * XXX: It is substantiated? Could we change something here?
+ */
+ foreach (lc, baserestrictinfo)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+ RestrictInfo *rinfo_base = copyObject(rinfo);
+ List *or_list = NIL;
+ ListCell *lc_eargs,
+ *lc_rargs;
+ bool change_apply = false;
+ HASHCTL info;
+ HTAB *or_group_htab = NULL;
+ int len_ors;
+ HASH_SEQ_STATUS hash_seq;
+ OrClauseGroupEntry *gentry;
+
+ if (!enable_or_transformation || !restriction_is_or_clause(rinfo))
+ {
+ /* Add a clause without changes */
+ modified_rinfo = lappend(modified_rinfo, rinfo);
+ continue;
+ }
+
+ len_ors = ((BoolExpr *) rinfo->clause)->args? list_length(((BoolExpr *) rinfo->clause)->args) : 0;
+
+ if (len_ors < 2)
+ {
+ /* Add a clause without changes */
+ modified_rinfo = lappend(modified_rinfo, rinfo);
+ continue;
+ }
+
+ MemSet(&info, 0, sizeof(info));
+ info.keysize = sizeof(OrClauseGroupKey);
+ info.entrysize = sizeof(OrClauseGroupEntry);
+ info.hash = orclause_hash;
+ info.keycopy = orclause_keycopy;
+ info.match = orclause_match;
+ or_group_htab = hash_create("OR Groups",
+ len_ors,
+ &info,
+ HASH_ELEM | HASH_FUNCTION | HASH_COMPARE | HASH_KEYCOPY);
+
+ /*
+ * NOTE:
+ * It is an OR-clause. So, rinfo->orclause is a BoolExpr node, contains
+ * a list of sub-restrictinfo args, and rinfo->clause - which is the
+ * same expression, made from bare clauses. To not break selectivity
+ * caches and other optimizations, use both:
+ * - use rinfos from orclause if no transformation needed
+ * - use bare quals from rinfo->clause in the case of transformation,
+ * to create new RestrictInfo: in this case we have no options to avoid
+ * selectivity estimation procedure.
+ */
+ forboth(lc_eargs, ((BoolExpr *) rinfo->clause)->args,
+ lc_rargs, ((BoolExpr *) rinfo->orclause)->args)
+ {
+ Expr *or_qual = (Expr *) lfirst(lc_eargs);
+ RestrictInfo *sub_rinfo;
+ Node *const_expr;
+ Node *nconst_expr;
+ OrClauseGroupKey hashkey;
+ bool found = false;
+
+ /* It may be one more boolean expression, skip it for now */
+ if (!IsA(lfirst(lc_rargs), RestrictInfo))
+ {
+ or_list = lappend(or_list, (void *) or_qual);
+ continue;
+ }
+
+ sub_rinfo = lfirst_node(RestrictInfo, lc_rargs);
+
+ /* Check: it is an expr of the form 'F(x) oper ConstExpr' */
+ if (!IsA(or_qual, OpExpr) ||
+ !(bms_is_empty(sub_rinfo->left_relids) ^
+ bms_is_empty(sub_rinfo->right_relids)) ||
+ contain_volatile_functions((Node *) or_qual))
+ {
+ /* Again, it's not the expr we can transform */
+ or_list = lappend(or_list, (void *) or_qual);
+ continue;
+ }
+
+ /* Get pointers to constant and expression sides of the clause */
+ const_expr =bms_is_empty(sub_rinfo->left_relids) ?
+ get_leftop(sub_rinfo->clause) :
+ get_rightop(sub_rinfo->clause);
+ nconst_expr = bms_is_empty(sub_rinfo->left_relids) ?
+ get_rightop(sub_rinfo->clause) :
+ get_leftop(sub_rinfo->clause);
+
+ if (!op_mergejoinable(((OpExpr *) sub_rinfo->clause)->opno, exprType(nconst_expr)))
+ {
+ /* And again, filter out non-equality operators */
+ or_list = lappend(or_list, (void *) or_qual);
+ continue;
+ }
+
+ /*
+ * At this point we definitely have a transformable clause.
+ * Classify it and add into specific group of clauses, or create new
+ * group.
+ */
+ hashkey.expr = (Expr *) nconst_expr;
+ hashkey.opno = ((OpExpr *) or_qual)->opno;
+ hashkey.exprtype = exprType(nconst_expr);
+ gentry = hash_search(or_group_htab, &hashkey, HASH_ENTER, &found);
+
+ if (unlikely(found))
+ {
+ gentry->consts = lappend(gentry->consts, const_expr);
+ gentry->exprs = lappend(gentry->exprs, or_qual);
+ }
+ else
+ {
+ gentry->node = nconst_expr;
+ gentry->consts = list_make1(const_expr);
+ gentry->exprs = list_make1(or_qual);
+ gentry->rinfo = sub_rinfo;
+ gentry->collation = exprInputCollation((Node *)sub_rinfo->clause);
+ }
+ }
+
+ if (list_length(or_list) == len_ors)
+ {
+ /*
+ * No any transformations possible with this list of arguments. Here we
+ * already made all underlying transformations. Thus, just return the
+ * transformed bool expression.
+ */
+ modified_rinfo = lappend(modified_rinfo, rinfo);
+ continue;
+ }
+
+ if (!or_group_htab && hash_get_num_entries(or_group_htab) < 1)
+ {
+ /*
+ * No any transformations possible with this rinfo, just add itself
+ * to the list and go further.
+ */
+ modified_rinfo = lappend(modified_rinfo, rinfo);
+ continue;
+ }
+
+ hash_seq_init(&hash_seq, or_group_htab);
+
+ /* Let's convert each group of clauses to an IN operation. */
+
+ /*
+ * Go through the list of groups and convert each, where number of
+ * consts more than 1. trivial groups move to OR-list again
+ */
+
+ while ((gentry = (OrClauseGroupEntry *) hash_seq_search(&hash_seq)) != NULL)
+ {
+ Oid scalar_type;
+ Oid array_type;
+
+ Assert(list_length(gentry->consts) > 0);
+ Assert(list_length(gentry->exprs) == list_length(gentry->consts));
+
+ if (list_length(gentry->consts) == 1)
+ {
+ /*
+ * Only one element in the class. Return rinfo into the BoolExpr
+ * args list unchanged.
+ */
+ list_free(gentry->consts);
+ or_list = list_concat(or_list, gentry->exprs);
+ continue;
+ }
+
+ /*
+ * Do the transformation.
+ *
+ * First of all, try to select a common type for the array elements.
+ * Note that since the LHS' type is first in the list, it will be
+ * preferred when there is doubt (eg, when all the RHS items are
+ * unknown literals).
+ *
+ * Note: use list_concat here not lcons, to avoid damaging rnonvars.
+ *
+ * As a source of insides, use make_scalar_array_op()
+ */
+ scalar_type = gentry->key.exprtype;
+
+ if (scalar_type != RECORDOID && OidIsValid(scalar_type))
+ array_type = get_array_type(scalar_type);
+ else
+ array_type = InvalidOid;
+
+ if (array_type != InvalidOid && scalar_type != InvalidOid)
+ {
+ /*
+ * OK: coerce all the right-hand non-Var inputs to the common
+ * type and build an ArrayExpr for them.
+ */
+ List *aexprs = NULL;
+ ArrayExpr *newa = NULL;
+ ScalarArrayOpExpr *saopexpr = NULL;
+ HeapTuple opertup;
+ Form_pg_operator operform;
+ List *namelist = NIL;
+
+ aexprs = gentry->consts;
+
+ newa = makeNode(ArrayExpr);
+ /* array_collid will be set by parse_collate.c */
+ newa->element_typeid = scalar_type;
+ newa->array_typeid = array_type;
+ newa->multidims = false;
+ newa->elements = aexprs;
+ newa->location = -1;
+
+ opertup = SearchSysCache1(OPEROID,
+ ObjectIdGetDatum(gentry->key.opno));
+
+ if (!HeapTupleIsValid(opertup))
+ elog(ERROR, "cache lookup failed for operator %u",
+ gentry->key.opno);
+
+ operform = (Form_pg_operator) GETSTRUCT(opertup);
+ if (!OperatorIsVisible(gentry->key.opno))
+ namelist = lappend(namelist, makeString(get_namespace_name(operform->oprnamespace)));
+
+ namelist = lappend(namelist, makeString(pstrdup(NameStr(operform->oprname))));
+ ReleaseSysCache(opertup);
+
+ saopexpr = makeNode(ScalarArrayOpExpr);
+ saopexpr->opno = ((OpExpr *) gentry->rinfo->clause)->opno;
+ saopexpr->useOr = true;
+ saopexpr->inputcollid = gentry->collation;
+ saopexpr->args = list_make2(gentry->node, newa);
+ saopexpr->location = -1;
+
+
+ or_list = lappend(or_list, (void *) saopexpr);
+
+ something_changed = true;
+ change_apply = true;
+
+ }
+ else
+ {
+ list_free(gentry->consts);
+ or_list = lappend(or_list, (void *) gentry->rinfo->clause);
+ }
+ hash_search(or_group_htab, &gentry->key, HASH_REMOVE, NULL);
+ }
+
+ hash_destroy(or_group_htab);
+
+ if (!change_apply)
+ {
+ /*
+ * Each group contains only one element - use rinfo as is.
+ */
+ modified_rinfo = lappend(modified_rinfo, rinfo);
+ list_free(or_list);
+ continue;
+ }
+
+ /*
+ * Make a new version of the restriction. Remember source restriction
+ * can be used in another path (SeqScan, for example).
+ */
+
+ /* One more trick: assemble correct clause */
+ rinfo = make_restrictinfo(root,
+ list_length(or_list) > 1 ? makeBoolExpr(OR_EXPR, or_list, ((BoolExpr *) rinfo->clause)->location) :
+ linitial(or_list),
+ rinfo->is_pushed_down,
+ rinfo->has_clone,
+ rinfo->is_clone,
+ rinfo->pseudoconstant,
+ rinfo->security_level,
+ rinfo->required_relids,
+ rinfo->incompatible_relids,
+ rinfo->outer_relids);
+ rinfo->eval_cost=rinfo_base->eval_cost;
+ rinfo->norm_selec=rinfo_base->norm_selec;
+ rinfo->outer_selec=rinfo_base->outer_selec;
+ rinfo->left_bucketsize=rinfo_base->left_bucketsize;
+ rinfo->right_bucketsize=rinfo_base->right_bucketsize;
+ rinfo->left_mcvfreq=rinfo_base->left_mcvfreq;
+ rinfo->right_mcvfreq=rinfo_base->right_mcvfreq;
+ modified_rinfo = lappend(modified_rinfo, rinfo);
+ something_changed = true;
+ }
+
+ /*
+ * Check if transformation has made. If nothing changed - return
+ * baserestrictinfo as is.
+ */
+ if (something_changed)
+ {
+ return modified_rinfo;
+ }
+
+ list_free(modified_rinfo);
+ return baserestrictinfo;
+}
/*
* create_index_paths()
@@ -719,6 +1108,9 @@ get_index_paths(PlannerInfo *root, RelOptInfo *rel,
bool skip_lower_saop = false;
ListCell *lc;
+ if (rel->reloptkind == RELOPT_BASEREL)
+ rel->baserestrictinfo = transform_ors(root, rel->baserestrictinfo);
+
/*
* Build simple index paths using the clauses. Allow ScalarArrayOpExpr
* clauses only if the index AM supports them natively, and skip any such
diff --git a/src/backend/utils/misc/guc_tables.c b/src/backend/utils/misc/guc_tables.c
index b764ef69980..2ca8a21caf0 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 e48c066a5b1..f31778074b4 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -370,6 +370,7 @@
# - Planner Method Configuration -
+#enable_or_transformation = on
#enable_async_append = on
#enable_bitmapscan = on
#enable_gathermerge = on
diff --git a/src/include/nodes/queryjumble.h b/src/include/nodes/queryjumble.h
index 0769081c7ab..4aaa31aa80e 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/paths.h b/src/include/optimizer/paths.h
index 9e7408c7ecf..f8be9f9f605 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -24,6 +24,7 @@ extern PGDLLIMPORT bool enable_geqo;
extern PGDLLIMPORT int geqo_threshold;
extern PGDLLIMPORT int min_parallel_table_scan_size;
extern PGDLLIMPORT int min_parallel_index_scan_size;
+extern PGDLLIMPORT bool enable_or_transformation;
/* Hook for plugins to get control in set_rel_pathlist() */
typedef void (*set_rel_pathlist_hook_type) (PlannerInfo *root,
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index acfd9d1f4f7..5a73c8f51a6 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1883,6 +1883,126 @@ SELECT count(*) FROM tenk1
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 (ARRAY[1, 3, 42])))
+(2 rows)
+
+SELECT * FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+ 42 | 5530 | 0 | 2 | 2 | 2 | 42 | 42 | 42 | 42 | 42 | 84 | 85 | QBAAAA | SEIAAA | OOOOxx
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+ QUERY PLAN
+-----------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: ((hundred = 42) AND (thousand = ANY (ARRAY[42, 99])))
+ -> BitmapAnd
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 42)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = ANY (ARRAY[42, 99]))
+(8 rows)
+
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+ count
+-------
+ 10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: ((((thousand = 42) AND (tenthous = 1)) OR ((thousand = 42) AND (tenthous = 3))) OR (thousand = 41))
+ -> BitmapOr
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: ((thousand = 42) AND (tenthous = 1))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: ((thousand = 42) AND (tenthous = 3))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 41)
+(11 rows)
+
+SELECT count(*) FROM tenk1
+ WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3) OR thousand = 41;
+ count
+-------
+ 10
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: (((hundred = 42) AND ((thousand = 42) OR (thousand = 99) OR (tenthous < 2))) OR (thousand = 41))
+ -> BitmapOr
+ -> BitmapAnd
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 42)
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 42)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 99)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (tenthous < 2)
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 41)
+(16 rows)
+
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 99 OR tenthous < 2) OR thousand = 41;
+ count
+-------
+ 20
+(1 row)
+
+EXPLAIN (COSTS OFF)
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2);
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
+ Aggregate
+ -> Bitmap Heap Scan on tenk1
+ Recheck Cond: ((hundred = 42) AND (((thousand = 99) AND (tenthous = 2)) OR (thousand = ANY (ARRAY[42, 41]))))
+ -> BitmapAnd
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 42)
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: ((thousand = 99) AND (tenthous = 2))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = ANY (ARRAY[42, 41]))
+(11 rows)
+
+SELECT count(*) FROM tenk1
+ WHERE hundred = 42 AND (thousand = 42 OR thousand = 41 OR thousand = 99 AND tenthous = 2);
+ count
+-------
+ 10
+(1 row)
+
+RESET 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 127c9532976..0f2b1b16200 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/join.out b/src/test/regress/expected/join.out
index 2c73270143b..54b8fdb2e88 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -4233,6 +4233,54 @@ select * from tenk1 a join tenk1 b on
Index Cond: (unique2 = 7)
(19 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 = 3) OR (a.unique2 = 7)) AND (b.hundred = 4)))
+ -> Bitmap Heap Scan on tenk1 b
+ Recheck Cond: ((unique1 = 2) OR (hundred = 4))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 2)
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 4)
+ -> Materialize
+ -> Bitmap Heap Scan on tenk1 a
+ Recheck Cond: ((unique2 = ANY (ARRAY[3, 7])) OR (unique1 = 1))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique2
+ Index Cond: (unique2 = ANY (ARRAY[3, 7]))
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = 1)
+(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 = 3) OR (a.unique2 = 7)) AND (b.hundred = 4)))
+ -> Seq Scan on tenk1 b
+ -> Materialize
+ -> Bitmap Heap Scan on tenk1 a
+ Recheck Cond: ((unique1 < 20) OR (unique1 = ANY (ARRAY[3, 1])) OR (unique2 = ANY (ARRAY[3, 7])))
+ -> BitmapOr
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 < 20)
+ -> Bitmap Index Scan on tenk1_unique1
+ Index Cond: (unique1 = ANY (ARRAY[3, 1]))
+ -> Bitmap Index Scan on tenk1_unique2
+ Index Cond: (unique2 = ANY (ARRAY[3, 7]))
+(13 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 9a4c48c0556..38b4ec98403 100644
--- a/src/test/regress/expected/partition_prune.out
+++ b/src/test/regress/expected/partition_prune.out
@@ -101,6 +101,28 @@ explain (costs off) select * from lp where a is not null and (a = 'a' or a = 'c'
Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
(5 rows)
+SET 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 = 'a'::bpchar) OR (a = 'c'::bpchar))
+ -> Seq Scan on lp_bc lp_2
+ Filter: ((a = 'a'::bpchar) OR (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 = 'a'::bpchar) OR (a = 'c'::bpchar)))
+ -> Seq Scan on lp_bc lp_2
+ Filter: ((a IS NOT NULL) AND ((a = 'a'::bpchar) OR (a = 'c'::bpchar)))
+(5 rows)
+
+RESET enable_or_transformation;
explain (costs off) select * from lp where a <> 'g';
QUERY PLAN
------------------------------------
@@ -671,6 +693,163 @@ explain (costs off) select * from rlp where (a = 1 and a = 3) or (a > 1 and a =
Filter: (((a = 1) AND (a = 3)) OR ((a > 1) AND (a = 15)))
(11 rows)
+SET 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 = 1) OR (a = 7))
+(2 rows)
+
+explain (costs off) select * from rlp where a = 1 or b = 'ab';
+ QUERY PLAN
+-------------------------------------------------------
+ Append
+ -> Seq Scan on rlp1 rlp_1
+ Filter: ((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 = 20) OR (a = 40))
+ -> Seq Scan on rlp5_default rlp_2
+ Filter: ((a = 20) OR (a = 40))
+(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;
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 271313ebf86..566f51df428 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -123,6 +123,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_memoize | on
enable_mergejoin | on
enable_nestloop | on
+ enable_or_transformation | off
enable_parallel_append | on
enable_parallel_hash | on
enable_partition_pruning | on
@@ -133,7 +134,7 @@ select name, setting from pg_settings where name like 'enable%';
enable_seqscan | on
enable_sort | on
enable_tidscan | on
-(22 rows)
+(23 rows)
-- There are always wait event descriptions for various types.
select type, count(*) > 0 as ok FROM pg_wait_events
diff --git a/src/test/regress/expected/tidscan.out b/src/test/regress/expected/tidscan.out
index f133b5a4ac7..2e12944bd13 100644
--- a/src/test/regress/expected/tidscan.out
+++ b/src/test/regress/expected/tidscan.out
@@ -56,6 +56,23 @@ SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
(0,2) | 2
(2 rows)
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+ QUERY PLAN
+--------------------------------------------------------------
+ Tid Scan on tidscan
+ TID Cond: (ctid = ANY (ARRAY['(0,2)'::tid, '(0,1)'::tid]))
+(2 rows)
+
+SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
+ ctid | id
+-------+----
+ (0,1) | 1
+ (0,2) | 2
+(2 rows)
+
+RESET enable_or_transformation;
-- ctid = ScalarArrayOp - implemented as tidscan
EXPLAIN (COSTS OFF)
SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index d49ce9f3007..48bb1bc0a0a 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -737,6 +737,38 @@ SELECT count(*) FROM tenk1
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
+SET enable_or_transformation = on;
+EXPLAIN (COSTS OFF)
+SELECT * FROM tenk1
+ 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 8a8a63bd2f1..55d7e2ae7d6 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 7bf3920827f..1e270ae9c06 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 dba3498a13e..c9004250a5f 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -1641,6 +1641,7 @@ NumericVar
OM_uint32
OP
OSAPerGroupState
+OrClauseGroupEntry
OSAPerQueryState
OSInfo
OSSLCipher
--
2.34.1
diff -U3
/home/alena/postgrespro__copy32/src/test/regress/expected/create_index.out
/home/alena/postgrespro__copy32/src/test/regress/results/create_index.out
--- /home/alena/postgrespro__copy32/src/test/regress/expected/create_index.out
2023-11-30 13:50:34.576537110 +0300
+++ /home/alena/postgrespro__copy32/src/test/regress/results/create_index.out
2023-11-30 13:52:10.940412842 +0300
@@ -1838,18 +1838,14 @@
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
- QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
+ 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)
+ Recheck Cond: (thousand = 42)
+ Filter: (tenthous = ANY (ARRAY[1, 3, 42]))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 42)
+(5 rows)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -1861,20 +1857,15 @@
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)))
- -> BitmapAnd
- -> Bitmap Index Scan on tenk1_hundred
- Index Cond: (hundred = 42)
- -> BitmapOr
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 42)
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = 99)
-(11 rows)
+ Recheck Cond: (hundred = 42)
+ Filter: (thousand = ANY (ARRAY[42, 99]))
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 42)
+(6 rows)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
@@ -1887,11 +1878,14 @@
EXPLAIN (COSTS OFF)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
- QUERY PLAN
-------------------------------------------------------------------------
- Index Scan using tenk1_thous_tenthous on tenk1
- Index Cond: ((thousand = 42) AND (tenthous = ANY (ARRAY[1, 3, 42])))
-(2 rows)
+ QUERY PLAN
+-------------------------------------------------
+ Bitmap Heap Scan on tenk1
+ Recheck Cond: (thousand = 42)
+ Filter: (tenthous = ANY (ARRAY[1, 3, 42]))
+ -> Bitmap Index Scan on tenk1_thous_tenthous
+ Index Cond: (thousand = 42)
+(5 rows)
SELECT * FROM tenk1
WHERE thousand = 42 AND (tenthous = 1 OR tenthous = 3 OR tenthous = 42);
@@ -1903,17 +1897,15 @@
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 = ANY (ARRAY[42, 99])))
- -> BitmapAnd
- -> Bitmap Index Scan on tenk1_hundred
- Index Cond: (hundred = 42)
- -> Bitmap Index Scan on tenk1_thous_tenthous
- Index Cond: (thousand = ANY (ARRAY[42, 99]))
-(8 rows)
+ Recheck Cond: (hundred = 42)
+ Filter: (thousand = ANY (ARRAY[42, 99]))
+ -> Bitmap Index Scan on tenk1_hundred
+ Index Cond: (hundred = 42)
+(6 rows)
SELECT count(*) FROM tenk1
WHERE hundred = 42 AND (thousand = 42 OR thousand = 99);
diff -U3 /home/alena/postgrespro__copy32/src/test/regress/expected/join.out
/home/alena/postgrespro__copy32/src/test/regress/results/join.out
--- /home/alena/postgrespro__copy32/src/test/regress/expected/join.out
2023-11-30 13:50:34.576537110 +0300
+++ /home/alena/postgrespro__copy32/src/test/regress/results/join.out
2023-11-30 13:52:14.972407590 +0300
@@ -4223,15 +4223,13 @@
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 (ARRAY[3, 7])))
-> BitmapOr
-> Bitmap Index Scan on tenk1_unique1
Index Cond: (unique1 = 1)
-> Bitmap Index Scan on tenk1_unique2
- Index Cond: (unique2 = 3)
- -> Bitmap Index Scan on tenk1_unique2
- Index Cond: (unique2 = 7)
-(19 rows)
+ Index Cond: (unique2 = ANY (ARRAY[3, 7]))
+(17 rows)
SET enable_or_transformation = on;
explain (costs off)
@@ -4251,12 +4249,12 @@
Index Cond: (hundred = 4)
-> Materialize
-> Bitmap Heap Scan on tenk1 a
- Recheck Cond: ((unique2 = ANY (ARRAY[3, 7])) OR (unique1 = 1))
+ Recheck Cond: ((unique1 = 1) OR (unique2 = ANY (ARRAY[3, 7])))
-> BitmapOr
- -> Bitmap Index Scan on tenk1_unique2
- Index Cond: (unique2 = ANY (ARRAY[3, 7]))
-> Bitmap Index Scan on tenk1_unique1
Index Cond: (unique1 = 1)
+ -> Bitmap Index Scan on tenk1_unique2
+ Index Cond: (unique2 = ANY (ARRAY[3, 7]))
(17 rows)
explain (costs off)
diff -U3 /home/alena/postgrespro__copy32/src/test/regress/expected/sysviews.out
/home/alena/postgrespro__copy32/src/test/regress/results/sysviews.out
--- /home/alena/postgrespro__copy32/src/test/regress/expected/sysviews.out
2023-11-30 13:50:34.584537100 +0300
+++ /home/alena/postgrespro__copy32/src/test/regress/results/sysviews.out
2023-11-30 13:52:24.524395130 +0300
@@ -123,7 +123,7 @@
enable_memoize | on
enable_mergejoin | on
enable_nestloop | on
- enable_or_transformation | off
+ enable_or_transformation | on
enable_parallel_append | on
enable_parallel_hash | on
enable_partition_pruning | on
diff -U3 /home/alena/postgrespro__copy32/src/test/regress/expected/tidscan.out
/home/alena/postgrespro__copy32/src/test/regress/results/tidscan.out
--- /home/alena/postgrespro__copy32/src/test/regress/expected/tidscan.out
2023-11-30 13:50:34.584537100 +0300
+++ /home/alena/postgrespro__copy32/src/test/regress/results/tidscan.out
2023-11-30 13:52:24.328395386 +0300
@@ -62,7 +62,7 @@
QUERY PLAN
--------------------------------------------------------------
Tid Scan on tidscan
- TID Cond: (ctid = ANY (ARRAY['(0,2)'::tid, '(0,1)'::tid]))
+ TID Cond: ((ctid = '(0,2)'::tid) OR ('(0,1)'::tid = ctid))
(2 rows)
SELECT ctid, * FROM tidscan WHERE ctid = '(0,2)' OR '(0,1)' = ctid;
