Re: truncate vs. delete

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