To: Adriaan van Os <postgres@microbizz.nl>
cc: pgsql-performance@postgresql.org
Subject: Re: TRUNCATE TABLE 
In-reply-to: <4695DA44.2060000@microbizz.nl> 
References: <46953949.3030201@microbizz.nl> <3561.1184190885@sss.pgh.pa.us>
	<4695DA44.2060000@microbizz.nl>
Comments: In-reply-to Adriaan van Os <postgres@microbizz.nl>
	message dated "Thu, 12 Jul 2007 09:37:40 +0200"
Date: Thu, 12 Jul 2007 15:20:02 -0400
Message-ID: <9071.1184268002@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Adriaan van Os <postgres@microbizz.nl> writes:
> Tom Lane wrote:
>> When you don't quantify that statement at all, it's hard to make an
>> intelligent comment on it, but TRUNCATE per se shouldn't be slow.

> Below are some timings, in milliseconds.

I can only conclude that you're using a seriously bad filesystem :-(

I tried to replicate your results on a fairly old and slow HPUX box.
I get a fairly repeatable time of around 40msec to truncate a table;
this is presumably mostly filesystem time to create one file and delete
another.  I used CVS HEAD for this because the devel version of psql
supports reporting \timing for \copy commands, but I'm quite sure that
TRUNCATE isn't any faster than it was in 8.2:

regression=# create table tab(f1 int);
CREATE TABLE
Time: 63.775 ms
regression=# insert into tab select random()*10000 from generate_series(1,5000);
INSERT 0 5000
Time: 456.011 ms
regression=# \copy tab to 'tab.data' binary
Time: 80.343 ms
regression=# truncate table tab;
TRUNCATE TABLE
Time: 35.825 ms
regression=# \copy tab from 'tab.data' binary
Time: 391.928 ms
regression=# select count(*) from tab;
 count 
-------
  5000
(1 row)

Time: 21.457 ms
regression=# truncate table tab;
TRUNCATE TABLE
Time: 47.867 ms
regression=# \copy tab from 'tab.data' binary
Time: 405.074 ms
regression=# select count(*) from tab;
 count 
-------
  5000
(1 row)

Time: 20.247 ms

If I increase the test size to 200K rows, I get a proportional increase
in the copy and select times, but truncate stays about the same:

regression=# truncate table tab;
TRUNCATE TABLE
Time: 40.196 ms
regression=# \copy tab from 'tab.data' binary
Time: 15779.689 ms
regression=# select count(*) from tab;
 count  
--------
 200000
(1 row)

Time: 642.965 ms

Your numbers are not making any sense to me.  In particular there is no
reason in the Postgres code for it to take longer to truncate a 200K-row
table than a 5K-row table.  (I would expect some increment at the point
of having 1GB in the table, where we'd create a second table segment
file, but you are nowhere near that.)

The bottom line seems to be that you have a filesystem that takes a
long time to delete a file, with the cost rising rapidly as the file
gets bigger.  Can you switch to a different filesystem?

			regards, tom lane