Re: Propagate pathkeys from CTEs up to the outer query

On Tue, Mar 26, 2024 at 1:39 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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.

Thanks
Richard