Re: *_collapse_limit, geqo_threshold

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> 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.)

Well, I can cite one reasonably plausible use case: when you have an
umpteen-way join you need to execute a lot, and you don't want to pay
for an exhaustive search, but GEQO doesn't reliably find a good plan.
What you can do is let the system do an exhaustive search once to find
the best plan, then you rearrange the query to specify that join order
via JOINs, and turn off join collapsing. Presto, good plan every time
with very little planning time expended.

Now, your answer will probably be that we should provide some better
mechanism for re-using a previously identified plan structure. No
doubt that would be ideal, but the amount of effort required to get
there is nontrivial, and I'm not at all convinced it would be repaid
in usefulness. Whereas what I describe above is something that costs
us nearly nothing to provide. The usefulness might be marginal too,
but on the basis of cost/benefit ratio it's a clear win.

>> 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.

I was imagining a GUC that would make the reset optional, in case anyone
really does want to have unstable plans. I don't have much doubt about
what typical users will prefer though.

regards, tom lane