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 1qHRaK-0007PH-9q for pgsql-general@arkaria.postgresql.org; Thu, 06 Jul 2023 16:12:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1qHRaJ-0000f3-6M for pgsql-general@arkaria.postgresql.org; Thu, 06 Jul 2023 16:11:59 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qHRaI-0000es-S1 for pgsql-general@lists.postgresql.org; Thu, 06 Jul 2023 16:11:58 +0000 Received: from mout.gmx.net ([212.227.15.19]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qHRaG-002Nrc-9l for pgsql-general@lists.postgresql.org; Thu, 06 Jul 2023 16:11:57 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1688659914; x=1689264714; i=jimis@gmx.net; bh=ja8Bgz0c7kaHVPYVuG6f8RwUGdFBlwjkYKvfCx/ThPc=; h=X-UI-Sender-Class:Date:From:To:Subject:In-Reply-To:References; b=oEBH9tddc6ycZraEDrtrtV4khV7J9iyL3+anQX1byX1CRH7RhxqHMkv3KL7YxYKCEllvqfa TdbrVANcZiY2eju99UobJsyRfMBJtieYChmsIy4Re6OB7YFZ4zWe6MYMLG4TV7LqsWN2Ehicb 3dd428WiIWpLW2jotCT/5tJuOuD1bMSw6wXFn/IEBm/Y436sGEuNDC/2Uu2RUq++Bo9vqH+RQ F6eEIALkE4a+L/pQxPkQ/8o3ciQ++nwzMoE80b7wyXyHcwJ6Q+Dn0KDAeuLbCG8zGW+U3te5Y WZDfUwSHQYPyDVL2+l+WrqonAlL0xO2ePuyOMn0uBkaRS7sd7tdg== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.9.70.65] ([185.55.107.82]) by mail.gmx.net (mrgmx004 [212.227.17.190]) with ESMTPSA (Nemesis) id 1MnJhO-1pYAcI17dU-00jMpv for ; Thu, 06 Jul 2023 18:11:54 +0200 Date: Thu, 6 Jul 2023 18:11:53 +0200 (CEST) From: Dimitrios Apostolou To: pgsql-general@lists.postgresql.org Subject: Re: Moving data from huge table slow, min() query on indexed column taking 38s In-Reply-To: <096aa4b3-892e-7fa5-7370-3a1895e7d6a0@gmx.net> Message-ID: References: <096aa4b3-892e-7fa5-7370-3a1895e7d6a0@gmx.net> MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII; format=flowed X-Provags-ID: V03:K1:FGUbNQWuubekSw64YuXKkEgPkFgG2aMim1eezEjn8mReaUCFUd0 elACEO98yQbvXfY9qZWmm2efIsph/jbPtNUaaXWKzcwn4H+PdLYiMHDEIyZHffdgMlc/N9x wmZYzyRLXE0skHPINtm+KK1L6kLxLdCTc0DPHzJHkAJvfoABV6wK30vXwhBIu8b9xHMGPKR JJDvV7xi52pZQ8FEgIWAw== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:IrH17Jv4NLs=;dPGJfAU1uI00SNzKJCKtSY6etAQ rsUlCfIgG5pp9orSA9nIqlsW/Lv9sAAp1Rx4+cx6wN0WR71MTVlfiA63IqGmAr73HA+ZkdfZd jVwlTVyNF5eXv3Y3t3mVqMP0J/v0bFyfy6zogc68RzfkwLBZvmhwGNbX6Litk6ZmgFw6wArY4 edkp0ayuGotQK+Fk3VyMICwDCRsGYOxHmpdwdIyXI/oOjNN08DKzL+umnvMzPNjJ47JMnOP6w IfXiw5jMDxpvlCWjxT7mEyGav1HTwZmm6cuXJLgLf6l0NxlU/eK5RVyYGwqxCOE9x4hQJEybk k4APlFmF4sm2SStRSXFTnJylCKE3kMEiP6O65e1wA5cwQSMf3TmDATzLzZdorrg/SlTRMKMGe PT0I9/76UYfA4WUWB7xwcgO1atjE1LkYJLe8529W5PVnaFVFOJychMOgjJ0AAjowF0Z9W6D3x DsX4tN8Ms1IrpKh8Bq3cMZQyn5SZwPJtoVpRA6eh9YJMqXyfzupdc/0jxY2sBxEsvI70Hb2xC AK4H5tFDbT4CPIigOTySRMpbccen+cPXk0D4cPi7vElUuuE8qTAaw9UhXZnLornC3ElK2TS91 xcOlGBXmEBqJ9H1HM/P7PbJI+vPGn4NBPuPgijE0/2CHPyfA25G6TcKS1ehqYBL6qLmI1cn+N jo7pgQJlJ+8upNX0ptRO5J9nob8WCgjRUgsLLjtL4GDq0bu9t174kvRU1y7gyABq5/zEb+n2H 0C5mI7WcM+sIsiISrywDfBq7MRP9Dgl2H6em03KzFFTJMx7W5AJMey1PfoRqvycPYOOreSM69 EJcyP0Jpf75WgwS+5sEnDNtCWz9ELPBOILMcfeQ/8KLTBWowTwUFpf+QalYavex5guX15+0qa s5Oyp4+LJveBnl+RfEs4l3yMw90rzwnAtyiGQSOysb0zZ0bM1iZEQituM Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Thu, 6 Jul 2023, Dimitrios Apostolou wrote: > + First I chose the method to DELETE...INSERT everything. Just to clarify, the query looked more like WITH rows AS ( DELETE FROM tbl_legacy RETURNING * ) INSERT INTO tbl SELECT * FROM rows; > I noticed that the postgres process was growing without bounds, up to > the point that it consumed almost all of swap space and I had to kill > the command. Any idea why that happens? Also note that my settings for work_mem, temp_buffers, shared_buffers etc are all well below the RAM size and postgres has never shown unbound growth again. Postgres version is 15.2. Dimitris