Re: Improve planner cost estimations for alternative subplans

On Mon, Aug 17, 2020 at 10:12 PM Andy Fan <zhihui.fan1213@gmail.com> wrote:

On Mon, Jun 22, 2020 at 9:39 AM Tom Lane <tgl@sss.pgh.pa.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). 

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