Skip site navigation (1) Skip section navigation (2)

Re: truncate vs. delete

From: Shane Ambler <pgsql(at)Sheeky(dot)Biz>
To: emilu(at)encs(dot)concordia(dot)ca
Cc: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>, pgsql-sql(at)postgresql(dot)org
Subject: Re: truncate vs. delete
Date: 2008-07-24 14:38:58
Message-ID: 48889402.3070505@Sheeky.Biz (view raw or flat)
Thread:
Lists: pgsql-sql
Emi Lu wrote:

> Thank you. I am quite sure that I will not use "delete" now.
> Now I a question about how efficient between
> 
> (1) truncate a big table (with 200, 000)
>     vacuum it (optional?)
>     drop primary key
>     load new data
>     load primary key
>     vacuum it
> 
> (2) drop table (this table has no trigger, no foreign key)
>     re-create table (without primary key)
>     load new data
>     setup primary key
>     vacuum it
> 
> suggestions PLEASE?
> 
> Thanks a lot!
> 

Shouldn't be a noticeable difference either way.

A quick test -

postgres=# \timing
Timing is on.
postgres=# create table test (id serial primary key,data integer);
NOTICE:  CREATE TABLE will create implicit sequence "test_id_seq" for 
serial column "test.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"test_pkey" for table "test"
CREATE TABLE
Time: 26.779 ms
postgres=# insert into test (data) values (generate_series(1,200000));
INSERT 0 200000
Time: 4604.307 ms
postgres=# truncate table test;
TRUNCATE TABLE
Time: 31.278 ms
postgres=# insert into test (data) values (generate_series(1,200000));
INSERT 0 200000
Time: 4545.386 ms
postgres=# drop table test;
DROP TABLE
Time: 45.261 ms
postgres=#

shows a 10ms difference between truncate and drop.




-- 

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

In response to

pgsql-sql by date

Next:From: Jaime CasanovaDate: 2008-07-24 16:47:06
Subject: Re: postgres time zone settings
Previous:From: Emi LuDate: 2008-07-24 14:36:39
Subject: Re: truncate vs. delete

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group