pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Hartmut Raschick <hartmut.raschick@ke-elektronik.de>
To: pgsql-performance@postgresql.org
Subject: TRUNCATE veeeery slow compared to DELETE in 7.4
Date: Mon, 08 Dec 2003 15:03:34 +0100
Message-ID: <3FD484B6.EF39A92F@ke-elektronik.de> (raw)

has anyone else noticed a huge difference in "DELETE TABLE <lol>"
vs. "TRUNCATE <lol>" starting w/postgres 7.4?
putting aside details (num rows, indexes....): ca. 300 tables
(already empty if desired...) ALL to be emptied (via batch file).
here's a small "time pgsql -f kill_all" output:

DELETE:
1) 0.03u 0.04s 0:02.46 2.8% (already empty)
2) 0.05u 0.06s 0:01.19 9.2% (already empty)

TRUNCATE:
1) 0.10u 0.06s 6:58.66 0.0% (already empty, compile runnig simult.)
2) 0.10u 0.02s 2:51.71 0.0% (already empty)

lovely, innit?

settings in 7.4 (wal, shm...) are as for 7.3.x unless dead or (in their
7.4 default version) even higher.

glimpsing at the quantify output (of the truncate version) it looks
as if this is "for (i = 0; i < all; i++)" whereas (from exec. time)
delete does "\rm -rf"

is this a pay-off for autocommit gone away?
a conspiracy?
...what am i saying...

we are using TRUNCATE btw, because someone once noted that this was
"good style", saying: "yes, i want to empty the whole thing", not:
"oops! forgot the where-clause, sorry for your table!"

well, enlight me, please!

P.S.: Grammarians dispute - and the case is still before the courts.
        - Horace, Epistles (Ars Poetica)

-- 
Hartmut "Hardy" Raschick / Dept. t2
ke Kommunikations-Elektronik GmbH
Wohlenberstr. 3, 30179 Hannover
Phone: ++49 (0)511 6747-564
Fax: ++49 (0)511 6747-340
e-Mail: hartmut.raschick@ke-elektronik.de
http://www.ke-elektronik.de



view thread (5+ messages)  latest in thread

Message-ID: <3FD484B6.EF39A92F@ke-elektronik.de>
Permalink:  ../3FD484B6.EF39A92F@ke-elektronik.de/
Also on:    postgresql.org/message-id/3FD484B6.EF39A92F@ke-elektronik.de

 · 

reply

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: hartmut.raschick@ke-elektronik.de
  Subject: Re: TRUNCATE veeeery slow compared to DELETE in 7.4
  In-Reply-To: <3FD484B6.EF39A92F@ke-elektronik.de>

* 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