Re: Improve planner cost estimations for alternative subplans

From: Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>, Melanie Plageman <melanieplageman(at)gmail(dot)com>, Alexey Bashtanov <bashtanov(at)imap(dot)cc>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>, David Rowley <dgrowleyml(at)gmail(dot)com>
Subject: Re: Improve planner cost estimations for alternative subplans
Date: 2020-08-26 08:21:35
Message-ID: CAKU4AWoGw6EtJyGLDyAZSVBF8xd3v0f0WxJzV3pw8MeUMxHVxA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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(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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Kyotaro Horiguchi 2020-08-26 08:25:25 Re: Strange behavior with polygon and NaN
Previous Message Michael Paquier 2020-08-26 08:11:10 Re: some unused parameters cleanup