On Wed, Aug 28, 2024 at 12:15 AM Tom Lane <t...@sss.pgh.pa.us> wrote: > If we > are willing to accept a HEAD-only fix, it'd likely be better to > attack the other end and make it possible to remove no-op PHVs. > I think that'd require marking PHVs that need to be kept because > they are serving to isolate subexpressions.
I think it's always desirable to remove no-op PHVs, even if we end up with a different approach to fix the issue discussed here. Doing that could potentially open up opportunities for optimization in other cases. For example: explain (costs off) select * from t t1 left join lateral (select t1.a as x, * from t t2) s on true where t1.a = s.a; QUERY PLAN ---------------------------- Nested Loop -> Seq Scan on t t1 -> Seq Scan on t t2 Filter: (t1.a = a) (4 rows) The target entry s.x is wrapped in a PHV that contains lateral reference to t1, which forces us to resort to nestloop join. However, since the left join has been reduced to an inner join, and it is removed from the PHV's nullingrels, leaving the nullingrels being empty, we should be able to remove this PHV and use merge or hash joins, depending on which is cheaper. I think there may be more cases where no-op PHVs constrain optimization opportunities. In [1] when working on the fix-grouping-sets patch, I included a mechanism in 0003 to remove no-op PHVs by including a flag in PlaceHolderVar to indicate whether it is safe to remove the PHV when its phnullingrels becomes empty. In that patch this flag is only set in cases where the PHV is used to carry the nullingrel bit that represents the grouping step. Maybe we can extend its use to remove all no-op PHVs, except those that are serving to isolate subexpressions. Any thoughts on this? [1] https://postgr.es/m/cambws4_2t2pqqcfds3nyjlwmmkazyqkbohkweft-we3yoi7...@mail.gmail.com Thanks Richard