Re: *_collapse_limit, geqo_threshold

On Wed, 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.

In the Oracle world they use the hint "ORDERED" for this purpose. As
the Oracle optimizer has gotten smarter over the years I've seen less
need to use it, but over all, compared to other Oracle hints it does
not seem to be extremely
sensitive to data / index / stats changes. When you think about why
such ordering might work that makes sense to me: small tables can be
used early to prune large tables later on. Typically, these smaller
tables are static config info type data. Eg. pick species, then choose
which of the 10 million pathology samples you have match that species.

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

Again Oracle has a mechanism for doing this. Don't know the details,
but our DBA would if anyone cares...

--
Peter Hunsberger