Re: TRUNCATE TABLE - Mailing list pgsql-performance

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
tablefoo;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)