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 1qMkRV-003BnQ-RM for pgsql-general@arkaria.postgresql.org; Fri, 21 Jul 2023 07:20:49 +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 1qMkRU-00E3mi-4h for pgsql-general@arkaria.postgresql.org; Fri, 21 Jul 2023 07:20:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qMkRT-00E3ma-Oh for pgsql-general@lists.postgresql.org; Fri, 21 Jul 2023 07:20:47 +0000 Received: from mout.gmx.net ([212.227.17.22]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qMkRM-000tMw-LV for pgsql-general@lists.postgresql.org; Fri, 21 Jul 2023 07:20:47 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1689924039; x=1690528839; i=jimis@gmx.net; bh=1MVVVOqhfJqjSdsmXwhJTDTgcY0RfWqVnBopg2YzPiI=; h=X-UI-Sender-Class:Date:From:To:Subject:In-Reply-To:References; b=IdR+A4j6HuPGbvxxgJm2L2SJ5yi7K+fy/D4pRRz0Z2Ux1X+egqvX2iSOLM6Oo/HKst6gR1t /OTCBuelZjYoralzm96UH0TodHcpgmadMDpZy/n+Q3A5IUVgdwEiUJOqE34Lr0PSwHryhY7bG MdEagHjOx35NXw3o0SemRC3NGb2Hym152FWcA9SJxDeVGcTnw8kFSihO//wQKRwxHJCwak31/ txUP4WVPH2VB8agUJGX+UzPr0fI+pMeIhC/DArWZ6BL10CBxbviAy3e+foR1NPQNdDok3Z4Sv JU26Xx/86kXJKgpUjTTu50dPh8cH6MRUb3YNSvb8UgS4IB4zN2LQ== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from [10.9.70.65] ([185.55.107.82]) by mail.gmx.net (mrgmx104 [212.227.17.168]) with ESMTPSA (Nemesis) id 1MVeI8-1qWgaZ1d7U-00Rbsa for ; Fri, 21 Jul 2023 09:20:39 +0200 Date: Fri, 21 Jul 2023 09:20:37 +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: <68257d84-96e5-9dca-ff31-c9f45e356543@gmx.net> 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:r63NW4H61RvULQ5YGye9lP1PWbcrVDzK/Er/ZfrtgnvlA7LVC9G 4He/Be15f+kLB0QC1JqJzfn2/rCYl7b7A0ixkT60TI3mu/ubIIQ6je8AMGM6MVTeRGo8gP4 2V38dNEzY3zTXcKJebTSmoCi8hF/OGUuash9NuEjyehagMoYJ6+D04NdH1pCP9+3hz8uzMn Mvt9Q+WQDLLIzmADuZ39w== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:SYiplgEb8dc=;/7Md4ULxW64jR+nuiFzTdyoONTE RQjRPCADa7sNcUInXGFuKyZKVonUSbhAjQYtvhwBgj5ZT4qP6IdnFOYLB4k01UYMjuC5RU4/0 fYaNrZUvdNeHEOfMJ0v+mMiX8YoyGU7lIGhrmvOZcn8Z2OWBUL33dI0fv7UaWRCgiD/Io28nC AAuM5c+2TJSCB9JiViR72czic+WnKrRzF+YtJcK71JlYGFJ+8FpQUoM1of70wW6fzU5ZF//AF gPCHuLjwhvdPadl8OxdwDBtQaGPQybnjYHE97XEUGa0BpB5suQfCKuI2BRmJEAb6bHg8ZZyn1 dEalyuP9iQhkQosLeabRh9NiUsPqH4C3c8VMpVw1Po+6SMABqsWmydIsJ4rYuCCxtlPLhdfv8 EFtxzVK9Ucn1mRYoTUJb/Bx/XFR38042GiSll0LODrfPRmXjicRQ1m094xn6fxzPV7j6XuCpI q7JinsTsNv5qTnmdR4KRUlrkrRxEjzXO7NSJMroRA5v3im+jfkfstTGOBUpEyuKpqm1VDVLAg qV2Nwr6MSAn7zOFNDwx8xN5cXnMxXC3YuS6/CxWqU9HuobibFLPEMGEvDVWV5X+csB+BPJQ7+ u8p1lcKiqOvaYEYvR2u3a9xYnSMUL2ql//ijSkJOaVLCpmgAsLklY5BUEnofn+h7tioRB+TpK IUl9KXvlNcU60C9OgwYLo1aGzx+eYX/GtmOo7JyfT6Uxv+8lfINSE2ALvFX98MlkfrrfKqV45 PgIHEt2SRBO4H9rLzBNBZ05NjbzX5Mh7jFrGEl0dYHfFjR+hqcCsM1hYUp1+GHYli2W/unurZ 4P78piGNqFibwvw0tV1RoZALSSAluLiAidEkMN8e9rZFS3TUIpRxd3cXdDBybDaw0Jlzle5+m /yDD+Swg7o+9/RGcigKRCaPX+4t5SWHAVwnRs87g9IF6rTILM4JW3ApZE 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: > + Then I broke the command above in many small chunks > > WITH rows AS ( > DELETE FROM tbl_legacy AS t > WHERE (partition_key_column < $1) > RETURNING t.* > ) INSERT INTO tbl SELECT * FROM rows; > COMMIT; > > I increase the parameter $1 and keep going in a loop. At first this > goes OK, after one day though I notice that it has slowed down > significantly. My investigation shows that the simple query > > SELECT min(partition_key_column) from tbl_legacy; > > takes 38s, despite having an index on the column! A VACUUM fixes that, > so I guess the index has a ton of dead tuples. I guess autovacuum does > not do its job because the table is constantly busy. > > Unfortunately VACUUM takes long (several hours) on this huge table, so= I > can't add in the loop after the DELETE command. > > Is there a better way to avoid the bloat in the loop above? Why can't > the DELETE command update the index by pointing the minimum element > beyond the dead tuples? Any comments on this one? It annoys me that a simple loop deteriorated so much and kept filling the table with bloat. What is that VACUUM does that DELETE can't do, to keep the index fresh?