Re: [PROPOSAL] Expand OR clauses in joins to UNION ALL paths

Hello,

On 07/10/2026 23:44, Zsolt Parragi wrote:
> Hello!
>
> I see the motivation / idea behind it, but I think this needs some more thought.
Thanks for the review!
>
> The patch seems to cause some test failures in the test suite
> (test_misc/003_check_guc, test_plan_advice/001_replan_regress.

I was aware of GUC test, i forgot about that, so its fixed now.

For the plan_advice test, there's a join test where previously planner
was doing ScalarArrayOpExpr transformation, when i added or expansion it
became cheaper for that test case. IIUC pg_plan_advice doesn't support
having different join strategies across Append arms for the same
relation pair, i'm not sure how should i avoid this test fail without
doing something wrong like disabling or expansion before the join test ?

I suppose this might be a pg_plan_advice issue ?

>
> And some quick testing revealed two more specific issues:
>
> 1. Shouldn't the patch either handle subplans, or skip queries containing them?
>
> CREATE TABLE t1 (id int PRIMARY KEY, a int, c int);
> CREATE TABLE t2 (id int PRIMARY KEY, b int);
> INSERT INTO t1 SELECT g, g % 10, g % 7 FROM generate_series(1, 2000) g;
> INSERT INTO t2 SELECT g, g % 10 FROM generate_series(1, 2000) g;
> CREATE INDEX ON t2 (b);
> ANALYZE t1, t2;
> CREATE SEQUENCE flip_seq;
> CREATE FUNCTION flip() RETURNS bool VOLATILE LANGUAGE sql
> AS $$ SELECT nextval('flip_seq') % 3 = 0 $$;
> SELECT count(*) FROM t1, t2
> WHERE (SELECT flip() WHERE t1.id > 0) OR t1.a = t2.b;
Correct:
+ if (contain_subplans((Node *) parse->jointree->quals))
this should be enough i think.
>
> 2.
>
>> The default of 8 is a nice guess because i found that starting from 7 arms,
>> planning time sometimes doubles (in complicated queries).
> The planning time and memory use seems to be exponential with nesting,
> this explain takes 153 seconds for me in a debug build:
>
> create table t1 (id int primary key, a int, c int);
> create table t2 (id int primary key, b int);
> insert into t1 select g, g % 10, g % 7 from generate_series(1, 2000) g;
> insert into t2 select g, g % 10 from generate_series(1, 2000) g;
> analyze t1, t2;
> explain (summary, memory, costs off, timing off) select x.id, max(s.b)
> as b from t1 x, (select x.id, max(s.b) as b from t1 x, (select x.id,
> max(s.b) as b from t1 x, (select x.id, max(s.b) as b from t1 x,
> (select x.id, max(s.b) as b from t1 x, (select x.id, max(s.b) as b
> from t1 x, (select x.id, max(s.b) as b from t1 x, (select id, b from
> t2) s where x.a = s.b or x.c = s.id or x.id = s.b group by x.id) s
> where x.a = s.b or x.c = s.id or x.id = s.b group by x.id) s where x.a
> = s.b or x.c = s.id or x.id = s.b group by x.id) s where x.a = s.b or
> x.c = s.id or x.id = s.b group by x.id) s where x.a = s.b or x.c =
> s.id or x.id = s.b group by x.id) s where x.a = s.b or x.c = s.id or
> x.id = s.b group by x.id) s where x.a = s.b or x.c = s.id or x.id =
> s.b group by x.id;

True, this shouldn't be allowed for subqueries IMO.

+ /* + * 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;

I also found two other issues:

- Previously i had a comment on whether LateralRTEs should be allowed, i
found that they are not safe for this transformation, therefore there's
a new guard for it too.

- Derived tables with un-grouped aggregates can be optimized into
MinMaxAggPath nodes whose InitPlan replacement parameters are populated
in that arm's private subroot->minmax_aggs during plan creation. Because
set_subqueryscan_references() resolves the subquery's planner state
through the top-level root->simple_rel_array, this mapping is missing
during reference fixing, causing an Aggref found in non-Agg plan node
executor error.

Example:
SELECT * FROM agg_t1 t1, agg_t2 t2, (SELECT min(x) as mx FROM agg_t2) s
WHERE (t1.a = t2.x OR t1.b = t2.y) AND s.mx = 1;

so i added a guard for this too. (attached as v2 of the patch)

I'm not sure whether it is worth making some of the guarded cases work
with or-expansion, if the list doesn't grow bigger than this, i think
that not considering them would be cheaper.

Or we can do the inverse: the query is safe for expansion only if all
RTEs are base relations, not even inherited and partitioned relations.
The latter might be too restrictive but far simpler, maybe (attached as
v3 of the patch).

Thoughts ?

Regards,
Ayoub Kazar