Обсуждение: truncate vs. delete
Good morning, If I remember correctly, "delete" does not release space, while truncate will. I have an option now (1) Use object creator(with create/drop permission which I do not need in my cronjob script) to truncate table1(>100,000 recs) records (2) Use user1(has r/w only) to delete from table1, then vacuum it May I know how inefficient "delete from" comparing to truncate please? Thanks a lot!
I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well? http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html Emi Lu wrote: > Good morning, > > If I remember correctly, "delete" does not release space, while truncate > will. > > I have an option now > > (1) Use object creator(with create/drop permission which I do not need > in my cronjob script) to truncate table1(>100,000 recs) records > > (2) Use user1(has r/w only) to delete from table1, then vacuum it > > May I know how inefficient "delete from" comparing to truncate please? > > Thanks a lot! > > >
am Thu, dem 24.07.2008, um 9:47:48 -0400 mailte Emi Lu folgendes: > I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well? > http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html Not realy, for instance, pg can rollback a truncate, and a sequence are not reset. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
The truncate is more fast to delete, the truncate command not scan the table http://www.postgresql.org/docs/8.3/static/sql-truncate.html --- On Thu, 7/24/08, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote: > From: A. Kretschmer <andreas.kretschmer@schollglas.com> > Subject: Re: [SQL] truncate vs. delete > To: pgsql-sql@postgresql.org > Date: Thursday, July 24, 2008, 1:53 PM > am Thu, dem 24.07.2008, um 9:47:48 -0400 mailte Emi Lu > folgendes: > > I found a link for SQL Server, it applies to > PostgreSQL 8.0.x as well? > > > http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html > > Not realy, for instance, pg can rollback a truncate, and a > sequence are > not reset. > > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: > -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA > http://wwwkeys.de.pgp.net > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
A. Kretschmer wrote: > am Thu, dem 24.07.2008, um 9:47:48 -0400 mailte Emi Lu folgendes: >> I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well? >> http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html > > Not realy, for instance, pg can rollback a truncate, and a sequence are > not reset. > > 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 ke vacuum it (2) drop table (this table has no trigger, no foreign key) re-create table (without primary key) load new data setupprimary key vacuum it suggestions PLEASE? Thanks a lot!
am Thu, dem 24.07.2008, um 10:01:46 -0400 mailte Emi Lu folgendes: > A. Kretschmer wrote: > >am Thu, dem 24.07.2008, um 9:47:48 -0400 mailte Emi Lu folgendes: > >>I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well? > >>http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html > > > >Not realy, for instance, pg can rollback a truncate, and a sequence are > >not reset. > > > > > 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?) not required > drop primary key > load new data > load primary ke > vacuum it analyse it, instead vacuum. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
n Thu, Jul 24, 2008 at 7:53 AM, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote: > am Thu, dem 24.07.2008, um 9:47:48 -0400 mailte Emi Lu folgendes: >> I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well? >> http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html > > Not realy, for instance, pg can rollback a truncate, and a sequence are > not reset. Also you can truncate a table with foreign key references (using cascade), something SQL Server apparently can't do either.
A. Kretschmer wrote: > am Thu, dem 24.07.2008, um 10:01:46 -0400 mailte Emi Lu folgendes: >> A. Kretschmer wrote: >>> am Thu, dem 24.07.2008, um 9:47:48 -0400 mailte Emi Lu folgendes: >>>> I found a link for SQL Server, it applies to PostgreSQL 8.0.x as well? >>>> http://vadivel.blogspot.com/2004/06/delete-vs-truncate-statement.html >>> Not realy, for instance, pg can rollback a truncate, and a sequence are >>> not reset. >>> >>> >> 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?) > > not required > > >> drop primary key >> load new data >> load primary ke >> vacuum it > > analyse it, instead vacuum. It gets more and more clear to me know! I guess I need only do analyze(primary key column) after loading data. The new picture will be: . truncate table . drop primary key . load data . set primary key . analyze interesting columns
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