Re: DELETE vs TRUNCATE explanation

От: ktm@rice.edu
Тема: Re: DELETE vs TRUNCATE explanation
Дата: ,
Msg-id: 20120711141954.GJ15829@aart.rice.edu
(см: обсуждение, исходный текст)
Ответ на: Re: DELETE vs TRUNCATE explanation  (Tom Lane)
Список: 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, )

On Wed, Jul 11, 2012 at 10:05:48AM -0400, Tom Lane wrote:
> 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
>

+1 TRUNCATE needs to keep the same properties independent of the size
of the table. Smearing it into a DELETE would not be good at all. If
there are optimizations that can be done to keep its current behavior,
those might be possible, but the complexity may not be worthwhile for
a relative corner case.

Regards,
Ken


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

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