From: Jim C. Nasby <decibel@decibel.org>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Alvaro Herrera <alvherre@commandprompt.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Adriaan van Os <postgres@microbizz.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: TRUNCATE TABLE
Date: Mon, 16 Jul 2007 16:39:45 -0500
Message-ID: <20070716213944.GA39272@nasby.net> (raw)
In-Reply-To: <162867790707131212g17cbc5a9q5d098dbf01c8e502@mail.gmail.com>
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>
On Fri, Jul 13, 2007 at 09:12:34PM +0200, Pavel Stehule wrote:
> Hello,
>
> I tested speed difference between TRUNCATE TABLE and DROP TABLE
> (tested on my notebook ext3 and Linux fedora 7):
>
> 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 := clock_timestamp();
> TRUNCATE TABLE foo;
> RETURN NEXT EXTRACT('ms' FROM clock_timestamp()-t1);
> END LOOP;
> RETURN;
> END;
> $$ LANGUAGE plpgsql;
>
> 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 := 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 following as a test, but my repeatability sucks... :/
CREATE OR REPLACE FUNCTION test02() RETURNS SETOF double precision AS $$
DECLARE t1 timestamp with time zone;
BEGIN
CREATE TEMP TABLE foo(a integer);
FOR i IN 1..1000 LOOP
EXECUTE 'INSERT INTO foo SELECT 1 FROM generate_series(1,10000)';
t1 := clock_timestamp();
EXECUTE 'TRUNCATE TABLE foo';
RETURN NEXT EXTRACT('ms' FROM clock_timestamp()-t1);
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
decibel=# 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);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);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
-------+-------+----------+----------+------------------+------------------
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
-------+-------+----------+----------+------------------+------------------
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
-------+------+--------+----------+-----------------+------------------
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
-------+-------+----------+----------+------------------+------------------
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
-------+-------+----------+----------+-----------------+-----------------
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
-------+------+--------+----------+------------------+------------------
1000 | 0.42 | 20.721 | 2.064845 | 1.24241007069275 | 1.24178871027844
(1 row)
Time: 47906.628 ms
decibel=#
--
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, pavel.stehule@gmail.com, alvherre@commandprompt.com, tgl@sss.pgh.pa.us, postgres@microbizz.nl
Subject: Re: TRUNCATE TABLE
In-Reply-To: <20070716213944.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