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