Re: Planner hints in Postgresql

On Tue, Mar 18, 2014 at 4:48 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:

> > That alone could improve things considerably, and statistical info could
> be
> > propagated along expressions to make it possible to model uncertainty in
> > complex expressions as well.
>
> But how would that work? I see no solution adumbrated there :-).
>

I would have to tipify the SQL expression grammar for this, but I don't
think it would be impossible. Most non-function expression nodes seem
rather trivial. Even CASE, as long as you have a distribution for the
conditional, you can derive a distribution for the whole. User defined
functions would be another game, though. Correlation would have to be
measured, and that can be troublesome and a weak spot of risk computation
as much as it is of planning, but it could be fuzzed arbitrarily until
properly computed - after all, dependency on correlation or non-correlation
is a known source of risk, and accounting for it in any way is better than
not.

> Let's say you change the rowcount estimate to low/bestguess/high *and*
> you only engage extra searches when there is enough disparity between
> those values you still get exponentially more searches.

I was under the impression the planner already did an exhaustive search for
some queries. So it's just a matter of picking the best plan among those
(ie: estimating cost). The case of GEQO isn't any different, except perhaps
introducing a risk-decreasing transformation would be needed, unless I'm
missing something.

> (my thinking
> is that if bestguess estimated execution time is some user definable
> amount faster then low/high at any node), a more skeptical plan is
> introduced. All that could end up being pessimal to the general case
> though.

I think the cost estimate would be replaced by a distribution (simplified
perhaps into an array of moments, or whatever is easily manipulated in the
face of complex expressions). What the user would pick, is a sampling
method of said distribution. Then, plans get measured by the user's stick
(say: arithmetic mean, median, 90th percentile, etc). The arithmetic mean
would I guess be the default, and that ought to be roughly equivalent to
the planner's current behavior.