Content-Type: multipart/mixed; boundary="------------fDD6LRBGf136MdUTXTrOmPT7"
Message-ID: <4e9d279c-83c1-42d3-b74d-7366ecd7f085@tantorlabs.com>
Date: Wed, 3 Sep 2025 00:07:01 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pull-up subquery if JOIN-ON contains refs to upper-query
To: Alena Rybakina <a.rybakina@postgrespro.ru>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Peter Petrov <p.petrov@postgrespro.ru>, David Rowley <dgrowleyml@gmail.com>
Cc: Ranier Vilela <ranier.vf@gmail.com>
References: <0b1f670d-b39d-4966-bf32-f0d502ebc564@postgrespro.ru>
 <CAEudQAoD707uh5Pjpg5NMyF-QO=fzajA+BmtcoqQAeXN1C+TkQ@mail.gmail.com>
 <46bc4eaf-58c5-42ab-8041-d3380a0768de@postgrespro.ru>
 <9b041978-06e3-4a50-8a5d-dacbb054f23e@tantorlabs.com>
 <8d7cbbf6-ddc0-4696-8af2-a68d740e14f2@postgrespro.ru>
 <6078f7e1-4b29-4b31-bd28-b84149e404e4@postgrespro.ru>
 <975a3736-a8b5-49b3-8009-4d4e86867aa1@postgrespro.ru>
 <0adbf75a-2059-4bb7-b878-c33892dbc1f0@tantorlabs.com>
 <300d7bb0-345c-4c0b-a0ef-4de573fcc94b@postgrespro.ru>
 <e172de23-7acb-4330-a40a-58ead7a47944@postgrespro.ru>
 <32446f52-1b18-499a-a77b-d6b66ad0f4cf@tantorlabs.com>
 <87a6fcbf-929a-408a-9471-28be3080254a@postgrespro.ru>
Content-Language: en-US
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
In-Reply-To: <87a6fcbf-929a-408a-9471-28be3080254a@postgrespro.ru>
Archived-At: 
 <https://www.postgresql.org/message-id/4e9d279c-83c1-42d3-b74d-7366ecd7f085%40tantorlabs.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------fDD6LRBGf136MdUTXTrOmPT7
Content-Type: multipart/alternative;
 boundary="------------lJGITW1Za06uWgalIw1ERn0x"

--------------lJGITW1Za06uWgalIw1ERn0x
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit


On 04.06.2025 13:40, Alena Rybakina wrote:
>
> Do you have any ideas on how to solve this problem? So far, the only 
> approach I see is to try an alternative plan but I'm still learning this.
>

Hi,

I've reviewed this patch, and I have suggestion about the approach.

Currently, the patch extends 'convert_EXISTS_sublick_to_join' with quite 
complex logic (clause collection, volatile checks, rewriting join quals, 
etc). While it works, the amount of branching and special cases makes 
the function harder to follow.

Looking at the logic, it seems that a large part of the complexity comes 
from trying to directly adapt 'convert_EXISTS_sublink_to_join' instead 
of factoring out a dedicated path. An alternative would be to introduce 
a separate function *'convert_EXISTS_sublink_to_lateral_join' *- with a 
similar API to 'convert_ANY_sublink_to_join'. Such a function can focus 
only on the EXISTS-to-join case, while keeping the existing function 
shorter and easier to reason about.

I even made some first rough sketches of this approach (not a finished 
patch, just an outline). Of course, it would still need proper 
adaptation, but I think it demonstrates that the overall structure can 
be kept simpler.

What do you think about refactoring in this direction?

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com

--------------lJGITW1Za06uWgalIw1ERn0x
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 04.06.2025 13:40, Alena Rybakina
      wrote:</div>
    <blockquote type="cite"
      cite="mid:87a6fcbf-929a-408a-9471-28be3080254a@postgrespro.ru">
      <p>Do you have any ideas on how to solve this problem? So far, the
        only approach I see is to try an alternative plan but I'm still
        learning this.</p>
    </blockquote>
    <p><br>
    </p>
    <p>Hi,</p>
    <p>I've reviewed this patch, and I have suggestion about the
      approach.</p>
    <p>Currently, the patch extends 'convert_EXISTS_sublick_to_join'
      with quite complex logic (clause collection, volatile checks,
      rewriting join quals, etc). While it works, the amount of
      branching and special cases makes the function harder to follow.</p>
    <p>Looking at the logic, it seems that a large part of the
      complexity comes from trying to directly adapt
      'convert_EXISTS_sublink_to_join' instead of factoring out a
      dedicated path. An alternative would be to introduce a separate
      function <b>'convert_EXISTS_sublink_to_lateral_join' </b>- with
      a similar API to 'convert_ANY_sublink_to_join'. Such a function
      can focus only on the EXISTS-to-join case, while keeping the
      existing function shorter and easier to reason about.</p>
    <p>I even made some first rough sketches of this approach (not a
      finished patch, just an outline). Of course, it would still need
      proper adaptation, but I think it demonstrates that the overall
      structure can be kept simpler.</p>
    <p>What do you think about refactoring in this direction?</p>
    <p>--<br>
      Best regards,<br>
      Ilia Evdokimov,<br>
      Tantor Labs LLC,<br>
      <a class="moz-txt-link-freetext" href="https://tantorlabs.com">https://tantorlabs.com</a><br>
    </p>
  </body>
</html>

--------------lJGITW1Za06uWgalIw1ERn0x--

--------------fDD6LRBGf136MdUTXTrOmPT7
Content-Type: text/x-patch; charset=UTF-8;
 name="convert_EXISTS_sublink_to_lateral_join.patch"
Content-Disposition: attachment;
 filename="convert_EXISTS_sublink_to_lateral_join.patch"
Content-Transfer-Encoding: base64

ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wbGFuL3N1YnNlbGVjdC5jIGIv
c3JjL2JhY2tlbmQvb3B0aW1pemVyL3BsYW4vc3Vic2VsZWN0LmMKaW5kZXggZDcxZWQ5NThl
MzEuLjZkYmJjM2Q5YjcyIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxh
bi9zdWJzZWxlY3QuYworKysgYi9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxhbi9zdWJzZWxl
Y3QuYwpAQCAtMTA0LDYgKzEwNCw5NiBAQCBzdGF0aWMgQml0bWFwc2V0ICpmaW5hbGl6ZV9w
bGFuKFBsYW5uZXJJbmZvICpyb290LAogc3RhdGljIGJvb2wgZmluYWxpemVfcHJpbW5vZGUo
Tm9kZSAqbm9kZSwgZmluYWxpemVfcHJpbW5vZGVfY29udGV4dCAqY29udGV4dCk7CiBzdGF0
aWMgYm9vbCBmaW5hbGl6ZV9hZ2dfcHJpbW5vZGUoTm9kZSAqbm9kZSwgZmluYWxpemVfcHJp
bW5vZGVfY29udGV4dCAqY29udGV4dCk7CiAKKy8qCisgKiBjb252ZXJ0X0VYSVNUU19zdWJs
aW5rX3RvX2xhdGVyYWxfam9pbjogCisgKiAJCXRyeSB0byBjb252ZXJ0IGFuIEVYSVNUUyBT
dWJMaW5rIHRvIGEgbGF0ZXJhbCBqb2luCisgKgorICogVGhlIEFQSSBvZiB0aGlzIGZ1bmN0
aW9uIGlzIGlkZW50aWNhbCB0byBjb252ZXJ0X0FOWV9zdWJsaW5rX3RvX2pvaW4ncywKKyAq
IGV4Y2VwdCB0aGF0IHdlIGFsc28gc3VwcG9ydCB0aGUgY2FzZSB3aGVyZSB0aGUgY2FsbGVy
IGhhcyBmb3VuZCBOT1QgRVhJU1RTLAorICogc28gd2UgbmVlZCBhbiBhZGRpdGlvbmFsIGlu
cHV0IHBhcmFtZXRlciAidW5kZXJfbm90Ii4KKyAqLworSm9pbkV4cHIgKgorY29udmVydF9F
WElTVFNfc3VibGlua190b19sYXRlcmFsX2pvaW4oUGxhbm5lckluZm8gKnJvb3QsIFN1Ykxp
bmsgKnN1YmxpbmssCisJCQkJCQkJICAgYm9vbCB1bmRlcl9ub3QsIFJlbGlkcyBhdmFpbGFi
bGVfcmVscykKK3sKKwlKb2luRXhwciAgICpyZXN1bHQ7CisJUXVlcnkJICAgKnBhcnNlID0g
cm9vdC0+cGFyc2U7CisJUXVlcnkJICAgKnN1YnNlbGVjdCA9IChRdWVyeSAqKSBzdWJsaW5r
LT5zdWJzZWxlY3Q7CisJaW50CQkJcnRpbmRleDsKKwlQYXJzZU5hbWVzcGFjZUl0ZW0gKm5z
aXRlbTsKKwlSYW5nZVRibEVudHJ5ICpydGU7CisJUmFuZ2VUYmxSZWYgKnJ0cjsKKwlQYXJz
ZVN0YXRlICpwc3RhdGU7CisJUmVsaWRzCQlzdWJfcmVmX291dGVyX3JlbGlkczsKKwlib29s
CQl1c2VfbGF0ZXJhbDsKKworCUFzc2VydChzdWJsaW5rLT5zdWJMaW5rVHlwZSA9PSBFWElT
VFNfU1VCTElOSyk7CisKKwkvKgorCSAqIFNlZSBpZiB0aGUgc3VicXVlcnkgY2FuIGJlIHNp
bXBsaWZpZWQgYmFzZWQgb24gdGhlIGtub3dsZWRnZSB0aGF0IGl0J3MKKwkgKiBiZWluZyB1
c2VkIGluIEVYSVNUUygpLiAgSWYgd2UgYXJlbid0IGFibGUgdG8gZ2V0IHJpZCBvZiBpdHMK
KwkgKiB0YXJnZXRsaXN0LCB3ZSBoYXZlIHRvIGZhaWwsIGJlY2F1c2UgdGhlIHB1bGx1cCBv
cGVyYXRpb24gbGVhdmVzIHVzCisJICogd2l0aCBub3BsYWNlIHRvIGV2YWx1YXRlIHRoZSB0
YXJnZXRsaXN0LgorCSAqLworCWlmICghc2ltcGxpZnlfRVhJU1RTX3F1ZXJ5KHJvb3QsIHN1
YnNlbGVjdCkpCisJCXJldHVybiBOVUxMOworCisJLyoKKwkgKiBJZiB0aGUgc3ViLXNlbGVj
dCBjb250YWlucyBhbnkgVmFycyBvZiB0aGUgcGFyZW50IHF1ZXJ5LCB3ZSB0cmVhdCBpdCBh
cworCSAqIExBVEVSQUwuICAoVmFycyBmcm9tIGhpZ2hlciBsZXZlbHMgZG9uJ3QgbWF0dGVy
IGhlcmUuKQorCSAqLworCXN1Yl9yZWZfb3V0ZXJfcmVsaWRzID0gcHVsbF92YXJub3Nfb2Zf
bGV2ZWwoTlVMTCwgKE5vZGUgKikgc3Vic2VsZWN0LCAxKTsKKwl1c2VfbGF0ZXJhbCA9ICFi
bXNfaXNfZW1wdHkoc3ViX3JlZl9vdXRlcl9yZWxpZHMpOworCisJLyoKKwkgKiBDYW4ndCBj
b252ZXJ0IGlmIHRoZSBzdWItc2VsZWN0IGNvbnRhaW5zIHBhcmVudC1sZXZlbCBWYXJzIG9m
IHJlbGF0aW9ucworCSAqIG5vdCBpbiBhdmFpbGFibGVfcmVscy4KKwkgKi8KKwlpZiAoIWJt
c19pc19zdWJzZXQoc3ViX3JlZl9vdXRlcl9yZWxpZHMsIGF2YWlsYWJsZV9yZWxzKSkKKwkJ
cmV0dXJuIE5VTEw7CisKKwkvKiBDcmVhdGUgYSBkdW1teSBQYXJzZVN0YXRlIGZvciBhZGRS
YW5nZVRhYmxlRW50cnlGb3JTdWJxdWVyeSAqLworCXBzdGF0ZSA9IG1ha2VfcGFyc2VzdGF0
ZShOVUxMKTsKKworCS8qCisJICogT2theSwgcHVsbCB1cCB0aGUgc3ViLXNlbGVjdCBpbnRv
IHVwcGVyIHJhbmdlIHRhYmxlLgorCSAqCisJICogV2UgcmVseSBoZXJlIG9uIHRoZSBhc3N1
bXB0aW9uIHRoYXQgdGhlIG91dGVyIHF1ZXJ5IGhhcyBubyByZWZlcmVuY2VzCisJICogdG8g
dGhlIGlubmVyIChuZWNlc3NhcmlseSB0cnVlLCBvdGhlciB0aGFuIHRoZSBWYXJzIHRoYXQg
d2UgYnVpbGQKKwkgKiBiZWxvdykuIFRoZXJlZm9yZSB0aGlzIGlzIGEgbG90IGVhc2llciB0
aGFuIHdoYXQgcHVsbF91cF9zdWJxdWVyaWVzIGhhcworCSAqIHRvIGdvIHRocm91Z2guCisJ
ICovCisJbnNpdGVtID0gYWRkUmFuZ2VUYWJsZUVudHJ5Rm9yU3VicXVlcnkocHN0YXRlLAor
CQkJCQkJCQkJCSAgIHN1YnNlbGVjdCwKKwkJCQkJCQkJCQkgICBtYWtlQWxpYXMoIkVYSVNU
U19zdWJxdWVyeSIsIE5JTCksCisJCQkJCQkJCQkJICAgdXNlX2xhdGVyYWwsCisJCQkJCQkJ
CQkJICAgZmFsc2UpOworCXJ0ZSA9IG5zaXRlbS0+cF9ydGU7CisJcGFyc2UtPnJ0YWJsZSA9
IGxhcHBlbmQocGFyc2UtPnJ0YWJsZSwgcnRlKTsKKwlydGluZGV4ID0gbGlzdF9sZW5ndGgo
cGFyc2UtPnJ0YWJsZSk7CisKKwkvKgorCSAqIEZvcm0gYSBSYW5nZVRibFJlZiBmb3IgdGhl
IHB1bGxlZC11cCBzdWItc2VsZWN0LgorCSAqLworCXJ0ciA9IG1ha2VOb2RlKFJhbmdlVGJs
UmVmKTsKKwlydHItPnJ0aW5kZXggPSBydGluZGV4OworCisJLyoKKwkgKiBBbmQgZmluYWxs
eSwgYnVpbGQgdGhlIEpvaW5FeHByIG5vZGUuCisJICovCisJcmVzdWx0ID0gbWFrZU5vZGUo
Sm9pbkV4cHIpOworCXJlc3VsdC0+am9pbnR5cGUgPSB1bmRlcl9ub3QgPyBKT0lOX0FOVEkg
OiBKT0lOX1NFTUk7OworCXJlc3VsdC0+aXNOYXR1cmFsID0gZmFsc2U7CisJcmVzdWx0LT5s
YXJnID0gTlVMTDsJCS8qIGNhbGxlciBtdXN0IGZpbGwgdGhpcyBpbiAqLworCXJlc3VsdC0+
cmFyZyA9IChOb2RlICopIHJ0cjsKKwlyZXN1bHQtPnVzaW5nQ2xhdXNlID0gTklMOworCXJl
c3VsdC0+am9pbl91c2luZ19hbGlhcyA9IE5VTEw7CisJcmVzdWx0LT5xdWFscyA9IE5VTEw7
CisJcmVzdWx0LT5hbGlhcyA9IE5VTEw7CisJcmVzdWx0LT5ydGluZGV4ID0gMDsJCS8qIHdl
IGRvbid0IG5lZWQgYW4gUlRFIGZvciBpdCAqLworCisJcmV0dXJuIHJlc3VsdDsKK30KIAog
LyoKICAqIEdldCB0aGUgZGF0YXR5cGUvdHlwbW9kL2NvbGxhdGlvbiBvZiB0aGUgZmlyc3Qg
Y29sdW1uIG9mIHRoZSBwbGFuJ3Mgb3V0cHV0LgpAQCAtMTQ2Miw2ICsxNTUyLDkgQEAgY29u
dmVydF9FWElTVFNfc3VibGlua190b19qb2luKFBsYW5uZXJJbmZvICpyb290LCBTdWJMaW5r
ICpzdWJsaW5rLAogCiAJQXNzZXJ0KHN1YmxpbmstPnN1YkxpbmtUeXBlID09IEVYSVNUU19T
VUJMSU5LKTsKIAorCXJldHVybiBjb252ZXJ0X0VYSVNUU19zdWJsaW5rX3RvX2xhdGVyYWxf
am9pbihyb290LCBzdWJsaW5rLCAKKwkJCQkJCQkJCQl1bmRlcl9ub3QsIGF2YWlsYWJsZV9y
ZWxzKTsKKwogCS8qCiAJICogQ2FuJ3QgZmxhdHRlbiBpZiBpdCBjb250YWlucyBXSVRILiAg
KFdlIGNvdWxkIGFycmFuZ2UgdG8gcHVsbCB1cCB0aGUKIAkgKiBXSVRIIGludG8gdGhlIHBh
cmVudCBxdWVyeSdzIGN0ZUxpc3QsIGJ1dCB0aGF0IHJpc2tzIGNoYW5naW5nIHRoZQo=

--------------fDD6LRBGf136MdUTXTrOmPT7--