Re: Query optimization problem

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> In the example, we do have d1.id and d2.basedon grouped in an
> equivalence class. So in principle you could substitute d1.id into the
> WHERE clause in place of d2.basedon, once you'd checked that it was
> being used with an operator that's compatible with the specific
> equivalence class (ie it's in one of the eclass's opfamilies, I think).
> The problem is to recognize that such a rewrite would be a win --- it
> could just as easily be a big loss.

Ok, that was my feeling too.

> Even if we understood how to direct the rewriting process, I'm really
> dubious that it would win often enough to justify the added planning
> time. The particular problem here seems narrow enough that solving it
> on the client side is probably a whole lot easier and cheaper than
> trying to get the planner to do it.

My overly naive uneducated idea here would be to produce both the plans
and let the planner evaluate their respective costs. Maybe that's what
you mean here by "how to direct the rewriting process". Then we don't
want to generate too many useless plans when you have lots of eclass
around.

This brings back the idea of pondering somehow the optimiser effort
pushed into "solving" a query plan. Like in gcc we can use different
effort targets and we don't know for sure before hand if -O3 will
produce faster code than -O2, all we know is that it will try harder.

Is it possible to imagine having a plan_eclass_permutations default to
false that would activate the discussed behavior here? Ok, I'm not sure
what form should take such a setting, but clearly, there's a need to be
able to impact the optimiser effort.

Regards,
--
dim