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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
