Content-Type: multipart/mixed; boundary="------------NVgVX6YVYtV5lQiCfJAZKMWo"
Message-ID: <8100af4a-d0aa-450d-a09f-541f26b04ac2@postgrespro.ru>
Date: Thu, 2 May 2024 16:44:54 +0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Removing unneeded self joins
To: Alexander Korotkov <aekorotkov@gmail.com>,
 Alexander Lakhin <exclusion@gmail.com>, Richard Guo <guofenglinux@gmail.com>
Cc: "Gregory Stark (as CFM)" <stark.cfm@gmail.com>,
 =?UTF-8?Q?Micha=C5=82_K=C5=82eczek?= <michal@kleczek.org>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
References: <64486b0b-0404-e39e-322d-0801154901f3@postgrespro.ru>
 <CAPpHfdt9G3EU+vXuqexhweEQoMF-acPUUFuB6hV2Kg0gCy7AjA@mail.gmail.com>
 <ebe66358-428c-439f-85dd-da72c9b2b0c2@postgrespro.ru>
 <CAPpHfdt-0kVV7O==aJEbjY2iGYBu+XBzTHEbPv_6sVNeC7fffQ@mail.gmail.com>
 <48c11b23-9c23-440e-9a3c-020e05196449@postgrespro.ru>
 <CAPpHfdsQkY0Gu7bQ9Ekdyzo_5Xxz74TYKbwmk1on1WAASpxXcQ@mail.gmail.com>
 <8800ccf2-ae82-461c-92a1-d4c0a22f8cf2@postgrespro.ru>
 <CAPpHfdvJ3jBQaJQKrf69K++kXi+Tvzn6TXO-DKXj0KVJjvMebg@mail.gmail.com>
 <1bd80083-a13e-4e9f-a7ce-73c3faab521c@postgrespro.ru>
 <CAPpHfdsz8E085YF2VoJe2+h4YAShsh7p--t3kQfQTntiCy63Xg@mail.gmail.com>
 <922623af-872e-4cdc-9238-129b18bee42d@postgrespro.ru>
 <CAPpHfduoAHTp6O7Q8PDe_w=30v0n_06pGe5M65iR4DYcfaYnRw@mail.gmail.com>
 <0ed2cf02-e655-6ec7-a4fe-52fd8d572f65@gmail.com>
 <CAPpHfdsHWbdPVzUdmoKv3dpo8RHJOa=LQR-ZAT+89uvktMs9XA@mail.gmail.com>
 <735b5cd4-568d-fbf7-ad8c-dcfb4c3af86e@gmail.com>
 <CAPpHfdssga9CBQQ6s32t1Xb2Js2WuPfiporbAse334CJiyBb1Q@mail.gmail.com>
Content-Language: en-AU
From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
Organization: Postgres Professional
In-Reply-To: 
 <CAPpHfdssga9CBQQ6s32t1Xb2Js2WuPfiporbAse334CJiyBb1Q@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/8100af4a-d0aa-450d-a09f-541f26b04ac2%40postgrespro.ru>
Precedence: bulk

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

On 5/1/24 18:59, Alexander Korotkov wrote:
> I think we probably could forbid SJE for the tables with TABLESAMPLE
> altogether.  Please, check the attached patch.
Your patch looks good to me. I added some comments and test case into 
the join.sql.

One question for me is: Do we anticipate other lateral self-references 
except the TABLESAMPLE case? Looking into the extract_lateral_references 
implementation, I see the only RTE_SUBQUERY case to be afraid of. But we 
pull up subqueries before extracting lateral references. So, if we have 
a reference to a subquery, it means we will not flatten this subquery 
and don't execute SJE. Do we need more code, as you have written in the 
first patch?

-- 
regards,
Andrei Lepikhov
Postgres Professional

--------------NVgVX6YVYtV5lQiCfJAZKMWo
Content-Type: text/x-patch; charset=UTF-8;
 name="0001-Forbid-self-join-elimination-on-table-sampling-scans.patch"
Content-Disposition: attachment;
 filename*0="0001-Forbid-self-join-elimination-on-table-sampling-scans.pa";
 filename*1="tch"
Content-Transfer-Encoding: base64

RnJvbSBkYWM4YWZkMjA5NTU4NjkyMWRmY2Y1NTY0ZTdmMjk3OWU4OWI3N2RlIE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiAiQW5kcmVpIFYuIExlcGlraG92IiA8bGVwaWhvdkBn
bWFpbC5jb20+CkRhdGU6IFRodSwgMiBNYXkgMjAyNCAxNjoxNzo1MiArMDcwMApTdWJqZWN0
OiBbUEFUQ0hdIEZvcmJpZCBzZWxmLWpvaW4gZWxpbWluYXRpb24gb24gdGFibGUgc2FtcGxp
bmcgc2NhbnMuCgpIYXZpbmcgY3VzdG9tIHRhYmxlIHNhbXBsaW5nIG1ldGhvZHMgd2UgY2Fu
IHN0dWNrIGludG8gdW5wcmVkaWN0YWJsZSBpc3N1ZXMKcmVwbGFjaW5nIGpvaW4gd2l0aCBz
Y2FuIG9wZXJhdGlvbi4gSXQgbWF5IGhhZCBzZW5zZSB0byBhbmFseXNlIHBvc3NpYmxlCnNp
dHVhdGlvbnMgYW5kIGVuYWJsZSBTSkUsIGJ1dCB0aGUgcmVhbCBwcm9maXQgZnJvbSB0aGlz
IG9wZXJhdGlvbiBsb29rcwp0b28gbG93LgotLS0KIHNyYy9iYWNrZW5kL29wdGltaXplci9w
bGFuL2FuYWx5emVqb2lucy5jIHwgIDggKysrKysrKysKIHNyYy9iYWNrZW5kL29wdGltaXpl
ci9wbGFuL2luaXRzcGxhbi5jICAgIHwgIDUgKysrKy0KIHNyYy90ZXN0L3JlZ3Jlc3MvZXhw
ZWN0ZWQvam9pbi5vdXQgICAgICAgIHwgMTMgKysrKysrKysrKysrKwogc3JjL3Rlc3QvcmVn
cmVzcy9zcWwvam9pbi5zcWwgICAgICAgICAgICAgfCAgNSArKysrKwogNCBmaWxlcyBjaGFu
Z2VkLCAzMCBpbnNlcnRpb25zKCspLCAxIGRlbGV0aW9uKC0pCgpkaWZmIC0tZ2l0IGEvc3Jj
L2JhY2tlbmQvb3B0aW1pemVyL3BsYW4vYW5hbHl6ZWpvaW5zLmMgYi9zcmMvYmFja2VuZC9v
cHRpbWl6ZXIvcGxhbi9hbmFseXplam9pbnMuYwppbmRleCA1MDZmY2NkMjBjLi5iYjg5NTU4
ZGNkIDEwMDY0NAotLS0gYS9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxhbi9hbmFseXplam9p
bnMuYworKysgYi9zcmMvYmFja2VuZC9vcHRpbWl6ZXIvcGxhbi9hbmFseXplam9pbnMuYwpA
QCAtMjE0OCw2ICsyMTQ4LDE0IEBAIHJlbW92ZV9zZWxmX2pvaW5zX29uZV9ncm91cChQbGFu
bmVySW5mbyAqcm9vdCwgUmVsaWRzIHJlbGlkcykKIAkJCUFzc2VydChyb290LT5zaW1wbGVf
cnRlX2FycmF5W2tdLT5yZWxpZCA9PQogCQkJCSAgIHJvb3QtPnNpbXBsZV9ydGVfYXJyYXlb
cl0tPnJlbGlkKTsKIAorCQkJLyoKKwkJCSAqIFRvIGF2b2lkIGNvcm5lciBjYXNlcyB3aXRo
IHRhYmxlIHNhbXBsaW5nIG1ldGhvZHMganVzdCBmb3JiaWQKKwkJCSAqIFNKRSBmb3IgdGFi
bGUgc2FtcGxpbmcgZW50cmllcy4KKwkJCSAqLworCQkJaWYgKHJvb3QtPnNpbXBsZV9ydGVf
YXJyYXlba10tPnRhYmxlc2FtcGxlIHx8CisJCQkJcm9vdC0+c2ltcGxlX3J0ZV9hcnJheVty
XS0+dGFibGVzYW1wbGUpCisJCQkJY29udGludWU7CisKIAkJCS8qCiAJCQkgKiBJdCBpcyBp
bXBvc3NpYmxlIHRvIGVsaW1pbmF0ZSBqb2luIG9mIHR3byByZWxhdGlvbnMgaWYgdGhleQog
CQkJICogYmVsb25nIHRvIGRpZmZlcmVudCBydWxlcyBvZiBvcmRlci4gT3RoZXJ3aXNlIHBs
YW5uZXIgY2FuJ3QgYmUKZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL29wdGltaXplci9wbGFu
L2luaXRzcGxhbi5jIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVyL3BsYW4vaW5pdHNwbGFuLmMK
aW5kZXggZTJjNjhmZTZmOS4uYmY4MzliY2FmNiAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQv
b3B0aW1pemVyL3BsYW4vaW5pdHNwbGFuLmMKKysrIGIvc3JjL2JhY2tlbmQvb3B0aW1pemVy
L3BsYW4vaW5pdHNwbGFuLmMKQEAgLTQxNSw3ICs0MTUsMTAgQEAgZXh0cmFjdF9sYXRlcmFs
X3JlZmVyZW5jZXMoUGxhbm5lckluZm8gKnJvb3QsIFJlbE9wdEluZm8gKmJyZWwsIEluZGV4
IHJ0aW5kZXgpCiAJaWYgKCFydGUtPmxhdGVyYWwpCiAJCXJldHVybjsKIAotCS8qIEZldGNo
IHRoZSBhcHByb3ByaWF0ZSB2YXJpYWJsZXMgKi8KKwkvKiBGZXRjaCB0aGUgYXBwcm9wcmlh
dGUgdmFyaWFibGVzLgorCSAqIENoYW5nZXMgaW4gdGhpcyBwbGFjZSBtYXkgbmVlZCBjaGFu
Z2VzIGluIFNKRSBsb2dpYywgc2VlCisJICogdGhlIHJlbW92ZV9zZWxmX2pvaW5zX29uZV9n
cm91cCByb3V0aW5lLgorCSAqLwogCWlmIChydGUtPnJ0ZWtpbmQgPT0gUlRFX1JFTEFUSU9O
KQogCQl2YXJzID0gcHVsbF92YXJzX29mX2xldmVsKChOb2RlICopIHJ0ZS0+dGFibGVzYW1w
bGUsIDApOwogCWVsc2UgaWYgKHJ0ZS0+cnRla2luZCA9PSBSVEVfU1VCUVVFUlkpCmRpZmYg
LS1naXQgYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2pvaW4ub3V0IGIvc3JjL3Rlc3Qv
cmVncmVzcy9leHBlY3RlZC9qb2luLm91dAppbmRleCA4YjY0MGMyZmMyLi42MzE0M2ZlNTVm
IDEwMDY0NAotLS0gYS9zcmMvdGVzdC9yZWdyZXNzL2V4cGVjdGVkL2pvaW4ub3V0CisrKyBi
L3NyYy90ZXN0L3JlZ3Jlc3MvZXhwZWN0ZWQvam9pbi5vdXQKQEAgLTY5MDAsNiArNjkwMCwx
OSBAQCB3aGVyZSBzMS54ID0gMTsKICAgICAgICAgICAgICAgIEZpbHRlcjogKDEgPSAxKQog
KDkgcm93cykKIAorLS0gQ2hlY2sgdGhhdCBTSkUgZG9lc24ndCB0b3VjaCBUQUJMRVNBTVBM
RSBqb2lucworZXhwbGFpbiAoY29zdHMgb2ZmKQorU0VMRUNUICogRlJPTSBlbXAxIHQxIE5B
VFVSQUwgSk9JTiBMQVRFUkFMCisgIChTRUxFQ1QgKiBGUk9NIGVtcDEgdDIgVEFCTEVTQU1Q
TEUgU1lTVEVNICh0MS5jb2RlKSk7CisgICAgICAgICAgICAgICAgICAgICBRVUVSWSBQTEFO
ICAgICAgICAgICAgICAgICAgICAgIAorLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0KKyBOZXN0ZWQgTG9vcAorICAgLT4gIFNlcSBTY2Fu
IG9uIGVtcDEgdDEKKyAgIC0+ICBTYW1wbGUgU2NhbiBvbiBlbXAxIHQyCisgICAgICAgICBT
YW1wbGluZzogc3lzdGVtICh0MS5jb2RlKQorICAgICAgICAgRmlsdGVyOiAoKHQxLmlkID0g
aWQpIEFORCAodDEuY29kZSA9IGNvZGUpKQorKDUgcm93cykKKwogLS0gQ2hlY2sgdGhhdCBQ
SFZzIGRvIG5vdCBpbXBvc2UgYW55IGNvbnN0cmFpbnRzIG9uIHJlbW92aW5nIHNlbGYgam9p
bnMKIGV4cGxhaW4gKHZlcmJvc2UsIGNvc3RzIG9mZikKIHNlbGVjdCAqIGZyb20gZW1wMSB0
MSBqb2luIGVtcDEgdDIgb24gdDEuaWQgPSB0Mi5pZCBsZWZ0IGpvaW4KZGlmZiAtLWdpdCBh
L3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2pvaW4uc3FsIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwv
am9pbi5zcWwKaW5kZXggYzRjNmM3YjhiYS4uMTg0ZmQwODc2YiAxMDA2NDQKLS0tIGEvc3Jj
L3Rlc3QvcmVncmVzcy9zcWwvam9pbi5zcWwKKysrIGIvc3JjL3Rlc3QvcmVncmVzcy9zcWwv
am9pbi5zcWwKQEAgLTI2NTIsNiArMjY1MiwxMSBAQCBzZWxlY3QgMSBmcm9tIGVtcDEgdDEg
bGVmdCBqb2luCiAgICAgb24gdHJ1ZQogd2hlcmUgczEueCA9IDE7CiAKKy0tIENoZWNrIHRo
YXQgU0pFIGRvZXNuJ3QgdG91Y2ggVEFCTEVTQU1QTEUgam9pbnMKK2V4cGxhaW4gKGNvc3Rz
IG9mZikKK1NFTEVDVCAqIEZST00gZW1wMSB0MSBOQVRVUkFMIEpPSU4gTEFURVJBTAorICAo
U0VMRUNUICogRlJPTSBlbXAxIHQyIFRBQkxFU0FNUExFIFNZU1RFTSAodDEuY29kZSkpOwor
CiAtLSBDaGVjayB0aGF0IFBIVnMgZG8gbm90IGltcG9zZSBhbnkgY29uc3RyYWludHMgb24g
cmVtb3Zpbmcgc2VsZiBqb2lucwogZXhwbGFpbiAodmVyYm9zZSwgY29zdHMgb2ZmKQogc2Vs
ZWN0ICogZnJvbSBlbXAxIHQxIGpvaW4gZW1wMSB0MiBvbiB0MS5pZCA9IHQyLmlkIGxlZnQg
am9pbgotLSAKMi4zOS4yCgo=

--------------NVgVX6YVYtV5lQiCfJAZKMWo--