On Fri, Nov 17, 2023 at 11:38 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
That line of argument also leads to the conclusion that it'd be okay to expose info about the ordering of the CTE result to the upper planner. This patch doesn't do that, and I'm not sufficiently excited about the issue to go write some code. But if someone else does, I think we shouldn't exclude doing it on the grounds of wanting to preserve an optimization fence. The fence is sort of one-way in this line of thinking: information can propagate up to the outer planner level, but not down into the CTE plan.
In the light of this conclusion, I had a go at propagating the pathkeys from CTEs up to the outer planner and came up with the attached.