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

Hello again,

On 13/10/2026 00:13, Zsolt Parragi wrote:
> + /* Only plain base relations are permitted */
> + if (rte->rtekind != RTE_RELATION)
> + return false;
>
> This will even reject simple GROUP BY (RTE_GROUP)
>
> SELECT a.x FROM a JOIN b ... WHERE a.f=1 OR b.g=2 GROUP BY a.x
>
> And there are probably many other interesting query features the patch
> could support without problems.
Indeed, so this takes us to v4 which has guards that allow more cases:

+ /*
+ * Check RTEs. We allow only known to be safe RTE cases: - RTE_RELATION:
+ * plain base tables (not partitioned, no inheritance, no tablesample, not
+ * foreign) - RTE_JOIN: joins (except FULL joins) - RTE_VALUES:
+ * non-volatile values lists without subplans - RTE_FUNCTION: non-volatile
+ * function RTEs without subplans - RTE_TABLEFUNC: non-volatile table
+ * functions without subplans - RTE_SUBQUERY: only flattened/inert
+ * subqueries (subquery == NULL) - RTE_GROUP, RTE_RESULT: synthetic
+ * planner entries Any other or active RTE kind (e.g. active RTE_SUBQUERY,
+ * RTE_CTE) is rejected for safety.
+ */

These are the cases that i found safe to support without any extra effort.

Previously there were failing tests related to pg_plan_advice, because
since we generate the paths directly without a boundary between it and
outer query, pg_plan_advice can't make a difference between every
occurrence of a relation, therefore generating conflicting advice
Example:

Consider a query where each expanded arm prefers a different scan or
join strategy:
SELECT a.id, b.val FROM a JOIN b ON a.id = b.x
WHERE a.val = 10 OR b.val = 300;

In v4, arm paths were placed directly under Append without any relation
boundary.
Because both arms operated in the top-level query scope, pg_plan_advice
could not
differentiate table occurrences between Arm 1 and Arm 2, generating
conflicting
advice for the exact same relation aliases:

-- v4: Ambiguous/conflicting advice emitted for the same relation:
Generated Plan Advice:
JOIN_ORDER(a b)
JOIN_ORDER(b a) -- CONFLICT: Arm 1 joins (a b), Arm 2 joins (b a)
NESTED_LOOP_PLAIN(b a) -- CONFLICT: Arm 1 loops on b, Arm 2 loops on a
SEQ_SCAN(b) -- CONFLICT: Arm 2 wants Seq Scan on b...
INDEX_SCAN(a public.orexp_a_val b public.orexp_b_x a public.orexp_a_pkey)
PARTITIONWISE((a b))
NO_GATHER(a b)

When this advice was fed back into the planner, pg_plan_advice could not
tell which
arm each hint targeted, breaking its round-trip plan stability guarantee
and causing
test failures.

Solution in v5:
In v5, each arm is given its own PlannerInfo with a distinct plan_name
and wrapped in a synthetic
SubqueryScanPath pointing to an RTE_SUBQUERY.
Now its more closer to what would an equivalent SQL query be, a lesser
non-standard way to do it.

This allows pg_plan_advice to qualify relation identifiers per arm:

-- v5: Disambiguated per-arm relation identifiers:

JOIN_ORDER(a(at)orexpand b(at)orexpand)
JOIN_ORDER(b(at)orexpand_1 a(at)orexpand_1)
NESTED_LOOP_PLAIN(b(at)orexpand a(at)orexpand_1)
SEQ_SCAN(b(at)orexpand_1)
INDEX_SCAN(a(at)orexpand public.orexp_a_val b(at)orexpand public.orexp_b_x
a(at)orexpand_1 public.orexp_a_pkey)
PARTITIONWISE((a b))
NO_GATHER(a b orexpand orexpand_1 a(at)orexpand b(at)orexpand a(at)orexpand_1
b(at)orexpand_1)

Though, currently pg_plan_advice still thinks this is a PARTITIONWISE,
so i think there must be some things that should be done to
pg_plan_advice itself, to add more considerations of what actually can
be an Append over base relations.
This is the reason for v5, i concluded from a quick talk with Robert at
PGConf.EU that maybe it is half of the problem of v4 doing a
non-standard transformation without subqueries (AFAIK, there's no other
part of planner that does a change like this direct) and pg_plan_advice
assumptions (which are fair, CC'ed Robert for this).

Parallel query for this is also an opportunity to not miss, which i'll
do in a later state of the patch.
>
>
> I also want to point out that this can result in queries that
> otherwise work on master erroring out with the patch:
>
> CREATE TABLE item (id int PRIMARY KEY, kind text);
> CREATE TABLE attr (item_id int, val text, tag int);
> INSERT INTO item
> SELECT g, CASE WHEN g % 100 = 0 THEN 'txt' ELSE 'num' END
> FROM generate_series(1, 100000) g;
> INSERT INTO attr
> SELECT g, CASE WHEN g % 100 = 0 THEN 'abc' ELSE (g % 500)::text END,
> CASE WHEN g % 100 = 0 THEN 7 ELSE g % 1000 END
> FROM generate_series(1, 100000) g;
> CREATE INDEX ON item (kind);
> CREATE INDEX ON attr (tag);
> ANALYZE item, attr;
>
> SELECT count(*) FROM item i JOIN attr a ON a.item_id = i.id
> WHERE i.kind = 'txt' OR (a.val::int = 7 AND a.tag = 7);
>
> Every 'txt' item in this database fails the right side of the OR, but
> because the left side is true, the right side is never evaluated.
> The evaluation order isn't guaranteed in this case, the documentation
> explicitly tells people not to do this. So in that sense, breaking it
> isn't wrong, but it is still a noticeable behavior change.

Nice catch, noted to be discussed later.

Regards,
Ayoub