Thread

1. Memoize in between of two JOIN nodes

   Andrei Lepikhov <lepihov@gmail.com> — 2025-03-06T11:17:22Z

   Playing with memoisation, I found the case where the Memoize is put over 
   a JOIN node (see attachment).
   I recall a discussion we had with Richard in which he mentioned [1] that 
   this feature is still not implemented and is hard to design.
   I'm not sure, but may it be a sign of a potential bug?
   
   [1] 
   https://www.postgresql.org/message-id/CAMbWs4_CQR9ikyAhjH0CwzCdp%2BFcRAeoVsi5Oohd7s%3Dc5RZABg%40mail.gmail.com
   
   -- 
   regards, Andrei Lepikhov
   

2. Re: Memoize in between of two JOIN nodes

   Richard Guo <guofenglinux@gmail.com> — 2025-03-07T01:13:27Z

   On Thu, Mar 6, 2025 at 8:17 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
   > Playing with memoisation, I found the case where the Memoize is put over
   > a JOIN node (see attachment).
   > I recall a discussion we had with Richard in which he mentioned [1] that
   > this feature is still not implemented and is hard to design.
   > I'm not sure, but may it be a sign of a potential bug?
   
   In your case, the Memoize node is added on top of a base relation of a
   subquery RTE, not a join relation.  The final plan might be kind of
   confusing because the SubqueryScan node is considered trivial and is
   removed from the plan tree.
   
   Thanks
   Richard
   
   
   
   

3. Re: Memoize in between of two JOIN nodes

   Andrei Lepikhov <lepihov@gmail.com> — 2025-03-10T09:22:17Z

   On 7/3/2025 02:13, Richard Guo wrote:
   > On Thu, Mar 6, 2025 at 8:17 PM Andrei Lepikhov <lepihov@gmail.com> wrote:
   >> Playing with memoisation, I found the case where the Memoize is put over
   >> a JOIN node (see attachment).
   >> I recall a discussion we had with Richard in which he mentioned [1] that
   >> this feature is still not implemented and is hard to design.
   >> I'm not sure, but may it be a sign of a potential bug?
   > 
   > In your case, the Memoize node is added on top of a base relation of a
   > subquery RTE, not a join relation.  The final plan might be kind of
   > confusing because the SubqueryScan node is considered trivial and is
   > removed from the plan tree.
   Yes, I understand your point. But I kept in mind a different idea, sorry 
   for my bad explanation:
   In the case of another subquery, we can't use the memoize node even if 
   it would help a lot:
   
   EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, BUFFERS OFF, SUMMARY ON)
   SELECT * FROM t1 LEFT JOIN (
      SELECT t2.* FROM t2 JOIN (
        VALUES (1,1), (2,2)) AS q2(x,y)
      ON (t2.y=q2.y)) q
   ON (t1.x = q.x);
   
   see the full case reproduction script in the attachment.
   So, the question is: may we play with subquery flattening to let memoize 
   caching a join result?
   In our previous discussion, you pointed out the problem of reference 
   detection. But maybe the SubqueryScan hack can make it simpler?
   
   -- 
   regards, Andrei Lepikhov