Date: Fri, 13 Jul 2007 12:12:47 -0500
From: "Jim C. Nasby" <decibel@decibel.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Adriaan van Os <postgres@microbizz.nl>,
	pgsql-performance@postgresql.org
Subject: Re: TRUNCATE TABLE
Message-ID: <20070713171247.GA39272@nasby.net>
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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="RpDyejMaDGJhP2PU"
Content-Disposition: inline
In-Reply-To: <23092.1184344246@sss.pgh.pa.us>
User-Agent: Mutt/1.5.13 (2006-08-11)


--RpDyejMaDGJhP2PU
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

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).
>=20
> Can you try it with plain psql?  pgAdmin is a variable that wasn't
> accounted for in my tests.
>=20
> > The hardware is an Intel dual-core 17-inch
> > MacBook Pro running Mac OS X 10.4.
>=20
> 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.
>=20
> Does anyone else want to try replicating these tests?

The following is consistently between 1 and 3 ms:
decibel=3D# create table i as select * from generate_series(1,20000) i; dro=
p table i;
SELECT
Time: 42.413 ms
DROP TABLE
Time: 1.415 ms
decibel=3D# select version();
                                                                  version  =
                                                                =20
---------------------------------------------------------------------------=
-----------------------------------------------------------------
 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=3D# \! 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=3D#=20

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

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

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

--RpDyejMaDGJhP2PU
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.3 (FreeBSD)

iD8DBQFGl7KPdO30qud8SkgRAlDCAKCyunTHPspz2qWpFvqt/0JWB2HS2ACgp6ZM
Rga/cZIwqPBhQBlAA3VE8y0=
=zml+
-----END PGP SIGNATURE-----

--RpDyejMaDGJhP2PU--