Re: TRUNCATE TABLE

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;

vacuum pg_class; vacuum pg_type; vacuum pg_attribute;

postgres=# select count(*), min(t), max(t), avg(t), stddev_samp(t),
stddev_pop(t) from test01() t(t);
count | min | max | avg | stddev_samp | stddev_pop
-------+-------+---------+----------+------------------+------------------
1000 | 0.295 | 803.971 | 3.032483 | 30.0036729610037 | 29.9886673721876
(1 row)

Time: 33826,841 ms
postgres=# select count(*), min(t), max(t), avg(t), stddev_samp(t),
stddev_pop(t) from test02() t(t);
count | min | max | avg | stddev_samp | stddev_pop
-------+-------+--------+----------+------------------+-------------------
1000 | 0.418 | 20.792 | 0.619168 | 0.81550718804297 | 0.815099332459549
(1 row)

Time: 33568,818 ms

It's true, stddev_samp(TRUNCATE) >> stddev_samp(DROP)

Regards
Pavel Stehule