Re: DELETE vs TRUNCATE explanation

От: Matthew Woodcraft
Тема: Re: DELETE vs TRUNCATE explanation
Дата: ,
Msg-id: 20120711181037.GF11608@golux.woodcraft.me.uk
(см: обсуждение, исходный текст)
Ответ на: Re: DELETE vs TRUNCATE explanation  (Tom Lane)
Ответы: Re: DELETE vs TRUNCATE explanation  (Craig Ringer)
Список: pgsql-performance

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

DELETE vs TRUNCATE explanation  (Craig Ringer, )
 Re: DELETE vs TRUNCATE explanation  (Daniel Farina, )
  Re: DELETE vs TRUNCATE explanation  (Tom Lane, )
   Re: DELETE vs TRUNCATE explanation  ("", )
   Re: DELETE vs TRUNCATE explanation  (Matthew Woodcraft, )
    Re: DELETE vs TRUNCATE explanation  (Craig Ringer, )
   Re: DELETE vs TRUNCATE explanation  (Craig James, )
    Re: DELETE vs TRUNCATE explanation  (Shaun Thomas, )
     Re: DELETE vs TRUNCATE explanation  (Andrew Dunstan, )
    Re: DELETE vs TRUNCATE explanation  (Mark Thornton, )
     Re: DELETE vs TRUNCATE explanation  (Craig James, )
   Re: DELETE vs TRUNCATE explanation  (Daniel Farina, )
    Re: DELETE vs TRUNCATE explanation  (Craig Ringer, )
     Re: DELETE vs TRUNCATE explanation  (Daniel Farina, )
      Re: DELETE vs TRUNCATE explanation  (Craig Ringer, )
    Re: DELETE vs TRUNCATE explanation  (Jeff Janes, )
     Re: DELETE vs TRUNCATE explanation  ("Harold A. Giménez", )
      Re: DELETE vs TRUNCATE explanation  (Jeff Janes, )
      Re: DELETE vs TRUNCATE explanation  (Jeff Janes, )
  Re: DELETE vs TRUNCATE explanation  (Craig Ringer, )
 Proposed change for 9.3(?): Require full restart to change fsync parameter, not just pg_ctl reload  (Craig Ringer, )

Tom Lane wrote:
> (3) The performance of the truncation itself should not be viewed in
> isolation; subsequent behavior also needs to be considered.  An example
> of possible degradation is that index bloat would no longer be
> guaranteed to be cleaned up over a series of repeated truncations.
> (You might argue that if the table is small then the indexes couldn't
> be very bloated, but I don't think that holds up over a long series.)
>
> IOW, I think it's fine as-is.  I'd certainly wish to see many more
> than one complainant before we expend effort in this area.

I think a documentation change would be worthwhile.

At the moment the TRUNCATE page says, with no caveats, that it is faster than
unqualified DELETE.

It surprised me to find that this wasn't true (with 7.2, again with small
tables in a testsuite), and evidently it's still surprising people today.

-M-


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

От: Daniel Farina
Дата:
Сообщение: Re: DELETE vs TRUNCATE explanation
От: Craig Ringer
Дата:
Сообщение: Re: how could select id=xx so slow?