Re: TRUNCATE TABLE - Mailing list pgsql-performance

From Jim C. Nasby
Subject Re: TRUNCATE TABLE
Date
Msg-id 20070713171247.GA39272@nasby.net
Whole thread Raw
In response to Re: TRUNCATE TABLE  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
On Fri, Jul 13, 2007 at 12:30:46PM -0400, Tom Lane wrote:
> Adriaan van Os <postgres@microbizz.nl> writes:
> > I started another test. I copied an existing database (not very large,
> > 35 tables, typically a few hundred up to a few thousand records) with
> > CREATE DATABASE testdb TEMPLATE mydb and started to remove random
> > tables from testdb with DROP TABLE and TRUNCATE TABLE. I did this with
> > the query tool of pgAdmin III, to exclude any doubts about my own
> > software (that uses pqlib).
>
> Can you try it with plain psql?  pgAdmin is a variable that wasn't
> accounted for in my tests.
>
> > The hardware is an Intel dual-core 17-inch
> > MacBook Pro running Mac OS X 10.4.
>
> Hmm.  I thought you said Fedora before.  However, I'd done a few tests
> yesterday on my own Mac laptop (Al G4) and not gotten results that were
> out of line with HPUX or Fedora.
>
> Does anyone else want to try replicating these tests?

The following is consistently between 1 and 3 ms:
decibel=# create table i as select * from generate_series(1,20000) i; drop table i;
SELECT
Time: 42.413 ms
DROP TABLE
Time: 1.415 ms
decibel=# select version();
                                                                  version
                    

--------------------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.3devel on i386-apple-darwin8.10.1, compiled by GCC i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple
Computer,Inc. build 5363) 
(1 row)

Time: 46.870 ms
decibel=# \! uname -a
Darwin platter.local 8.10.1 Darwin Kernel Version 8.10.1: Wed May 23 16:33:00 PDT 2007;
root:xnu-792.22.5~1/RELEASE_I386i386 i386 
decibel=#

Truncate is a different story... this is consistently either 6 something ms or
17 something ms:

decibel=# insert into i select generate_series(1,20000); truncate i;
INSERT 0 20000
Time: 600.940 ms
TRUNCATE TABLE
Time: 6.313 ms
decibel=#

This is on a 17" MBP, fsync turned on.
--
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

Attachment

pgsql-performance by date:

Previous
From: smiley2211
Date:
Subject: Database Statistics???
Next
From: Alvaro Herrera
Date:
Subject: Re: TRUNCATE TABLE