Re: *_collapse_limit, geqo_threshold

On Jul 8, 2009, at 8:26 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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

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