Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id E92061EB83EA for ; Thu, 23 Feb 2012 14:11:19 -0400 (AST) Received: from li339-205.members.linode.com ([96.126.114.205] helo=squeakycode.net) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S0d8O-0003Yu-NH for pgsql-performance@postgresql.org; Thu, 23 Feb 2012 18:11:18 +0000 Received: from [192.168.10.84] (97-64-237-59.client.mchsi.com [97.64.237.59]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: andy) by squeakycode.net (Postfix) with ESMTPSA id 9AAED1696F; Thu, 23 Feb 2012 13:11:01 -0500 (EST) Message-ID: <4F468140.2090704@squeakycode.net> Date: Thu, 23 Feb 2012 12:11:12 -0600 From: Andy Colson User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2 MIME-Version: 1.0 To: sthomas@peak6.com CC: Greg Spiegelberg , "Reuven M. Lerner" , Andrew Dunstan , =?UTF-8?B?TWFyY2luIE1hxYRr?= , pgsql-performance@postgresql.org Subject: Re: Very long deletion time on a 200 GB database References: <4F45FB55.1020600@lerner.co.il> <4F464C49.4050503@dunslane.net> <4F465A7A.4000905@lerner.co.il> <4F467FCD.8090100@peak6.com> In-Reply-To: <4F467FCD.8090100@peak6.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201202/174 X-Sequence-Number: 46346 On 2/23/2012 12:05 PM, Shaun Thomas wrote: > On 02/23/2012 11:56 AM, Greg Spiegelberg wrote: > >> I know there are perils in using ctid but with the LOCK it should be >> safe. This transaction took perhaps 30 minutes and removed 100k rows >> and once the table was VACUUM'd afterward it freed up close to 20 GB >> on the file system. > > It took *30 minutes* to delete 100k rows? And 100k rows were using 20GB? > Is that off by an order of magnitude? > > Using the ctid is a cute trick, though. :) > And I'm not sure the LOCK is necessary, while googling for "delete from table limit 10" I ran across this thread: http://archives.postgresql.org/pgsql-hackers/2010-11/msg02028.php They use it without locks. -Andy