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, whole thread or download thread mbox)
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 ""
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index 
"test_pkey" for table "test"
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;
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;
Time: 45.261 ms

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-2017 The PostgreSQL Global Development Group