X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 02C09D1B445 for ; Mon, 8 Dec 2003 14:04:48 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 26196-04 for ; Mon, 8 Dec 2003 10:04:19 -0400 (AST) Received: from smtp02do.de.uu.net (smtp02do.de.uu.net [192.76.144.69]) by svr1.postgresql.org (Postfix) with ESMTP id D29F0D1B4B1 for ; Mon, 8 Dec 2003 10:04:14 -0400 (AST) Received: from bommel.kecam-han.de ([193.99.158.1]) by smtp02do.de.uu.net (8.9.3p2/5.5.5) with ESMTP id PAA22793 for ; Mon, 8 Dec 2003 15:04:17 +0100 (MET) Received: from mailrelay.kecam-han.de (ldap [10.9.1.54]) by bommel.kecam-han.de (8.11.6+Sun/8.9.1) with ESMTP id hB8E4c609689 for ; Mon, 8 Dec 2003 15:04:38 +0100 (MET) Received: from ke-elektronik.de (localhost [127.0.0.1]) by mailrelay.kecam-han.de (8.12.2/8.12.2) with ESMTP id hB8E3ZUr019106 for ; Mon, 8 Dec 2003 15:03:55 +0100 (MET) Message-ID: <3FD484B6.EF39A92F@ke-elektronik.de> Date: Mon, 08 Dec 2003 15:03:34 +0100 From: Hartmut Raschick Organization: ke Kommunikations-Elektronik GmbH X-Mailer: Mozilla 4.79 [en] (X11; U; SunOS 5.6 sun4u) X-Accept-Language: de, ru, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: TRUNCATE veeeery slow compared to DELETE in 7.4 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200312/162 X-Sequence-Number: 5022 has anyone else noticed a huge difference in "DELETE TABLE " vs. "TRUNCATE " 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