From: Gregory Stark <stark@enterprisedb.com>
To: Adriaan van Os <postgres@microbizz.nl>
Cc: pgsql-performance@postgresql.org
Subject: Re: TRUNCATE TABLE
Date: Wed, 11 Jul 2007 23:15:52 +0100
Message-ID: <87ps2ypox3.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <46953949.3030201@microbizz.nl>
References: <46953949.3030201@microbizz.nl>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stark@enterprisedb.com, postgres@microbizz.nl
Subject: Re: TRUNCATE TABLE
In-Reply-To: <87ps2ypox3.fsf@oxford.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox