From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Recalculate where-needed data accurately after a join removal.
Date: Fri, 27 Sep 2024 20:04:11 +0000
Message-ID: <E1suHCE-001CFp-Eq@gemulon.postgresql.org> (raw)
Recalculate where-needed data accurately after a join removal.
Up to now, remove_rel_from_query() has done a pretty shoddy job
of updating our where-needed bitmaps (per-Var attr_needed and
per-PlaceHolderVar ph_needed relid sets). It removed direct mentions
of the to-be-removed baserel and outer join, which is the minimum
amount of effort needed to keep the data structures self-consistent.
But it didn't account for the fact that the removed join ON clause
probably mentioned Vars of other relations, and those Vars might now
not be needed as high up in the join tree as before. It's easy to
show cases where this results in failing to remove a lower outer join
that could also have been removed.
To fix, recalculate the where-needed bitmaps from scratch after
each successful join removal. This sounds expensive, but it seems
to add only negligible planner runtime. (We cheat a little bit
by preserving "relation 0" entries in the bitmaps, allowing us to
skip re-scanning the targetlist and HAVING qual.)
The submitted test case drew attention because we had successfully
optimized away the lower join prior to v16. I suspect that that's
somewhat accidental and there are related cases that were never
optimized before and now can be. I've not tried to come up with
one, though.
Perhaps we should back-patch this into v16 and v17 to repair the
performance regression. However, since it took a year for anyone
to notice the problem, it can't be affecting too many people. Let's
let the patch bake awhile in HEAD, and see if we get more complaints.
Per bug #18627 from Mikaël Gourlaouen. No back-patch for now.
Discussion: https://postgr.es/m/18627-44f950eb6a8416c2@postgresql.org
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/a3179ab692be4314d5ee5cd56598976c487d5ef2
Modified Files
--------------
src/backend/optimizer/path/equivclass.c | 41 ++++++-
src/backend/optimizer/plan/analyzejoins.c | 85 ++++++++------
src/backend/optimizer/plan/initsplan.c | 179 +++++++++++++++++++++++++++++-
src/backend/optimizer/util/placeholder.c | 27 +++++
src/include/optimizer/paths.h | 1 +
src/include/optimizer/placeholder.h | 1 +
src/include/optimizer/planmain.h | 4 +
src/test/regress/expected/join.out | 12 +-
src/test/regress/sql/join.sql | 7 +-
9 files changed, 320 insertions(+), 37 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: Recalculate where-needed data accurately after a join removal.
In-Reply-To: <E1suHCE-001CFp-Eq@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