Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oAs99-0007q4-8p for pgsql-bugs@arkaria.postgresql.org; Mon, 11 Jul 2022 12:04:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oAs97-0004PC-79 for pgsql-bugs@arkaria.postgresql.org; Mon, 11 Jul 2022 12:04:13 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oAOwO-0003Kn-2X for pgsql-bugs@lists.postgresql.org; Sun, 10 Jul 2022 04:53:08 +0000 Received: from mahout.postgresql.org ([2001:4800:3e1:1::227]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oAOwL-0000Vs-3P for pgsql-bugs@lists.postgresql.org; Sun, 10 Jul 2022 04:53:06 +0000 DKIM-Signature: v=1; a=rsa-sha256; q=dns/txt; c=relaxed/relaxed; d=postgresql.org; s=20171124; h=Message-ID:Date:Reply-To:Cc:From:To:Subject: MIME-Version:Content-Type:Sender:Content-Transfer-Encoding:Content-ID: Content-Description:In-Reply-To:References; bh=7b0jiBAOtY3tomLCaUmn7fFp8WKZdvhALzTxeR3XnqU=; b=ddTnv1Dehni3PxpS7nf6CUDUS6 kBdEsBmbgHIw58uGE1IBiswPStC68pLCOTStNOXYKtTY41FPkkgFfu8S4G0++h8Yz71YHqZzYTHpO sCwjdfaYtQoskhBAMuI6+MygTwA4R+7txr2NXyTNQ9wQIBOH9B+gRxfQGotXhVmhSLuyspUXKlP9N lyKiYkdymBukkG70y7VrONBdoKyioCWb/H8wspsylQGJxw7eOfkDwEfzDmGSUdUqxTbxs1BgX847r kyjQW/YNdRwqqrkgHLZobjCKodwqeCE/yJy6KyLEoXMGpBj1cu0YKuUjAxAdAeEIgVmFuSpOVV84/ DKtafAIA==; Received: from wrigleys.postgresql.org ([2a02:c0:301:0:ffff::34]) by mahout.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oAOwK-0000QE-Dm for pgsql-bugs@lists.postgresql.org; Sun, 10 Jul 2022 04:53:04 +0000 Received: from localhost ([127.0.0.1] helo=wrigleys.postgresql.org) by wrigleys.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1oAOwH-007o0H-VG for pgsql-bugs@lists.postgresql.org; Sun, 10 Jul 2022 04:53:02 +0000 Content-Type: multipart/mixed; boundary="===============4159322930923127412==" MIME-Version: 1.0 Subject: BUG #17544: Join order for INNER JOIN ... USING with GROUP BY on join column affects selected query plan To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: _@ericsheng.com Reply-To: _@ericsheng.com, pgsql-bugs@lists.postgresql.org Date: Sun, 10 Jul 2022 04:52:26 +0000 Message-ID: <17544-ebd06b00b8836a04@postgresql.org> X-Auto-Response-Suppress: All Auto-Submitted: auto-generated List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --===============4159322930923127412== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJzaXRlOgoKQnVnIHJl ZmVyZW5jZTogICAgICAxNzU0NApMb2dnZWQgYnk6ICAgICAgICAgIEVyaWMgU2hlbmcKRW1haWwg YWRkcmVzczogICAgICBfQGVyaWNzaGVuZy5jb20KUG9zdGdyZVNRTCB2ZXJzaW9uOiAxNC40Ck9w ZXJhdGluZyBzeXN0ZW06ICAgVWJ1bnR1IDIwLjA0CkRlc2NyaXB0aW9uOiAgICAgICAgCgpJdCBz ZWVtcyBsaWtlIHRoZSBqb2luIG9yZGVyIG9mIGEgc2ltcGxlIElOTkVSIEpPSU4gLi4uIFVTSU5H KC4uLikgYWZmZWN0cwpxdWVyeSBwbGFuIHNlbGVjdGlvbiBldmVuIHdoZW4gb25seSBqb2luaW5n IHR3byB0YWJsZXMsIHdoZW4gdXNlZCB3aXRoIEdST1VQCkJZIG9uIHRoZSBqb2luIGNvbHVtbi4N Cg0KU2NoZW1hOiBncm91cHMgd2l0aCBwcmlvcml0eSwgY29udGFpbmluZyB6ZXJvIG9yIG1vcmUg dGFza3Mgd2l0aCBhIGJvb2xlYW4NCg0KICAgIENSRUFURSBUQUJMRSBncm91cHMoZ3JvdXBfaWQg SU5UIFBSSU1BUlkgS0VZIE5PVCBOVUxMLCBwcmlvcml0eSBJTlQpOw0KICAgIENSRUFURSBUQUJM RSB0YXNrcyh0YXNrX2lkIElOVCBQUklNQVJZIEtFWSBOT1QgTlVMTCwgZ3JvdXBfaWQgSU5UIE5P VApOVUxMIFJFRkVSRU5DRVMgZ3JvdXBzKGdyb3VwX2lkKSwgZmluaXNoZWQgQk9PTEVBTiBOT1Qg TlVMTCk7DQogICAgQ1JFQVRFIElOREVYIGdyb3Vwc19wcmlvcml0eV9pbmRleCBPTiBncm91cHMo cHJpb3JpdHksIGdyb3VwX2lkKTsNCiAgICBDUkVBVEUgSU5ERVggdGFza3NfZ3JvdXBfaWRfaW5k ZXggT04gdGFza3MoZ3JvdXBfaWQpOw0KDQpEYXRhOiAxMDAwIGdyb3VwcyAocmFuZG9tIHByaW9y aXR5KSB3aXRoIDEwMDAgdGFza3MgcGVyIGdyb3VwDQoNCiAgICBETyAkJA0KICAgICAgQkVHSU4N CiAgICAgICAgRk9SIGkgSU4gMCAuLiA5OTkgTE9PUA0KICAgICAgICAgIElOU0VSVCBJTlRPIGdy b3Vwcyhncm91cF9pZCwgcHJpb3JpdHkpIFZBTFVFUyhpLCBGTE9PUihSQU5ET00oKSAqCjIwMDAw MDAwMDApOjpJTlQpOw0KICAgICAgICAgIEZPUiBqIGluIDAgLi4gOTk5IExPT1ANCiAgICAgICAg ICAgIElOU0VSVCBJTlRPIHRhc2tzKHRhc2tfaWQsIGdyb3VwX2lkLCBmaW5pc2hlZCkgVkFMVUVT ICgxMDAwICogaQorIGosIGksIFJBTkRPTSgpICogMTAwIDwgOTkpOw0KICAgICAgICAgIEVORCBM T09QOw0KICAgICAgICBFTkQgTE9PUDsNCiAgICAgIEVORDsNCiAgICAkJDsNCg0KUXVlcmllczog ZmV0Y2hpbmcgdGhlIGxvd2VzdCBwcmlvcml0eSBncm91cHMgYW5kIHdoZXRoZXIgb3Igbm90IGFs bCB0YXNrcyBpbgp0aGVtIGhhdmUgZmluaXNoZWQgKHNhbWUgcXVlcnkgdHdpY2UsIGp1c3QgZGlm ZmVyZW50IGpvaW4gb3JkZXIpDQoNCiAgICBFWFBMQUlOIChBTkFMWVpFLCBUSU1JTkcpIFNFTEVD VCBncm91cF9pZCwgQk9PTF9BTkQoZmluaXNoZWQpIEZST00KZ3JvdXBzIElOTkVSIEpPSU4gdGFz a3MgVVNJTkcoZ3JvdXBfaWQpIEdST1VQIEJZIGdyb3VwX2lkLCBwcmlvcml0eSBPUkRFUiBCWQpw cmlvcml0eSBBU0MgTElNSVQgMTA7DQoNCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgClFVRVJZIFBMQU4NCiAg IAotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KICAgICBMaW1pdCAg KGNvc3Q9MTU3NzEuOTkuLjE1NzcyLjAyIHJvd3M9MTAgd2lkdGg9OSkgKGFjdHVhbAp0aW1lPTI5 My4wNTQuLjMwMi45MTkgcm93cz0xMCBsb29wcz0xKQ0KICAgICAgIC0+ICBTb3J0ICAoY29zdD0x NTc3MS45OS4uMTU3NzcuNjQgcm93cz0yMjYwIHdpZHRoPTkpIChhY3R1YWwKdGltZT0yOTMuMDUx Li4zMDIuOTE0IHJvd3M9MTAgbG9vcHM9MSkNCiAgICAgICAgICAgICBTb3J0IEtleTogZ3JvdXBz LnByaW9yaXR5DQogICAgICAgICAgICAgU29ydCBNZXRob2Q6IHRvcC1OIGhlYXBzb3J0ICBNZW1v cnk6IDI1a0INCiAgICAgICAgICAgICAtPiAgRmluYWxpemUgSGFzaEFnZ3JlZ2F0ZSAgKGNvc3Q9 MTU3MDAuNTYuLjE1NzIzLjE2IHJvd3M9MjI2MAp3aWR0aD05KSAoYWN0dWFsIHRpbWU9MjkyLjc0 NC4uMzAyLjc3NyByb3dzPTEwMDAgbG9vcHM9MSkNCiAgICAgICAgICAgICAgICAgICBHcm91cCBL ZXk6IGdyb3Vwcy5ncm91cF9pZA0KICAgICAgICAgICAgICAgICAgIEJhdGNoZXM6IDEgIE1lbW9y eSBVc2FnZTogMjQxa0INCiAgICAgICAgICAgICAgICAgICAtPiAgR2F0aGVyICAoY29zdD0xNTIw My4zNi4uMTU2NzcuOTYgcm93cz00NTIwIHdpZHRoPTkpCihhY3R1YWwgdGltZT0yOTEuMzA4Li4z MDEuNzI1IHJvd3M9MjgzNiBsb29wcz0xKQ0KICAgICAgICAgICAgICAgICAgICAgICAgIFdvcmtl cnMgUGxhbm5lZDogMg0KICAgICAgICAgICAgICAgICAgICAgICAgIFdvcmtlcnMgTGF1bmNoZWQ6 IDINCiAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgUGFydGlhbCBIYXNoQWdncmVnYXRlICAo Y29zdD0xNDIwMy4zNi4uMTQyMjUuOTYKcm93cz0yMjYwIHdpZHRoPTkpIChhY3R1YWwgdGltZT0y NjMuMzQ0Li4yNjMuNTYwIHJvd3M9OTQ1IGxvb3BzPTMpDQogICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgR3JvdXAgS2V5OiBncm91cHMuZ3JvdXBfaWQNCiAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICBCYXRjaGVzOiAxICBNZW1vcnkgVXNhZ2U6IDI0MWtCDQogICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgV29ya2VyIDA6ICBCYXRjaGVzOiAxICBNZW1vcnkgVXNhZ2U6IDI0 MWtCDQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgV29ya2VyIDE6ICBCYXRjaGVzOiAx ICBNZW1vcnkgVXNhZ2U6IDI0MWtCDQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4g IEhhc2ggSm9pbiAgKGNvc3Q9NjAuODUuLjExNzI1LjYxCnJvd3M9NDk1NTUwIHdpZHRoPTkpIChh Y3R1YWwgdGltZT0wLjcxOS4uMTgyLjk3NyByb3dzPTMzMzMzMyBsb29wcz0zKQ0KICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgIEhhc2ggQ29uZDogKHRhc2tzLmdyb3VwX2lkID0K Z3JvdXBzLmdyb3VwX2lkKQ0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ ICBQYXJhbGxlbCBTZXEgU2NhbiBvbiB0YXNrcyAKKGNvc3Q9MC4wMC4uMTAzNjEuNTAgcm93cz00 OTU1NTAgd2lkdGg9NSkgKGFjdHVhbCB0aW1lPTAuMDQ0Li42MS4zNjYKcm93cz0zMzMzMzMgbG9v cHM9MykNCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgSGFzaCAgKGNv c3Q9MzIuNjAuLjMyLjYwIHJvd3M9MjI2MAp3aWR0aD04KSAoYWN0dWFsIHRpbWU9MC42MjcuLjAu NjI3IHJvd3M9MTAwMCBsb29wcz0zKQ0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgIEJ1Y2tldHM6IDQwOTYgIEJhdGNoZXM6IDEgIE1lbW9yeQpVc2FnZTogNzJrQg0K ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBv biBncm91cHMgCihjb3N0PTAuMDAuLjMyLjYwIHJvd3M9MjI2MCB3aWR0aD04KSAoYWN0dWFsIHRp bWU9MC4wNDIuLjAuMjk4IHJvd3M9MTAwMApsb29wcz0zKQ0KICAgICBQbGFubmluZyBUaW1lOiAw LjMxMCBtcw0KICAgICBFeGVjdXRpb24gVGltZTogMzAzLjI5NCBtcw0KDQogICAgRVhQTEFJTiAo QU5BTFlaRSwgVElNSU5HKSBTRUxFQ1QgZ3JvdXBfaWQsIEJPT0xfQU5EKGZpbmlzaGVkKSBGUk9N IHRhc2tzCklOTkVSIEpPSU4gZ3JvdXBzIFVTSU5HKGdyb3VwX2lkKSBHUk9VUCBCWSBncm91cF9p ZCwgcHJpb3JpdHkgT1JERVIgQlkKcHJpb3JpdHkgQVNDIExJTUlUIDEwOw0KDQogICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAKUVVFUlkgUExBTg0KICAgCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0NCiAgICAgTGltaXQgIChjb3N0PTAuNzAuLjEuMzUgcm93cz0xMCB3aWR0aD05KSAo YWN0dWFsIHRpbWU9MS4xNDYuLjEwLjYwNgpyb3dzPTEwIGxvb3BzPTEpDQogICAgICAgLT4gIEdy b3VwQWdncmVnYXRlICAoY29zdD0wLjcwLi42NDkzMi4yNyByb3dzPTEwMDAwMDAgd2lkdGg9OSkK KGFjdHVhbCB0aW1lPTEuMTQ0Li4xMC41OTkgcm93cz0xMCBsb29wcz0xKQ0KICAgICAgICAgICAg IEdyb3VwIEtleTogZ3JvdXBzLnByaW9yaXR5LCB0YXNrcy5ncm91cF9pZA0KICAgICAgICAgICAg IC0+ICBOZXN0ZWQgTG9vcCAgKGNvc3Q9MC43MC4uNDc0MzIuMjcgcm93cz0xMDAwMDAwIHdpZHRo PTkpCihhY3R1YWwgdGltZT0wLjA1NS4uOC4wMjggcm93cz0xMDAwMSBsb29wcz0xKQ0KICAgICAg ICAgICAgICAgICAgIC0+ICBJbmRleCBPbmx5IFNjYW4gdXNpbmcgZ3JvdXBzX3ByaW9yaXR5X2lu ZGV4IG9uIGdyb3VwcwogKGNvc3Q9MC4yOC4uNDMuMjcgcm93cz0xMDAwIHdpZHRoPTgpIChhY3R1 YWwgdGltZT0wLjAyOC4uMC4wMzYgcm93cz0xMQpsb29wcz0xKQ0KICAgICAgICAgICAgICAgICAg ICAgICAgIEhlYXAgRmV0Y2hlczogMA0KICAgICAgICAgICAgICAgICAgIC0+ICBJbmRleCBTY2Fu IHVzaW5nIHRhc2tzX2dyb3VwX2lkX2luZGV4IG9uIHRhc2tzIAooY29zdD0wLjQyLi4zNy4zOSBy b3dzPTEwMDAgd2lkdGg9NSkgKGFjdHVhbCB0aW1lPTAuMDE3Li4wLjQzMyByb3dzPTkwOQpsb29w cz0xMSkNCiAgICAgICAgICAgICAgICAgICAgICAgICBJbmRleCBDb25kOiAoZ3JvdXBfaWQgPSBn cm91cHMuZ3JvdXBfaWQpDQogICAgIFBsYW5uaW5nIFRpbWU6IDAuODc4IG1zDQogICAgIEV4ZWN1 dGlvbiBUaW1lOiAxMC42NzIgbXMNCg0KQWxsIGpvaW4gb3JkZXJzIGZvciBpbm5lciBqb2lucyBn aXZlIHNlbWFudGljYWxseSBlcXVpdmFsZW50IHJlc3VsdHMsIGFuZApkb2N1bWVudGF0aW9uIGF0 IGh0dHBzOi8vd3d3LnBvc3RncmVzcWwub3JnL2RvY3MvY3VycmVudC9leHBsaWNpdC1qb2lucy5o dG1sCmluZGljYXRlcyB0aGF0IHRoZSBwbGFubmVyIHNob3VsZCBleHBsb3JlIGFsbCBqb2luIG9y ZGVycyB1bmxlc3MgdGhlcmUgYXJlCnRvbyBtYW55IHRhYmxlcywgc28gSSB3b3VsZCBoYXZlIGV4 cGVjdGVkIHRoZSBqb2luIG9yZGVyIGhlcmUgdG8gYmUKaW1tYXRlcmlhbCB0byB0aGUgcXVlcnkg cGxhbiBjaG9zZW4uDQoNCkRpZ2dpbmcgYSBiaXQgZGVlcGVyLCB0aGlzIGFwcGVhcnMgdG8gYmUg ZHVlIHRvIHRoZSBVU0lORyBjbGF1c2UgY2F1c2luZyB0aGUKR1JPVVAgQlkgZ3JvdXBfaWQgdG8g YmUgcmV3cml0dGVuIHRvIGBncm91cHMuZ3JvdXBfaWRgIGluIHRoZSBmaXJzdCBxdWVyeQphbmQg YHRhc2tzLmdyb3VwX2lkYCBpbiB0aGUgc2Vjb25kIHF1ZXJ5LCBhbmQgdGhlIGZvcm1lciByZXN1 bHRpbmcgaW4gdGhlCnNpbXBsZXIgcGxhbiBub3QgYmVpbmcgdXNlZC4gVGhlIHNhbWUgZGlmZmVy ZW5jZSBjYW4gYmUgb2JzZXJ2ZWQgcnVubmluZzoNCg0KICAgIEVYUExBSU4gKEFOQUxZWkUsIFRJ TUlORykgU0VMRUNUIGdyb3Vwcy5ncm91cF9pZCwgQk9PTF9BTkQoZmluaXNoZWQpCkZST00gdGFz a3MgSU5ORVIgSk9JTiBncm91cHMgT04gdGFza3MuZ3JvdXBfaWQgPSBncm91cHMuZ3JvdXBfaWQg R1JPVVAgQlkKZ3JvdXBzLmdyb3VwX2lkLCBwcmlvcml0eSBPUkRFUiBCWSBwcmlvcml0eSBBU0Mg TElNSVQgMTA7DQogICAgKHRoZSBzbG93IHBsYW4pDQoNCiAgICBFWFBMQUlOIChBTkFMWVpFLCBU SU1JTkcpIFNFTEVDVCB0YXNrcy5ncm91cF9pZCwgQk9PTF9BTkQoZmluaXNoZWQpIEZST00KdGFz a3MgSU5ORVIgSk9JTiBncm91cHMgT04gdGFza3MuZ3JvdXBfaWQgPSBncm91cHMuZ3JvdXBfaWQg R1JPVVAgQlkKdGFza3MuZ3JvdXBfaWQsIHByaW9yaXR5IE9SREVSIEJZIHByaW9yaXR5IEFTQyBM SU1JVCAxMDsNCiAgICAodGhlIGZhc3QgcGxhbikKCg== --===============4159322930923127412==--