Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qd26S-001xht-HV for pgsql-hackers@arkaria.postgresql.org; Mon, 04 Sep 2023 05:26:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qd26R-008aNY-1Y for pgsql-hackers@arkaria.postgresql.org; Mon, 04 Sep 2023 05:26:22 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qd26Q-008aNO-Hq for pgsql-hackers@lists.postgresql.org; Mon, 04 Sep 2023 05:26:22 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qd26M-002chr-Cs for pgsql-hackers@lists.postgresql.org; Mon, 04 Sep 2023 05:26:21 +0000 Received: from auth1-smtp.messagingengine.com (auth1-smtp.messagingengine.com [66.111.4.227]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) (Authenticated sender: a.lepikhov@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id A4DD9E20E63; Mon, 4 Sep 2023 08:26:15 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1693805176; bh=JnLzCcgHnhLDZhZxUYBHmEQeCCp+P9TYumcxJmPz9ck=; h=User-Agent:Message-Id:Date:From:To:Cc:Subject:From; b=3NrLBQO1Pzy8C5mCXlMdkr0EDAj8YaW+XvJYsuLE2nUH/WR+WijM3RreP3dXEwJW5 2aDH7o31QodNxk5gvq0A89pvBsgFrTcD3bi30xxYtz8xgDzS2H6RMxyq+KPLcJQ3mi l+uhbjtdEDIx/HUkXhkZqfhx2UQlpTb7+xehYxonFibOXMzxPuuvF1rHyVXJdvPyEc tDlFuguidseAoe4hOY4rbGOFsOLLHIDlisehI1UDtDrGx4mZ01Urqfoo8ai4o1zpbt vp6qL+tClXS6L+IQRROqvuWDN/SFM9cASDCswe2TVLISqWSHN/xXOiHJvV4+XWkmDX drJWV90qIcryQ== Received: from compute1.internal (compute1.nyi.internal [10.202.2.41]) by mailauth.nyi.internal (Postfix) with ESMTP id D944E27C0054; Mon, 4 Sep 2023 01:26:13 -0400 (EDT) Received: from imap53 ([10.202.2.103]) by compute1.internal (MEProxy); Mon, 04 Sep 2023 01:26:13 -0400 X-ME-Sender: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedviedrudegjedgleekucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucenucfjughrpefofgggkfffhffvvefutgesmhdtre erreertdenucfhrhhomhepfdfnvghpihhkhhhovhcutehnughrvghifdcuoegrrdhlvghp ihhkhhhovhesphhoshhtghhrvghsphhrohdrrhhuqeenucggtffrrghtthgvrhhnpeejfe ekffekveefgfelledtkeejvdetleejtdeuledugfegueelgfethefgffekfeenucffohhm rghinhepphhoshhtghhrvghsqhhlrdhorhhgnecuvehluhhsthgvrhfuihiivgeptdenuc frrghrrghmpehmrghilhhfrhhomheplhgvphhikhhhohhvodhmvghsmhhtphgruhhthhhp vghrshhonhgrlhhithihqddufeelgeehudejhedvqdeftddtiedtkeefvddqrgdrlhgvph hikhhhohhvpeepphhoshhtghhrvghsphhrohdrrhhusehfrghsthhmrghilhdrtghomh X-ME-Proxy: Feedback-ID: ie8b149e1:Fastmail Received: by mailuser.nyi.internal (Postfix, from userid 501) id 1F87C88005E; Mon, 4 Sep 2023 01:26:13 -0400 (EDT) X-Mailer: MessagingEngine.com Webmail Interface User-Agent: Cyrus-JMAP/3.9.0-alpha0-701-g9b2f44d3ee-fm-20230823.001-g9b2f44d3 Mime-Version: 1.0 Message-Id: Date: Mon, 04 Sep 2023 12:25:44 +0700 From: "Lepikhov Andrei" To: pgsql-hackers@lists.postgresql.org Cc: =?UTF-8?Q?=D0=95=D0=B2=D0=B3=D0=B5=D0=BD=D0=B8=D0=B9_=D0=91=D1=80=D0=B5?= =?UTF-8?Q?=D0=B4=D0=BD=D1=8F?= Subject: Optimize planner memory consumption for huge arrays Content-Type: multipart/mixed; boundary=fe705382cb98444d93c19a31928636e5 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --fe705382cb98444d93c19a31928636e5 Content-Type: text/plain Hi, hackers, Looking at the planner behaviour with the memory consumption patch [1], I figured out that arrays increase memory consumption by the optimizer significantly. See init.sql in attachment. The point here is that the planner does small memory allocations for each element during estimation. As a result, it looks like the planner consumes about 250 bytes for each integer element. It is maybe not a problem most of the time. However, in the case of partitions, memory consumption multiplies by each partition. Such a corner case looks weird, but the fix is simple. So, why not? The diff in the attachment is proof of concept showing how to reduce wasting of memory. Having benchmarked a bit, I didn't find any overhead. [1] Report planning memory in EXPLAIN ANALYZE https://www.postgresql.org/message-id/flat/CAExHW5sZA=5LJ_ZPpRO-w09ck8z9p7eaYAqq3Ks9GDfhrxeWBw@mail.gmail.com -- Regards, Andrey Lepikhov --fe705382cb98444d93c19a31928636e5 Content-Disposition: attachment; filename="init.sql" Content-Type: application/sql; name="init.sql" Content-Transfer-Encoding: BASE64 RFJPUCBGVU5DVElPTiBJRiBFWElTVFMgdGVzdF9mdW5jOwpDUkVBVEUgT1IgUkVQTEFDRSBG VU5DVElPTiB0ZXN0X2Z1bmMoeCBpbnRlZ2VyKSBSRVRVUk5TIE9pZFtdIEFTICQkCiAgU0VM RUNUIGFycmF5X2FnZyhpZCkgQVMgYXJyIEZST00gZ2VuZXJhdGVfc2VyaWVzKDEsIHgsIDEp IEFTIGlkCiQkIExBTkdVQUdFIFNRTCBJTU1VVEFCTEU7CgpFWFBMQUlOIChTVU1NQVJZIE9O KQpTRUxFQ1QgcmVsbmFtZSBGUk9NIHBnX2NsYXNzIGMxCldIRVJFIG9pZCA9IEFOWSAodGVz dF9mdW5jKDEwMCkpOwoKLyoKZWxlbXMsIG1lbW9yeV9jb25zdW1lZAoxCQkxMmtCCjEwMAkJ MzdrQgoxMDAwMAkyLjZNQgogKi8= --fe705382cb98444d93c19a31928636e5 Content-Disposition: attachment; filename="array_compact_memusage.diff" Content-Type: application/octet-stream; name="array_compact_memusage.diff" Content-Transfer-Encoding: BASE64 ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9zZWxmdW5jcy5jIGIvc3JjL2Jh Y2tlbmQvdXRpbHMvYWR0L3NlbGZ1bmNzLmMKaW5kZXggZmUzN2U2NWFmMC4uOTYzNDY0Yzlh ZCAxMDA2NDQKLS0tIGEvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L3NlbGZ1bmNzLmMKKysrIGIv c3JjL2JhY2tlbmQvdXRpbHMvYWR0L3NlbGZ1bmNzLmMKQEAgLTI4MCw2ICsyODAsNyBAQCBl cXNlbF9pbnRlcm5hbChQR19GVU5DVElPTl9BUkdTLCBib29sIG5lZ2F0ZSkKIAkJc2VsZWMg PSB2YXJfZXFfbm9uX2NvbnN0KCZ2YXJkYXRhLCBvcGVyYXRvciwgY29sbGF0aW9uLCBvdGhl ciwKIAkJCQkJCQkJIHZhcm9ubGVmdCwgbmVnYXRlKTsKIAorCXBmcmVlKG90aGVyKTsKIAlS ZWxlYXNlVmFyaWFibGVTdGF0cyh2YXJkYXRhKTsKIAogCXJldHVybiBzZWxlYzsKQEAgLTE5 NjAsMTUgKzE5NjEsMTUgQEAgc2NhbGFyYXJyYXlzZWwoUGxhbm5lckluZm8gKnJvb3QsCiAJ CXsKIAkJCUxpc3QJICAgKmFyZ3M7CiAJCQlTZWxlY3Rpdml0eSBzMjsKLQotCQkJYXJncyA9 IGxpc3RfbWFrZTIobGVmdG9wLAotCQkJCQkJCSAgbWFrZUNvbnN0KG5vbWluYWxfZWxlbWVu dF90eXBlLAotCQkJCQkJCQkJCS0xLAotCQkJCQkJCQkJCW5vbWluYWxfZWxlbWVudF9jb2xs YXRpb24sCi0JCQkJCQkJCQkJZWxtbGVuLAotCQkJCQkJCQkJCWVsZW1fdmFsdWVzW2ldLAot CQkJCQkJCQkJCWVsZW1fbnVsbHNbaV0sCi0JCQkJCQkJCQkJZWxtYnl2YWwpKTsKKwkJCUNv bnN0ICpjID0gbWFrZUNvbnN0KG5vbWluYWxfZWxlbWVudF90eXBlLAorCQkJCQkJCQktMSwK KwkJCQkJCQkJbm9taW5hbF9lbGVtZW50X2NvbGxhdGlvbiwKKwkJCQkJCQkJZWxtbGVuLAor CQkJCQkJCQllbGVtX3ZhbHVlc1tpXSwKKwkJCQkJCQkJZWxlbV9udWxsc1tpXSwKKwkJCQkJ CQkJZWxtYnl2YWwpOworCisJCQlhcmdzID0gbGlzdF9tYWtlMihsZWZ0b3AsIGMpOwogCQkJ aWYgKGlzX2pvaW5fY2xhdXNlKQogCQkJCXMyID0gRGF0dW1HZXRGbG9hdDgoRnVuY3Rpb25D YWxsNUNvbGwoJm9wcnNlbHByb2MsCiAJCQkJCQkJCQkJCQkJICBjbGF1c2UtPmlucHV0Y29s bGlkLApAQCAtMTk4NCw3ICsxOTg1LDggQEAgc2NhbGFyYXJyYXlzZWwoUGxhbm5lckluZm8g KnJvb3QsCiAJCQkJCQkJCQkJCQkJICBPYmplY3RJZEdldERhdHVtKG9wZXJhdG9yKSwKIAkJ CQkJCQkJCQkJCQkgIFBvaW50ZXJHZXREYXR1bShhcmdzKSwKIAkJCQkJCQkJCQkJCQkgIElu dDMyR2V0RGF0dW0odmFyUmVsaWQpKSk7Ci0KKwkJCWxpc3RfZnJlZShhcmdzKTsKKwkJCXBm cmVlKGMpOwogCQkJaWYgKHVzZU9yKQogCQkJewogCQkJCXMxID0gczEgKyBzMiAtIHMxICog czI7CkBAIC0yMDUxLDYgKzIwNTMsNyBAQCBzY2FsYXJhcnJheXNlbChQbGFubmVySW5mbyAq cm9vdCwKIAkJCQkJCQkJCQkJCQkgIE9iamVjdElkR2V0RGF0dW0ob3BlcmF0b3IpLAogCQkJ CQkJCQkJCQkJCSAgUG9pbnRlckdldERhdHVtKGFyZ3MpLAogCQkJCQkJCQkJCQkJCSAgSW50 MzJHZXREYXR1bSh2YXJSZWxpZCkpKTsKKwkJCWxpc3RfZnJlZShhcmdzKTsKIAogCQkJaWYg KHVzZU9yKQogCQkJewo= --fe705382cb98444d93c19a31928636e5--