Re: Memoize in between of two JOIN nodes

On 7/3/2025 02:13, Richard Guo wrote:
> On Thu, Mar 6, 2025 at 8:17 PM Andrei Lepikhov <lepihov(at)gmail(dot)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