Subject: Re: Delete operation VERY slow...
From: David Leangen <postgres@leangen.net>
Reply-To: postgres@leangen.net
To: pgsql-performance@postgresql.org
In-Reply-To: <674d1f8a0606160022p1b042676gba28553bac5ab5ea@mail.gmail.com>
References: <1150441126.4812.23.camel@sonoda.bioscene.co.jp>
	<674d1f8a0606160022p1b042676gba28553bac5ab5ea@mail.gmail.com>
Content-Type: text/plain
Date: Fri, 16 Jun 2006 17:12:22 +0900
Message-Id: <1150445542.4812.34.camel@sonoda.bioscene.co.jp>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit


Wow! That was almost instantaneous. I can't believe the difference.

The only inconvenience is that I need to remove all the foreign key
constraints before truncating, then put them back after. But I suppose
it is a small price to pay for this incredible optimization.


Thank you!


On Fri, 2006-06-16 at 12:52 +0530, Gourish Singbal wrote:
>  
> David,
>  
> Truncate table would be a good idea if u want to delete all the data
> in the table.
> You need not perform vacuum in this case since there are no dead rows
> created.
>  
> ~gourish
> 
>  
> On 6/16/06, David Leangen <postgres@leangen.net> wrote: 
>         
>         Hello!
>         
>         I am trying to delete an entire table. There are about 41,000
>         rows in
>         the table (based on count(*)). 
>         
>         I am using the SQL comment: delete from table;
>         
>         The operation seems to take in the order of hours, rather than
>         seconds
>         or minutes.
>         
>         "Explain delete from table" gives me:
>         
>                                   QUERY PLAN 
>         ----------------------------------------------------------------
>         Seq Scan on table  (cost=0.00..3967.74 rows=115374 width=6)
>         (1 row)
>         
>         
>         I am using an Intel Pentium D 2.8GHz CPU. My system has about
>         1.2GB of
>         RAM. This should be ok... my database isn't that big, I think.
>         
>         
>         Any ideas why this takes so long and how I could speed this
>         up?
>         
>         Or alternatively, is there a better way to delete all the
>         contents from 
>         a table?
>         
>         
>         Thank you!
>         
>         
>         
>         ---------------------------(end of
>         broadcast)---------------------------
>         TIP 5: don't forget to increase your free space map settings
> 
> 
> 
> -- 
> Best,
> Gourish Singbal