Re: *_collapse_limit, geqo_threshold

On Wed, Jul 08, 2009 at 09:26:35PM -0400, Tom Lane 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.

This sounds like planner hints to me. The argument against hinting, AIUI, is
that although the plan you've guaranteed via hints may be a good one today,
when the data change a bit your carefully crafted plan happens to become a bad
one, but you're no longer around to change the hints accordingly. Entire
stored plans, or predetermined seeds for GEQO's random number generator all
boil down to saying, "I want you to use this plan henceforth and forever."

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

Do we know that GEQO plans are, in reality, less stable than than usual
planner? Certainly on paper it appears they could be, but the mailing lists
are full of emails about "this query's plan changed and performance suddenly
tanked; how do I fix it?" so I'm unconvinced this is a problem unique to GEQO.
Which in turn boils down to "we need real world data to look at".

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com