Re: plan shape work

On Thu, Sep 4, 2025 at 4:21 AM Richard Guo <guofenglinux(at)gmail(dot)com> wrote:
> I found some issues with 0003 though. It seems get_scanned_rtindexes
> is intended to return RTI sets with outer join relids excluded. For
> some node types, such as Append and MergeAppend, it fails to do so,
> which can cause the assertion in assert_join_preserves_scan_rtis to
> fail. For example:
>
> create table p (a int, b int) partition by range(a);
> create table p1 partition of p for values from (0) to (10);
> create table p2 partition of p for values from (10) to (20);
>
> set enable_partitionwise_join to on;
>
> explain (costs off)
> select * from p t1
> left join p t2 on t1.a = t2.a
> left join p t3 on t2.b = t3.b;
> server closed the connection unexpectedly

Ouch. Good catch.

> Besides, to exclude outer join relids, it iterates over the RTI sets,
> checks each RTE for type RTE_JOIN, and bms_del_member it if found (cf.
> remove_join_rtis). I think a simpler approach would be to leverage
> PlannerInfo.outer_join_rels:
>
> scanrelids = bms_difference(scanrelids, root->outer_join_rels);

I was not aware of outer_join_rels, so thank you for pointing it out.
However, consider this query:

select 1 from pg_class a inner join pg_class b on a.relfilenode = b.relfilenode;

Here, we end up with a three-item range table: one for a, one for b,
and one for the join. But the join is not an outer join, and does not
appear in root->outer_join_rels. Therefore, I'm not sure we can rely
on outer_join_rels in this scenario.

--
Robert Haas
EDB: http://www.enterprisedb.com