On Wed, Dec 24, 2025 at 08:32:42AM -0500, Robert Haas wrote:
> On Sat, Dec 20, 2025 at 5:49 PM Bruce Momjian <bruce@momjian.us> wrote:
> > It seems we have to identify these joins _before_ we actually start the
> > main optimizer. We can identify restriction joins since we see the
> > restriction in the query, and we can identify neutral joins because of
> > foreign keys. How do we identify expansion joins? Is it all the joins
> > which are not the previous types?
>
> We unfortunately have no way to identify these joins before we
> actually start the main optimizer; that's not how the code works. I'm
> not sure if there's a reasonable way to do better, because whether the
> join inflates or reduces the row count can't be known independently of
> the join order in general, even though in practice it often can.
Okay, makes sense. Are we moving forward with this patch, or are we
considering the greedy join search algorithm as a possible better
solution to this problem?
https://www.postgresql.org/message-id/flat/3FF63E99-AB4F-41A9-BC78-AAB28823FBD0%40Outlook.com
> > In the group execution order we are considering:
> >
> > 1. restriction joins
> > 2. neutral joins
> > 3. expansion joins
> >
> > do we generate only the cheapest path for each group or return multiple
> > paths that can be considered by later group executions? If not, if we
> > join the same column in stages 1 & 2, would it make sense to move the
> > stage 2 join into stage 1 so we can potentially use sort order
> > (pathkeys) for both joins? Similarly if we join the same column in
> > stage 2 & 3 can we move the stage 2 join to stage 3? I don't think it
> > is worth moving stage 1 to 3 or 3 to 1 since it seems too risky.
>
> I'm not sure this is what the patch is actually doing -- it's
> definitely not what the current code is doing.
I think we have general consensus that short-circuiting queries that do
many joins to a single table, like star joins, is a valid optimization.
I guess we have to continue considering the best solution.
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
