From: Gregory Stark <stark@enterprisedb.com>
To: "Adriaan van Os" <postgres@microbizz.nl>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: TRUNCATE TABLE
In-Reply-To: <46953949.3030201@microbizz.nl> (Adriaan van Os's message of
	"Wed, 11 Jul 2007 22:10:49 +0200")
Organization: EnterpriseDB
References: <46953949.3030201@microbizz.nl>
Date: Wed, 11 Jul 2007 23:15:52 +0100
Message-ID: <87ps2ypox3.fsf@oxford.xeocode.com>
User-Agent: Gnus/5.110006 (No Gnus v0.6) Emacs/21.4 (gnu/linux)
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

"Adriaan van Os" <postgres@microbizz.nl> writes:

> Recently, I have been doing extensive profiling of a version 8.1.4 Postgres DB
> with about 175 tables and 5 GB of data (the server running on Fedora Linux and
> the clients on Windows XP). Surprisingly, one of the bottlenecks is TRUNCATE
> TABLE and that command is really slow as compared to other operations. For
> example, we have operations like:

What filesystem is this? Some filesystems are notoriously slow at deleting
large files. The mythtv folk who face this problem regularly recommend either
JFS or XFS for this purpose.

Postgres generally doesn't really need to be able to delete large files
quickly. The only times files are deleted which come to mind are when you DROP
or TRUNCATE or possibly when you VACUUM a table.

> I noticed, by the way, that removing records in general is painfully slow, but
> I didn't do a detailed analysis of that issue yet.

That's strange. Deleting should be the *quickest* operation in Postgres. Do
you perchance have foreign key references referencing this table? Do you have
any triggers?

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com