From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Propagate pathkeys from CTEs up to the outer query.
Date: Tue, 26 Mar 2024 17:06:06 +0000
Message-ID: <E1rpAFR-005ndo-P7@gemulon.postgresql.org> (raw)
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
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/a65724dfa73db8b451d0c874a9161935a34a914e
Modified Files
--------------
src/backend/optimizer/path/allpaths.c | 16 ++++++++++++++--
src/backend/optimizer/plan/planner.c | 1 +
src/backend/optimizer/plan/subselect.c | 28 ++++++++++++++++++----------
src/backend/optimizer/util/pathnode.c | 5 +++--
src/include/nodes/pathnodes.h | 3 +++
src/include/optimizer/pathnode.h | 2 +-
src/test/regress/expected/with.out | 17 +++++++++++++++++
src/test/regress/sql/with.sql | 7 +++++++
8 files changed, 64 insertions(+), 15 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-committers@postgresql.org
Cc: tgl@sss.pgh.pa.us, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Propagate pathkeys from CTEs up to the outer query.
In-Reply-To: <E1rpAFR-005ndo-P7@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox