pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedMemoize in between of two JOIN nodes
3+ messages / 2 participants
[nested] [flat]
* Memoize in between of two JOIN nodes
@ 2025-03-06 11:17 Andrei Lepikhov <lepihov@gmail.com>
2025-03-07 01:13 ` Re: Memoize in between of two JOIN nodes Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Andrei Lepikhov @ 2025-03-06 11:17 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: Richard Guo <guofenglinux@gmail.com>
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...
--
regards, Andrei Lepikhov
Attachments:
reproduction.sql (application/sql, 1.8K) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Memoize in between of two JOIN nodes
2025-03-06 11:17 Memoize in between of two JOIN nodes Andrei Lepikhov <lepihov@gmail.com>
@ 2025-03-07 01:13 ` Richard Guo <guofenglinux@gmail.com>
2025-03-10 09:22 ` Re: Memoize in between of two JOIN nodes Andrei Lepikhov <lepihov@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Richard Guo @ 2025-03-07 01:13 UTC (permalink / raw)
To: Andrei Lepikhov <lepihov@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Memoize in between of two JOIN nodes
2025-03-06 11:17 Memoize in between of two JOIN nodes Andrei Lepikhov <lepihov@gmail.com>
2025-03-07 01:13 ` Re: Memoize in between of two JOIN nodes Richard Guo <guofenglinux@gmail.com>
@ 2025-03-10 09:22 ` Andrei Lepikhov <lepihov@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Andrei Lepikhov @ 2025-03-10 09:22 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
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
Attachments:
demo.sql (application/sql, 1.1K) download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2025-03-10 09:22 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-03-06 11:17 Memoize in between of two JOIN nodes Andrei Lepikhov <lepihov@gmail.com>
2025-03-07 01:13 ` Richard Guo <guofenglinux@gmail.com>
2025-03-10 09:22 ` Andrei Lepikhov <lepihov@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox