From: Robert Haas <rhaas@postgresql.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Assign each subquery a unique name prior to planning it.
Date: Tue, 07 Oct 2025 13:24:18 +0000
Message-ID: <E1v67fu-000Vpy-2F@gemulon.postgresql.org> (raw)
Assign each subquery a unique name prior to planning it.
Previously, subqueries were given names only after they were planned,
which makes it difficult to use information from a previous execution of
the query to guide future planning. If, for example, you knew something
about how you want "InitPlan 2" to be planned, you won't know whether
the subquery you're currently planning will end up being "InitPlan 2"
until after you've finished planning it, by which point it's too late to
use the information that you had.
To fix this, assign each subplan a unique name before we begin planning
it. To improve consistency, use textual names for all subplans, rather
than, as we did previously, a mix of numbers (such as "InitPlan 1") and
names (such as "CTE foo"), and make sure that the same name is never
assigned more than once.
We adopt the somewhat arbitrary convention of using the type of sublink
to set the plan name; for example, a query that previously had two
expression sublinks shown as InitPlan 2 and InitPlan 1 will now end up
named expr_1 and expr_2. Because names are assigned before rather than
after planning, some of the regression test outputs show the numerical
part of the name switching positions: what was previously SubPlan 2 was
actually the first one encountered, but we finished planning it later.
We assign names even to subqueries that aren't shown as such within the
EXPLAIN output. These include subqueries that are a FROM clause item or
a branch of a set operation, rather than something that will be turned
into an InitPlan or SubPlan. The purpose of this is to make sure that,
below the topmost query level, there's always a name for each subquery
that is stable from one planning cycle to the next (assuming no changes
to the query or the database schema).
Author: Robert Haas <rhaas@postgresql.org>
Co-authored-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: Alexandra Wang <alexandra.wang.oss@gmail.com>
Reviewed-by: Richard Guo <guofenglinux@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Reviewed-by: Junwang Zhao <zhjwpku@gmail.com>
Discussion: http://postgr.es/m/3641043.1758751399@sss.pgh.pa.us
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/8c49a484e8ebb0199fba4bd68eaaedaf49b48ed0
Modified Files
--------------
contrib/postgres_fdw/expected/postgres_fdw.out | 70 +++---
src/backend/commands/explain.c | 15 +-
src/backend/optimizer/path/allpaths.c | 6 +-
src/backend/optimizer/plan/planagg.c | 3 +
src/backend/optimizer/plan/planner.c | 78 ++++++-
src/backend/optimizer/plan/subselect.c | 87 ++++---
src/backend/optimizer/prep/prepjointree.c | 1 +
src/backend/optimizer/prep/prepunion.c | 5 +-
src/backend/utils/adt/ruleutils.c | 33 ++-
src/include/nodes/pathnodes.h | 6 +
src/include/nodes/primnodes.h | 1 +
src/include/optimizer/planner.h | 4 +
src/test/regress/expected/aggregates.out | 58 ++---
src/test/regress/expected/create_index.out | 14 +-
src/test/regress/expected/groupingsets.out | 94 ++++----
src/test/regress/expected/incremental_sort.out | 8 +-
src/test/regress/expected/inherit.out | 32 +--
src/test/regress/expected/insert_conflict.out | 4 +-
src/test/regress/expected/join.out | 78 +++----
src/test/regress/expected/join_hash.out | 32 +--
src/test/regress/expected/memoize.out | 4 +-
src/test/regress/expected/merge.out | 12 +-
src/test/regress/expected/partition_prune.out | 300 ++++++++++++-------------
src/test/regress/expected/portals.out | 12 +-
src/test/regress/expected/predicate.out | 8 +-
src/test/regress/expected/returning.out | 24 +-
src/test/regress/expected/rowsecurity.out | 138 ++++++------
src/test/regress/expected/rowtypes.out | 12 +-
src/test/regress/expected/select_parallel.out | 56 ++---
src/test/regress/expected/sqljson.out | 4 +-
src/test/regress/expected/subselect.out | 174 +++++++-------
src/test/regress/expected/updatable_views.out | 52 ++---
src/test/regress/expected/update.out | 8 +-
src/test/regress/expected/window.out | 10 +-
src/test/regress/expected/with.out | 20 +-
35 files changed, 810 insertions(+), 653 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: rhaas@postgresql.org, pgsql-committers@lists.postgresql.org
Subject: Re: pgsql: Assign each subquery a unique name prior to planning it.
In-Reply-To: <E1v67fu-000Vpy-2F@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