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 1jSS9q-00027E-BE for pgsql-bugs@arkaria.postgresql.org; Sat, 25 Apr 2020 21:16:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jSS9o-0005tn-0u for pgsql-bugs@arkaria.postgresql.org; Sat, 25 Apr 2020 21:16:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jSMJi-0000OS-KN for pgsql-bugs@lists.postgresql.org; Sat, 25 Apr 2020 15:02:06 +0000 Received: from wrigleys.postgresql.org ([2a02:c0:301:0:ffff::34]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jSMJe-0006Kt-Vb for pgsql-bugs@lists.postgresql.org; Sat, 25 Apr 2020 15:02:05 +0000 Content-Type: multipart/mixed; boundary="===============7579788286275751252==" MIME-Version: 1.0 Subject: BUG #16390: Regression between 12.2 and 11.6 on a recursive query : very slow and overestimation of rows To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: postgresql@thefreecat.org Reply-To: postgresql@thefreecat.org, pgsql-bugs@lists.postgresql.org Date: Sat, 25 Apr 2020 15:01:04 +0000 Message-ID: <16390-e9866af103d5a03a@postgresql.org> X-Auto-Response-Suppress: All List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --===============7579788286275751252== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJzaXRlOgoKQnVnIHJl ZmVyZW5jZTogICAgICAxNjM5MApMb2dnZWQgYnk6ICAgICAgICAgIEpDIEJvZ2dpbwpFbWFpbCBh ZGRyZXNzOiAgICAgIHBvc3RncmVzcWxAdGhlZnJlZWNhdC5vcmcKUG9zdGdyZVNRTCB2ZXJzaW9u OiAxMi4yCk9wZXJhdGluZyBzeXN0ZW06ICAgTGludXgvVWJ1bnR1CkRlc2NyaXB0aW9uOiAgICAg ICAgCgpIZWxsbywNCg0KRXZlcnl0aGluZyBzdGFydGVkIG9uIGEgYmlnIHJlY3Vyc2l2ZSBxdWVy eSB3b3JraW5nIGZpbmUgd2l0aCBhIGZpbGxlZApkYXRhYmFzZSAoYWxsIHRhYmxlcyBjb250YWlu IGF0IGxlYXN0IGEgZmV3IHJvd3MpLiBJdCBleGVjdXRlcyBpbiB+MTIwbXMuCkJ1dCB0aGUgc2Ft ZSBxdWVyeSwgb24gYSBuZWFybHkgZW1wdHkgREIgdGFrZXMgfjEwMDAwbXMgb24gMTIuMiBhbmQg c3RpbGwKfjEwMG1zIG9uIDExLjYgKHNhbWUgREIsIHZhY3V1bWVkIGZ1bGwgYW5kIGFuYWx5emVk KS4NCg0KU28gSSBzdGFydGVkIGRpZ2dpbmcgYW5kIHNpbXBsaWZpZWQgdGhlIHF1ZXJ5IHRvIHRy eSB0byB1bmRlcnN0YW5kLg0KDQpzZWxlY3QgdmVyc2lvbigpOw0KUG9zdGdyZVNRTCAxMi4yIChV YnVudHUgMTIuMi0yLnBnZGcxOS4xMCsxKSBvbiB4ODZfNjQtcGMtbGludXgtZ251LCBjb21waWxl ZApieSBnY2MgKFVidW50dSA5LjIuMS05dWJ1bnR1MikgOS4yLjEgMjAxOTEwMDgsIDY0LWJpdA0K DQpDUkVBVEUgREFUQUJBU0UgdGVzdDsNClxjIHRlc3QNCmNyZWF0ZSB0YWJsZSBhIChpZCBpbnQp Ow0KY3JlYXRlIHRhYmxlIGIgKGlkIGludCk7DQpjcmVhdGUgdGFibGUgYyAoaWQgaW50KTsNCmlu c2VydCBpbnRvIGMgdmFsdWVzICgxKTsNCnZhY3V1bSBmdWxsIGFuYWx5emU7DQoNCldJVEggUkVD VVJTSVZFDQp0bXBjdHIxIEFTICgNCiAgICAgICAgU0VMRUNUIDEgQVMgaWQNCgkJRlJPTSBhDQoJ CUpPSU4gYiBiMSBPTiBhLmlkPWIxLmlkDQoJCUpPSU4gYiBiMiBPTiBhLmlkPWIyLmlkICAtLSgx KQ0KCSkNCgkscmVjMSBBUyAoDQoJCVNFTEVDVCBjLmlkLCAxIEFTIGxldmVsDQoJCUZST00gYyAN CiAgICANCgkJVU5JT04NCg0KCQlTRUxFQ1Qgci5pZCwgci5sZXZlbCsxDQoJCUZST00gcmVjMSBy DQoJCUxFRlQgSk9JTiB0bXBjdHIxIGMgT04gci5pZD1jLmlkDQoJCUxFRlQgSk9JTiB0bXBjdHIx IGMyIE9OIHIuaWQ9YzIuaWQgIC0tKDIpDQoJCVdIRVJFIHIubGV2ZWw8MjANCgkJYW5kIChjLmlk IGlzIG5vdCBudWxsIA0KICAgICAgICBvciBjMi5pZCBpcyBub3QgbnVsbCAgLS0oMykNCiAgICAg ICAgKQ0KCSkNCnNlbGVjdCAqIGZyb20gcmVjMTsNCg0KVGltaW5nOiAxOTBtcw0KDQpJdCBpcyAi b25seSIgODRtcyBvbiBQRyAxMS42DQoNCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBRVUVSWSBQTEFOICAKICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgDQotLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NCiBDVEUgU2NhbiBvbiByZWMxICAoY29zdD01NDgwMTI3 LjQ5Li44MDU3NTk4LjExIHJvd3M9MTI4ODczNTMxIHdpZHRoPTgpCihhY3R1YWwgdGltZT0xODcu MzY4Li4xODcuNDA3IHJvd3M9MSBsb29wcz0xKQ0KICAgQ1RFIHRtcGN0cjENCiAgICAgLT4gIE1l cmdlIEpvaW4gIChjb3N0PTUzOS4zNS4uNzI3MC40NSByb3dzPTQxNDUyOCB3aWR0aD00KSAoYWN0 dWFsCnRpbWU9MC4wMDcuLjAuMDA3IHJvd3M9MCBsb29wcz0xKQ0KICAgICAgICAgICBNZXJnZSBD b25kOiAoYjIuaWQgPSBhLmlkKQ0KICAgICAgICAgICAtPiAgU29ydCAgKGNvc3Q9MTc5Ljc4Li4x ODYuMTYgcm93cz0yNTUwIHdpZHRoPTQpIChhY3R1YWwKdGltZT0wLjAwNi4uMC4wMDYgcm93cz0w IGxvb3BzPTEpDQogICAgICAgICAgICAgICAgIFNvcnQgS2V5OiBiMi5pZA0KICAgICAgICAgICAg ICAgICBTb3J0IE1ldGhvZDogcXVpY2tzb3J0ICBNZW1vcnk6IDI1a0INCiAgICAgICAgICAgICAg ICAgLT4gIFNlcSBTY2FuIG9uIGIgYjIgIChjb3N0PTAuMDAuLjM1LjUwIHJvd3M9MjU1MCB3aWR0 aD00KQooYWN0dWFsIHRpbWU9MC4wMDUuLjAuMDA1IHJvd3M9MCBsb29wcz0xKQ0KICAgICAgICAg ICAtPiAgTWF0ZXJpYWxpemUgIChjb3N0PTM1OS41Ny4uOTQxLjI4IHJvd3M9MzI1MTIgd2lkdGg9 OCkgKG5ldmVyCmV4ZWN1dGVkKQ0KICAgICAgICAgICAgICAgICAtPiAgTWVyZ2UgSm9pbiAgKGNv c3Q9MzU5LjU3Li44NjAuMDAgcm93cz0zMjUxMiB3aWR0aD04KQoobmV2ZXIgZXhlY3V0ZWQpDQog ICAgICAgICAgICAgICAgICAgICAgIE1lcmdlIENvbmQ6IChhLmlkID0gYjEuaWQpDQogICAgICAg ICAgICAgICAgICAgICAgIC0+ICBTb3J0ICAoY29zdD0xNzkuNzguLjE4Ni4xNiByb3dzPTI1NTAg d2lkdGg9NCkKKG5ldmVyIGV4ZWN1dGVkKQ0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICBT b3J0IEtleTogYS5pZA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgU2VxIFNjYW4g b24gYSAgKGNvc3Q9MC4wMC4uMzUuNTAgcm93cz0yNTUwCndpZHRoPTQpIChuZXZlciBleGVjdXRl ZCkNCiAgICAgICAgICAgICAgICAgICAgICAgLT4gIFNvcnQgIChjb3N0PTE3OS43OC4uMTg2LjE2 IHJvd3M9MjU1MCB3aWR0aD00KQoobmV2ZXIgZXhlY3V0ZWQpDQogICAgICAgICAgICAgICAgICAg ICAgICAgICAgIFNvcnQgS2V5OiBiMS5pZA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAt PiAgU2VxIFNjYW4gb24gYiBiMSAgKGNvc3Q9MC4wMC4uMzUuNTAKcm93cz0yNTUwIHdpZHRoPTQp IChuZXZlciBleGVjdXRlZCkNCiAgIENURSByZWMxDQogICAgIC0+ICBSZWN1cnNpdmUgVW5pb24g IChjb3N0PTAuMDAuLjU0NzI4NTcuMDUgcm93cz0xMjg4NzM1MzEgd2lkdGg9OCkKKGFjdHVhbCB0 aW1lPTE4Ny4zNjUuLjE4Ny40MDMgcm93cz0xIGxvb3BzPTEpDQogICAgICAgICAgIC0+ICBTZXEg U2NhbiBvbiBjICAoY29zdD0wLjAwLi4xLjAxIHJvd3M9MSB3aWR0aD04KSAoYWN0dWFsCnRpbWU9 MTg3LjM2MC4uMTg3LjM2MyByb3dzPTEgbG9vcHM9MSkNCiAgICAgICAgICAgLT4gIE1lcmdlIExl ZnQgSm9pbiAgKGNvc3Q9NjI5MzcuNjEuLjI4OTUzOC41NCByb3dzPTEyODg3MzUzCndpZHRoPTgp IChhY3R1YWwgdGltZT0wLjAzNS4uMC4wMzUgcm93cz0wIGxvb3BzPTEpDQogICAgICAgICAgICAg ICAgIE1lcmdlIENvbmQ6IChyLmlkID0gYzIuaWQpDQogICAgICAgICAgICAgICAgIEZpbHRlcjog KChjXzEuaWQgSVMgTk9UIE5VTEwpIE9SIChjMi5pZCBJUyBOT1QgTlVMTCkpDQogICAgICAgICAg ICAgICAgIFJvd3MgUmVtb3ZlZCBieSBGaWx0ZXI6IDENCiAgICAgICAgICAgICAgICAgLT4gIFNv cnQgIChjb3N0PTEwMjk5LjI5Li4xMDMxNC44MyByb3dzPTYyMTggd2lkdGg9MTIpCihhY3R1YWwg dGltZT0wLjAzMC4uMC4wMzEgcm93cz0xIGxvb3BzPTEpDQogICAgICAgICAgICAgICAgICAgICAg IFNvcnQgS2V5OiByLmlkDQogICAgICAgICAgICAgICAgICAgICAgIFNvcnQgTWV0aG9kOiBxdWlj a3NvcnQgIE1lbW9yeTogMjVrQg0KICAgICAgICAgICAgICAgICAgICAgICAtPiAgSGFzaCBSaWdo dCBKb2luICAoY29zdD0wLjI2Li45OTA3LjQ4IHJvd3M9NjIxOAp3aWR0aD0xMikgKGFjdHVhbCB0 aW1lPTAuMDIyLi4wLjAyMiByb3dzPTEgbG9vcHM9MSkNCiAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgSGFzaCBDb25kOiAoY18xLmlkID0gci5pZCkNCiAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgLT4gIENURSBTY2FuIG9uIHRtcGN0cjEgY18xIAooY29zdD0wLjAwLi44MjkwLjU2IHJv d3M9NDE0NTI4IHdpZHRoPTQpIChhY3R1YWwgdGltZT0wLjAwNy4uMC4wMDcgcm93cz0wCmxvb3Bz PTEpDQogICAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBIYXNoICAoY29zdD0wLjIyLi4w LjIyIHJvd3M9MyB3aWR0aD04KQooYWN0dWFsIHRpbWU9MC4wMDUuLjAuMDA1IHJvd3M9MSBsb29w cz0xKQ0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBCdWNrZXRzOiAxMDI0ICBC YXRjaGVzOiAxICBNZW1vcnkgVXNhZ2U6CjlrQg0KICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAtPiAgV29ya1RhYmxlIFNjYW4gb24gcmVjMSByIAooY29zdD0wLjAwLi4wLjIyIHJv d3M9MyB3aWR0aD04KSAoYWN0dWFsIHRpbWU9MC4wMDMuLjAuMDAzIHJvd3M9MSBsb29wcz0xKQ0K ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICBGaWx0ZXI6IChsZXZlbCA8 IDIwKQ0KICAgICAgICAgICAgICAgICAtPiAgTWF0ZXJpYWxpemUgIChjb3N0PTUyNjM4LjMyLi41 NDcxMC45NiByb3dzPTQxNDUyOAp3aWR0aD00KSAoYWN0dWFsIHRpbWU9MC4wMDIuLjAuMDAyIHJv d3M9MCBsb29wcz0xKQ0KICAgICAgICAgICAgICAgICAgICAgICAtPiAgU29ydCAgKGNvc3Q9NTI2 MzguMzIuLjUzNjc0LjY0IHJvd3M9NDE0NTI4CndpZHRoPTQpIChhY3R1YWwgdGltZT0wLjAwMS4u MC4wMDEgcm93cz0wIGxvb3BzPTEpDQogICAgICAgICAgICAgICAgICAgICAgICAgICAgIFNvcnQg S2V5OiBjMi5pZA0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICBTb3J0IE1ldGhvZDogcXVp Y2tzb3J0ICBNZW1vcnk6IDI1a0INCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIENU RSBTY2FuIG9uIHRtcGN0cjEgYzIgIChjb3N0PTAuMDAuLjgyOTAuNTYKcm93cz00MTQ1Mjggd2lk dGg9NCkgKGFjdHVhbCB0aW1lPTAuMDAwLi4wLjAwMCByb3dzPTAgbG9vcHM9MSkNCiBQbGFubmlu ZyBUaW1lOiAwLjU3NCBtcw0KIEpJVDoNCiAgIEZ1bmN0aW9uczogMzYNCiAgIE9wdGlvbnM6IElu bGluaW5nIHRydWUsIE9wdGltaXphdGlvbiB0cnVlLCBFeHByZXNzaW9ucyB0cnVlLCBEZWZvcm1p bmcKdHJ1ZQ0KICAgVGltaW5nOiBHZW5lcmF0aW9uIDkuOTk2IG1zLCBJbmxpbmluZyAxMi41MjAg bXMsIE9wdGltaXphdGlvbiAxMTYuNjc5IG1zLApFbWlzc2lvbiA1Ny44OTQgbXMsIFRvdGFsIDE5 Ny4wOTAgbXMNCiBFeGVjdXRpb24gVGltZTogMTk4LjgwMyBtcw0KKDQ1IGxpZ25lcykNCg0KVGVt cHMgOiAyMDAsMzM2IG1zDQoNCg0KWW91IGNhbiBzZWUgdGhhdCBpbiB0aGUgcXVlcnkgSSBoYXZl IG1hcmtlZCBsaW5lcyAoMSkgKDIpIGFuZCAoMykuDQoNCklmIEkgY29tbWVudCBvdXQgbGluZSAo MSksIGV4ZWMgdGltZSBjb21lcyBkb3duIHRvIH4ybXMgYW5kIGlmIEkgY29tbWVudCBvdXQKbGlu ZXMgKDIpIGFuZCAoMykgaXQgZmFsbHMgdG8gNDJtcy4NCg0KSSdtIGNvbXBsZXRlbHkgbG9zdCBv biB0aGlzLiBUYWJsZXMgImEiIGFuZCAiYiIgYXJlIGVtcHR5LCBzbyAidG1wY3RyMSIgaXMKZW1w dHkgYWxzby4gQnV0IHRoZSBlc3RpbWF0ZWQgcm93cywgY29zdHMgYW5kIGV4ZWN1dGlvbiB0aW1l IGFyZSBjcmF6eS4NCkhvdyBjYW4gdGhlIFNlcSBzY2FuIG9uICJjIiAoMSByb3cpIHRha2UgMTg3 bXMgPyBBbmQgaG93IGRvZXMgdGhlIFNlcSBzY2FuCm9uICJhIiBhbmQgImIiIGJvdGggZXZhbHVh dGUgdG8gMjU1MCByb3dzID8NCkFuZCB3aHkgY29tbWVudGluZyBvdXQgbGluZSAoMSkgbWFrZXMg c3VjaCBhIGRpZmZlcmVuY2UgPw0KDQpUaGFua3MgZm9yIHlvdXIgaGVscC4KCg== --===============7579788286275751252==--