Rebased v2 and v3.
Regards,
Ayoub Kazar
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 7bbddb8bee4..ca653439b01 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -162,6 +162,7 @@ bool enable_partitionwise_join = false;
bool enable_partitionwise_aggregate = false;
bool enable_parallel_append = true;
bool enable_parallel_hash = true;
+int or_expansion_limit = 8;
bool enable_partition_pruning = true;
bool enable_presorted_aggregate = true;
bool enable_async_append = true;
diff --git a/src/backend/optimizer/plan/Makefile b/src/backend/optimizer/plan/Makefile
index 80ef162e484..3abff3baf65 100644
--- a/src/backend/optimizer/plan/Makefile
+++ b/src/backend/optimizer/plan/Makefile
@@ -18,6 +18,7 @@ OBJS = \
initsplan.o \
planagg.o \
planmain.o \
+ planorexpand.o \
planner.o \
setrefs.o \
subselect.o
diff --git a/src/backend/optimizer/plan/meson.build b/src/backend/optimizer/plan/meson.build
index c565b2adbcc..7e900b3107b 100644
--- a/src/backend/optimizer/plan/meson.build
+++ b/src/backend/optimizer/plan/meson.build
@@ -6,6 +6,7 @@ backend_sources += files(
'initsplan.c',
'planagg.c',
'planmain.c',
+ 'planorexpand.c',
'planner.c',
'setrefs.c',
'subselect.c',
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index edaa42d713a..f0f0e526fe0 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1850,6 +1850,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction,
List *activeWindows = NIL;
grouping_sets_data *gset_data = NULL;
standard_qp_extra qp_extra;
+ List *or_cands;
/* A recursive query should always have setOperations */
Assert(!root->hasRecursion);
@@ -1925,6 +1926,11 @@ grouping_planner(PlannerInfo *root, double tuple_fraction,
if (parse->hasAggs)
preprocess_minmax_aggregates(root);
+ /*
+ * Consider expanding eligible OR clauses to UNION ALL (Append) paths.
+ */
+ or_cands = plan_or_expansion_arms(root);
+
/*
* Figure out whether there's a hard limit on the number of rows that
* query_planner's result subplan needs to return. Even if we know a
@@ -1961,6 +1967,13 @@ grouping_planner(PlannerInfo *root, double tuple_fraction,
*/
current_rel = query_planner(root, standard_qp_callback, &qp_extra);
+ /*
+ * If we generated any OR-expansion candidates, add the resulting
+ * Append path(s) to the topmost scan/join relation.
+ */
+ if (or_cands != NIL)
+ add_or_expansion_paths(root, current_rel, or_cands);
+
/*
* Convert the query's result tlist into PathTarget format.
*
diff --git a/src/backend/optimizer/plan/planorexpand.c b/src/backend/optimizer/plan/planorexpand.c
new file mode 100644
index 00000000000..6266c244f56
--- /dev/null
+++ b/src/backend/optimizer/plan/planorexpand.c
@@ -0,0 +1,531 @@
+/*-------------------------------------------------------------------------
+ *
+ * planorexpand.c
+ * Consider whether OR clauses in join queries can be expanded to
+ * UNION ALL (Append) paths.
+ *
+ * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/backend/optimizer/plan/planorexpand.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "catalog/pg_class.h"
+#include "catalog/pg_type.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "optimizer/clauses.h"
+#include "optimizer/cost.h"
+#include "optimizer/optimizer.h"
+#include "optimizer/pathnode.h"
+#include "optimizer/planmain.h"
+#include "optimizer/prep.h"
+#include "optimizer/tlist.h"
+
+
+static bool is_query_safe_for_or_expansion(PlannerInfo *root);
+static void or_expansion_qp_callback(PlannerInfo *root, void *extra);
+static List *create_or_expansion_subpaths(PlannerInfo *root, BoolExpr *orclause,
+ int qual_idx);
+static bool has_outer_joins(Node *jtnode);
+static bool match_target_expr(Node *want, Node *have);
+static Path *or_expansion_align_path(PlannerInfo *root, RelOptInfo *joinrel, Path *arm);
+
+/*
+ * is_query_safe_for_or_expansion
+ * Verify query-level safety gates for OR expansion.
+ */
+static bool
+is_query_safe_for_or_expansion(PlannerInfo *root)
+{
+ Query *parse = root->parse;
+ ListCell *lc;
+
+ /*
+ * Only top-level queries may be expanded. Expanding within subqueries
+ * leads to extreme planning overhead with nested subqueries.
+ */
+ if (root->parent_root != NULL)
+ return false;
+
+ /* Volatile functions in jointree (WHERE or FROM) are unsafe to duplicate */
+ if (contain_volatile_functions((Node *) parse->jointree))
+ return false;
+
+ /*
+ * Reject if the jointree contains any SubPlan or SubLink (in WHERE or
+ * outer join ON conditions). At this point, SS_process_sublinks() has
+ * already converted sublinks into SubPlan nodes; contain_subplans()
+ * catches SubPlans, InitPlans, and any remaining SubLinks.
+ */
+ if (contain_subplans((Node *) parse->jointree))
+ return false;
+
+ /* Disallow queries with lateral references */
+ if (root->hasLateralRTEs)
+ return false;
+
+ /*
+ * Check RTEs. OR-expansion is strictly designed for multi-table joins
+ * across base relations. Disallow non-base relations (derived subqueries,
+ * functions, values lists) and any relation subject to inheritance or
+ * partitioning. Those subsystems dynamically create subroots or modify
+ * the range table during planning, leaving side-channel state that cannot
+ * be preserved across independent arm planning.
+ */
+ foreach(lc, parse->rtable)
+ {
+ RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
+
+ if (rte->rtekind == RTE_JOIN)
+ {
+ if (rte->jointype == JOIN_FULL)
+ return false;
+ continue;
+ }
+
+ /* Only plain base relations are permitted */
+ if (rte->rtekind != RTE_RELATION)
+ return false;
+
+ /* Disallow partitioned tables and traditional table inheritance */
+ if (rte->inh || rte->relkind == RELKIND_PARTITIONED_TABLE)
+ return false;
+
+ /* Disallow tablesample (arms could sample different rows) */
+ if (rte->tablesample != NULL)
+ return false;
+
+ /* Security barrier quals must not contain volatile functions */
+ if (contain_volatile_functions((Node *) rte->securityQuals))
+ return false;
+ }
+
+ return true;
+}
+
+static bool
+has_outer_joins(Node *jtnode)
+{
+ if (jtnode == NULL)
+ return false;
+ if (IsA(jtnode, FromExpr))
+ {
+ FromExpr *f = (FromExpr *) jtnode;
+ ListCell *lc;
+
+ foreach(lc, f->fromlist)
+ {
+ if (has_outer_joins(lfirst(lc)))
+ return true;
+ }
+ return false;
+ }
+ if (IsA(jtnode, JoinExpr))
+ {
+ JoinExpr *j = (JoinExpr *) jtnode;
+
+ if (IS_OUTER_JOIN(j->jointype))
+ return true;
+ return has_outer_joins(j->larg) || has_outer_joins(j->rarg);
+ }
+
+ return false;
+}
+
+/*
+ * or_expansion_qp_callback
+ * Query planner callback for subroot arm planning.
+ */
+static void
+or_expansion_qp_callback(PlannerInfo *root, void *extra)
+{
+ /*
+ * Ordering is destroyed through UNION ALL / Append, so clear pathkeys
+ * that might otherwise restrict lower paths.
+ */
+ root->group_pathkeys = NIL;
+ root->window_pathkeys = NIL;
+ root->distinct_pathkeys = NIL;
+ root->sort_pathkeys = NIL;
+ root->query_pathkeys = NIL;
+}
+
+/*
+ * create_or_expansion_subpaths
+ * Create best paths for each arm of the candidate OR clause.
+ */
+static List *
+create_or_expansion_subpaths(PlannerInfo *root, BoolExpr *orclause, int qual_idx)
+{
+ List *subpaths = NIL;
+ int num_arms = list_length(orclause->args);
+ int k;
+
+ for (k = 0; k < num_arms; k++)
+ {
+ PlannerInfo *subroot;
+ Query *parse;
+ List *arm_quals = NIL;
+ ListCell *lc;
+ int q_idx;
+ Node *arm_k;
+ int j;
+ RelOptInfo *final_rel;
+ Path *subpath;
+
+ /* Clone PlannerInfo state modeled on planagg.c */
+ subroot = (PlannerInfo *) palloc(sizeof(PlannerInfo));
+ memcpy(subroot, root, sizeof(PlannerInfo));
+ subroot->parse = parse = (Query *) copyObject(root->parse);
+
+ /*
+ * subroot will set up its own rel/rte arrays inside query_planner.
+ */
+ subroot->simple_rel_array = NULL;
+ subroot->simple_rte_array = NULL;
+ subroot->simple_rel_array_size = 0;
+
+ subroot->processed_tlist = (List *) copyObject(root->processed_tlist);
+ subroot->append_rel_list = (List *) copyObject(root->append_rel_list);
+ subroot->tuple_fraction = 1.0;
+ subroot->limit_tuples = -1.0;
+
+ /*
+ * give subroot its own JoinDomain and reset planner working lists so
+ * query_planner(subroot) does not corrupt root's state.
+ */
+ subroot->join_domains = list_make1(makeNode(JoinDomain));
+ subroot->plan_params = NIL;
+ subroot->outer_params = NULL;
+ subroot->rowMarks = NIL;
+ subroot->init_plans = NIL;
+ subroot->agginfos = NIL;
+ subroot->aggtransinfos = NIL;
+ subroot->join_info_list = NIL;
+ subroot->eq_classes = NIL;
+ subroot->placeholder_list = NIL;
+ subroot->placeholder_array = NULL;
+ subroot->placeholder_array_size = 0;
+ subroot->left_join_clauses = NIL;
+ subroot->right_join_clauses = NIL;
+ subroot->full_join_clauses = NIL;
+ subroot->fkey_list = NIL;
+ subroot->initial_rels = NIL;
+ subroot->last_rinfo_serial = 0;
+
+ /*
+ * Build arm quals: R AND C_k AND (C_0 IS NOT TRUE) AND ... AND
+ * (C_{k-1} IS NOT TRUE)
+ */
+ q_idx = 0;
+ foreach(lc, (List *) root->parse->jointree->quals)
+ {
+ if (q_idx != qual_idx)
+ arm_quals = lappend(arm_quals, copyObject(lfirst(lc)));
+ q_idx++;
+ }
+
+ /* Add arm k's disjunct, flattened if it is an AND expression */
+ arm_k = (Node *) copyObject(list_nth(orclause->args, k));
+ arm_quals = list_concat(arm_quals, make_ands_implicit((Expr *) arm_k));
+
+ /* Add null-safe exclusion conditions for all previous arms */
+ for (j = 0; j < k; j++)
+ {
+ Node *prev_arm = (Node *) copyObject(list_nth(orclause->args, j));
+ BooleanTest *btest;
+
+ btest = makeNode(BooleanTest);
+ btest->arg = (Expr *) prev_arm;
+ btest->booltesttype = IS_NOT_TRUE;
+ btest->location = -1;
+
+ arm_quals = lappend(arm_quals, btest);
+ }
+
+ parse->jointree->quals = (Node *) arm_quals;
+
+ /* Re-run reduce_outer_joins if rtable contains outer joins */
+ if (has_outer_joins((Node *) parse->jointree))
+ reduce_outer_joins(subroot);
+
+ /* Plan this arm */
+ final_rel = query_planner(subroot, or_expansion_qp_callback, NULL);
+
+ if (!final_rel || !final_rel->cheapest_total_path ||
+ final_rel->cheapest_total_path->param_info != NULL)
+ return NIL;
+
+ subpath = final_rel->cheapest_total_path;
+ subpaths = lappend(subpaths, subpath);
+ }
+
+ return subpaths;
+}
+
+/*
+ * plan_or_expansion_arms
+ * Check eligibility and generate subpaths for OR clauses in top-level WHERE.
+ * Called by grouping_planner() before query_planner().
+ */
+List *
+plan_or_expansion_arms(PlannerInfo *root)
+{
+ List *results = NIL;
+ Query *parse = root->parse;
+ bool checked_query = false;
+ ListCell *lc;
+ int qual_idx;
+
+ /* Cheap gates first */
+ if (or_expansion_limit < 2)
+ return NIL;
+
+ if (parse->commandType != CMD_SELECT ||
+ parse->rowMarks != NIL ||
+ parse->setOperations != NULL ||
+ parse->cteList != NIL ||
+ root->hasRecursion)
+ return NIL;
+
+ if (list_length(parse->rtable) < 2)
+ return NIL;
+
+ if (parse->jointree == NULL || parse->jointree->quals == NULL)
+ return NIL;
+
+ /* Scan top-level WHERE quals for eligible OR clauses */
+ qual_idx = 0;
+ foreach(lc, (List *) parse->jointree->quals)
+ {
+ Node *qual = (Node *) lfirst(lc);
+
+ if (is_orclause(qual))
+ {
+ BoolExpr *orclause = (BoolExpr *) qual;
+ int num_arms = list_length(orclause->args);
+ Relids all_arm_varnos = NULL;
+ ListCell *lc_arm;
+
+ if (num_arms < 2 || num_arms > or_expansion_limit)
+ {
+ qual_idx++;
+ continue;
+ }
+
+ /* Structural case gate: check referenced relations */
+ foreach(lc_arm, orclause->args)
+ {
+ Node *arm = (Node *) lfirst(lc_arm);
+ Relids arm_varnos = pull_varnos(root, arm);
+
+ all_arm_varnos = bms_join(all_arm_varnos, arm_varnos);
+ }
+
+ /*
+ * Require the OR clause to reference at least 2 distinct
+ * relations.
+ */
+ if (bms_num_members(all_arm_varnos) < 2)
+ {
+ bms_free(all_arm_varnos);
+ qual_idx++;
+ continue;
+ }
+ bms_free(all_arm_varnos);
+
+ /* Query-level safety check */
+ if (!checked_query)
+ {
+ if (!is_query_safe_for_or_expansion(root))
+ return NIL;
+ checked_query = true;
+ }
+
+ /* Transform OR clause and generate subpaths for each arm */
+ {
+ List *subpaths = create_or_expansion_subpaths(root, orclause, qual_idx);
+
+ if (subpaths != NIL)
+ results = lappend(results, subpaths);
+ }
+ }
+ qual_idx++;
+ }
+
+ return results;
+}
+
+/*
+ * match_target_expr
+ * Check if two targetlist expressions match, ignoring varnullingrels
+ * differences that may arise from outer join reduction in subroot.
+ */
+static bool
+match_target_expr(Node *want, Node *have)
+{
+ if (want == have)
+ return true;
+ if (want == NULL || have == NULL)
+ return false;
+ if (nodeTag(want) != nodeTag(have))
+ return false;
+
+ if (IsA(want, Var))
+ {
+ Var *v1 = (Var *) want;
+ Var *v2 = (Var *) have;
+
+ return (v1->varno == v2->varno &&
+ v1->varattno == v2->varattno &&
+ v1->varlevelsup == v2->varlevelsup);
+ }
+
+ if (IsA(want, PlaceHolderVar))
+ {
+ PlaceHolderVar *p1 = (PlaceHolderVar *) want;
+ PlaceHolderVar *p2 = (PlaceHolderVar *) have;
+
+ return (p1->phid == p2->phid &&
+ p1->phlevelsup == p2->phlevelsup);
+ }
+
+ return equal(want, have);
+}
+
+/*
+ * or_expansion_align_path
+ * Ensure the arm path emits columns in the exact order needed by joinrel.
+ * If column order differs, wrap with a projection using the arm's own
+ * expressions.
+ */
+static Path *
+or_expansion_align_path(PlannerInfo *root, RelOptInfo *joinrel, Path *arm)
+{
+ List *want = joinrel->reltarget->exprs;
+ List *have = arm->pathtarget->exprs;
+ List *new_exprs = NIL;
+ bool identical = true;
+ ListCell *lc_want;
+ ListCell *lc_have;
+ PathTarget *newtarget;
+
+ if (list_length(want) != list_length(have))
+ identical = false;
+
+ lc_have = list_head(have);
+ foreach(lc_want, want)
+ {
+ Node *want_node = (Node *) lfirst(lc_want);
+ Node *matched = NULL;
+ ListCell *lc;
+
+ if (identical && lc_have != NULL)
+ {
+ if (match_target_expr(want_node, (Node *) lfirst(lc_have)))
+ {
+ matched = (Node *) lfirst(lc_have);
+ lc_have = lnext(have, lc_have);
+ }
+ else
+ identical = false;
+ }
+
+ if (matched == NULL)
+ {
+ foreach(lc, have)
+ {
+ Node *have_node = (Node *) lfirst(lc);
+
+ if (match_target_expr(want_node, have_node))
+ {
+ matched = have_node;
+ break;
+ }
+ }
+ }
+
+ if (matched == NULL)
+ return NULL;
+
+ new_exprs = lappend(new_exprs, matched);
+ }
+
+ if (identical)
+ return arm;
+
+ newtarget = create_empty_pathtarget();
+ newtarget->exprs = new_exprs;
+ set_pathtarget_cost_width(root, newtarget);
+
+ /* Apply projection in-place if supported, or wrap with a ProjectionPath */
+ return apply_projection_to_path(root, joinrel, arm, newtarget);
+}
+
+/*
+ * add_or_expansion_paths
+ * Combine arm subpaths into an AppendPath and add to the top join rel.
+ * Called by grouping_planner() right after query_planner().
+ */
+void
+add_or_expansion_paths(PlannerInfo *root, RelOptInfo *joinrel, List *or_cands)
+{
+ ListCell *lc;
+ bool added_any = false;
+
+ if (joinrel == NULL || joinrel->reltarget == NULL)
+ return;
+
+ foreach(lc, or_cands)
+ {
+ List *subpaths = (List *) lfirst(lc);
+ List *aligned_subpaths = NIL;
+ ListCell *lc_subpath;
+ bool valid = true;
+ AppendPathInput ainput;
+ AppendPath *appendpath;
+
+ foreach(lc_subpath, subpaths)
+ {
+ Path *subpath = (Path *) lfirst(lc_subpath);
+ Path *aligned;
+
+ aligned = or_expansion_align_path(root, joinrel, subpath);
+ if (aligned == NULL)
+ {
+ valid = false;
+ break;
+ }
+ aligned_subpaths = lappend(aligned_subpaths, aligned);
+ }
+
+ if (!valid)
+ continue;
+
+ ainput.subpaths = aligned_subpaths;
+ ainput.partial_subpaths = NIL;
+ ainput.child_append_relid_sets = NIL;
+
+ /* Build Append path combining arm subpaths */
+ appendpath = create_append_path(root,
+ joinrel,
+ ainput,
+ NIL,
+ NULL,
+ 0,
+ false,
+ -1);
+
+ add_path(joinrel, (Path *) appendpath);
+ added_any = true;
+ }
+
+ /* We need to refigure which is the cheapest path for the joinrel */
+ if (added_any)
+ set_cheapest(joinrel);
+}
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 380679a76c0..8f6d63d0726 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -2319,6 +2319,16 @@
ifdef => 'DEBUG_BOUNDED_SORT',
},
+{ name => 'or_expansion_limit', type => 'int', context => 'PGC_USERSET', group => 'QUERY_TUNING_OTHER',
+ short_desc => 'Sets the maximum number of OR arms considered for OR-expansion.',
+ long_desc => 'The planner will consider expanding OR clauses if they have no more than this many arms. 0 disables OR-expansion.',
+ flags => 'GUC_EXPLAIN',
+ variable => 'or_expansion_limit',
+ boot_val => '8',
+ min => '0',
+ max => 'INT_MAX',
+},
+
{ name => 'output_plugin_libraries', type => 'string', context => 'PGC_SUSET', group => 'REPLICATION_SENDING',
short_desc => 'Lists libraries that may be named as logical decoding output plugins.',
long_desc => 'Users with REPLICATION privileges may only use plugins in this list when creating logical replication slots.',
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 01f98de8a9f..b11ea2a847e 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -495,6 +495,7 @@
#jit = off # allow JIT compilation
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses
+#or_expansion_limit = 8 # 0 disables OR-expansion to UNION ALL
#plan_cache_mode = auto # auto, force_generic_plan or
# force_custom_plan
#recursive_worktable_factor = 10.0 # range 0.001-1000000
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 14255c900fc..f5e22c39c6c 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -68,6 +68,7 @@ extern PGDLLIMPORT bool enable_partitionwise_join;
extern PGDLLIMPORT bool enable_partitionwise_aggregate;
extern PGDLLIMPORT bool enable_parallel_append;
extern PGDLLIMPORT bool enable_parallel_hash;
+extern PGDLLIMPORT int or_expansion_limit;
extern PGDLLIMPORT bool enable_partition_pruning;
extern PGDLLIMPORT bool enable_presorted_aggregate;
extern PGDLLIMPORT bool enable_async_append;
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index c23ab568b98..e7c780d774c 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -36,6 +36,13 @@ extern RelOptInfo *query_planner(PlannerInfo *root,
*/
extern void preprocess_minmax_aggregates(PlannerInfo *root);
+/*
+ * prototypes for plan/planorexpand.c
+ */
+extern List *plan_or_expansion_arms(PlannerInfo *root);
+extern void add_or_expansion_paths(PlannerInfo *root, RelOptInfo *joinrel,
+ List *or_cands);
+
/*
* prototypes for plan/createplan.c
*/
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 4ee94d00ade..b8a23193969 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -12102,14 +12102,26 @@ WHERE ss.unique1 = ss.phv AND t1.unique1 < 100;
EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM tenk1 t1, tenk1 t2
WHERE t2.thousand = t1.tenthous OR t2.thousand = t1.unique1 OR t2.thousand = t1.unique2;
- QUERY PLAN
------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------
Aggregate
- -> Nested Loop
- -> Seq Scan on tenk1 t1
- -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
- Index Cond: (thousand = ANY (ARRAY[t1.tenthous, t1.unique1, t1.unique2]))
-(5 rows)
+ -> Append
+ -> Hash Join
+ Hash Cond: (t2.thousand = t1.tenthous)
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
+ -> Hash
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 t1
+ -> Merge Join
+ Merge Cond: (t1.unique1 = t2.thousand)
+ Join Filter: ((t2.thousand = t1.tenthous) IS NOT TRUE)
+ -> Index Scan using tenk1_unique1 on tenk1 t1
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
+ -> Merge Join
+ Merge Cond: (t1.unique2 = t2.thousand)
+ Join Filter: (((t2.thousand = t1.tenthous) IS NOT TRUE) AND ((t2.thousand = t1.unique1) IS NOT TRUE))
+ -> Index Scan using tenk1_unique2 on tenk1 t1
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
+(17 rows)
SELECT COUNT(*) FROM tenk1 t1, tenk1 t2
WHERE t2.thousand = t1.tenthous OR t2.thousand = t1.unique1 OR t2.thousand = t1.unique2;
diff --git a/src/test/regress/expected/or_expansion.out b/src/test/regress/expected/or_expansion.out
new file mode 100644
index 00000000000..aae75315f27
--- /dev/null
+++ b/src/test/regress/expected/or_expansion.out
@@ -0,0 +1,249 @@
+--
+-- Tests for OR-expansion to UNION ALL (Append)
+--
+CREATE TABLE orexp_a (id int, val int);
+CREATE TABLE orexp_b (x int, y int, val int);
+INSERT INTO orexp_a VALUES
+ (1, 10),
+ (2, 20),
+ (3, 30),
+ (4, NULL),
+ (5, 50);
+INSERT INTO orexp_b VALUES
+ (1, 99, 100),
+ (99, 2, 200),
+ (3, 3, 300),
+ (4, 99, 400),
+ (99, 99, 500),
+ (NULL, NULL, 600);
+-- Verify GUC exists and can be toggled
+SHOW or_expansion_limit;
+ or_expansion_limit
+--------------------
+ 8
+(1 row)
+
+-- Case C3: Join condition OR (disjunction of join keys)
+-- With default or_expansion_limit = 8, planner should expand to Append of joins
+EXPLAIN (COSTS OFF)
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+ QUERY PLAN
+-------------------------------------------------------
+ Sort
+ Sort Key: a.id, b.x, b.y
+ -> Append
+ -> Merge Join
+ Merge Cond: (b.x = a.id)
+ -> Sort
+ Sort Key: b.x
+ -> Seq Scan on orexp_b b
+ -> Sort
+ Sort Key: a.id
+ -> Seq Scan on orexp_a a
+ -> Merge Join
+ Merge Cond: (b.y = a.id)
+ Join Filter: ((a.id = b.x) IS NOT TRUE)
+ -> Sort
+ Sort Key: b.y
+ -> Seq Scan on orexp_b b
+ -> Sort
+ Sort Key: a.id
+ -> Seq Scan on orexp_a a
+(20 rows)
+
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+ id | x | y
+----+----+----
+ 1 | 1 | 99
+ 2 | 99 | 2
+ 3 | 3 | 3
+ 4 | 4 | 99
+(4 rows)
+
+-- Result must match or_expansion_limit = 0 (disabled)
+SET or_expansion_limit = 0;
+EXPLAIN (COSTS OFF)
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+ QUERY PLAN
+-----------------------------------------------------
+ Sort
+ Sort Key: a.id, b.x, b.y
+ -> Nested Loop
+ Join Filter: ((a.id = b.x) OR (a.id = b.y))
+ -> Seq Scan on orexp_a a
+ -> Materialize
+ -> Seq Scan on orexp_b b
+(7 rows)
+
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+ id | x | y
+----+----+----
+ 1 | 1 | 99
+ 2 | 99 | 2
+ 3 | 3 | 3
+ 4 | 4 | 99
+(4 rows)
+
+-- Case C4: Cross-table filter with separate join key
+-- Tests null-safety, 3-valued logic, and duplicate exclusion
+SET or_expansion_limit = 8;
+EXPLAIN (COSTS OFF)
+SELECT a.id, a.val, b.val FROM orexp_a a JOIN orexp_b b ON a.id = b.x
+WHERE a.val = 10 OR b.val = 300
+ORDER BY a.id, a.val, b.val;
+ QUERY PLAN
+------------------------------------------------------
+ Sort
+ Sort Key: a.id, a.val, b.val
+ -> Append
+ -> Hash Join
+ Hash Cond: (b.x = a.id)
+ -> Seq Scan on orexp_b b
+ -> Hash
+ -> Seq Scan on orexp_a a
+ Filter: (val = 10)
+ -> Hash Join
+ Hash Cond: (a.id = b.x)
+ -> Seq Scan on orexp_a a
+ Filter: ((val = 10) IS NOT TRUE)
+ -> Hash
+ -> Seq Scan on orexp_b b
+ Filter: (val = 300)
+(16 rows)
+
+SELECT a.id, a.val, b.val FROM orexp_a a JOIN orexp_b b ON a.id = b.x
+WHERE a.val = 10 OR b.val = 300
+ORDER BY a.id, a.val, b.val;
+ id | val | val
+----+-----+-----
+ 1 | 10 | 100
+ 3 | 30 | 300
+(2 rows)
+
+SET or_expansion_limit = 0;
+SELECT a.id, a.val, b.val FROM orexp_a a JOIN orexp_b b ON a.id = b.x
+WHERE a.val = 10 OR b.val = 300
+ORDER BY a.id, a.val, b.val;
+ id | val | val
+----+-----+-----
+ 1 | 10 | 100
+ 3 | 30 | 300
+(2 rows)
+
+-- Case C5: Non-flattened subquery over ordinary table with cross-table OR
+-- Verifies expression_tree_walker is not called on RangeTblEntry nodes
+CREATE TABLE orexp_c (id int, tier text);
+CREATE TABLE orexp_d (cid int, val int);
+INSERT INTO orexp_c VALUES (1, 'vip'), (2, 'std');
+INSERT INTO orexp_d VALUES (1, 10), (1, 20), (2, 5);
+SET or_expansion_limit = 8;
+EXPLAIN (COSTS OFF)
+SELECT c.id, s.n FROM orexp_c c
+ JOIN (SELECT cid, count(*) n FROM orexp_d GROUP BY cid) s ON s.cid = c.id
+WHERE c.tier = 'vip' OR s.n > 20
+ORDER BY c.id;
+ QUERY PLAN
+-------------------------------------------------------------
+ Sort
+ Sort Key: c.id
+ -> Hash Join
+ Hash Cond: (c.id = s.cid)
+ Join Filter: ((c.tier = 'vip'::text) OR (s.n > 20))
+ -> Seq Scan on orexp_c c
+ -> Hash
+ -> Subquery Scan on s
+ -> HashAggregate
+ Group Key: orexp_d.cid
+ -> Seq Scan on orexp_d
+(11 rows)
+
+SELECT c.id, s.n FROM orexp_c c
+ JOIN (SELECT cid, count(*) n FROM orexp_d GROUP BY cid) s ON s.cid = c.id
+WHERE c.tier = 'vip' OR s.n > 20
+ORDER BY c.id;
+ id | n
+----+---
+ 1 | 2
+(1 row)
+
+SET or_expansion_limit = 0;
+SELECT c.id, s.n FROM orexp_c c
+ JOIN (SELECT cid, count(*) n FROM orexp_d GROUP BY cid) s ON s.cid = c.id
+WHERE c.tier = 'vip' OR s.n > 20
+ORDER BY c.id;
+ id | n
+----+---
+ 1 | 2
+(1 row)
+
+DROP TABLE orexp_c;
+DROP TABLE orexp_d;
+-- Case C6: Subquery over partitioned table (gate must reject OR expansion)
+CREATE TABLE orexp_part (id int, d int) PARTITION BY RANGE (d);
+CREATE TABLE orexp_part1 PARTITION OF orexp_part FOR VALUES FROM (0) TO (10);
+CREATE TABLE orexp_part2 PARTITION OF orexp_part FOR VALUES FROM (10) TO (20);
+INSERT INTO orexp_part VALUES (1, 5), (2, 15);
+SET or_expansion_limit = 8;
+EXPLAIN (COSTS OFF)
+SELECT a.id, s.d FROM orexp_a a,
+ (SELECT id, d FROM orexp_part GROUP BY id, d) s
+WHERE a.id = s.id OR s.d = 15
+ORDER BY a.id, s.d;
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Sort
+ Sort Key: a.id, s.d
+ -> Nested Loop
+ Join Filter: ((a.id = s.id) OR (s.d = 15))
+ -> Seq Scan on orexp_a a
+ -> Materialize
+ -> Subquery Scan on s
+ -> HashAggregate
+ Group Key: orexp_part.id, orexp_part.d
+ -> Append
+ -> Seq Scan on orexp_part1 orexp_part_1
+ -> Seq Scan on orexp_part2 orexp_part_2
+(12 rows)
+
+SELECT a.id, s.d FROM orexp_a a,
+ (SELECT id, d FROM orexp_part GROUP BY id, d) s
+WHERE a.id = s.id OR s.d = 15
+ORDER BY a.id, s.d;
+ id | d
+----+----
+ 1 | 5
+ 1 | 15
+ 2 | 15
+ 3 | 15
+ 4 | 15
+ 5 | 15
+(6 rows)
+
+SET or_expansion_limit = 0;
+SELECT a.id, s.d FROM orexp_a a,
+ (SELECT id, d FROM orexp_part GROUP BY id, d) s
+WHERE a.id = s.id OR s.d = 15
+ORDER BY a.id, s.d;
+ id | d
+----+----
+ 1 | 5
+ 1 | 15
+ 2 | 15
+ 3 | 15
+ 4 | 15
+ 5 | 15
+(6 rows)
+
+DROP TABLE orexp_part;
+-- Clean up
+RESET or_expansion_limit;
+DROP TABLE orexp_a;
+DROP TABLE orexp_b;
diff --git a/src/test/regress/sql/or_expansion.sql b/src/test/regress/sql/or_expansion.sql
new file mode 100644
index 00000000000..a3a035d6f05
--- /dev/null
+++ b/src/test/regress/sql/or_expansion.sql
@@ -0,0 +1,122 @@
+--
+-- Tests for OR-expansion to UNION ALL (Append)
+--
+
+CREATE TABLE orexp_a (id int, val int);
+CREATE TABLE orexp_b (x int, y int, val int);
+
+INSERT INTO orexp_a VALUES
+ (1, 10),
+ (2, 20),
+ (3, 30),
+ (4, NULL),
+ (5, 50);
+
+INSERT INTO orexp_b VALUES
+ (1, 99, 100),
+ (99, 2, 200),
+ (3, 3, 300),
+ (4, 99, 400),
+ (99, 99, 500),
+ (NULL, NULL, 600);
+
+-- Verify GUC exists and can be toggled
+SHOW or_expansion_limit;
+
+-- Case C3: Join condition OR (disjunction of join keys)
+-- With default or_expansion_limit = 8, planner should expand to Append of joins
+EXPLAIN (COSTS OFF)
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+
+-- Result must match or_expansion_limit = 0 (disabled)
+SET or_expansion_limit = 0;
+EXPLAIN (COSTS OFF)
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+
+-- Case C4: Cross-table filter with separate join key
+-- Tests null-safety, 3-valued logic, and duplicate exclusion
+SET or_expansion_limit = 8;
+EXPLAIN (COSTS OFF)
+SELECT a.id, a.val, b.val FROM orexp_a a JOIN orexp_b b ON a.id = b.x
+WHERE a.val = 10 OR b.val = 300
+ORDER BY a.id, a.val, b.val;
+
+SELECT a.id, a.val, b.val FROM orexp_a a JOIN orexp_b b ON a.id = b.x
+WHERE a.val = 10 OR b.val = 300
+ORDER BY a.id, a.val, b.val;
+
+SET or_expansion_limit = 0;
+SELECT a.id, a.val, b.val FROM orexp_a a JOIN orexp_b b ON a.id = b.x
+WHERE a.val = 10 OR b.val = 300
+ORDER BY a.id, a.val, b.val;
+
+-- Case C5: Non-flattened subquery over ordinary table with cross-table OR
+-- Verifies expression_tree_walker is not called on RangeTblEntry nodes
+CREATE TABLE orexp_c (id int, tier text);
+CREATE TABLE orexp_d (cid int, val int);
+INSERT INTO orexp_c VALUES (1, 'vip'), (2, 'std');
+INSERT INTO orexp_d VALUES (1, 10), (1, 20), (2, 5);
+
+SET or_expansion_limit = 8;
+EXPLAIN (COSTS OFF)
+SELECT c.id, s.n FROM orexp_c c
+ JOIN (SELECT cid, count(*) n FROM orexp_d GROUP BY cid) s ON s.cid = c.id
+WHERE c.tier = 'vip' OR s.n > 20
+ORDER BY c.id;
+
+SELECT c.id, s.n FROM orexp_c c
+ JOIN (SELECT cid, count(*) n FROM orexp_d GROUP BY cid) s ON s.cid = c.id
+WHERE c.tier = 'vip' OR s.n > 20
+ORDER BY c.id;
+
+SET or_expansion_limit = 0;
+SELECT c.id, s.n FROM orexp_c c
+ JOIN (SELECT cid, count(*) n FROM orexp_d GROUP BY cid) s ON s.cid = c.id
+WHERE c.tier = 'vip' OR s.n > 20
+ORDER BY c.id;
+
+DROP TABLE orexp_c;
+DROP TABLE orexp_d;
+
+-- Case C6: Subquery over partitioned table (gate must reject OR expansion)
+CREATE TABLE orexp_part (id int, d int) PARTITION BY RANGE (d);
+CREATE TABLE orexp_part1 PARTITION OF orexp_part FOR VALUES FROM (0) TO (10);
+CREATE TABLE orexp_part2 PARTITION OF orexp_part FOR VALUES FROM (10) TO (20);
+INSERT INTO orexp_part VALUES (1, 5), (2, 15);
+
+SET or_expansion_limit = 8;
+EXPLAIN (COSTS OFF)
+SELECT a.id, s.d FROM orexp_a a,
+ (SELECT id, d FROM orexp_part GROUP BY id, d) s
+WHERE a.id = s.id OR s.d = 15
+ORDER BY a.id, s.d;
+
+SELECT a.id, s.d FROM orexp_a a,
+ (SELECT id, d FROM orexp_part GROUP BY id, d) s
+WHERE a.id = s.id OR s.d = 15
+ORDER BY a.id, s.d;
+
+SET or_expansion_limit = 0;
+SELECT a.id, s.d FROM orexp_a a,
+ (SELECT id, d FROM orexp_part GROUP BY id, d) s
+WHERE a.id = s.id OR s.d = 15
+ORDER BY a.id, s.d;
+
+DROP TABLE orexp_part;
+
+-- Clean up
+RESET or_expansion_limit;
+DROP TABLE orexp_a;
+DROP TABLE orexp_b;
diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index 7bbddb8bee4..ca653439b01 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -162,6 +162,7 @@ bool enable_partitionwise_join = false;
bool enable_partitionwise_aggregate = false;
bool enable_parallel_append = true;
bool enable_parallel_hash = true;
+int or_expansion_limit = 8;
bool enable_partition_pruning = true;
bool enable_presorted_aggregate = true;
bool enable_async_append = true;
diff --git a/src/backend/optimizer/plan/Makefile b/src/backend/optimizer/plan/Makefile
index 80ef162e484..3abff3baf65 100644
--- a/src/backend/optimizer/plan/Makefile
+++ b/src/backend/optimizer/plan/Makefile
@@ -18,6 +18,7 @@ OBJS = \
initsplan.o \
planagg.o \
planmain.o \
+ planorexpand.o \
planner.o \
setrefs.o \
subselect.o
diff --git a/src/backend/optimizer/plan/meson.build b/src/backend/optimizer/plan/meson.build
index c565b2adbcc..7e900b3107b 100644
--- a/src/backend/optimizer/plan/meson.build
+++ b/src/backend/optimizer/plan/meson.build
@@ -6,6 +6,7 @@ backend_sources += files(
'initsplan.c',
'planagg.c',
'planmain.c',
+ 'planorexpand.c',
'planner.c',
'setrefs.c',
'subselect.c',
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index edaa42d713a..f0f0e526fe0 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -1850,6 +1850,7 @@ grouping_planner(PlannerInfo *root, double tuple_fraction,
List *activeWindows = NIL;
grouping_sets_data *gset_data = NULL;
standard_qp_extra qp_extra;
+ List *or_cands;
/* A recursive query should always have setOperations */
Assert(!root->hasRecursion);
@@ -1925,6 +1926,11 @@ grouping_planner(PlannerInfo *root, double tuple_fraction,
if (parse->hasAggs)
preprocess_minmax_aggregates(root);
+ /*
+ * Consider expanding eligible OR clauses to UNION ALL (Append) paths.
+ */
+ or_cands = plan_or_expansion_arms(root);
+
/*
* Figure out whether there's a hard limit on the number of rows that
* query_planner's result subplan needs to return. Even if we know a
@@ -1961,6 +1967,13 @@ grouping_planner(PlannerInfo *root, double tuple_fraction,
*/
current_rel = query_planner(root, standard_qp_callback, &qp_extra);
+ /*
+ * If we generated any OR-expansion candidates, add the resulting
+ * Append path(s) to the topmost scan/join relation.
+ */
+ if (or_cands != NIL)
+ add_or_expansion_paths(root, current_rel, or_cands);
+
/*
* Convert the query's result tlist into PathTarget format.
*
diff --git a/src/backend/optimizer/plan/planorexpand.c b/src/backend/optimizer/plan/planorexpand.c
new file mode 100644
index 00000000000..3c5e46768f1
--- /dev/null
+++ b/src/backend/optimizer/plan/planorexpand.c
@@ -0,0 +1,587 @@
+/*-------------------------------------------------------------------------
+ *
+ * planorexpand.c
+ * Consider whether OR clauses in join queries can be expanded to
+ * UNION ALL (Append) paths.
+ *
+ * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/backend/optimizer/plan/planorexpand.c
+ *
+ *-------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "catalog/pg_class.h"
+#include "catalog/pg_type.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "optimizer/clauses.h"
+#include "optimizer/cost.h"
+#include "optimizer/optimizer.h"
+#include "optimizer/pathnode.h"
+#include "optimizer/planmain.h"
+#include "optimizer/prep.h"
+#include "optimizer/tlist.h"
+
+
+static bool is_query_safe_for_or_expansion(PlannerInfo *root);
+static bool has_partitioned_table_walker(Node *node, void *ctx);
+static void or_expansion_qp_callback(PlannerInfo *root, void *extra);
+static List *create_or_expansion_subpaths(PlannerInfo *root, BoolExpr *orclause,
+ int qual_idx);
+static bool has_outer_joins(Node *jtnode);
+static bool match_target_expr(Node *want, Node *have);
+static Path *or_expansion_align_path(PlannerInfo *root, RelOptInfo *joinrel, Path *arm);
+
+/*
+ * has_partitioned_table_walker
+ * Return true if the query (or any subquery within it) references a
+ * partitioned table. Used by is_query_safe_for_or_expansion to catch
+ * partitioned tables that are hidden inside derived-table subqueries.
+ */
+static bool
+has_partitioned_table_walker(Node *node, void *ctx)
+{
+ if (node == NULL)
+ return false;
+ if (IsA(node, RangeTblEntry))
+ {
+ RangeTblEntry *rte = (RangeTblEntry *) node;
+
+ if (rte->rtekind == RTE_RELATION &&
+ rte->relkind == RELKIND_PARTITIONED_TABLE)
+ return true;
+ /* subquery contents are visited by query_tree_walker automatically */
+ return false;
+ }
+ if (IsA(node, Query))
+ return query_tree_walker((Query *) node,
+ has_partitioned_table_walker, ctx,
+ QTW_EXAMINE_RTES_BEFORE);
+ return expression_tree_walker(node, has_partitioned_table_walker, ctx);
+}
+
+/*
+ * is_query_safe_for_or_expansion
+ * Verify query-level safety gates for OR expansion.
+ */
+static bool
+is_query_safe_for_or_expansion(PlannerInfo *root)
+{
+ Query *parse = root->parse;
+ ListCell *lc;
+
+ /*
+ * Only top-level queries may be expanded. Expanding within subqueries
+ * leads to extreme planning overhead with nested subqueries.
+ */
+ if (root->parent_root != NULL)
+ return false;
+
+ /* Volatile functions in jointree (WHERE or FROM) are unsafe to duplicate */
+ if (contain_volatile_functions((Node *) parse->jointree))
+ return false;
+
+ /*
+ * Reject if the WHERE quals being duplicated contain any SubPlan or
+ * SubLink. At this point, SS_process_sublinks() has already converted
+ * WHERE sublinks into SubPlan nodes; contain_subplans() catches SubPlans,
+ * InitPlans, and any remaining SubLinks.
+ */
+ if (contain_subplans((Node *) parse->jointree->quals))
+ return false;
+
+ /* Disallow queries with lateral references */
+ if (root->hasLateralRTEs)
+ return false;
+
+ /* Check RTEs */
+ foreach(lc, parse->rtable)
+ {
+ RangeTblEntry *rte = (RangeTblEntry *) lfirst(lc);
+
+ /*
+ * Do not attempt transformation if the query contains FULL joins.
+ */
+ if (rte->rtekind == RTE_JOIN && rte->jointype == JOIN_FULL)
+ return false;
+
+ /*
+ * Disallow partitioned tables (directly or inside any subquery) until
+ * arm partition pruning and rangetable index synchronization are
+ * supported. A partitioned table hidden inside a derived-table
+ * subquery triggers the same index-mismatch as a top-level one.
+ */
+ if (rte->rtekind == RTE_RELATION &&
+ rte->relkind == RELKIND_PARTITIONED_TABLE)
+ return false;
+ if (rte->rtekind == RTE_SUBQUERY &&
+ has_partitioned_table_walker((Node *) rte->subquery, NULL))
+ return false;
+
+ /* Disallow tablesample (arms could sample different rows ?) */
+ if (rte->tablesample != NULL)
+ return false;
+
+ /* Security barrier quals must not contain volatile functions */
+ if (contain_volatile_functions((Node *) rte->securityQuals))
+ return false;
+
+ /* Disallow volatile function RTEs */
+ if (rte->rtekind == RTE_FUNCTION &&
+ contain_volatile_functions((Node *) rte->functions))
+ return false;
+
+ /* Disallow volatile expressions in subquery/values/tablefunc */
+ if (rte->rtekind == RTE_SUBQUERY &&
+ contain_volatile_functions((Node *) rte->subquery))
+ return false;
+
+ /*
+ * Derived tables with un-grouped aggregates may creating
+ * MinMaxAggPath nodes whose param replacements in setrefs.c require
+ * subroot state that is not preserved across arm planning.
+ */
+ if (rte->rtekind == RTE_SUBQUERY &&
+ rte->subquery != NULL &&
+ rte->subquery->hasAggs &&
+ rte->subquery->groupClause == NIL)
+ return false;
+
+ if (rte->rtekind == RTE_VALUES &&
+ contain_volatile_functions((Node *) rte->values_lists))
+ return false;
+
+ if (rte->rtekind == RTE_TABLEFUNC &&
+ contain_volatile_functions((Node *) rte->tablefunc))
+ return false;
+
+ }
+
+ return true;
+}
+
+static bool
+has_outer_joins(Node *jtnode)
+{
+ if (jtnode == NULL)
+ return false;
+ if (IsA(jtnode, FromExpr))
+ {
+ FromExpr *f = (FromExpr *) jtnode;
+ ListCell *lc;
+
+ foreach(lc, f->fromlist)
+ {
+ if (has_outer_joins(lfirst(lc)))
+ return true;
+ }
+ return false;
+ }
+ if (IsA(jtnode, JoinExpr))
+ {
+ JoinExpr *j = (JoinExpr *) jtnode;
+
+ if (IS_OUTER_JOIN(j->jointype))
+ return true;
+ return has_outer_joins(j->larg) || has_outer_joins(j->rarg);
+ }
+
+ return false;
+}
+
+/*
+ * or_expansion_qp_callback
+ * Query planner callback for subroot arm planning.
+ */
+static void
+or_expansion_qp_callback(PlannerInfo *root, void *extra)
+{
+ /*
+ * Ordering is destroyed through UNION ALL / Append, so clear pathkeys
+ * that might otherwise restrict lower paths.
+ */
+ root->group_pathkeys = NIL;
+ root->window_pathkeys = NIL;
+ root->distinct_pathkeys = NIL;
+ root->sort_pathkeys = NIL;
+ root->query_pathkeys = NIL;
+}
+
+/*
+ * create_or_expansion_subpaths
+ * Create best paths for each arm of the candidate OR clause.
+ */
+static List *
+create_or_expansion_subpaths(PlannerInfo *root, BoolExpr *orclause, int qual_idx)
+{
+ List *subpaths = NIL;
+ int num_arms = list_length(orclause->args);
+ int k;
+
+ for (k = 0; k < num_arms; k++)
+ {
+ PlannerInfo *subroot;
+ Query *parse;
+ List *arm_quals = NIL;
+ ListCell *lc;
+ int q_idx;
+ Node *arm_k;
+ int j;
+ RelOptInfo *final_rel;
+ Path *subpath;
+
+ /* Clone PlannerInfo state modeled on planagg.c */
+ subroot = (PlannerInfo *) palloc(sizeof(PlannerInfo));
+ memcpy(subroot, root, sizeof(PlannerInfo));
+ subroot->parse = parse = (Query *) copyObject(root->parse);
+
+ /*
+ * subroot will set up its own rel/rte arrays inside query_planner.
+ */
+ subroot->simple_rel_array = NULL;
+ subroot->simple_rte_array = NULL;
+ subroot->simple_rel_array_size = 0;
+
+ subroot->processed_tlist = (List *) copyObject(root->processed_tlist);
+ subroot->append_rel_list = (List *) copyObject(root->append_rel_list);
+ subroot->tuple_fraction = 1.0;
+ subroot->limit_tuples = -1.0;
+
+ /*
+ * give subroot its own JoinDomain and reset planner working lists so
+ * query_planner(subroot) does not corrupt root's state.
+ */
+ subroot->join_domains = list_make1(makeNode(JoinDomain));
+ subroot->plan_params = NIL;
+ subroot->outer_params = NULL;
+ subroot->rowMarks = NIL;
+ subroot->init_plans = NIL;
+ subroot->agginfos = NIL;
+ subroot->aggtransinfos = NIL;
+ subroot->join_info_list = NIL;
+ subroot->eq_classes = NIL;
+ subroot->placeholder_list = NIL;
+ subroot->placeholder_array = NULL;
+ subroot->placeholder_array_size = 0;
+ subroot->left_join_clauses = NIL;
+ subroot->right_join_clauses = NIL;
+ subroot->full_join_clauses = NIL;
+ subroot->fkey_list = NIL;
+ subroot->initial_rels = NIL;
+ subroot->last_rinfo_serial = 0;
+
+ /*
+ * Build arm quals: R AND C_k AND (C_0 IS NOT TRUE) AND ... AND
+ * (C_{k-1} IS NOT TRUE)
+ */
+ q_idx = 0;
+ foreach(lc, (List *) root->parse->jointree->quals)
+ {
+ if (q_idx != qual_idx)
+ arm_quals = lappend(arm_quals, copyObject(lfirst(lc)));
+ q_idx++;
+ }
+
+ /* Add arm k's disjunct, flattened if it is an AND expression */
+ arm_k = (Node *) copyObject(list_nth(orclause->args, k));
+ arm_quals = list_concat(arm_quals, make_ands_implicit((Expr *) arm_k));
+
+ /* Add null-safe exclusion conditions for all previous arms */
+ for (j = 0; j < k; j++)
+ {
+ Node *prev_arm = (Node *) copyObject(list_nth(orclause->args, j));
+ BooleanTest *btest;
+
+ btest = makeNode(BooleanTest);
+ btest->arg = (Expr *) prev_arm;
+ btest->booltesttype = IS_NOT_TRUE;
+ btest->location = -1;
+
+ arm_quals = lappend(arm_quals, btest);
+ }
+
+ parse->jointree->quals = (Node *) arm_quals;
+
+ /* Re-run reduce_outer_joins if rtable contains outer joins */
+ if (has_outer_joins((Node *) parse->jointree))
+ reduce_outer_joins(subroot);
+
+ /* Plan this arm */
+ final_rel = query_planner(subroot, or_expansion_qp_callback, NULL);
+
+ if (!final_rel || !final_rel->cheapest_total_path ||
+ final_rel->cheapest_total_path->param_info != NULL)
+ return NIL;
+
+ subpath = final_rel->cheapest_total_path;
+ subpaths = lappend(subpaths, subpath);
+ }
+
+ return subpaths;
+}
+
+/*
+ * plan_or_expansion_arms
+ * Check eligibility and generate subpaths for OR clauses in top-level WHERE.
+ * Called by grouping_planner() before query_planner().
+ */
+List *
+plan_or_expansion_arms(PlannerInfo *root)
+{
+ List *results = NIL;
+ Query *parse = root->parse;
+ bool checked_query = false;
+ ListCell *lc;
+ int qual_idx;
+
+ /* Cheap gates first */
+ if (or_expansion_limit < 2)
+ return NIL;
+
+ if (parse->commandType != CMD_SELECT ||
+ parse->rowMarks != NIL ||
+ parse->setOperations != NULL ||
+ parse->cteList != NIL ||
+ root->hasRecursion)
+ return NIL;
+
+ if (list_length(parse->rtable) < 2)
+ return NIL;
+
+ if (parse->jointree == NULL || parse->jointree->quals == NULL)
+ return NIL;
+
+ /* Scan top-level WHERE quals for eligible OR clauses */
+ qual_idx = 0;
+ foreach(lc, (List *) parse->jointree->quals)
+ {
+ Node *qual = (Node *) lfirst(lc);
+
+ if (is_orclause(qual))
+ {
+ BoolExpr *orclause = (BoolExpr *) qual;
+ int num_arms = list_length(orclause->args);
+ Relids all_arm_varnos = NULL;
+ ListCell *lc_arm;
+
+ if (num_arms < 2 || num_arms > or_expansion_limit)
+ {
+ qual_idx++;
+ continue;
+ }
+
+ /* Structural case gate: check referenced relations */
+ foreach(lc_arm, orclause->args)
+ {
+ Node *arm = (Node *) lfirst(lc_arm);
+ Relids arm_varnos = pull_varnos(root, arm);
+
+ all_arm_varnos = bms_join(all_arm_varnos, arm_varnos);
+ }
+
+ /*
+ * Require the OR clause to reference at least 2 distinct
+ * relations.
+ */
+ if (bms_num_members(all_arm_varnos) < 2)
+ {
+ bms_free(all_arm_varnos);
+ qual_idx++;
+ continue;
+ }
+ bms_free(all_arm_varnos);
+
+ /* Query-level safety check */
+ if (!checked_query)
+ {
+ if (!is_query_safe_for_or_expansion(root))
+ return NIL;
+ checked_query = true;
+ }
+
+ /* Transform OR clause and generate subpaths for each arm */
+ {
+ List *subpaths = create_or_expansion_subpaths(root, orclause, qual_idx);
+
+ if (subpaths != NIL)
+ results = lappend(results, subpaths);
+ }
+ }
+ qual_idx++;
+ }
+
+ return results;
+}
+
+/*
+ * match_target_expr
+ * Check if two targetlist expressions match, ignoring varnullingrels
+ * differences that may arise from outer join reduction in subroot.
+ */
+static bool
+match_target_expr(Node *want, Node *have)
+{
+ if (want == have)
+ return true;
+ if (want == NULL || have == NULL)
+ return false;
+ if (nodeTag(want) != nodeTag(have))
+ return false;
+
+ if (IsA(want, Var))
+ {
+ Var *v1 = (Var *) want;
+ Var *v2 = (Var *) have;
+
+ return (v1->varno == v2->varno &&
+ v1->varattno == v2->varattno &&
+ v1->varlevelsup == v2->varlevelsup);
+ }
+
+ if (IsA(want, PlaceHolderVar))
+ {
+ PlaceHolderVar *p1 = (PlaceHolderVar *) want;
+ PlaceHolderVar *p2 = (PlaceHolderVar *) have;
+
+ return (p1->phid == p2->phid &&
+ p1->phlevelsup == p2->phlevelsup);
+ }
+
+ return equal(want, have);
+}
+
+/*
+ * or_expansion_align_path
+ * Ensure the arm path emits columns in the exact order needed by joinrel.
+ * If column order differs, wrap with a projection using the arm's own
+ * expressions.
+ */
+static Path *
+or_expansion_align_path(PlannerInfo *root, RelOptInfo *joinrel, Path *arm)
+{
+ List *want = joinrel->reltarget->exprs;
+ List *have = arm->pathtarget->exprs;
+ List *new_exprs = NIL;
+ bool identical = true;
+ ListCell *lc_want;
+ ListCell *lc_have;
+ PathTarget *newtarget;
+
+ if (list_length(want) != list_length(have))
+ identical = false;
+
+ lc_have = list_head(have);
+ foreach(lc_want, want)
+ {
+ Node *want_node = (Node *) lfirst(lc_want);
+ Node *matched = NULL;
+ ListCell *lc;
+
+ if (identical && lc_have != NULL)
+ {
+ if (match_target_expr(want_node, (Node *) lfirst(lc_have)))
+ {
+ matched = (Node *) lfirst(lc_have);
+ lc_have = lnext(have, lc_have);
+ }
+ else
+ identical = false;
+ }
+
+ if (matched == NULL)
+ {
+ foreach(lc, have)
+ {
+ Node *have_node = (Node *) lfirst(lc);
+
+ if (match_target_expr(want_node, have_node))
+ {
+ matched = have_node;
+ break;
+ }
+ }
+ }
+
+ if (matched == NULL)
+ return NULL;
+
+ new_exprs = lappend(new_exprs, matched);
+ }
+
+ if (identical)
+ return arm;
+
+ newtarget = create_empty_pathtarget();
+ newtarget->exprs = new_exprs;
+ set_pathtarget_cost_width(root, newtarget);
+
+ /* Apply projection in-place if supported, or wrap with a ProjectionPath */
+ return apply_projection_to_path(root, joinrel, arm, newtarget);
+}
+
+/*
+ * add_or_expansion_paths
+ * Combine arm subpaths into an AppendPath and add to the top join rel.
+ * Called by grouping_planner() right after query_planner().
+ */
+void
+add_or_expansion_paths(PlannerInfo *root, RelOptInfo *joinrel, List *or_cands)
+{
+ ListCell *lc;
+ bool added_any = false;
+
+ if (joinrel == NULL || joinrel->reltarget == NULL)
+ return;
+
+ foreach(lc, or_cands)
+ {
+ List *subpaths = (List *) lfirst(lc);
+ List *aligned_subpaths = NIL;
+ ListCell *lc_subpath;
+ bool valid = true;
+ AppendPathInput ainput;
+ AppendPath *appendpath;
+
+ foreach(lc_subpath, subpaths)
+ {
+ Path *subpath = (Path *) lfirst(lc_subpath);
+ Path *aligned;
+
+ aligned = or_expansion_align_path(root, joinrel, subpath);
+ if (aligned == NULL)
+ {
+ valid = false;
+ break;
+ }
+ aligned_subpaths = lappend(aligned_subpaths, aligned);
+ }
+
+ if (!valid)
+ continue;
+
+ ainput.subpaths = aligned_subpaths;
+ ainput.partial_subpaths = NIL;
+ ainput.child_append_relid_sets = NIL;
+
+ /* Build Append path combining arm subpaths */
+ appendpath = create_append_path(root,
+ joinrel,
+ ainput,
+ NIL,
+ NULL,
+ 0,
+ false,
+ -1);
+
+ add_path(joinrel, (Path *) appendpath);
+ added_any = true;
+ }
+
+ /* We need to refigure which is the cheapest path for the joinrel */
+ if (added_any)
+ set_cheapest(joinrel);
+}
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 380679a76c0..8f6d63d0726 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -2319,6 +2319,16 @@
ifdef => 'DEBUG_BOUNDED_SORT',
},
+{ name => 'or_expansion_limit', type => 'int', context => 'PGC_USERSET', group => 'QUERY_TUNING_OTHER',
+ short_desc => 'Sets the maximum number of OR arms considered for OR-expansion.',
+ long_desc => 'The planner will consider expanding OR clauses if they have no more than this many arms. 0 disables OR-expansion.',
+ flags => 'GUC_EXPLAIN',
+ variable => 'or_expansion_limit',
+ boot_val => '8',
+ min => '0',
+ max => 'INT_MAX',
+},
+
{ name => 'output_plugin_libraries', type => 'string', context => 'PGC_SUSET', group => 'REPLICATION_SENDING',
short_desc => 'Lists libraries that may be named as logical decoding output plugins.',
long_desc => 'Users with REPLICATION privileges may only use plugins in this list when creating logical replication slots.',
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 01f98de8a9f..b11ea2a847e 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -495,6 +495,7 @@
#jit = off # allow JIT compilation
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses
+#or_expansion_limit = 8 # 0 disables OR-expansion to UNION ALL
#plan_cache_mode = auto # auto, force_generic_plan or
# force_custom_plan
#recursive_worktable_factor = 10.0 # range 0.001-1000000
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 14255c900fc..f5e22c39c6c 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -68,6 +68,7 @@ extern PGDLLIMPORT bool enable_partitionwise_join;
extern PGDLLIMPORT bool enable_partitionwise_aggregate;
extern PGDLLIMPORT bool enable_parallel_append;
extern PGDLLIMPORT bool enable_parallel_hash;
+extern PGDLLIMPORT int or_expansion_limit;
extern PGDLLIMPORT bool enable_partition_pruning;
extern PGDLLIMPORT bool enable_presorted_aggregate;
extern PGDLLIMPORT bool enable_async_append;
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index c23ab568b98..e7c780d774c 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -36,6 +36,13 @@ extern RelOptInfo *query_planner(PlannerInfo *root,
*/
extern void preprocess_minmax_aggregates(PlannerInfo *root);
+/*
+ * prototypes for plan/planorexpand.c
+ */
+extern List *plan_or_expansion_arms(PlannerInfo *root);
+extern void add_or_expansion_paths(PlannerInfo *root, RelOptInfo *joinrel,
+ List *or_cands);
+
/*
* prototypes for plan/createplan.c
*/
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 4ee94d00ade..b8a23193969 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -12102,14 +12102,26 @@ WHERE ss.unique1 = ss.phv AND t1.unique1 < 100;
EXPLAIN (COSTS OFF)
SELECT COUNT(*) FROM tenk1 t1, tenk1 t2
WHERE t2.thousand = t1.tenthous OR t2.thousand = t1.unique1 OR t2.thousand = t1.unique2;
- QUERY PLAN
------------------------------------------------------------------------------------------
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------
Aggregate
- -> Nested Loop
- -> Seq Scan on tenk1 t1
- -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
- Index Cond: (thousand = ANY (ARRAY[t1.tenthous, t1.unique1, t1.unique2]))
-(5 rows)
+ -> Append
+ -> Hash Join
+ Hash Cond: (t2.thousand = t1.tenthous)
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
+ -> Hash
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 t1
+ -> Merge Join
+ Merge Cond: (t1.unique1 = t2.thousand)
+ Join Filter: ((t2.thousand = t1.tenthous) IS NOT TRUE)
+ -> Index Scan using tenk1_unique1 on tenk1 t1
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
+ -> Merge Join
+ Merge Cond: (t1.unique2 = t2.thousand)
+ Join Filter: (((t2.thousand = t1.tenthous) IS NOT TRUE) AND ((t2.thousand = t1.unique1) IS NOT TRUE))
+ -> Index Scan using tenk1_unique2 on tenk1 t1
+ -> Index Only Scan using tenk1_thous_tenthous on tenk1 t2
+(17 rows)
SELECT COUNT(*) FROM tenk1 t1, tenk1 t2
WHERE t2.thousand = t1.tenthous OR t2.thousand = t1.unique1 OR t2.thousand = t1.unique2;
diff --git a/src/test/regress/expected/or_expansion.out b/src/test/regress/expected/or_expansion.out
new file mode 100644
index 00000000000..aae75315f27
--- /dev/null
+++ b/src/test/regress/expected/or_expansion.out
@@ -0,0 +1,249 @@
+--
+-- Tests for OR-expansion to UNION ALL (Append)
+--
+CREATE TABLE orexp_a (id int, val int);
+CREATE TABLE orexp_b (x int, y int, val int);
+INSERT INTO orexp_a VALUES
+ (1, 10),
+ (2, 20),
+ (3, 30),
+ (4, NULL),
+ (5, 50);
+INSERT INTO orexp_b VALUES
+ (1, 99, 100),
+ (99, 2, 200),
+ (3, 3, 300),
+ (4, 99, 400),
+ (99, 99, 500),
+ (NULL, NULL, 600);
+-- Verify GUC exists and can be toggled
+SHOW or_expansion_limit;
+ or_expansion_limit
+--------------------
+ 8
+(1 row)
+
+-- Case C3: Join condition OR (disjunction of join keys)
+-- With default or_expansion_limit = 8, planner should expand to Append of joins
+EXPLAIN (COSTS OFF)
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+ QUERY PLAN
+-------------------------------------------------------
+ Sort
+ Sort Key: a.id, b.x, b.y
+ -> Append
+ -> Merge Join
+ Merge Cond: (b.x = a.id)
+ -> Sort
+ Sort Key: b.x
+ -> Seq Scan on orexp_b b
+ -> Sort
+ Sort Key: a.id
+ -> Seq Scan on orexp_a a
+ -> Merge Join
+ Merge Cond: (b.y = a.id)
+ Join Filter: ((a.id = b.x) IS NOT TRUE)
+ -> Sort
+ Sort Key: b.y
+ -> Seq Scan on orexp_b b
+ -> Sort
+ Sort Key: a.id
+ -> Seq Scan on orexp_a a
+(20 rows)
+
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+ id | x | y
+----+----+----
+ 1 | 1 | 99
+ 2 | 99 | 2
+ 3 | 3 | 3
+ 4 | 4 | 99
+(4 rows)
+
+-- Result must match or_expansion_limit = 0 (disabled)
+SET or_expansion_limit = 0;
+EXPLAIN (COSTS OFF)
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+ QUERY PLAN
+-----------------------------------------------------
+ Sort
+ Sort Key: a.id, b.x, b.y
+ -> Nested Loop
+ Join Filter: ((a.id = b.x) OR (a.id = b.y))
+ -> Seq Scan on orexp_a a
+ -> Materialize
+ -> Seq Scan on orexp_b b
+(7 rows)
+
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+ id | x | y
+----+----+----
+ 1 | 1 | 99
+ 2 | 99 | 2
+ 3 | 3 | 3
+ 4 | 4 | 99
+(4 rows)
+
+-- Case C4: Cross-table filter with separate join key
+-- Tests null-safety, 3-valued logic, and duplicate exclusion
+SET or_expansion_limit = 8;
+EXPLAIN (COSTS OFF)
+SELECT a.id, a.val, b.val FROM orexp_a a JOIN orexp_b b ON a.id = b.x
+WHERE a.val = 10 OR b.val = 300
+ORDER BY a.id, a.val, b.val;
+ QUERY PLAN
+------------------------------------------------------
+ Sort
+ Sort Key: a.id, a.val, b.val
+ -> Append
+ -> Hash Join
+ Hash Cond: (b.x = a.id)
+ -> Seq Scan on orexp_b b
+ -> Hash
+ -> Seq Scan on orexp_a a
+ Filter: (val = 10)
+ -> Hash Join
+ Hash Cond: (a.id = b.x)
+ -> Seq Scan on orexp_a a
+ Filter: ((val = 10) IS NOT TRUE)
+ -> Hash
+ -> Seq Scan on orexp_b b
+ Filter: (val = 300)
+(16 rows)
+
+SELECT a.id, a.val, b.val FROM orexp_a a JOIN orexp_b b ON a.id = b.x
+WHERE a.val = 10 OR b.val = 300
+ORDER BY a.id, a.val, b.val;
+ id | val | val
+----+-----+-----
+ 1 | 10 | 100
+ 3 | 30 | 300
+(2 rows)
+
+SET or_expansion_limit = 0;
+SELECT a.id, a.val, b.val FROM orexp_a a JOIN orexp_b b ON a.id = b.x
+WHERE a.val = 10 OR b.val = 300
+ORDER BY a.id, a.val, b.val;
+ id | val | val
+----+-----+-----
+ 1 | 10 | 100
+ 3 | 30 | 300
+(2 rows)
+
+-- Case C5: Non-flattened subquery over ordinary table with cross-table OR
+-- Verifies expression_tree_walker is not called on RangeTblEntry nodes
+CREATE TABLE orexp_c (id int, tier text);
+CREATE TABLE orexp_d (cid int, val int);
+INSERT INTO orexp_c VALUES (1, 'vip'), (2, 'std');
+INSERT INTO orexp_d VALUES (1, 10), (1, 20), (2, 5);
+SET or_expansion_limit = 8;
+EXPLAIN (COSTS OFF)
+SELECT c.id, s.n FROM orexp_c c
+ JOIN (SELECT cid, count(*) n FROM orexp_d GROUP BY cid) s ON s.cid = c.id
+WHERE c.tier = 'vip' OR s.n > 20
+ORDER BY c.id;
+ QUERY PLAN
+-------------------------------------------------------------
+ Sort
+ Sort Key: c.id
+ -> Hash Join
+ Hash Cond: (c.id = s.cid)
+ Join Filter: ((c.tier = 'vip'::text) OR (s.n > 20))
+ -> Seq Scan on orexp_c c
+ -> Hash
+ -> Subquery Scan on s
+ -> HashAggregate
+ Group Key: orexp_d.cid
+ -> Seq Scan on orexp_d
+(11 rows)
+
+SELECT c.id, s.n FROM orexp_c c
+ JOIN (SELECT cid, count(*) n FROM orexp_d GROUP BY cid) s ON s.cid = c.id
+WHERE c.tier = 'vip' OR s.n > 20
+ORDER BY c.id;
+ id | n
+----+---
+ 1 | 2
+(1 row)
+
+SET or_expansion_limit = 0;
+SELECT c.id, s.n FROM orexp_c c
+ JOIN (SELECT cid, count(*) n FROM orexp_d GROUP BY cid) s ON s.cid = c.id
+WHERE c.tier = 'vip' OR s.n > 20
+ORDER BY c.id;
+ id | n
+----+---
+ 1 | 2
+(1 row)
+
+DROP TABLE orexp_c;
+DROP TABLE orexp_d;
+-- Case C6: Subquery over partitioned table (gate must reject OR expansion)
+CREATE TABLE orexp_part (id int, d int) PARTITION BY RANGE (d);
+CREATE TABLE orexp_part1 PARTITION OF orexp_part FOR VALUES FROM (0) TO (10);
+CREATE TABLE orexp_part2 PARTITION OF orexp_part FOR VALUES FROM (10) TO (20);
+INSERT INTO orexp_part VALUES (1, 5), (2, 15);
+SET or_expansion_limit = 8;
+EXPLAIN (COSTS OFF)
+SELECT a.id, s.d FROM orexp_a a,
+ (SELECT id, d FROM orexp_part GROUP BY id, d) s
+WHERE a.id = s.id OR s.d = 15
+ORDER BY a.id, s.d;
+ QUERY PLAN
+--------------------------------------------------------------------------
+ Sort
+ Sort Key: a.id, s.d
+ -> Nested Loop
+ Join Filter: ((a.id = s.id) OR (s.d = 15))
+ -> Seq Scan on orexp_a a
+ -> Materialize
+ -> Subquery Scan on s
+ -> HashAggregate
+ Group Key: orexp_part.id, orexp_part.d
+ -> Append
+ -> Seq Scan on orexp_part1 orexp_part_1
+ -> Seq Scan on orexp_part2 orexp_part_2
+(12 rows)
+
+SELECT a.id, s.d FROM orexp_a a,
+ (SELECT id, d FROM orexp_part GROUP BY id, d) s
+WHERE a.id = s.id OR s.d = 15
+ORDER BY a.id, s.d;
+ id | d
+----+----
+ 1 | 5
+ 1 | 15
+ 2 | 15
+ 3 | 15
+ 4 | 15
+ 5 | 15
+(6 rows)
+
+SET or_expansion_limit = 0;
+SELECT a.id, s.d FROM orexp_a a,
+ (SELECT id, d FROM orexp_part GROUP BY id, d) s
+WHERE a.id = s.id OR s.d = 15
+ORDER BY a.id, s.d;
+ id | d
+----+----
+ 1 | 5
+ 1 | 15
+ 2 | 15
+ 3 | 15
+ 4 | 15
+ 5 | 15
+(6 rows)
+
+DROP TABLE orexp_part;
+-- Clean up
+RESET or_expansion_limit;
+DROP TABLE orexp_a;
+DROP TABLE orexp_b;
diff --git a/src/test/regress/sql/or_expansion.sql b/src/test/regress/sql/or_expansion.sql
new file mode 100644
index 00000000000..a3a035d6f05
--- /dev/null
+++ b/src/test/regress/sql/or_expansion.sql
@@ -0,0 +1,122 @@
+--
+-- Tests for OR-expansion to UNION ALL (Append)
+--
+
+CREATE TABLE orexp_a (id int, val int);
+CREATE TABLE orexp_b (x int, y int, val int);
+
+INSERT INTO orexp_a VALUES
+ (1, 10),
+ (2, 20),
+ (3, 30),
+ (4, NULL),
+ (5, 50);
+
+INSERT INTO orexp_b VALUES
+ (1, 99, 100),
+ (99, 2, 200),
+ (3, 3, 300),
+ (4, 99, 400),
+ (99, 99, 500),
+ (NULL, NULL, 600);
+
+-- Verify GUC exists and can be toggled
+SHOW or_expansion_limit;
+
+-- Case C3: Join condition OR (disjunction of join keys)
+-- With default or_expansion_limit = 8, planner should expand to Append of joins
+EXPLAIN (COSTS OFF)
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+
+-- Result must match or_expansion_limit = 0 (disabled)
+SET or_expansion_limit = 0;
+EXPLAIN (COSTS OFF)
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+
+SELECT a.id, b.x, b.y FROM orexp_a a, orexp_b b
+WHERE a.id = b.x OR a.id = b.y
+ORDER BY a.id, b.x, b.y;
+
+-- Case C4: Cross-table filter with separate join key
+-- Tests null-safety, 3-valued logic, and duplicate exclusion
+SET or_expansion_limit = 8;
+EXPLAIN (COSTS OFF)
+SELECT a.id, a.val, b.val FROM orexp_a a JOIN orexp_b b ON a.id = b.x
+WHERE a.val = 10 OR b.val = 300
+ORDER BY a.id, a.val, b.val;
+
+SELECT a.id, a.val, b.val FROM orexp_a a JOIN orexp_b b ON a.id = b.x
+WHERE a.val = 10 OR b.val = 300
+ORDER BY a.id, a.val, b.val;
+
+SET or_expansion_limit = 0;
+SELECT a.id, a.val, b.val FROM orexp_a a JOIN orexp_b b ON a.id = b.x
+WHERE a.val = 10 OR b.val = 300
+ORDER BY a.id, a.val, b.val;
+
+-- Case C5: Non-flattened subquery over ordinary table with cross-table OR
+-- Verifies expression_tree_walker is not called on RangeTblEntry nodes
+CREATE TABLE orexp_c (id int, tier text);
+CREATE TABLE orexp_d (cid int, val int);
+INSERT INTO orexp_c VALUES (1, 'vip'), (2, 'std');
+INSERT INTO orexp_d VALUES (1, 10), (1, 20), (2, 5);
+
+SET or_expansion_limit = 8;
+EXPLAIN (COSTS OFF)
+SELECT c.id, s.n FROM orexp_c c
+ JOIN (SELECT cid, count(*) n FROM orexp_d GROUP BY cid) s ON s.cid = c.id
+WHERE c.tier = 'vip' OR s.n > 20
+ORDER BY c.id;
+
+SELECT c.id, s.n FROM orexp_c c
+ JOIN (SELECT cid, count(*) n FROM orexp_d GROUP BY cid) s ON s.cid = c.id
+WHERE c.tier = 'vip' OR s.n > 20
+ORDER BY c.id;
+
+SET or_expansion_limit = 0;
+SELECT c.id, s.n FROM orexp_c c
+ JOIN (SELECT cid, count(*) n FROM orexp_d GROUP BY cid) s ON s.cid = c.id
+WHERE c.tier = 'vip' OR s.n > 20
+ORDER BY c.id;
+
+DROP TABLE orexp_c;
+DROP TABLE orexp_d;
+
+-- Case C6: Subquery over partitioned table (gate must reject OR expansion)
+CREATE TABLE orexp_part (id int, d int) PARTITION BY RANGE (d);
+CREATE TABLE orexp_part1 PARTITION OF orexp_part FOR VALUES FROM (0) TO (10);
+CREATE TABLE orexp_part2 PARTITION OF orexp_part FOR VALUES FROM (10) TO (20);
+INSERT INTO orexp_part VALUES (1, 5), (2, 15);
+
+SET or_expansion_limit = 8;
+EXPLAIN (COSTS OFF)
+SELECT a.id, s.d FROM orexp_a a,
+ (SELECT id, d FROM orexp_part GROUP BY id, d) s
+WHERE a.id = s.id OR s.d = 15
+ORDER BY a.id, s.d;
+
+SELECT a.id, s.d FROM orexp_a a,
+ (SELECT id, d FROM orexp_part GROUP BY id, d) s
+WHERE a.id = s.id OR s.d = 15
+ORDER BY a.id, s.d;
+
+SET or_expansion_limit = 0;
+SELECT a.id, s.d FROM orexp_a a,
+ (SELECT id, d FROM orexp_part GROUP BY id, d) s
+WHERE a.id = s.id OR s.d = 15
+ORDER BY a.id, s.d;
+
+DROP TABLE orexp_part;
+
+-- Clean up
+RESET or_expansion_limit;
+DROP TABLE orexp_a;
+DROP TABLE orexp_b;
