Re: Trying to pull up EXPR SubLinks

On Fri, Apr 24, 2020 at 5:24 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Fri, 24 Apr 2020 at 15:26, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com> wrote:
> >
> > Actually I have a different opinion to handle this issue, to execute the
> > a > (select avg(a) from tinner where x = touer.x); The drawback of
> current
> > path is because it may calculates the same touer.x value multi-times. So
> > if we cache the values we have calculated before, we can avoid the cost.
> > Material path may be the one we can reference but it assumes all the
> tuples
> > in the tuplestore matches the input params, which is not the fact here.
> >
> > But what if the input params doesn't change? If so we can use Material
> path
> > to optimize this case. But since we don't know if the if the input
> params changed
> > or not during plan time, we just add the path (let's assume we can add
> it with some
> > rules or cost calculation). If the input params is not changed, we use
> the cached
> > values, if the input params changed, we can ReScan the Material node.
> To optimize
> > the the cache invalidation frequent issue like (1, 2, 1, 2, 1, 2) case,
> we may consider
> > a sort path to change the input values to (1, 1, 1, 2, 2, 2). But
> overall it is a big effort.
>
> This does not seem quite right to me. What you need is some sort of
> parameterized materialize. Materialize just reads its subnode and
> stores the entire thing input and reuses it any time that it
> rescanned.
>
> You likely need something more like what is mentioned in [1]. There's
> also a bunch of code from Heikki in the initial email in that thread.
> Heikki put it in nodeSubplan.c. I think it should be a node of its
> own.
>
>
Glad to see your feedback, David:). Actually I thought about this idea
some
time ago, but since we have to implement a new path and handle
the cached data is too huge case, I gave it up later. When I am working
on some other stuff, I found Material path with some chgParam change may
get a no harmful improvement with less effort, based on we know how to
add the material path and we can always get a correct result.

I will check the link you provide when I get time, It's a nice feature and
it will be a
good place to continue working on that feature.

Best Regards
Andy Fan