Re: DELETE vs TRUNCATE explanation

От: Tom Lane
Тема: Re: DELETE vs TRUNCATE explanation
Дата: ,
Msg-id: 23853.1342015548@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: DELETE vs TRUNCATE explanation  (Daniel Farina)
Ответы: Re: DELETE vs TRUNCATE explanation  ("")
Re: DELETE vs TRUNCATE explanation  (Matthew Woodcraft)
Re: DELETE vs TRUNCATE explanation  (Craig James)
Re: DELETE vs TRUNCATE explanation  (Daniel Farina)
Список: 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, )

Daniel Farina <> writes:
> TRUNCATE should simply be very nearly the fastest way to remove data
> from a table while retaining its type information, and if that means
> doing DELETE without triggers when the table is small, then it should.
>  The only person who could thwart me is someone who badly wants their
> 128K table to be exactly 8 or 0K, which seems unlikely given the 5MB
> of catalog anyway.

> Does that sound reasonable?  As in, would anyone object if TRUNCATE
> learned this behavior?

Yes, I will push back on that.

(1) We don't need the extra complexity.

(2) I don't believe that you know where the performance crossover point
would be (according to what metric, anyway?).

(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.

            regards, tom lane


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

От: Matthew Woodcraft
Дата:
Сообщение: Re: DELETE vs TRUNCATE explanation
От: Craig James
Дата:
Сообщение: Re: DELETE vs TRUNCATE explanation