| From: | Andy Colson <andy(at)squeakycode(dot)net> |
|---|---|
| To: | sthomas(at)peak6(dot)com |
| Cc: | Greg Spiegelberg <gspiegelberg(at)gmail(dot)com>, "Reuven M(dot) Lerner" <reuven(at)lerner(dot)co(dot)il>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Marcin Mańk <marcin(dot)mank(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org |
| Subject: | Re: Very long deletion time on a 200 GB database |
| Date: | 2012-02-23 18:11:12 |
| Message-ID: | 4F468140.2090704@squeakycode.net |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Greg Spiegelberg | 2012-02-23 18:13:39 | Re: Very long deletion time on a 200 GB database |
| Previous Message | Shaun Thomas | 2012-02-23 18:05:01 | Re: Very long deletion time on a 200 GB database |