Re: TRUNCATE TABLE

From: Adriaan van Os
Subject: Re: TRUNCATE TABLE
Date: ,
Msg-id: 469DC30D.3060603@microbizz.nl
(view: Whole thread, Raw)
In response to: Re: TRUNCATE TABLE  (Tom Lane)
List: pgsql-performance

Tree view

TRUNCATE TABLE  (Adriaan van Os, )
 Re: TRUNCATE TABLE  (Tom Lane, )
  Re: TRUNCATE TABLE  (Adriaan van Os, )
   Re: TRUNCATE TABLE  (Tom Lane, )
 Re: TRUNCATE TABLE  (Gregory Stark, )
  Re: TRUNCATE TABLE  (Adriaan van Os, )
   Re: TRUNCATE TABLE  (Gregory Stark, )
   Re: TRUNCATE TABLE  (Jean-Max Reymond, )
    Re: TRUNCATE TABLE  ("Thomas Samson", )
   Re: TRUNCATE TABLE  (Michael Stone, )
    Re: TRUNCATE TABLE  (Tom Lane, )
     Re: TRUNCATE TABLE  (Adriaan van Os, )
      Re: TRUNCATE TABLE  ("Steinar H. Gunderson", )
      Re: TRUNCATE TABLE  (Tom Lane, )
       Re: TRUNCATE TABLE  ("Jim C. Nasby", )
       Re: TRUNCATE TABLE  (Alvaro Herrera, )
        Re: TRUNCATE TABLE  ("Pavel Stehule", )
         Re: TRUNCATE TABLE  ("Jim C. Nasby", )
          Re: TRUNCATE TABLE  (Tom Lane, )
           Re: TRUNCATE TABLE  (Adriaan van Os, )
           Re: TRUNCATE TABLE  (Adriaan van Os, )
            Re: TRUNCATE TABLE  (Alvaro Herrera, )
           Re: TRUNCATE TABLE  ("Kevin Grittner", )
            Re: TRUNCATE TABLE  (Adriaan van Os, )
             Re: TRUNCATE TABLE  (Decibel!, )
              Re: TRUNCATE TABLE  (Tom Lane, )
       Re: TRUNCATE TABLE  (Adriaan van Os, )

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 in
> shared buffers), it's unsurprising that it is consistently fast.

In my original profiling, CREATE TEMPORARY TABLE/DROP TABLE wasn't much faster than TRUNCATE TABLE.
When I try it again now, I see that DROP TABLE is consistently fast, while the timings of CREATE
TEMPORARY TABLE vary as much as those of TRUNCATE TABLE. Your observations on the time needed to
open a file confirm that, I think.

In my test databases, autovacuum is off.

Regards,

Adriaan van Os


pgsql-performance by date:

From: "Michael Dengler"
Date:
Subject: How to use a trigger to write rows to a remote server
From: Heikki Linnakangas
Date:
Subject: Re: How to use a trigger to write rows to a remote server