Re: TRUNCATE TABLE

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(at)decibel(dot)org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)