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 1jRMDu-0000vy-R7 for pgsql-bugs@arkaria.postgresql.org; Wed, 22 Apr 2020 20:43:58 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jRMDs-00013J-7v for pgsql-bugs@arkaria.postgresql.org; Wed, 22 Apr 2020 20:43:56 +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 1jRLn0-0003pB-Og for pgsql-bugs@lists.postgresql.org; Wed, 22 Apr 2020 20:16:10 +0000 Received: from wrigleys.postgresql.org ([2a02:c0:301:0:ffff::34]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jRLmt-0003po-7l for pgsql-bugs@lists.postgresql.org; Wed, 22 Apr 2020 20:16:10 +0000 Content-Type: multipart/mixed; boundary="===============3791083801437240289==" MIME-Version: 1.0 Subject: BUG #16383: Query planner has (potentially very) wrong predicted row count for some group bys To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: alextkaiser@gmail.com Reply-To: alextkaiser@gmail.com, pgsql-bugs@lists.postgresql.org Date: Wed, 22 Apr 2020 20:15:28 +0000 Message-ID: <16383-a560bff3cc8ed33c@postgresql.org> X-Auto-Response-Suppress: All List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --===============3791083801437240289== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJzaXRlOgoKQnVnIHJl ZmVyZW5jZTogICAgICAxNjM4MwpMb2dnZWQgYnk6ICAgICAgICAgIEFsZXggS2Fpc2VyCkVtYWls IGFkZHJlc3M6ICAgICAgYWxleHRrYWlzZXJAZ21haWwuY29tClBvc3RncmVTUUwgdmVyc2lvbjog MTIuMgpPcGVyYXRpbmcgc3lzdGVtOiAgIG1hY09TIDEwLjE0LjYKRGVzY3JpcHRpb246ICAgICAg ICAKCkhlbGxvLA0KDQpUaGUgcXVlcnkgcGxhbm5lciBpcyBhIGJpZyBjb21wbGljYXRlZCBibGFj ayBib3ggdG8gbWUgc28gSSBkb24ndCBrbm93IGlmCnRoZSBmb2xsb3dpbmcgaXMganVzdCBub3Qg cG9zc2libGUgb3IgaWYgaXQncyBhY3R1YWxseSBhIGJ1ZywgYnV0IGl0IGRpZApjYXVzZSBsYXJn ZSBwcm9ibGVtcyBmb3IgdXMuDQoNClN1cHBvc2UgeW91IGhhdmUgYSBzaW1wbGUgdGFibGUgdG8g dHJhY2sgdXNlciBsb2dpbnMgbGlrZSB0aGlzOg0KDQpjcmVhdGUgdGFibGUgdXNlcl9sb2dpbnMg KA0KICB1c2VyX2lkIGJpZ2ludCwNCiAgbG9naW5fZGF0ZSB0aW1lc3RhbXB0eiwNCiAgaW5mbyB2 YXJjaGFyKDEwKQ0KKTsNCmNyZWF0ZSBpbmRleCBmaW5kX2xvZ2luIG9uIHVzZXJfbG9naW5zICh1 c2VyX2lkLCBsb2dpbl9kYXRlKTsNCg0KQW5kIGxldHMgYXNzdW1lIHlvdSBoYXZlIGF0IGxlYXN0 IDEwMDAgdXNlcnMgYW5kIHRoZXkgaGF2ZSBhbGwgbG9nZ2VkIGluIDUwCnRpbWVzLCBzbyB5b3Ug aGF2ZSA1MCwwMDAgcm93cy4gQW5kIHRoZW4geW91IGlzc3VlIHRoZSBmb2xsb3dpbmcgcXVlcnk6 DQoNCmV4cGxhaW4gYW5hbHl6ZQ0KICBTRUxFQ1QgdXNlcl9pZCwgbWF4KGxvZ2luX2RhdGUpIGFz IG1heF9sb2dpbl9kYXRlIEZST00gdXNlcl9sb2dpbnMgV0hFUkUKdXNlcl9pZCA9IDAgR1JPVVAg QlkgdXNlcl9pZDsNCg0KICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICBRVUVSWSBQTEFODQotLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0NCiBH cm91cEFnZ3JlZ2F0ZSAgKGNvc3Q9MC4yOS4uOTAuNDEgcm93cz00OSB3aWR0aD0xNikgKGFjdHVh bAp0aW1lPTAuMDY4Li4wLjA2OSByb3dzPTEgbG9vcHM9MSkNCiAgIEdyb3VwIEtleTogdXNlcl9p ZA0KICAgLT4gIEluZGV4IE9ubHkgU2NhbiB1c2luZyBmaW5kX2xvZ2luIG9uIHVzZXJfbG9naW5z ICAoY29zdD0wLjI5Li44OS42Nwpyb3dzPTUwIHdpZHRoPTE2KSAoYWN0dWFsIHRpbWU9MC4wMzgu LjAuMDU0IHJvd3M9NTAgbG9vcHM9MSkNCiAgICAgICAgIEluZGV4IENvbmQ6ICh1c2VyX2lkID0g MCkNCiAgICAgICAgIEhlYXAgRmV0Y2hlczogNTANCiBQbGFubmluZyBUaW1lOiAwLjI4MyBtcw0K IEV4ZWN1dGlvbiBUaW1lOiAwLjExOSBtcw0KDQpUaGUgcXVlcnkgcGxhbm5lciBlc3RpbWF0ZXMg dGhhdCB0aGVyZSB3aWxsIGJlIDQ5IHJvd3MgaW4gdGhlIHJlc3VsdCwKaG93ZXZlciBsb29raW5n IGF0IHRoZSBxdWVyeSBpdCBzaG91bGQgYmUgZWFzeSB0byBzZWUgdGhhdCB0aGVyZSB3aWxsIGFs d2F5cwpiZSBhIG1heCBvZiAxIHJvdyBiZWNhdXNlIHdlIGFyZSBncm91cGluZyBieSB1c2VyX2lk IGFuZCBhbHNvIGhhdmUgYW4KZXF1YWxpdHkgbGltaXRpbmcgdGhlIG51bWJlciBvZiB1c2VyX2lk cy4NCg0KVGhlIGFib3ZlIGNhc2Ugc2hvd3MgdGhlIGFjdHVhbCBpc3N1ZSBidXQgZG9lc24ndCBj YXVzZSBhbnkgdGFuZ2libGUKcHJvYmxlbXMgYXMgZm9yIHRoYXQgcXVlcnkgdGhlIHF1ZXJ5IHBs YW5uZXIgd2lsbCBzdGlsbCBhbHdheXMgY2hvb3NlIHRoZQpyaWdodCBwbGFuLCBpdCB3aWxsIGp1 c3QgYmUgb2ZmIGluIHRoZSBlc3RpbWF0ZWQgY29zdC4gIFRoZSByZWFsIHByb2JsZW0gaXMKd2hl biB0aGUgYWJvdmUgZ3JvdXAgYnkgaXMgYW4gaW5uZXIgcXVlcnkgaW4gYSBsYXJnZXIgcXVlcnku ICBGb3IgZXhhbXBsZQpzdXBwb3NlIHdlIHdhbnRlZCB0byBmaW5kIHRoZSAnaW5mbycgY29sdW1u IGZvciB0aGUgbGF0ZXN0IGxvZ2luIGZvciBYCnJhbmRvbSB1c2VycyAoVGhpcyBpcyBhIGNvbW1v biAiZmluZCB0aGUgd2hvbGUgZGF0YSBmb3IgdGhlIHJvdyB3aXRoIHNvbWUKbWF4IHZhbHVlIGlu IGEgY29sdW1uIHBlciBzb21lIGdyb3VwIGlkZW50aWZpZXIiIHNlZQpodHRwczovL3N0YWNrb3Zl cmZsb3cuY29tL3F1ZXN0aW9ucy83NzQ1NjA5L3NxbC1zZWxlY3Qtb25seS1yb3dzLXdpdGgtbWF4 LXZhbHVlLW9uLWEtY29sdW1uKS4KIEEgcXVlcnkgdG8gc29sdmUgdGhpcyBtaWdodCBiZToNCg0K ZXhwbGFpbiBhbmFseXplIFNFTEVDVCB1c2VyX2xvZ2luc18xLnVzZXJfaWQsIHVzZXJfbG9naW5z XzEuaW5mbw0KRlJPTSB1c2VyX2xvZ2lucyB1c2VyX2xvZ2luc18xIGlubmVyIGpvaW4gDQogICAg KFNFTEVDVCB1c2VyX2lkLCBtYXgobG9naW5fZGF0ZSkgYXMgbWF4X2xvZ2luX2RhdGUNCiAgICAg RlJPTSB1c2VyX2xvZ2lucw0KICAgICBXSEVSRSB1c2VyX2lkIGluICgwLCAxLCAyLCAzLCA0KQ0K ICAgICBHUk9VUCBCWSB1c2VyX2lkKSB1c2VyX2xvZ2luc18yDQpPTiB1c2VyX2xvZ2luc18xLnVz ZXJfaWQgPSB1c2VyX2xvZ2luc18yLnVzZXJfaWQNCkFORCB1c2VyX2xvZ2luc18xLmxvZ2luX2Rh dGUgPSB1c2VyX2xvZ2luc18yLm1heF9sb2dpbl9kYXRlOw0KICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIFFVRVJZClBM QU4NCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KIEhhc2ggSm9pbiAgKGNv c3Q9MzYzLjE2Li4xNDg0LjY2IHJvd3M9MSB3aWR0aD0xNikgKGFjdHVhbAp0aW1lPTAuMTQxLi4x MS40MzIgcm93cz01IGxvb3BzPTEpDQogICBIYXNoIENvbmQ6ICgodXNlcl9sb2dpbnNfMS51c2Vy X2lkID0gdXNlcl9sb2dpbnMudXNlcl9pZCkgQU5ECih1c2VyX2xvZ2luc18xLmxvZ2luX2RhdGUg PSAobWF4KHVzZXJfbG9naW5zLmxvZ2luX2RhdGUpKSkpDQogICAtPiAgU2VxIFNjYW4gb24gdXNl cl9sb2dpbnMgdXNlcl9sb2dpbnNfMSAgKGNvc3Q9MC4wMC4uODU5LjAwIHJvd3M9NTAwMDAKd2lk dGg9MjQpIChhY3R1YWwgdGltZT0wLjAxMS4uNC42MDcgcm93cz01MDAwMCBsb29wcz0xKQ0KICAg LT4gIEhhc2ggIChjb3N0PTM1OS44My4uMzU5LjgzIHJvd3M9MjIyIHdpZHRoPTE2KSAoYWN0dWFs CnRpbWU9MC4xMjIuLjAuMTIyIHJvd3M9NSBsb29wcz0xKQ0KICAgICAgICAgQnVja2V0czogMTAy NCAgQmF0Y2hlczogMSAgTWVtb3J5IFVzYWdlOiA5a0INCiAgICAgICAgIC0+ICBHcm91cEFnZ3Jl Z2F0ZSAgKGNvc3Q9MC4yOS4uMzU3LjYxIHJvd3M9MjIyIHdpZHRoPTE2KSAoYWN0dWFsCnRpbWU9 MC4wNDAuLjAuMTE3IHJvd3M9NSBsb29wcz0xKQ0KICAgICAgICAgICAgICAgR3JvdXAgS2V5OiB1 c2VyX2xvZ2lucy51c2VyX2lkDQogICAgICAgICAgICAgICAtPiAgSW5kZXggT25seSBTY2FuIHVz aW5nIGZpbmRfbG9naW4gb24gdXNlcl9sb2dpbnMgCihjb3N0PTAuMjkuLjM1NC4xNCByb3dzPTI1 MCB3aWR0aD0xNikgKGFjdHVhbCB0aW1lPTAuMDE0Li4wLjA4OSByb3dzPTI1MApsb29wcz0xKQ0K ICAgICAgICAgICAgICAgICAgICAgSW5kZXggQ29uZDogKHVzZXJfaWQgPSBBTlkgKCd7MCwxLDIs Myw0fSc6OmJpZ2ludFtdKSkNCiAgICAgICAgICAgICAgICAgICAgIEhlYXAgRmV0Y2hlczogMjUw DQogUGxhbm5pbmcgVGltZTogMC4xNzggbXMNCiBFeGVjdXRpb24gVGltZTogMTEuNDY5IG1zDQoN Ckhvd2V2ZXIgaWYgeW91IHR1cm4gb2ZmIHNlcXVlbnRpYWwgc2NhbnMgYnkgcnVubmluZyAic2V0 IGVuYWJsZV9zZXFzY2FuID0Kb2ZmOyIgeW91IHRoZW4gZ2V0Og0KICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIFFVRVJZClBM QU4NCi0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KIE5lc3RlZCBMb29wICAoY29zdD0w LjU4Li4xNjc4Ljg3IHJvd3M9MSB3aWR0aD0xNikgKGFjdHVhbCB0aW1lPTAuMDczLi4wLjIwNQpy b3dzPTUgbG9vcHM9MSkNCiAgIC0+ICBHcm91cEFnZ3JlZ2F0ZSAgKGNvc3Q9MC4yOS4uMzU3LjYx IHJvd3M9MjIyIHdpZHRoPTE2KSAoYWN0dWFsCnRpbWU9MC4wNjQuLjAuMTgwIHJvd3M9NSBsb29w cz0xKQ0KICAgICAgICAgR3JvdXAgS2V5OiB1c2VyX2xvZ2lucy51c2VyX2lkDQogICAgICAgICAt PiAgSW5kZXggT25seSBTY2FuIHVzaW5nIGZpbmRfbG9naW4gb24gdXNlcl9sb2dpbnMgCihjb3N0 PTAuMjkuLjM1NC4xNCByb3dzPTI1MCB3aWR0aD0xNikgKGFjdHVhbCB0aW1lPTAuMDIzLi4wLjE0 MCByb3dzPTI1MApsb29wcz0xKQ0KICAgICAgICAgICAgICAgSW5kZXggQ29uZDogKHVzZXJfaWQg PSBBTlkgKCd7MCwxLDIsMyw0fSc6OmJpZ2ludFtdKSkNCiAgICAgICAgICAgICAgIEhlYXAgRmV0 Y2hlczogMjUwDQogICAtPiAgSW5kZXggU2NhbiB1c2luZyBmaW5kX2xvZ2luIG9uIHVzZXJfbG9n aW5zIHVzZXJfbG9naW5zXzEgCihjb3N0PTAuMjkuLjUuOTMgcm93cz0xIHdpZHRoPTI0KSAoYWN0 dWFsIHRpbWU9MC4wMDMuLjAuMDAzIHJvd3M9MQpsb29wcz01KQ0KICAgICAgICAgSW5kZXggQ29u ZDogKCh1c2VyX2lkID0gdXNlcl9sb2dpbnMudXNlcl9pZCkgQU5EIChsb2dpbl9kYXRlID0KKG1h eCh1c2VyX2xvZ2lucy5sb2dpbl9kYXRlKSkpKQ0KIFBsYW5uaW5nIFRpbWU6IDAuMjQ4IG1zDQog RXhlY3V0aW9uIFRpbWU6IDAuMjQ0IG1zDQoNClRoZSBzZWNvbmQgcGxhbiBydW5zIGEgbG90IGZh c3Rlci4gIFRoaXMgaXMganVzdCBhIHNtYWxsIGV4YW1wbGUsIGJ1dCB3ZSByYW4KaW50byB0aGlz IHByb2JsZW0gd2l0aCBhIDIwMCBHQiB0YWJsZSB3aGVyZSB3aGVuIHRyeWluZyB0byBsb29rIHVw IGxvZ2lucwpmb3IgfjEwMCB1c2VycyBpdCBzdGFydGVkIHRvIGRvIGZ1bGwgdGFibGUgc2NhbnMg d2hlbiB0aGUgZWZmaWNpZW50IHBsYW4Kd291bGQgaGF2ZSBydW4gaW4gPCAxMCBtcy4NCg0KSGVy ZSBpcyBhIGxpbmsgdG8gYSBzcWwgZmlsZSB0byBzZXQgdXAgdGhlIHNjaGVtYSwgaW5zZXJ0IHRo ZSA1MCwwMDAgcm93cywKYW5kIHRoZW4gcnVuIHRoZSBwcm9ibGVtIHF1ZXJ5OgpodHRwczovL2dp c3QuZ2l0aHViLmNvbS9hdGthaXNlci8xZDI0MGIzMzMxNWJhNzVmYzhjZmJlMjA2ODIyYWQ4ZA0K DQpUaGFua3MsDQpBbGV4Cgo= --===============3791083801437240289==--