Re: A performance issue with Memoize

On Thu, Oct 26, 2023 at 12:07 PM Andrei Lepikhov <a.lepikhov@postgrespro.ru>
wrote:

> Do you've thought about the case, fixed with the commit 1db5667? As I
> see, that bugfix still isn't covered by regression tests. Could your
> approach of a PARAM_EXEC slot reusing break that case?


Hm, I don't think so.  The issue fixed by commit 1db5667 was caused by
sharing PARAM_EXEC slots between different levels of NestLoop.  AFAICS
it's safe to share PARAM_EXEC slots within the same level of NestLoop.

The change here is about sharing PARAM_EXEC slots between subquery's
subplan_params and outer-relation variables, which happens within the
same level of NestLoop.

Actually, even without this change, we'd still share PARAM_EXEC slots
between subquery's subplan_params and outer-relation variables in some
cases.  As an example, consider

explain (costs off)
select * from t t1 left join
        (t t2 left join
                lateral (select t1.a as t1a, t2.a as t2a, * from t t3) s
        on t2.b = s.b)
on t1.b = s.b and t1.a = t2.a;
                      QUERY PLAN
-------------------------------------------------------
 Nested Loop Left Join
   ->  Seq Scan on t t1
   ->  Nested Loop
         Join Filter: (t1.a = t2.a)
         ->  Seq Scan on t t2
         ->  Subquery Scan on s
               Filter: ((t1.b = s.b) AND (t2.b = s.b))
               ->  Seq Scan on t t3
(8 rows)

For outer-relation Var 't1.a' from qual 't1.a = t2.a', it shares
PARAM_EXEC slot 0 with the PlannerParamItem for 't1.a' within the
subquery (from its targetlist).

Did you notice a case that the change here breaks?

Hi Tom, could you share your insights on this issue and the proposed
fix?

Thanks
Richard