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 1hUB0m-0008VA-Ev for pgsql-hackers@arkaria.postgresql.org; Fri, 24 May 2019 14:17:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hUB0j-0003Sp-Ud for pgsql-hackers@arkaria.postgresql.org; Fri, 24 May 2019 14:17:29 +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 1hUB0j-0003SY-9R for pgsql-hackers@lists.postgresql.org; Fri, 24 May 2019 14:17:29 +0000 Received: from mail-yb1-xb42.google.com ([2607:f8b0:4864:20::b42]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hUB0f-0002fb-Dv for pgsql-hackers@lists.postgresql.org; Fri, 24 May 2019 14:17:28 +0000 Received: by mail-yb1-xb42.google.com with SMTP id x7so3676991ybg.5 for ; Fri, 24 May 2019 07:17:24 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=joeconway.com; s=google; h=subject:to:cc:references:from:openpgp:autocrypt:message-id:date :user-agent:mime-version:in-reply-to; bh=Cm0xxiHa4w3lpxB52QkqJYwLogkt/3sLCz7occwy/ME=; b=Dj0gy852az0dKAVQflQOLF2bCjZKrnA7zLM579FPJ5Kx9zNF4VDwqcQPvukEJwjL7u eMgqP8TwOyGf2/AQ7OqY1Tkm/DJZpA/XQOI3NR82y+3aGeguMQWFMMnFaTPeMn9jRzhK gsLWfK1pxcTTkUZD65IQgG9PAD1XO+PYeQRfAqRpUDLclUfoh9wRAF5uoV4oJjnE6NeU MNrglL0WQDTucCtGbFkHRYLPxa2Ta/+nkZM7Pa6hhFoU+Im5bA8dR4Om0SJCyyEwPK2X h2s4dUVKAjYjrFpn+0XbQtFKlkntHw/4ECQZzDTRI6X34BwKXy6ip1o0TgcthEFPt0W6 1Zmg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:cc:references:from:openpgp:autocrypt :message-id:date:user-agent:mime-version:in-reply-to; bh=Cm0xxiHa4w3lpxB52QkqJYwLogkt/3sLCz7occwy/ME=; b=c9GCP0dEGaHaj8KhEIWOjfZ5ogj8fRIW2yTmsvtUDIbJMbf57EfxLofflW6iZOXZzT OK059foClUdFIJx0EJDMLq+AMoOo/iwONr/mhgFkXlWsH0CnqkgY/Llbrxk2D+Mt3+Zd 9s4YhxfOqQ3wT/cxoRb20Ng52iTT/x90J7JEcdDgKjzB/dhjAyyepzf2YM8NGYJgyBUz Pmd6YLtsdVQoFr1zO5cPQBcQL+Uo3s5JwEz8jlwb+M4avifZWWN86s4qTyi/UBjvvAY4 /ECHac3LJsY3ATBoEruf6s4db87FOdT1yO7FstYebIe9twz48p7MN3ooeCqsdhnli5nQ igoA== X-Gm-Message-State: APjAAAWwzvJaf3nmlgpBTy+Mg/rXX1LsWDAs0ZqSYzwAMUZ/WyyeS98Y kdfr7o3QArNpfxgHZfkeEiAov0yblwE= X-Google-Smtp-Source: APXvYqzx9vyVi2T8mZXPIydUJX/tFFepdaY+kydADHZPzZaA50VOMnVg1wXZ6OlH8rlezXGGECVX+g== X-Received: by 2002:a25:d887:: with SMTP id p129mr26038940ybg.394.1558707443377; Fri, 24 May 2019 07:17:23 -0700 (PDT) Received: from [192.168.4.41] ([72.17.18.98]) by smtp.gmail.com with ESMTPSA id u73sm635704ywf.62.2019.05.24.07.17.22 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 24 May 2019 07:17:22 -0700 (PDT) Subject: Re: Excessive memory usage in multi-statement queries w/ partitioning To: David Rowley Cc: Amit Langote , Andreas Seltenreich , PostgreSQL Hackers References: <87ftp6l2qr.fsf@credativ.de> <0e717312-3a02-781b-7dfc-e0cc395e92d8@joeconway.com> From: Joe Conway Openpgp: preference=signencrypt Autocrypt: addr=mail@joeconway.com; keydata= mQINBEpXMCsBEADDnXUQzjlyi/cX02Gtdy2CLcroE5CsC7DJKdOBDbfgn0kfiIYoV5JniG4l VyzZUodY8yUAagqLYolh0UkBzs9N+qkm7erde4ypw3jzVQ37BuzIvk3nMUbuDZDgxWqX+nVS sKc+BQ5BpzgCHg48leoRO2ohjvYnUhgH3j2rFZCzaj6qQ7mv+XoxOJmUlVQtG06Jwkk7Vu14 7U9nMMM6hyUKzVnmCphnlcMNo26UyVU70MwFfFJgcI0c5fpp8byN56eD6VJVnufO5WAuEhzE qcrSJR2FAlmM90GBY+6vP29twLDCHuSFvrnujNCx/BvCC/a3/gPvyAFp4JtMm9eXAmq3m/Kw 94nTJXVdcbQeQQDp3KIG7MmWS4lnGvPn8v0CjgNaLvZXFLo1FgmUVsyEq1Lww4iRLa6sbpXJ ESx15UEue1k1YZM9C+4F/o3aeKNsAienjw2EXFzcaxIg/C4P493VMi3Qa8ycVxR5iYhUbYdo DFIUQhbFNsYfrtW/qZAELT3FCYFpZYG01e9Hj+cBrXXgyDDkQ5Lq4mlvmkRvuxn61V6Au4HA 0sJiCox5pM1FvzT+aI8HY1BYaiB9Pl4fhpKgmhhlSuglk9v39S4jmlUIb45iLAUVpeNM6Qjm 69pf5da9sm4aGFa7YlDSKf/WcU7z9ITZxsilOi2n7YJiwG7kTQARAQABtB9Kb2UgQ29ud2F5 IDxtYWlsQGpvZWNvbndheS5jb20+iQI8BBMBAgAmAhsDBwsJCAcDAgEGFQgCCQoLBBYCAwEC HgECF4AFAlpoksUCGQEACgkQMyt+aLaZQ0p4XQ/9F4WSf4ZFuL3ae356ApVqnLxphHXeXHli ePZe7qosvXMQLN9Y2m5cQwqojsQh4pZujIi4Iwxw7ORg6HbMO8dMhyu8TlDavErcgO561hG7 /TCcCLf5SkadxBqYWAJH09W8S2iC3gQtS+qs4Y7sIvRi881EsPzG5Lrzu7Ei8ufqsSnbLUQk zAaKjK3IzAFvzA3VCg/8Aqhf8obhqL0j5I5DNOpAt2U4gkEzIrX1g5xSXpRLMHLEK7hPViY8 pFQyRvyZioE4hckNsnidCgiv4HefJUFRIVxDkHBeLhTzqTPloesFT/bskhckIS6F4suMx044 WTTW6UiARuWw3aaBTmxay2rjPCsGaJPawzY0O3HlNlZherCzuBpa+Qk1kMLkcxMlYjCrMgIR YyBpIK46ltPQaog4Ujh21w1Z8m/ufG5v/HTuLdXM9hGfTlW7aY6psCAV/MwfycDHLRMc+UY7 W2cp6DlWQ7GHLP+vzvBcl5Odi04EhcYnHMZjKvRFsxhE0LZcTejrtxPkDgR9G59irlIx1+py ziK2B0hs7Er1Yq7qQGQiQGLoglSNDsvsUeyMGyabasmUd5xrRO35SpGv+hyYGAZ3Pyk9Vtxl CO7BJkZiV9YSMHp6hOdmYZd4SZRChd9sKd6eSw1xIlBALz1ZHuQVMBuZvuVGFsS81Zqckb/2 D5q5Ag0ESlcx7QEQAKhm5UhGfbe3hM4yCaa/pPaNaswlB2tK1DHAjDlDm7gDVEodfsrvAFUU unLodlvCd0g/GpLmXXdROU/I+HusVqQhDbVeHMwO2VUVHamRWqDfrCjMYVxLz80d96TYyeyW 6RUNkJmXJAOKgy8veI7Z1F+j1gXQR7UlzonnSkWbs0oQ3RglvyqhkUzWtXU7F+j2hQ2vQsct Lnq3lYo0bYkZ3K6RyKZXSly9Hd7DzYkMzz7StNRlyeQJxplPrVPup7N8SqDrI126sq0cwdUB Gn1sKVoO+bGidBjhJgxouNPkMJlPjbKOgb7tv5ZnM33FGXNdC5sPKoZ4mC+gaqRHYaH/V61v fCo1CYNLIK+Cj+Oua8mQ6CPeOb5VKcJ0JM96eGNlzF9EawppOG7xCDcbmBEr0m1go9MHEUel 0L1i8RSDYOHQQkLT2MVWYf4UsRlEGbME+kF7DEX9Fvie7qglFTXC2d+YVN73AnXcoM/IYUOR DXjVVaqQoHuA4uxwJ31o/rEaOihr2yCScr8mB8J3b3KyNfuCBm5gN0tUQw+t5szMYurDpZ1G L4O7btaQ5u4iJMfRTCnPrzW+wkdVrao7AmRNWEtSlXsycClKFCHz/Ej5rpXOKSp+a/xcaFX1 v2eS/+Ujp5ESu9p57JwlP6aSqYBiOALRJXZ9DVtZL96okjzXjrd7ABEBAAGJBD4EGAEIAAkF AkpXMe0CGwICKQkQMyt+aLaZQ0rBXSAEGQEIAAYFAkpXMe0ACgkQN/L3QzX32GV1Jw//fhvL Mk1YNS/2N5jc5HYmEjcL2OWBLLrO5RMCaOgCNmYsepkigy2U+GSloVy8Y40/UfYzIoNf1DiB Qyj5Ipa4eMjfKA7+1q43a5wkDDWogFNflKTTNemwrHDKR1WWDrxt8uAgJdlBGDQA7A0q3D2i Xhd7ALLOHhuXDY1++N9TK9KMfV90Oay0mIMLQhTG/UgsgrYYb78H2pk8XMYZY29vgZlj9VH2 f1RpOoh7lCaB+2glN27Rzh17q0/zEjwXk4sDyYHRIycaqnM4ozSnliXyoWM5bvmqSGBCm5jl BjNW+cFbFy15U595AIXXiUXl8hsTF2JWwgHr+hYdtoEH3oEOxzM18ENXKTTgN+BafdMsah4e HIcVvrVrH6rlsPwCvk3o/PJ07qVbUW+dbZG8UB1JwqUUqGhrCk87kztajlvZGZWxWDSB4Lnb T6Jc0L7/YXG8U3sJ2OtZ95Lal5XfNo0yBdPflmalH3xf/EcxaYFETbDrY5UgEVa2HBw8XzKk rTbM8rD318M+jBOuL6o3cGMOeFOm4C51fJZmOCFd03LhMkoj9IbLIPGKosIBsxb7zP5YLAbI b3el6pvo9CKtNuR+IcVOoFtH+KVOxGsbbxFEFbsAmPMreF1hIv+l+HXBaDhUkaUmpJ1Mhxol aM6O/LGG4VQV7wBQFunVaN8EPqMqYDph3A//TjQVd8fqvw5Ezj1958IUmV0jxeGX2m/fspGh njF+6Adx3PFbZoKyW71cA/TnlHVYCaizjYYRNMC3g5VFytOYOZFi0GKsxQTX5PcPhDjFFTUv FVVZwDYUK3QlC+wGDTeNWfAJIZnTPPIXSiHzh3si+ngKjE+ngcRF1VY5K4xvc6ss6uq8kI8L Ct+TpOMr99ijaYEbMZ8WJagynD5BLNYxpP6CCTMDi1Xqydg0E/mhR9w9aUOvdTZw1OOKtLnz B1E0wFbzJXEvjP2eEVNlStS5RTawFDpqxGwXJcHqe8qtg8B+C1r47RmHnKKm+DTOPFQWTj83 l775aNMzAHm+Pr1TdSA5XRyQ3wxwYKfhp1suKhILUZLjbiCQf92RZQ6yEdXskMr2A4n0GQWq Q8k7r9QPqtUP7lsiyyDem7r9h5mAm94r3Td22L3JaSHkBD+4jC4gIXeYoyd+lTz/zYOx0Fri J8dXHalmyTGsSxV7FXv1/thVEf6R7TDdVwhqf/WL3uWTmVBR0tJscG+9iCVch/OG/uCDkWZQ sOTotqj6f5jSZI+NOUqt62+8i8gjYEa7zRZ2HXFOTV8UduCjheWB2VCUj5EjFjTMjfTVkyZ5 SiFht7bM5l/odoYU6vn37/5s/ZsMTZX1zG5oXv6iULrHLYHhUxR93FlVyn4DiHsWND/Idj65 Ag0ESlcyJwEQAOkTBb9yDhJbMUgvhM11rZwT5tm4Y9TqtEHn0Zy3t9g7bdFFpMvav/KENd3o AtLFpMDf+H3AggFk4ftUwJwiVgJ88ilvCynJUGXiuYIaexY4DLgn4xpnuiEpYEFVdWnlw7dW VTc62exfqIz9bSWRzwfBCY9ruYGEb4RDPDSNSAVyI7sxHzef2asiYxIcxrTrw5VugWNlPZcV 5/EJ6PUvATjBF2TBkXV7KOciQng2tsQGrGMkY5mduNqwpuh6zfPcVF8LeObe96wv5ZhPRpO7 9nef7hnK2lJogp3JIo558Jlbz9WHtQEMZR85+bUhtI825QyNAFz3Jrn7NMgvDikc2OrWo7YM gMC5hDSWVFqA6/EQCNnDWGABWgeYHZFpnPwsvUWIYdhSilUuj/Tuzvz9ZmucFNbQbauDQw6V Q38ofGnoYDZFJsGncprB8dBi4tDrIQ+1RlIh6C2Z/eMipqJOT26+spluTjouvnKT0S5yOgyX 0PjbsysgwQdCGNJLHOjhHbSpSmOLaduV3CQo/0+DHT/TBjYfIXjTWouY9TkGxG4eNrxU0u2x Ay5bMqOPmsFdjLTWlQUlF/fTMhB54XwI3FHWgnSnXZzStDTmTebLNdT/ftgliAzA81uMj49j 0exv731/v+7udLA1bV8gnZ01zQCASDpWiRQR3fgwcugSUqgRABEBAAGJAh8EGAEIAAkFAkpX MicCGwwACgkQMyt+aLaZQ0pwAQ//bjcWnZg/jjRQ9gbZUGMqniItZYRglBMKIqt4Fia379Jm HwTvavnFkJ8XMZ56UB0FIrgS+sUkRH6cPRQR+7Qi392LD021DXgSsz9CwFHjFyBGHwLEOTRc fYQbtJy0shHDJB4aQTOX3ERDH1PsvJNuevmQMzS0DWFav9+xMz9rKP4N+HffoBIZE0C1xIE4 3nD4eLsbycte9sVIrmlNuUti3qUxJAQw8HwfJ6ZbBInHxquApR16uD1u99o6XlndFrDlY22t RmHCM0bR81GfGNdcU3Uo+rG/R/k4qa7s9/dgKvMbyH3fHhp/ceKag80Xo8IFurRl0ZJP3sHJ 2QDHCVLat7jRZ+43hi1WlIhFbrgn6IyI0i7XR/W8JjrC5MsKq4TUwGH077sU/kcHYebVJZRb UUst2hAGHDFVBcG12qoKf+ltL9qXJc1y7BGeCoUW6QjOpljpq6ZL4FQUsM0RSRjs5egE3szP cIf5SyPK6WDOApoAq6M7BBFMGDZwEylYMtr0YekA1u86UA9D2xwLHEbBBp/uiby1c9JbPJ1P n8zJP8WZNeRw4Q9TtqVK09+oLirMUSpIDd6KdZ1VgRxOK2re7tjDvkVuYsSrsiJ+1iJNEnp9 iK0ok0DlJpSCe6KhkxpaTdeoWMXdKuJWec0NIqoAd54ZgBPnr+UPxTixgPq/p6Q= Message-ID: Date: Fri, 24 May 2019 10:17:21 -0400 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.6.1 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="oqpY07VkS52ZAJeNWEjjqXrnYT2IN8kLT" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --oqpY07VkS52ZAJeNWEjjqXrnYT2IN8kLT Content-Type: multipart/mixed; boundary="IHSZJJz9W3UFiw0O1swvUt9S12oUfzfPA"; protected-headers="v1" From: Joe Conway To: David Rowley Cc: Amit Langote , Andreas Seltenreich , PostgreSQL Hackers Message-ID: Subject: Re: Excessive memory usage in multi-statement queries w/ partitioning References: <87ftp6l2qr.fsf@credativ.de> <0e717312-3a02-781b-7dfc-e0cc395e92d8@joeconway.com> In-Reply-To: --IHSZJJz9W3UFiw0O1swvUt9S12oUfzfPA Content-Type: text/plain; charset=utf-8 Content-Language: en-US Content-Transfer-Encoding: quoted-printable On 5/24/19 9:33 AM, David Rowley wrote: > On Sat, 25 May 2019 at 00:18, Joe Conway wrote: >> I admittedly haven't followed this thread too closely, but if having 1= 00 >> partitions causes out of memory on pg11, that sounds like a massive >> regression to me. >=20 > For it to have regressed it would have had to once have been better, > but where was that mentioned? The only thing I saw was > non-partitioned tables compared to partitioned tables, but you can't > really say it's a regression if you're comparing apples to oranges. I have very successfully used multiple hundreds and even low thousands of partitions without running out of memory under the older inheritance based "partitioning", and declarative partitioning is supposed to be (and we have advertised it to be) better, not worse, isn't it? At least from my point of view if 100 partitions is unusable due to memory leaks it is a regression. Perhaps not *technically* a regression assuming it behaves this way in pg10 also, but I bet lots of users would see it that way. Joe --=20 Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, & Open Source Development --IHSZJJz9W3UFiw0O1swvUt9S12oUfzfPA-- --oqpY07VkS52ZAJeNWEjjqXrnYT2IN8kLT Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAEBCgAdFiEEg14x9eymXoJyHrH+N/L3QzX32GUFAlzn/PEACgkQN/L3QzX3 2GXnORAAlotMijqSs/YqqSqYx/nsif9Rtxxv7aC21M/r9tXbHTTvcTKtvwWi8y/o /J146pvWQbIMKtjiWgJFq1Ydg9JW0KYt4hVTX+eqTtCiPowfDA4J65RBX+fjIFn4 6wYD0CaPVff/dblc8pjk2gbgBxX3UnPkxMhWZSEp6ZO6EnDRI0E/Xdwr7PmdO/G5 1byZCN9KXi2pbUxVPJfUwkHZZqJTICuFZSERe73dRtlgB2rT45TWTN+1DY1xzp77 SH3fsFDJBs6Wx+nJkhqqFbRXSJx/vVNJQpGEqdm3XSCBrzboKZ9JzhfJqcb4yOZP CINDnrq3ee1yZ2gYCmX5zWIZcdZ1/onAleMQseDNqANUGv3LbB80C2F0brYpAgp1 cs/B9DBDrwjBq+x9r/SWX0dq1kqiIHm6VtkfwyFOfL+0y1J/R52NbQ2TQ84M6ZBd /j11rxm3JvbaiMn+82HSJENJLhkzkwBeoRrE/CuqD/6fAylDRw2sJVOUxp3ue5wW yMax3od74KfonmcCaxvTUhuIWqnetsVJc0+UqAihXqIXjdhwBnt5q2HFY872pf78 xDshPOjlY9rn2Jb+T3GJ3N7MNKE78A+PEunQr8Zgs5A/EPuw4mYhvYIyU5E8wTQL biGbTLwJm0USiQ8yLIanHxpwDTDfJPiHPv5yKWhLCpGxgJPDEME= =V7pU -----END PGP SIGNATURE----- --oqpY07VkS52ZAJeNWEjjqXrnYT2IN8kLT--