Skip site navigation (1) Skip section navigation (2)

Re: OUTER JOIN performance regression remains in 8.3beta4

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: OUTER JOIN performance regression remains in 8.3beta4
Date: 2008-01-05 03:09:30
Message-ID: 26828.1199502570@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
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

In response to

pgsql-hackers by date

Next:From: Robert TreatDate: 2008-01-05 03:31:14
Subject: Re: Dynamic Partitioning using Segment Visibility Maps
Previous:From: Tom LaneDate: 2008-01-05 00:46:15
Subject: Re: OUTER JOIN performance regression remains in 8.3beta4

pgsql-patches by date

Next:From: Simon RiggsDate: 2008-01-05 12:09:44
Subject: Re: Archiver behavior at shutdown
Previous:From: Tom LaneDate: 2008-01-05 00:46:15
Subject: Re: OUTER JOIN performance regression remains in 8.3beta4

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group