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