From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Fix some issues with wrong placement of pseudo-constant quals.
Date: Wed, 22 Feb 2023 17:39:22 +0000
Message-ID: <E1pUt5N-000Ag4-So@gemulon.postgresql.org> (raw)
Fix some issues with wrong placement of pseudo-constant quals.
initsplan.c figured that it could push Var-free qual clauses to
the top of the current JoinDomain, which is okay in the abstract.
But if the current domain is inside some outer join, and we later
commute an inside-the-domain outer join with one outside it,
we end up placing the pushed-up qual clause incorrectly.
In distribute_qual_to_rels, avoid this by using the syntactic scope
of the qual clause; with the exception that if we're in the top-level
join domain we can still use the full query relid set, ensuring the
resulting gating Result node goes to the top of the plan. (This is
approximately as smart as the pre-v16 code was. Perhaps we can do
better later, but it's not clear that such cases are worth a lot of
sweat.)
In process_implied_equality, we don't have a clear notion of syntactic
scope, but we do have the results of SpecialJoinInfo construction.
Thumb through those and remove any lower outer joins that might get
commuted to above the join domain. Again, we can make an exception
for the top-level join domain. It'd be possible to work harder here
(for example, by keeping outer joins that aren't shown as potentially
commutable), but I'm going to stop here for the moment. This issue
has convinced me that the current representation of join domains
probably needs further refinement, so I'm disinclined to write
inessential dependent logic just yet.
In passing, tighten the qualscope passed to process_implied_equality
by generate_base_implied_equalities_no_const; there's no need for
it to be larger than the rel we are currently considering.
Tom Lane and Richard Guo, per report from Tender Wang.
Discussion: https://postgr.es/m/CAHewXNk9eJ35ru5xATWioTV4+xZPHptjy9etdcNPjUfY9RQ+uQ@mail.gmail.com
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/a75ff55c83342656266deb1890bcdd27466333ea
Modified Files
--------------
src/backend/optimizer/path/equivclass.c | 4 +-
src/backend/optimizer/plan/initsplan.c | 74 +++++++++++++++++++++++++++++----
src/test/regress/expected/join.out | 61 +++++++++++++++++++++++++++
src/test/regress/sql/join.sql | 25 +++++++++++
4 files changed, 155 insertions(+), 9 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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-committers@postgresql.org
Cc: tgl@sss.pgh.pa.us
Subject: Re: pgsql: Fix some issues with wrong placement of pseudo-constant quals.
In-Reply-To: <E1pUt5N-000Ag4-So@gemulon.postgresql.org>
References: <E1pUt5N-000Ag4-So@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