From: Jim C. Nasby <decibel@decibel.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Adriaan van Os <postgres@microbizz.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: TRUNCATE TABLE
Date: Fri, 13 Jul 2007 12:12:47 -0500
Message-ID: <20070713171247.GA39272@nasby.net> (raw)
In-Reply-To: <23092.1184344246@sss.pgh.pa.us>
References: <46953949.3030201@microbizz.nl>
<87ps2ypox3.fsf@oxford.xeocode.com>
<46972DFA.9000900@microbizz.nl>
<20070713124435.GL31358@mathom.us>
<21495.1184337630@sss.pgh.pa.us>
<4697A58E.8060904@microbizz.nl>
<23092.1184344246@sss.pgh.pa.us>
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_I386 i386 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)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: decibel@decibel.org, tgl@sss.pgh.pa.us, postgres@microbizz.nl
Subject: Re: TRUNCATE TABLE
In-Reply-To: <20070713171247.GA39272@nasby.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox