Re: OUTER JOIN performance regression remains in 8.3beta4 - Mailing list pgsql-hackers

From Tom Lane
Subject Re: OUTER JOIN performance regression remains in 8.3beta4
Date
Msg-id 26828.1199502570@sss.pgh.pa.us
Whole thread Raw
In response to Re: OUTER JOIN performance regression remains in 8.3beta4  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
I wrote:
> [ Pokes at older branches... ]  Oh, that's interesting, 8.1 seems to do
> the right thing already!

Seems that 8.1 does the right thing for the wrong reason :-(.  Just like
8.2, it falsely concludes that the f3 = f1 clause can be deleted, but
it fails to get rid of every copy of it.  The reason is that initsplan.c
puts the clause onto the joinlist of every rel mentioned in its
required_relids, but the eventual remove_join_clause_from_rels() call
only takes it off of the joinlists of rels specifically mentioned in the
clause.  In this test case, 8.1 thinks that t2 is part of the
required_relids for the upper join's clause, so f3 = f1 survives on that
rel's joinlist and eventually gets applied when its required_relids are
satisfied.  But 8.2 has outer-join rearrangement ability, so it
correctly figures that the required_relids for f3 = f1 shouldn't include
t2, and that means that the remove_join_clause_from_rels() call manages
to knock off every copy of the clause.

The net effect of this seems to be that 8.1 will preserve a copy of a
"redundant" outer-join clause if that clause appeared above any rels
that it didn't explicitly mention.  This is certainly not the design
intention, and it probably results in clauses being sometimes uselessly
kept.  But it prevents the bug at hand, so I'm not inclined to touch the
logic in 8.1 unless we realize there's another bug there.

Not sure yet what to do to fix 8.2.  The whole business of adding and
then deleting join clauses was pretty ugly, and I'm happy it's gone
in 8.3; but it doesn't seem very prudent to try to change that basic
approach in 8.2.  Somehow generate_outer_join_implications() needs
to be smarter about which clauses are really safe to remove.
        regards, tom lane


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: OUTER JOIN performance regression remains in 8.3beta4
Next
From: Robert Treat
Date:
Subject: Re: Dynamic Partitioning using Segment Visibility Maps