I got around to looking at this finally. I was a bit surprised by your choice of data structure. You made a per-CTE-item cte_paths list paralleling cte_plan_ids, but what I had had in mind was a per-subplan list of paths paralleling glob->subplans and subroots. This would mean that the code for ordinary SubqueryScans would also need to fill in that list, but surely that's a trivial cost compared to everything else we do to prepare a subplan. I don't think that we have any immediate need to remember that info for an ordinary SubqueryScan, but it seems plausible that we will in future. Also, I'm not sure that a Path is fully interpretable without the associated PlannerInfo (subroot), so keeping it beside the list of subroots seems more future-proof than dissociating it from that. This approach would also be more amenable to postponing creation of the subplans, as we speculated about earlier. (I have no near-term desire to actually do that, but maybe someday it will happen.)
I agree with your points. Previously I was thinking that CTEs were the only scenario where we needed to remember the best path and only required the best path's pathkeys. However, considering potential future use cases as you mentioned, I concur that having a per-subplan list of paths would be more future-proof. Please see attached v4 patch.
