Re: enable_joinremoval

On Mon, Mar 29, 2010 at 10:36 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Mon, Mar 29, 2010 at 4:33 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>> So I think we need a parameter for join removal also.
>
>> I had this in my original patch but Tom wanted it taken out.
>
> And I still don't want it.  We are NOT going in the direction of adding
> an enable_ knob for every single planner activity --- do you have the
> faintest idea how many there would be?  We have such knobs for a small
> number of cases where it's arguable that the action might be the wrong
> thing for a particular query.  Join removal, if applicable, can't
> possibly be the wrong choice; it's better than every other join strategy.

As Tom Lane would put it, you're attacking a straw man. No one has
proposed adding a planner knob for "every single planner activity".
What Simon and I have proposed is adding some planner knobs that are
virtually parallel to the existing ones, which are useful, and your
argument (or rather, your assertion) against that is apparently that
somehow even thought the OTHER ones are useful, these almost identical
ones for cases that aren't covered by the existing knobs will be
unuseful.

The argument that we don't need a knob to control join removal because
it has to always be the best strategy presuposes that enable_seqscan
exists because a sequential scan might not be the best strategy, or
that enable_hashjoin exists because a hash join might not be the best
strategy, which is emphatically nonsense. If that were the purpose of
those knobs, that would by definition make them planner hints, albeit
incredibly poorly designed ones, and we would be having discussions
about the best way to turn them into more useful planner hints, as by
allowing them to apply to only certain portions of the query tree
and/or changing them from booleans to floats so that you could vary
the relative level of discouragement given to any particular planner
method, rather than only allowing zero and infinity. Of course, we
are NOT having those conversations because that ISN'T the purpose of
those knobs. Rather, their purpose, at least AIUI, is to allow the
user to see what the planner would have done had it not had those
strategies as its disposal - which is just as legitimate for join
removal or materialization as it is for hash join or merge join. If
someone wants to ask a question like "how much does join removal speed
up this query?" or "how much does this material node (that didn't
exist in 8.4) speed up or slow down this query?", there is going to be
no easy way for them to understand that without these knobs.

And for the record, I believe I find it rather amusing that you're
asking me if I "have the faintest idea how many there would be". I
venture to say that after yourself I might be the person who knows
this code best. I know how many there will be, if I get my way, and
that number is two. The query planner is a great piece of code but it
is not so transparently simple that it doesn't need debugging or
instrumentation, and "why did the planner do X" has got to be one of
our top ten most-frequently asked questions. Failing to provide a few
trivially simple debugging tools for the small number of major new
planner features added in 9.0 is essentially pointless cruelty and
will result in an endless series of unanswerable questions on
-performance, particularly where materialization is concerned, since
for join removal there is at least a workaround (maybe not such an
easy one to use in all cases, but it exists - just select all the
columns).

...Robert