Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iu96c-0006Lr-OK for pgsql-bugs@arkaria.postgresql.org; Wed, 22 Jan 2020 06:03:10 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iu96a-0006aq-N9 for pgsql-bugs@arkaria.postgresql.org; Wed, 22 Jan 2020 06:03:08 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iu96a-0006aj-Eg for pgsql-bugs@lists.postgresql.org; Wed, 22 Jan 2020 06:03:08 +0000 Received: from wrigleys.postgresql.org ([2620:122:b000:7::246]) by magus.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1iu96X-00040u-Aq for pgsql-bugs@lists.postgresql.org; Wed, 22 Jan 2020 06:03:08 +0000 Content-Type: multipart/mixed; boundary="===============6748010674781854827==" MIME-Version: 1.0 Subject: BUG #16223: Performance regression between 11.6 and 12.1 in an SQL query with a recursive CTE based on function To: pgsql-bugs@lists.postgresql.org From: PG Bug reporting form Cc: schwaderer@ivocotec.de Reply-To: schwaderer@ivocotec.de, pgsql-bugs@lists.postgresql.org Date: Wed, 22 Jan 2020 06:02:40 +0000 Message-ID: <16223-908afaa0eaf46ea2@postgresql.org> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --===============6748010674781854827== Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: base64 VGhlIGZvbGxvd2luZyBidWcgaGFzIGJlZW4gbG9nZ2VkIG9uIHRoZSB3ZWJzaXRlOgoKQnVnIHJl ZmVyZW5jZTogICAgICAxNjIyMwpMb2dnZWQgYnk6ICAgICAgICAgIENocmlzdGlhblMKRW1haWwg YWRkcmVzczogICAgICBzY2h3YWRlcmVyQGl2b2NvdGVjLmRlClBvc3RncmVTUUwgdmVyc2lvbjog MTIuMQpPcGVyYXRpbmcgc3lzdGVtOiAgIFVidW50dSAxOC4wNApEZXNjcmlwdGlvbjogICAgICAg IAoKSSBoYXZlIGEgcXVlcnkgdGhhdCBydW5zIHNpZ25pZmljYW50bHkgc2xvd2VyIGluIFBvc3Rn cmVzIDEyLjEgdGhhbiBpdCBkb2VzCmluIFBvc3RncmVzIDExLjYuDQoNCihJIGhhdmUgYXNrZWQg b24gZGJhLnN0YWNrZXhjaGFuZ2UKaHR0cHM6Ly9kYmEuc3RhY2tleGNoYW5nZS5jb20vcXVlc3Rp b25zLzI1Nzc1OS9yZWN1cnNpdmUtY3RlLWJhc2VkLW9uLWZ1bmN0aW9uLXZhbHVlcy1zaWduaWZp Y2FudGx5LXNsb3dlci1vbi1wb3N0Z3Jlcy0xMi10aGFuCi0gaG9waW5nIHRoYXQgc29tZW9uZSB3 b3VsZCBqdW1wIGluIGFuZCB0ZWxsIG1lIHdoeSB0aGlzIGlzIG15IGZhdWx0IGFuZCB3aHkKaXQg aGFzIG5vdGhpbmcgdG8gZG8gd2l0aCBQb3N0Z3JlU1FMIGl0c2VsZi4gSG93ZXZlciwgc2luY2Ug dGhlcmUgaXMgc3RpbGwKbm8gcmVhY3Rpb24gYWZ0ZXIgdHdvIGRheXMsIEkgY2FuIGJlIHNvbWV3 aGF0IHN1cmUgdGhhdCBpdCdzIG5vdCBhIHZlcnkKb2J2aW91cyBtaXN0YWtlIG9mIG1pbmUgYW5k IG1heWJlIHNvbWV0aGluZyB5b3UgbWlnaHQgd2FudCB0bwppbnZlc3RpZ2F0ZS4pDQoNCg0KRmly c3QsIHdlIGNyZWF0ZSB0aGlzIHNpbXBsZSBmdW5jdGlvbg0KDQoNCkNSRUFURSBPUiBSRVBMQUNF IEZVTkNUSU9OIHB1YmxpYy5teV90ZXN0X2Z1bmN0aW9uKCkNCiBSRVRVUk5TIFNFVE9GIHJlY29y ZA0KIExBTkdVQUdFIHNxbA0KIElNTVVUQUJMRSBTRUNVUklUWSBERUZJTkVSDQpBUyAkZnVuY3Rp b24kIA0KDQpTRUxFQ1QgDQogICAgICAgIDE6OmludGVnZXIgQVMgaWQsDQogICAgICAgICcyMDE5 LTExLTIwJzo6ZGF0ZSBBUyAic3RhcnREYXRlIiwNCiAgICAgICAgJzIwMjAtMDEtMDEnOjpkYXRl IEFTICJlbmREYXRlIg0KDQokZnVuY3Rpb24kOw0KDQoNClRoZW4gZm9yIHRoZSBhY3R1YWwgcXVl cnkNCg0KDQpXSVRIICAic29tZVBlcmlvZHMiIEFTICAoDQogICAgICBTRUxFQ1QgKiBGUk9NIG15 X3Rlc3RfZnVuY3Rpb24oKSBBUyANCiAgICAgIGYoaWQgaW50ZWdlciwgInN0YXJ0RGF0ZSIgZGF0 ZSwgImVuZERhdGUiIGRhdGUpDQopLA0KDQoibWF4UmVjdXJzaXZlRW5kRGF0ZSIgQVMgKA0KDQpT RUxFQ1QgInN0YXJ0RGF0ZSIsICJlbmREYXRlIiwgaWQsIA0KKCANCiAgV0lUSCBSRUNVUlNJVkUg cHJlcCgic3RhcnREYXRlUGFyYW0iLCAiZW5kRGF0ZVBhcmFtIikgQVMgKA0KDQogIFNFTEVDVCAi c3RhcnREYXRlIiwiZW5kRGF0ZSIgRlJPTSAic29tZVBlcmlvZHMiIFdIRVJFIGlkID0gb2QuaWQN CiAgVU5JT04NCiAgU0VMRUNUICJzdGFydERhdGUiLCJlbmREYXRlIiBGUk9NICJzb21lUGVyaW9k cyIsIHByZXANCiAgV0hFUkUNCiAgICAic3RhcnREYXRlIiA8PSAoImVuZERhdGVQYXJhbSIgKyAn MSBkYXknOjppbnRlcnZhbCApIEFORCAoImVuZERhdGVQYXJhbSIKKyAnMSBkYXknOjppbnRlcnZh bCApIDw9ICJlbmREYXRlIg0KICApDQogIFNFTEVDVCBtYXgoImVuZERhdGVQYXJhbSIpIEZST00g cHJlcA0KKSBBUyAiZW5kRGF0ZU5ldyINCg0KRlJPTSAic29tZVBlcmlvZHMiIEFTIG9kDQoNCikN Cg0KU0VMRUNUICogRlJPTSAibWF4UmVjdXJzaXZlRW5kRGF0ZSI7DQoNCg0KV2hhdCB0aGlzIGFj dHVhbGx5IGRvZXMgaXQgbm90IHNvIGltcG9ydGFudCBoZXJlLCBJIGd1ZXNzLiBUaGUgaW1wb3J0 YW50CnBvaW50IGlzOiBJdCBydW5zIHZlcnkgZmFzdCBvbiBQb3N0Z3JlcyAxMS42IChsaWtlIGNh IDRtcykgYW5kIG11Y2ggc2xvd2VyCm9uIFBvc3RncmVTUUwgMTIuMSAoY2EgMTUwbXMpLiBUaGUg b3V0cHV0IG9mIEVYUExBSU4gQU5BTFlaRSBkaWQgbm90IGdpdmUgbWUKZnVydGhlciBoaW50cy4N CkEgY3J1Y2lhbCBwb2ludCBtaWdodCBvciBtaWdodCBiZSBub3QsIHRoYXQgYXJlIG11bHRpcGxl IENURXMgaW52b2x2ZWQsCmluY2x1ZGluZyBhIFJFQ1VSU0lWRSBvbmUuIEhvd2V2ZXIsIHRoYXQn cyBzcGVjdWxhdGlvbi4NCg0KV2hhdCBJIHRyaWVkIG91dDoNCi0gSSBkaWQgdHJ5IHdpdGhvdXQg bXlfdGVzdF9mdW5jdGlvbiwgaS5lLiBwdXR0aW5nIHRoZSB2YWx1ZXMgZGlyZWN0bHkgaW50bwp0 aGUgZmlyc3QgQ1RFIHdpdGhvdXQgdXNpbmcgYSBmdW5jdGlvbi4gVGhpcyB3YXksIHRoZXJlIHdh cyBubyBwcm9ibGVtIGF0CmFsbC4gTGlrZSB0aGlzLCBpdCBydW5zIGVxdWFsbHkgZmFzdCBib3Ro IG9uIDEyLjEgYW5kIG9uIDExLjYuDQotIE9uIFBvc3RncmVzIDEyLCBJIHBsYXllZCBhcm91bmQg d2l0aCBNQVRFUklBTElaRUQsIGJ1dCBjb3VsZCBub3Qgc2VlIGFueQplZmZlY3QuIFRoZSBxdWVy eSBzdGlsbCBydW5zIGFzIHNsb3cgYXMgYmVmb3JlLg0KDQpOb3RlIG9uIHJlcHJvZHVjaWJpbGl0 eToNCkkgd2FzIGFibGUgdG8gcmVwcm9kdWNlIHRoZSBwaGVub21lbm9uIG9uIHZhcmlvdXMgc3lz dGVtczogb24gbXVsdGlwbGUgVk1zCmluIFZpcnR1YWxCb3g7IHZpYSBEb2NrZXIgb24gdHdvIGRp ZmZlcmVudCBwaHlzaWNhbCBtYWNoaW5lcy4gKFNlZSBiZWxvdyBmb3IKRG9ja2VyIGNvbW1hbmRz LikgSG93ZXZlciwgc3RyYW5nZSBlbm91Z2gsIEkgY2Fubm90IHJlcHJvZHVjZSBpdCBvbgpodHRw czovL3d3dy5kYi1maWRkbGUuY29tLyAobm8gZGlmZmVyZW5jZSB0byBiZSBzZWVuIHRoZXJlLCBi b3RoIGFyZQpmYXN0KS4NCg0KDQpEb2NrZXIgY29tbWFuZHM6DQoNCiMgRmlyc3QsIHB1bGwgaW1h Z2VzIG9mIGJvdGggdmVyc2lvbnMNCg0KZG9ja2VyIHB1bGwgcG9zdGdyZXM6MTIuMQ0KZG9ja2Vy IHB1bGwgcG9zdGdyZXM6MTEuNg0KDQojIE5vdywgcnVuIFBvc3RncmVzIDEyDQoNCmRvY2tlciBy dW4gLWQgLS1uYW1lIG15X3Bvc3RncmVzXzEyX2NvbnRhaW5lciBwb3N0Z3JlczoxMi4xDQoNCiMg Tm93LCBleGVjdXRlIHRoZSBxdWVyeQ0KDQpkb2NrZXIgZXhlYyBteV9wb3N0Z3Jlc18xMl9jb250 YWluZXIgcHNxbCAtVSBwb3N0Z3JlcyAtYyAiDQoNCkNSRUFURSBPUiBSRVBMQUNFIEZVTkNUSU9O IHB1YmxpYy5teV90ZXN0X2Z1bmN0aW9uKCkNCiBSRVRVUk5TIFNFVE9GIHJlY29yZA0KIExBTkdV QUdFIHNxbA0KIElNTVVUQUJMRSBTRUNVUklUWSBERUZJTkVSDQpBUyBcJGZ1bmN0aW9uXCQgDQoN ClNFTEVDVCANCiAgICAgICAgMTo6aW50ZWdlciBBUyBpZCwNCiAgICAgICAgJzIwMTktMTEtMjAn OjpkYXRlIEFTIFwic3RhcnREYXRlXCIsDQogICAgICAgICcyMDIwLTAxLTAxJzo6ZGF0ZSBBUyBc ImVuZERhdGVcIg0KDQpcJGZ1bmN0aW9uXCQ7DQoNCkVYUExBSU4gQU5BTFlaRSBXSVRIICBcInNv bWVQZXJpb2RzXCIgQVMgICgNCiAgICAgIFNFTEVDVCAqIEZST00gbXlfdGVzdF9mdW5jdGlvbigp IEFTIA0KICAgICAgZihpZCBpbnRlZ2VyLCBcInN0YXJ0RGF0ZVwiIGRhdGUsIFwiZW5kRGF0ZVwi IGRhdGUpDQopLA0KDQpcIm1heFJlY3Vyc2l2ZUVuZERhdGVcIiBBUyAoDQoNClNFTEVDVCBcInN0 YXJ0RGF0ZVwiLCBcImVuZERhdGVcIiwgaWQsIA0KKCANCiAgV0lUSCBSRUNVUlNJVkUgcHJlcChc InN0YXJ0RGF0ZVBhcmFtXCIsIFwiZW5kRGF0ZVBhcmFtXCIpIEFTICgNCg0KICBTRUxFQ1QgXCJz dGFydERhdGVcIixcImVuZERhdGVcIiBGUk9NIFwic29tZVBlcmlvZHNcIiBXSEVSRSBpZCA9IG9k LmlkDQogIFVOSU9ODQogIFNFTEVDVCBcInN0YXJ0RGF0ZVwiLFwiZW5kRGF0ZVwiIEZST00gXCJz b21lUGVyaW9kc1wiLCBwcmVwDQogIFdIRVJFDQogICAgXCJzdGFydERhdGVcIiA8PSAoXCJlbmRE YXRlUGFyYW1cIiArICcxIGRheSc6OmludGVydmFsICkgQU5ECihcImVuZERhdGVQYXJhbVwiICsg JzEgZGF5Jzo6aW50ZXJ2YWwgKSA8PSBcImVuZERhdGVcIg0KICApDQogIFNFTEVDVCBtYXgoXCJl bmREYXRlUGFyYW1cIikgRlJPTSBwcmVwDQopIEFTIFwiZW5kRGF0ZU5ld1wiDQoNCkZST00gXCJz b21lUGVyaW9kc1wiIEFTIG9kDQoNCikNCg0KU0VMRUNUICogRlJPTSBcIm1heFJlY3Vyc2l2ZUVu ZERhdGVcIjsNCiINCg0KIyBTdG9wIHRoZSBQb3N0Z3JlcyAxMiBjb250YWluZXINCg0KZG9ja2Vy IHN0b3AgbXlfcG9zdGdyZXNfMTJfY29udGFpbmVyDQoNCiMgU3RhcnQgUG9zdGdyZXMgMTEgZm9y IGNvbXBhcmlzb24NCg0KZG9ja2VyIHJ1biAtZCAtLW5hbWUgbXlfcG9zdGdyZXNfMTFfY29udGFp bmVyIHBvc3RncmVzOjExLjYNCg0KIyBFeGVjdXRlIHRoZSBxdWVyeSBpbiBQb3N0Z3JlcyAxMQ0K DQpkb2NrZXIgZXhlYyBteV9wb3N0Z3Jlc18xMV9jb250YWluZXIgcHNxbCAtVSBwb3N0Z3JlcyAt YyAiDQoNCkNSRUFURSBPUiBSRVBMQUNFIEZVTkNUSU9OIHB1YmxpYy5teV90ZXN0X2Z1bmN0aW9u KCkNCiBSRVRVUk5TIFNFVE9GIHJlY29yZA0KIExBTkdVQUdFIHNxbA0KIElNTVVUQUJMRSBTRUNV UklUWSBERUZJTkVSDQpBUyBcJGZ1bmN0aW9uXCQgDQoNClNFTEVDVCANCiAgICAgICAgMTo6aW50 ZWdlciBBUyBpZCwNCiAgICAgICAgJzIwMTktMTEtMjAnOjpkYXRlIEFTIFwic3RhcnREYXRlXCIs DQogICAgICAgICcyMDIwLTAxLTAxJzo6ZGF0ZSBBUyBcImVuZERhdGVcIg0KDQpcJGZ1bmN0aW9u XCQ7DQoNCkVYUExBSU4gQU5BTFlaRSBXSVRIICBcInNvbWVQZXJpb2RzXCIgQVMgICgNCiAgICAg IFNFTEVDVCAqIEZST00gbXlfdGVzdF9mdW5jdGlvbigpIEFTIA0KICAgICAgZihpZCBpbnRlZ2Vy LCBcInN0YXJ0RGF0ZVwiIGRhdGUsIFwiZW5kRGF0ZVwiIGRhdGUpDQopLA0KDQpcIm1heFJlY3Vy c2l2ZUVuZERhdGVcIiBBUyAoDQoNClNFTEVDVCBcInN0YXJ0RGF0ZVwiLCBcImVuZERhdGVcIiwg aWQsIA0KKCANCiAgV0lUSCBSRUNVUlNJVkUgcHJlcChcInN0YXJ0RGF0ZVBhcmFtXCIsIFwiZW5k RGF0ZVBhcmFtXCIpIEFTICgNCg0KICBTRUxFQ1QgXCJzdGFydERhdGVcIixcImVuZERhdGVcIiBG Uk9NIFwic29tZVBlcmlvZHNcIiBXSEVSRSBpZCA9IG9kLmlkDQogIFVOSU9ODQogIFNFTEVDVCBc InN0YXJ0RGF0ZVwiLFwiZW5kRGF0ZVwiIEZST00gXCJzb21lUGVyaW9kc1wiLCBwcmVwDQogIFdI RVJFDQogICAgXCJzdGFydERhdGVcIiA8PSAoXCJlbmREYXRlUGFyYW1cIiArICcxIGRheSc6Omlu dGVydmFsICkgQU5ECihcImVuZERhdGVQYXJhbVwiICsgJzEgZGF5Jzo6aW50ZXJ2YWwgKSA8PSBc ImVuZERhdGVcIg0KICApDQogIFNFTEVDVCBtYXgoXCJlbmREYXRlUGFyYW1cIikgRlJPTSBwcmVw DQopIEFTIFwiZW5kRGF0ZU5ld1wiCgo= --===============6748010674781854827==--