Re: Improve planner cost estimations for alternative subplans

On Wed, Aug 26, 2020 at 4:21 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:

>
>
> On Mon, Aug 17, 2020 at 10:12 PM Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
> wrote:
>
>>
>>
>> On Mon, Jun 22, 2020 at 9:39 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>
>>> I wrote:
>>> > Nope. The entire reason why we have that kluge is that we don't know
>>> > until much later how many times we expect to execute the subplan.
>>> > AlternativeSubPlan allows the decision which subplan form to use to be
>>> > postponed till runtime; but when we're doing things like estimating the
>>> > cost and selectivity of a where-clause, we don't know that.
>>>
>>> > Maybe there's some way to recast things to avoid that problem,
>>> > but I have little clue what it'd look like.
>>>
>>> Actually ... maybe it's not that bad. Clearly there would be a
>>> circularity issue for selectivity estimation, but all the alternatives
>>> should have the same selectivity. Cost estimation is a different story:
>>> by the time we need to do cost estimation for a subexpression, we do in
>>> many cases have an idea how often the subexpression will be executed.
>>>
>>>
>> I read your idea of "ripping out all executor support for
>> AlternativeSubPlan
>> and instead having the planner replace an AlternativeSubPlan with
>> the desired specific SubPlan somewhere late in planning, possibly
>> setrefs.c."
>> in [1]. I was thinking that if we can do such a replacement sooner,
>> for example once we know the num_calls for the subplans, Unknown if it
>> is possible though. If we can, then we can handle the issue here as well.
>>
>> The attached is a very PoC version, I'm not sure if it is the right
>> direction
>> to go.
>>
>
> The idea behind it is if we have a RelOptInfo which have
> some AlternativeSubPlan,
> and assume these subplans have some correlated vars which can be expressed
> as
> deps_relids. Then we can convert the AlternativeSubPlan to SubPlan once
> bms_is_subset(subplan->deps_relids, rel->relids).
>

The way of figuring out subplan->deps_relids was wrong in my patch, I will
fix it later.
But the general idea is the same.

> My patch is able to fix the issue reported
> here and it only converts the AlternativeSubPlan in rel->reltarget for
> demo purpose.
>
> --
> Best Regards
> Andy Fan
>

--
Best Regards
Andy Fan