From a65724dfa73db8b451d0c874a9161935a34a914e Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Tue, 26 Mar 2024 13:05:49 -0400 Subject: Propagate pathkeys from CTEs up to the outer query. If we know the sort order of a CTE's output, and it is relevant to the outer query, label the CTE's outer-query access path using those pathkeys. This may enable optimizations such as avoiding a sort in the outer query. The code for hoisting pathkeys into the outer query already exists for regular RTE_SUBQUERY subqueries, but it wasn't getting used for CTEs, possibly out of concern for maintaining an optimization fence between the CTE and the outer query. However, on the same arguments used for commit f7816aec2, there seems no harm in letting the outer query know what the inner query decided to do. In support of this, we now remember the best Path as well as Plan for each subquery for the rest of the planner run. There may be future applications for having that at hand, and it surely costs little to build one more List. Richard Guo (minor mods by me) Discussion: https://postgr.es/m/CAMbWs49xYd3f8CrE8-WW3--dV1zH_sDSDn-vs2DzHj81Wcnsew@mail.gmail.com --- src/backend/optimizer/util/pathnode.c | 5 +++-- 1 file changed, 3 insertions(+), 2 deletions(-) (limited to 'src/backend/optimizer/util/pathnode.c') diff --git a/src/backend/optimizer/util/pathnode.c b/src/backend/optimizer/util/pathnode.c index c29ca5a0da2..e4a5ae83e4a 100644 --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -2121,7 +2121,8 @@ create_valuesscan_path(PlannerInfo *root, RelOptInfo *rel, * returning the pathnode. */ Path * -create_ctescan_path(PlannerInfo *root, RelOptInfo *rel, Relids required_outer) +create_ctescan_path(PlannerInfo *root, RelOptInfo *rel, + List *pathkeys, Relids required_outer) { Path *pathnode = makeNode(Path); @@ -2133,7 +2134,7 @@ create_ctescan_path(PlannerInfo *root, RelOptInfo *rel, Relids required_outer) pathnode->parallel_aware = false; pathnode->parallel_safe = rel->consider_parallel; pathnode->parallel_workers = 0; - pathnode->pathkeys = NIL; /* XXX for now, result is always unordered */ + pathnode->pathkeys = pathkeys; cost_ctescan(pathnode, root, rel, pathnode->param_info); -- cgit v1.2.3