Ответ 1
Это появилось несколько раз недавно, как в SO, так и в списках рассылки PostgreSQL.
TL; DR для двух последних точек:
(a) Большие shared_buffers могут быть причиной того, что TRUNCATE медленнее на сервере CI. Также может быть ошибкой другая конфигурация fsync или использование ротационных носителей вместо SSD.
(b) TRUNCATE имеет фиксированную стоимость, но не обязательно медленнее, чем DELETE, плюс это делает больше работы. См. Подробное объяснение, которое следует ниже.
ОБНОВЛЕНИЕ: значительное обсуждение pgsql-performance возникло из этой публикации. См. этот поток.
UPDATE 2: Усовершенствования были добавлены к 9.2beta3, которые должны помочь с этим, см. этот пост.
Подробное объяснение TRUNCATE vs DELETE FROM:
Пока я не эксперт по этой теме, я понимаю, что TRUNCATE имеет почти фиксированную стоимость за таблицу, а DELETE - не менее O (n) для n строк; хуже, если есть внешние ключи, ссылающиеся на удаляемую таблицу.
Я всегда предполагал, что фиксированная стоимость TRUNCATE была ниже стоимости DELETE на почти пустой таблице, но это совсем не так.
TRUNCATE table; делает больше, чем DELETE FROM table;
Состояние базы данных после TRUNCATE table будет таким же, как если бы вы запускали:
-
DELETE FROM table; -
VACCUUM (FULL, ANALYZE) table;(только для 9.0+, см. сноску)
... хотя, конечно, TRUNCATE фактически не достигает своих эффектов с помощью DELETE и a VACUUM.
Дело в том, что DELETE и TRUNCATE выполняют разные вещи, поэтому вы не просто сравниваете две команды с одинаковыми результатами.
A DELETE FROM table; позволяет сохранять мертвые строки и раздувание, позволяет индексировать нести мертвые записи, не обновляет статистику таблицы, используемую планировщиком запросов, и т.д.
A TRUNCATE дает вам совершенно новую таблицу и индексы, как если бы они были просто CREATE ed. Это похоже на то, что вы удалили все записи, переиндексировали таблицу и сделали VACUUM FULL.
Если вам все равно, осталось ли в таблице, потому что вы собираетесь снова залить его, вам может быть лучше использовать DELETE FROM table;.
Поскольку вы не используете VACUUM, вы обнаружите, что мертвые строки и записи индекса накапливаются как раздувание, которое необходимо отсканировать, а затем игнорировать; это замедляет все ваши запросы. Если ваши тесты на самом деле не создают и не удаляют все те данные, которые вы не заметите или не заботитесь, и вы всегда можете сделать VACUUM или два частичных путей через ваш тестовый прогон, если вы это сделаете. Лучше, пусть агрессивные настройки autovacuum гарантируют, что autovacuum сделает это для вас в фоновом режиме.
Вы все еще можете TRUNCATE поместить все свои таблицы после запуска всего тестового набора, чтобы убедиться, что во многих прогонах нет эффектов. На 9.0 и новее, VACUUM (FULL, ANALYZE); глобально на столе, по крайней мере, хорошо, если не лучше, и это намного проще.
IIRC Pg имеет несколько оптимизаций, которые означают, что он может заметить, когда ваша транзакция является единственной, которая может видеть таблицу и сразу же отмечать блоки как бесплатные. При тестировании, когда я хотел создать раздувание, мне пришлось иметь несколько одновременных подключений для этого. Я бы не стал полагаться на это.
DELETE FROM table; очень дешево для небольших таблиц без f/k refs
В DELETE все записи из таблицы без ссылок на внешние ключи, все Pg должны выполнить последовательное сканирование таблицы и установить xmax встречающихся кортежей. Это очень дешевая операция - в основном линейное чтение и полулинейная запись. AFAIK ему не нужно касаться индексов; они продолжают указывать на мертвые кортежи, пока они не будут очищены более поздним VACUUM, который также отмечает, что блоки в таблице содержат только мертвые кортежи как свободные.
DELETE становится дороже, если есть много записей, если есть много ссылок на внешние ключи, которые необходимо проверить, или если вы посчитаете следующий VACUUM (FULL, ANALYZE) table;, необходимый для соответствия TRUNCATE эффектам в пределах стоимости вашего DELETE.
В моих тестах здесь DELETE FROM table; обычно был в 4 раза быстрее, чем TRUNCATE со скоростью 0,5 мс против 2 мс. Это тестовая БД на SSD, работающая с fsync=off, потому что мне все равно, потеряю ли я все эти данные. Конечно, DELETE FROM table; не выполняет одну и ту же работу, и если я следую за VACUUM (FULL, ANALYZE) table;, это намного более дорогое 21 мс, поэтому DELETE - это только победа, если мне действительно не нужна таблица нетронутым.
TRUNCATE table; выполняет гораздо более фиксированную работу и работу, чем DELETE
Напротив, a TRUNCATE должен выполнять большую работу. Он должен выделять новые файлы для таблицы, ее таблицу TOAST, если таковые имеются, и каждый индекс, который имеет таблица. Заголовки должны быть записаны в эти файлы, и системные каталоги могут также нуждаться в обновлении (не уверен в этом, не проверял). Затем он должен заменить старые файлы на новые или удалить старые, и должен обеспечить, чтобы файловая система догнала изменения с помощью операции синхронизации - fsync() или аналогичной - обычно сбрасывает все буферы на диск, Я не уверен, пропускается ли синхронизация, если вы работаете с опцией (data-eating) fsync=off.
Недавно я узнал, что TRUNCATE должен также очистить все буферы PostgreSQL, связанные со старой таблицей. Это может занять нетривиальное количество времени с огромным shared_buffers. Я подозреваю, что это почему-то медленнее на вашем сервере CI.
Баланс
В любом случае вы можете видеть, что TRUNCATE таблицы, которая имеет связанную таблицу TOAST (большинство из них) и несколько индексов, может занять несколько минут. Не долго, но длиннее DELETE из почти пустой таблицы.
Следовательно, вам может быть лучше сделать DELETE FROM table;.
-
Примечание: в DB до 9.0 CLUSTER table_id_seq ON table; ANALYZE table; или VACUUM FULL ANALYZE table; REINDEX table; будет более близким к TRUNCATE. VACUUM FULL impl изменился на гораздо лучший в 9.0.