Looking closer at the codes, I think we still have some loose ends regarding how SJE handles PHVs.
...
2) Currently remove_self_joins_one_group() checks PHVs as below
/* there isn't any other place to eval PHV */ if (bms_is_subset(phinfo->ph_eval_at, joinrelids) || bms_is_subset(phinfo->ph_needed, joinrelids)) break;
I'm wondering if we can relax this restriction because it seems to me that a PHV evaluated/needed at or below the self join should not have problem if we remove the self join.
After some more thought, I think PHVs should not impose any constraints on removing self joins. If the removed rel is contained in the relids that a PHV is evaluated/needed at or laterally references, it can just be replaced with the rel that is kept.
Attached is a patch to remove such constraints. Any comments or feedback are welcome.