От: Adriaan van Os
Тема: TRUNCATE TABLE
Дата: ,
Msg-id: 46953949.3030201@microbizz.nl
(см: обсуждение, исходный текст)
Ответы: Re: TRUNCATE TABLE  (Tom Lane)
Re: TRUNCATE TABLE  (Gregory Stark)
Список: pgsql-performance

Скрыть дерево обсуждения

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, )

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:

TRUNCATE TABLE my_temporary_table
COPY my_temporary_table ... FROM STDIN BINARY
do_something

where do_something is using the data in my_temporary_table to do something like a JOIN or a mass
UPDATE or whatever.

Now, it turns out that typically most time is lost in TRUNCATE TABLE, in fact it spoils the
performance of most operations on the DB !

I read in a mailing list archive that TRUNCATE TABLE is slow since it was made transaction-safe
somewhere in version 7, but for operations on a temporary table (with data coming from the outside
world) that is irrelevant, at least for my application, in casu, a middleware software package.

So, my questions are

1. Why is TRUNCATE TABLE so slow (even if transaction-safe)
2. Is there is way to dig up in the source code somewhere a quick-and-dirty TRUNCATE TABLE
alternative for operations on temporary tables that need not be transaction-safe (because the
middleware itself can easily restore anything that goes wrong there).

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.

As an alternative to TRUNCATE TABLE I tried to CREATE and DROP a table, but that wasn't any faster.

Sincerely,

Adriaan van Os


В списке pgsql-performance по дате сообщения:

От: Greg Smith
Дата:
Сообщение: Estimating WAL volume
От: "A. Kretschmer"
Дата:
Сообщение: Re: bitmap-index-scan slower than normal index scan