Re: *_collapse_limit, geqo_threshold

On Jul 8, 2009, at 3:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Robert Haas <robertmhaas@gmail.com> writes:
>> On Tue, Jul 7, 2009 at 6:33 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
>>> It's pretty much all-or-nothing now: the GUC does not give you any  
>>> sort
>>> of useful control over *which* joins are reorderable.
>
>> Yes.  So the way I see it, the options are:
>
>> 1. We can remove join_collapse_limit completely and provide no
>> substitute.  In this case, the ability to explicitly specify the join
>> order will be gone.
>
>> 2. We can remove join_collapse_limit but provide a different, Boolean
>> GUC instead, like enable_join_reordering.  In this case, we're not
>> actually reducing the number of GUCs, just the size of the foot-gun.
>
>> 3. We can remove join_collapse_limit and provide an alternative way  
>> to
>> explicitly specify the join order that is more flexible.  This both
>> reduces the number of GUCs and arguably provides some useful
>> functionality that we don't have now.
>
>> It sounds like your vote is for #2, which, as I say, seems like a
>> feature with one arm tied behind its back, but hey, what do I know?
>
> Well, the reason I'm not voting for #3 is that it looks like a lot of
> work to implement something that would basically be a planner hint,
> which I'm generally against; furthermore, it's a hint that there's  
> been
> no demand for.  (We're not even certain that anyone is using the  
> ability
> to *fully* specify the join order, much less wanting some undetermined
> compromise between manual and automatic control.)  And anyway I didn't
> hear anyone volunteering to do it.  So the realistic alternatives are
> #1, #2, or "do nothing"; and out of those I like #2.

That was my first reaction too, but now I'm wondering whether we  
shouldn't just do #1.  #2 is a planner hint, too, just not a very good  
one.  If, as you suggest, it isn't actually useful, then why keep it  
at all? (On the other hand, if someone thinks they need it, it would  
be interesting to know the use case, and think about the best way to  
address it.)


>
>> Accepting that as the consensus in the absence of contrary votes, we
>> still need to decide what to do about from_collapse_threshold and
>> geqo_threshold.  I'm pretty sure that we shouldn't eliminate GEQO or
>> geqo_threshold, because the basic algorithm is clearly exponential
>> time and eventually you have to start worrying about that, but we
>> could raise the value.  What to do about from_collapse_threshold is
>> less clear to me.
>
> I do not think there is a good argument for eliminating  
> geqo_threshold.
> There might well be an argument for cranking up its default value;
> but that would take some hard data, which seems lacking at the moment.
>
> I'm on the fence about from_collapse_threshold.  The argument for  
> having
> it seems to be that there might be cases where not folding a subquery
> is preferable to folding it and then taking your chances with GEQO.
> But I'm not really convinced there are any.

Me either.  You could probably get the same effect in other ways if  
you actually needed it, like OFFSET 0 or wrapping the subquery in a  
SRF.  I'm leaning more and more toward thinking we should just nuke it.

> It occurs to me that one way to make GEQO less scary would be to take
> out the nondeterminism by resetting its random number generator for
> each query.  You might get a good plan or an awful one, but at least
> it'd be the same one each time.  DBAs like predictability.

Hmm, that doesn't sound appealing to me, but I'm only a DBA at need.

...Robert