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 <daniel@heroku.com>)
Ответы Re: DELETE vs TRUNCATE explanation  ("ktm@rice.edu" <ktm@rice.edu>)
Re: DELETE vs TRUNCATE explanation  (Matthew Woodcraft <matthew@woodcraft.me.uk>)
Re: DELETE vs TRUNCATE explanation  (Craig James <cjames@emolecules.com>)
Re: DELETE vs TRUNCATE explanation  (Daniel Farina <daniel@heroku.com>)
Список pgsql-performance
Daniel Farina <daniel@heroku.com> 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 по дате отправления:

Предыдущее
От: Ants Aasma
Дата:
Сообщение: Re: how could select id=xx so slow?
Следующее
От: "ktm@rice.edu"
Дата:
Сообщение: Re: DELETE vs TRUNCATE explanation