Re: *_collapse_limit, geqo_threshold

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

> Robert Haas <robertmhaas@gmail.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.

I don't think that would be my answer because plan caching sounds  
hard.  It would be nice to have, though. :-)

But it's clearly a planner hint, however you slice 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.
>
> 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.

OK.

...Robert