Date: Mon, 16 Jul 2007 16:39:45 -0500
From: "Jim C. Nasby" <decibel@decibel.org>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Adriaan van Os <postgres@microbizz.nl>, pgsql-performance@postgresql.org
Subject: Re: TRUNCATE TABLE
Message-ID: <20070716213944.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>
	<20070713173501.GD12109@alvh.no-ip.org>
	<162867790707131212g17cbc5a9q5d098dbf01c8e502@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="dKeYJkA19bNVVKdr"
Content-Disposition: inline
In-Reply-To: <162867790707131212g17cbc5a9q5d098dbf01c8e502@mail.gmail.com>
User-Agent: Mutt/1.5.13 (2006-08-11)


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

On Fri, Jul 13, 2007 at 09:12:34PM +0200, Pavel Stehule wrote:
> Hello,
>=20
> I tested speed difference between TRUNCATE TABLE and  DROP TABLE
> (tested on my notebook ext3 and Linux fedora 7):
>=20
> CREATE OR REPLACE FUNCTION test01() RETURNS SETOF double precision
> AS $$
> DECLARE t1 timestamp with time zone;
> BEGIN
>  CREATE TEMP TABLE foo(a integer);
>  FOR i IN 1..1000 LOOP
>    INSERT INTO foo SELECT 1 FROM generate_series(1,10000);
>    t1 :=3D clock_timestamp();
>    TRUNCATE TABLE foo;
>    RETURN NEXT EXTRACT('ms' FROM clock_timestamp()-t1);
>  END LOOP;
>  RETURN;
> END;
> $$ LANGUAGE plpgsql;
>=20
> CREATE OR REPLACE FUNCTION test02() RETURNS SETOF double precision
> AS $$
> DECLARE t1 timestamp with time zone;
> BEGIN
>  FOR i IN 1..1000 LOOP
>    EXECUTE 'CREATE TEMP TABLE foo(a integer);';
>    EXECUTE 'INSERT INTO foo SELECT 1 FROM generate_series(1,10000);';
>    t1 :=3D clock_timestamp();
>    EXECUTE 'DROP TABLE foo;';
>    RETURN NEXT EXTRACT('ms' FROM clock_timestamp()-t1);
>  END LOOP;
>  RETURN;
> END;
> $$ LANGUAGE plpgsql;

Are you sure you can ignore the added cost of an EXECUTE? I tried the follo=
wing as a test, but my repeatability sucks... :/

CREATE OR REPLACE FUNCTION test02() RETURNS SETOF double precision AS $$
DECLARE t1 timestamp with time zone;                                   =20
BEGIN                                                                  =20
  CREATE TEMP TABLE foo(a integer);                                    =20
  FOR i IN 1..1000 LOOP                                                =20
    EXECUTE 'INSERT INTO foo SELECT 1 FROM generate_series(1,10000)';  =20
    t1 :=3D clock_timestamp();                                           =
=20
    EXECUTE 'TRUNCATE TABLE foo';                                      =20
    RETURN NEXT EXTRACT('ms' FROM clock_timestamp()-t1);               =20
  END LOOP;                                                            =20
  RETURN;                                                              =20
END;=20
$$ LANGUAGE plpgsql;

decibel=3D# drop table foo;select count(*), min(t), max(t), avg(t), stddev_=
samp(t),stddev_pop(t) from test01() t(t);drop table foo;select count(*), mi=
n(t), max(t), avg(t), stddev_samp(t),stddev_pop(t) from test03() t(t);drop =
table foo;select count(*), min(t), max(t), avg(t), stddev_samp(t),stddev_po=
p(t) from test01() t(t);drop table foo;select count(*), min(t), max(t), avg=
(t), stddev_samp(t),stddev_pop(t) from test03() t(t);drop table foo;select =
count(*), min(t), max(t), avg(t), stddev_samp(t),stddev_pop(t) from test01(=
) t(t);drop table foo;select count(*), min(t), max(t), avg(t), stddev_samp(=
t),stddev_pop(t) from test03() t(t);
ERROR:  table "foo" does not exist
 count |  min  |   max    |   avg    |   stddev_samp    |    stddev_pop   =
=20
-------+-------+----------+----------+------------------+------------------
  1000 | 0.533 | 1405.747 | 3.444874 | 44.4166419484871 | 44.3944280726548
(1 row)

Time: 44945.101 ms
DROP TABLE
Time: 11.204 ms
 count |  min  |   max    |   avg    |   stddev_samp    |    stddev_pop   =
=20
-------+-------+----------+----------+------------------+------------------
  1000 | 0.446 | 1300.168 | 7.611269 | 79.7606049935278 | 79.7207147159672
(1 row)

Time: 44955.870 ms
DROP TABLE
Time: 148.186 ms
 count | min  |  max   |   avg    |   stddev_samp   |    stddev_pop   =20
-------+------+--------+----------+-----------------+------------------
  1000 | 0.46 | 21.585 | 1.991845 | 1.2259573313755 | 1.22534419938848
(1 row)

Time: 47566.985 ms
DROP TABLE
Time: 5.065 ms
 count |  min  |   max    |   avg    |   stddev_samp    |    stddev_pop   =
=20
-------+-------+----------+----------+------------------+------------------
  1000 | 0.479 | 1907.865 | 5.368207 | 73.8576562901696 | 73.8207182251985
(1 row)

Time: 48681.777 ms
DROP TABLE
Time: 7.863 ms
 count |  min  |   max    |   avg    |   stddev_samp   |   stddev_pop   =20
-------+-------+----------+----------+-----------------+-----------------
  1000 | 0.562 | 1009.578 | 2.998867 | 31.874023877249 | 31.858082879064
(1 row)

Time: 37426.441 ms
DROP TABLE
Time: 4.935 ms
 count | min  |  max   |   avg    |   stddev_samp    |    stddev_pop   =20
-------+------+--------+----------+------------------+------------------
  1000 | 0.42 | 20.721 | 2.064845 | 1.24241007069275 | 1.24178871027844
(1 row)

Time: 47906.628 ms
decibel=3D#=20
--=20
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

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

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

iD8DBQFGm+WgdO30qud8SkgRAqU9AJ48ezs6Hsj1K9TYqV5U6LhfPtT4BQCgiBKr
hfKZG3KyqYtV7XONyI592Og=
=89pO
-----END PGP SIGNATURE-----

--dKeYJkA19bNVVKdr--