Re: The science of optimization in practical terms?

On Wed, Feb 18, 2009 at 3:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> ... At any rate, we'd need to save quite
>> a bit to pay for carting around best and worst case costs for every
>> plan we consider.
>
> Another problem with this is it doesn't really do anything to solve the
> problem we were just discussing, namely having an intelligent way of
> combining inaccurate estimates for WHERE clauses. If you just take a
> range of plausible values and multiply then it doesn't take very many
> clauses to get to a range of [0,1] --- or at least a range of
> probabilities wide enough to be unhelpful.

Yeah.

> An idea that I think has been mentioned before is to try to identify
> cases where we can *prove* there is at most one row emitted by a
> sub-path (eg, because of a unique index, DISTINCT subplan, etc). Then
> we could penalize nestloops with outer relations that weren't provably a
> single row. This is basically restricting the notion of estimation
> confidence to a special case that's particularly important for SQL.

I thought about this, too, and I agree. Having this information
available would also be very helpful for join removal. I believe that
you did some work on this for SEMI/ANTI-join support in the form of
query_is_distinct_for, but I'm not sure if that takes the right sort
of inputs for what we need here. (It also doesn't seem to consider
the case of a baserel with a unique index for some reason...)

...Robert