Re: [PERFORM] TRUNCATE TABLE

Decibel! <[EMAIL PROTECTED]> writes: > Interesting. I'm guessing that ext3 has to sync out the entire journal > up to the point in time that fsync() is called, regardless of what > files/information the journal contains. Fortunately I think it's common > knowledge to mount PostgreSQL filesystems wi

Re: [PERFORM] TRUNCATE TABLE

On Sat, Aug 04, 2007 at 11:39:31PM +0200, Adriaan van Os wrote: > Kevin Grittner wrote: > On Mon, Jul 16, 2007 at 7:18 PM, in message > <[EMAIL PROTECTED]>, > >Tom Lane <[EMAIL PROTECTED]> wrote: > >>Somehow, autovac is doing something that makes the filesystem go nuts > >>every so often

Re: [PERFORM] TRUNCATE TABLE

Kevin Grittner wrote: On Mon, Jul 16, 2007 at 7:18 PM, in message <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> wrote: Somehow, autovac is doing something that makes the filesystem go nuts every so often, and take an astonishingly long time to create an empty file. But autovac itself does

Re: [PERFORM] TRUNCATE TABLE

Adriaan van Os wrote: > Tom Lane wrote: > >> Somehow, autovac is doing something that makes the filesystem go nuts >> every so often, and take an astonishingly long time to create an empty >> file. But autovac itself doesn't create or delete any files, so what's >> up here? >> Also, I was able to

Re: [PERFORM] TRUNCATE TABLE

>>> On Mon, Jul 16, 2007 at 7:18 PM, in message <[EMAIL PROTECTED]>, Tom Lane <[EMAIL PROTECTED]> wrote: > Somehow, autovac is doing something that makes the filesystem go nuts > every so often, and take an astonishingly long time to create an empty > file. But autovac itself doesn't create or d

Re: [PERFORM] TRUNCATE TABLE

Tom Lane wrote: Somehow, autovac is doing something that makes the filesystem go nuts every so often, and take an astonishingly long time to create an empty file. But autovac itself doesn't create or delete any files, so what's up here? Also, I was able to reproduce the variability in timing o

Re: [PERFORM] TRUNCATE TABLE

Tom Lane wrote: Thus the timing issue (at least as exhibited by this script) has nothing whatever to do with the time to delete a file, but with the time to create one. Since the part of DROP being timed has probably got no I/O involved at all (the tuples being touched are almost surely still i

Re: [PERFORM] TRUNCATE TABLE

"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Fri, Jul 13, 2007 at 09:12:34PM +0200, Pavel Stehule wrote: >> I tested speed difference between TRUNCATE TABLE and DROP TABLE >> (tested on my notebook ext3 and Linux fedora 7): > Are you sure you can ignore the added cost of an EXECUTE? I tried th

Re: [PERFORM] TRUNCATE TABLE

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 wit

Re: [PERFORM] TRUNCATE TABLE

Tom Lane wrote: Adriaan van Os <[EMAIL PROTECTED]> writes: I started another test. I copied an existing database (not very large, 35 tables, typically a few hundred up to a few thousand records) with CREATE DATABASE testdb TEMPLATE mydb and started to remove random tables from testdb with DROP T

Re: [PERFORM] TRUNCATE TABLE

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

Re: [PERFORM] TRUNCATE TABLE

Tom Lane wrote: > Adriaan van Os <[EMAIL PROTECTED]> writes: > > I started another test. I copied an existing database (not very large, > > 35 tables, typically a few hundred up to a few thousand records) with > > CREATE DATABASE testdb TEMPLATE mydb and started to remove random > > tables from tes

Re: [PERFORM] TRUNCATE TABLE

On Fri, Jul 13, 2007 at 12:30:46PM -0400, Tom Lane wrote: > Adriaan van Os <[EMAIL PROTECTED]> writes: > > I started another test. I copied an existing database (not very large, > > 35 tables, typically a few hundred up to a few thousand records) with > > CREATE DATABASE testdb TEMPLATE mydb and st

Re: [PERFORM] TRUNCATE TABLE

Adriaan van Os <[EMAIL PROTECTED]> writes: > I started another test. I copied an existing database (not very large, > 35 tables, typically a few hundred up to a few thousand records) with > CREATE DATABASE testdb TEMPLATE mydb and started to remove random > tables from testdb with DROP TABLE and TR

Re: [PERFORM] TRUNCATE TABLE

On Fri, Jul 13, 2007 at 06:17:18PM +0200, Adriaan van Os wrote: > The hardware is an Intel dual-core 17-inch MacBook Pro running Mac > OS X 10.4. To isolate things, have you tried testing a different operating system? /* Steinar */ -- Homepage: http://www.sesse.net/ ---

Re: [PERFORM] TRUNCATE TABLE

Tom Lane wrote: Michael Stone <[EMAIL PROTECTED]> writes: xfs' slowness is proportional to the *number* rather than the *size* of the files. In postgres you'll tend to have fewer, larger, files than you would in (e.g.) a source code repository, so it is generally more important to have a files

Re: [PERFORM] TRUNCATE TABLE

Michael Stone <[EMAIL PROTECTED]> writes: > xfs' slowness is proportional to the *number* rather than the *size* of > the files. In postgres you'll tend to have fewer, larger, files than you > would in (e.g.) a source code repository, so it is generally more > important to have a filesystem that

Re: [PERFORM] TRUNCATE TABLE

On Fri, Jul 13, 2007 at 09:47:06AM +0200, Adriaan van Os wrote: That's a remarkable advice, because XFS is known to be slow at creating and deleting files, see and . xfs' slowness is proportional to the *number

Re: [PERFORM] TRUNCATE TABLE

On 7/13/07, Jean-Max Reymond <[EMAIL PROTECTED]> wrote: Adriaan van Os a écrit : > That's a remarkable advice, because XFS is known to be slow at creating > and deleting files, see and > . > date of article: Fri

Re: [PERFORM] TRUNCATE TABLE

Adriaan van Os a écrit : That's a remarkable advice, because XFS is known to be slow at creating and deleting files, see and . date of article: Fri Jul 25 2003 ! ---(end of broadcast

Re: [PERFORM] TRUNCATE TABLE

"Adriaan van Os" <[EMAIL PROTECTED]> writes: > That's a remarkable advice, because XFS is known to be slow at creating and > deleting files, see and > . I think this is a case of "you're both right". XFS may have

Re: [PERFORM] TRUNCATE TABLE

Gregory Stark wrote: What filesystem is this? Ext3 on Fedora Linux. 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. That's a remarkable advice, because XFS is known to be slow at cre

Re: [PERFORM] TRUNCATE TABLE

Adriaan van Os <[EMAIL PROTECTED]> 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

Re: [PERFORM] TRUNCATE TABLE

Gregory Stark wrote: That's strange. Deleting should be the *quickest* operation in Postgres. Do you perchance have foreign key references referencing this table? No. Do you have any triggers? No. Tom Lane wrote: Adriaan van Os <[EMAIL PROTECTED]> writes: Surprisingly, one of the bottlen

Re: [PERFORM] TRUNCATE TABLE

"Adriaan van Os" <[EMAIL PROTECTED]> 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

Re: [PERFORM] TRUNCATE TABLE

Adriaan van Os <[EMAIL PROTECTED]> writes: > Surprisingly, one of the bottlenecks is TRUNCATE TABLE and that > command is really slow as compared to other operations. 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 slo

[PERFORM] TRUNCATE TABLE

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 op