Skip site navigation (1) Skip section navigation (2)

Re: TRUNCATE TABLE

From: "Jim C(dot) Nasby" <decibel(at)decibel(dot)org>
To: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>,Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,Adriaan van Os <postgres(at)microbizz(dot)nl>,pgsql-performance(at)postgresql(dot)org
Subject: Re: TRUNCATE TABLE
Date: 2007-07-16 21:39:45
Message-ID: 20070716213944.GA39272@nasby.net (view raw or flat)
Thread:
Lists: 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 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)

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2007-07-17 00:18:18
Subject: Re: TRUNCATE TABLE
Previous:From: Jim C. NasbyDate: 2007-07-16 21:02:11
Subject: Re: FORGOT TO CONFIGURE RAID! DELL POWEREDGE 2950

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group