Content-Type: multipart/mixed; boundary="------------dwbkqzajLsn8TAryusG9wdNR"
Message-ID: <f5d78d0c-b3b5-4173-aa46-118cda069223@gmail.com>
Date: Mon, 10 Mar 2025 10:22:17 +0100
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Memoize in between of two JOIN nodes
To: Richard Guo <guofenglinux@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
References: <531bcdbc-e734-4db8-b1d1-18af8bdf9eab@gmail.com>
 <CAMbWs4_qMy5ZGCRuSCEe9hB1r_G69neVLJPDtykN+t+umCOo6g@mail.gmail.com>
Content-Language: en-US
From: Andrei Lepikhov <lepihov@gmail.com>
In-Reply-To: 
 <CAMbWs4_qMy5ZGCRuSCEe9hB1r_G69neVLJPDtykN+t+umCOo6g@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/f5d78d0c-b3b5-4173-aa46-118cda069223%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------dwbkqzajLsn8TAryusG9wdNR
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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
--------------dwbkqzajLsn8TAryusG9wdNR
Content-Type: application/sql; name="demo.sql"
Content-Disposition: attachment; filename="demo.sql"
Content-Transfer-Encoding: base64

RFJPUCBUQUJMRSBJRiBFWElTVFMgdDEsdDIgQ0FTQ0FERTsKU0VUIGVuYWJsZV9tZXJnZWpv
aW4gPSAnb2ZmJzsKU0VUIGVuYWJsZV9oYXNoam9pbiA9ICdvZmYnOwpTRVQgZW5hYmxlX3Nl
cXNjYW4gPSAnb2ZmJzsKU0VUIG1heF9wYXJhbGxlbF93b3JrZXJzX3Blcl9nYXRoZXIgPSAw
OwoKQ1JFQVRFIFRBQkxFIHQxICh4IGludGVnZXIsIHkgbnVtZXJpYyBERUZBVUxUIDApOwpJ
TlNFUlQgSU5UTyB0MSAoeCkgKFNFTEVDVCBncyAlIDIgRlJPTSBnZW5lcmF0ZV9zZXJpZXMo
MSwxRTYpIEFTIGdzKTsKCkNSRUFURSBUQUJMRSB0MiAoeCBpbnRlZ2VyLCB5IG51bWVyaWMg
REVGQVVMVCAwKTsKSU5TRVJUIElOVE8gdDIgKHgpIChTRUxFQ1QgZ3MgRlJPTSBnZW5lcmF0
ZV9zZXJpZXMoMSwxRTUpIEFTIGdzKTsKQ1JFQVRFIElOREVYIE9OIHQyKHgpOwpWQUNVVU0g
QU5BTFlaRSB0MSx0MjsKCkVYUExBSU4gKEFOQUxZWkUsIENPU1RTIE9GRiwgVElNSU5HIE9G
RiwgQlVGRkVSUyBPRkYsIFNVTU1BUlkgT04pClNFTEVDVCAqIEZST00gdDEgTEVGVCBKT0lO
ICgKICBTRUxFQ1QgdDIuKiBGUk9NIHQyIEpPSU4gKFZBTFVFUyAoMSwxKSwgKDIsMikpIEFT
IHEyKHgseSkgT04gKHQyLnk9cTIueSkpIHEKT04gKHQxLnggPSBxLngpOwoKLyoKIE5lc3Rl
ZCBMb29wIExlZnQgSm9pbiAoYWN0dWFsIHJvd3M9MTAwMDAwMC4wMCBsb29wcz0xKQogICAt
PiAgU2VxIFNjYW4gb24gdDEgKGFjdHVhbCByb3dzPTEwMDAwMDAuMDAgbG9vcHM9MSkKICAg
ICAgICAgRGlzYWJsZWQ6IHRydWUKICAgLT4gIE5lc3RlZCBMb29wIChhY3R1YWwgcm93cz0w
LjAwIGxvb3BzPTEwMDAwMDApCiAgICAgICAgIEpvaW4gRmlsdGVyOiAodDIueSA9ICgiKlZB
TFVFUyoiLmNvbHVtbjIpOjpudW1lcmljKQogICAgICAgICBSb3dzIFJlbW92ZWQgYnkgSm9p
biBGaWx0ZXI6IDEKICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgdDJfeF9pZHggb24g
dDIgKGFjdHVhbCByb3dzPTAuNTAgbG9vcHM9MTAwMDAwMCkKICAgICAgICAgICAgICAgSW5k
ZXggQ29uZDogKHggPSB0MS54KQogICAgICAgICAtPiAgVmFsdWVzIFNjYW4gb24gIipWQUxV
RVMqIiAoYWN0dWFsIHJvd3M9Mi4wMCBsb29wcz01MDAwMDApCiBQbGFubmluZyBUaW1lOiAw
LjMxMSBtcwogRXhlY3V0aW9uIFRpbWU6IDcyMTcuMDI0IG1zCiAqLwo=

--------------dwbkqzajLsn8TAryusG9wdNR--