У меня есть таблица с примерно 2 миллионами строк транзакционных данных, которые мы используем для аналитики. Каждую неделю мы перезагружаем его новыми данными, поэтому мы использовали TRUNCATE, чтобы очистить его, а затем вставить новые строки.
На столе есть пара указателей. Если я не отбрасываю и не воссоздаю индексы, мне нужно будет переиндексировать после каждого усечения и повторного заполнения, или это не нужно? Должен ли я запускать VACUUM после TRUNCATE, или это тоже не нужно?
Нет, как правило, переиндексировать после TRUNCATE - и если вы это сделаете, вам будет гораздо лучше отбросить индексы, загрузить данные, а затем заново создать индексы в конце.
Это чем-то похоже на этот ответ о кластере - Pg автоматически сбрасывает индекс во время TRUNCATE а затем постепенно перестраивает его по мере вставки данных, чтобы не было раздувания индекса до TRUNCATE.
Вы можете получить несколько более компактные и эффективные индексы, если отбросите индексы, усечете, вставите данные и воссоздадите индексы. Их, безусловно, будут строить быстрее. Разница в производительности индексов после построения вряд ли будет достаточной, чтобы оправдать дополнительные усилия для большинства приложений, использующих только индексы в виде b-дерева, но разница во времени, необходимом для заполнения таблиц, может того стоить. Если вы используете GiST или (особенно) GIN, лучше всего отбросить индекс и в конце создать заново.
Если это удобно, отбросьте индексы и снова добавьте их в конце, просто не беспокойтесь слишком сильно, если это нецелесообразно для вас.
Для обычного b-дерева в моем тесте инкрементно созданный составной индекс составлял 3720 КБ по сравнению с разовым созданным индексом 2208 КБ. Время сборки составило 164 мс (вставки) + 347 мс (индекс) против 742 мс (вставки + индекс). Эта разница значительна, но недостаточна, чтобы вызывать серьезную озабоченность, если вы не делаете крупномасштабный DW. А REINDEX потребовалось еще 342 мс после выполнения вставки + индекса. Видеть
Итак, @TomTom прав (неудивительно) в том, что это может стоить отбрасывание и воссоздание индексы, если это удобно, например, при массовом заполнении таблиц для работы OLAP.
Тем не мение, переиндексация, вероятно, будет неправильным ответом поскольку это означает, что вы проделаете кучу дорогостоящей работы по созданию индекса, который затем выбросите. Отбросьте индекс и создайте заново вместо переиндексации.
Демо-сессия:
regress=# -- Create, populate, then create indexes:
regress=# CREATE TABLE demo (someint integer, sometext text);
CREATE TABLE
regress=# \timing on
regress=# INSERT INTO demo (someint, sometext)
SELECT x, (x%100)::text
FROM generate_series(1,100000) x;
INSERT 0 100000
Time: 164.678 ms
regress=# CREATE INDEX composite_idx ON demo(sometext, someint);
CREATE INDEX
Time: 347.958 ms
regress=# SELECT pg_size_pretty(pg_indexes_size('demo'::regclass));
pg_size_pretty
----------------
2208 kB
(1 row)
regress=# -- Total time: 347.958+164.678=512.636ms, index size 2208kB
regress=# -- Now, with truncate and insert:
regress=# TRUNCATE TABLE demo;
TRUNCATE TABLE
regress=# INSERT INTO demo (someint, sometext)
SELECT x, (x%100)::text
FROM generate_series(1,100000) x;
INSERT 0 100000
Time: 742.813 ms
regress=# SELECT pg_size_pretty(pg_indexes_size('demo'::regclass));
pg_size_pretty
----------------
3720 kB
(1 row)
regress=# -- Total time 742ms, index size 3720kB
regress=# -- Difference: about 44% time increase, about 68% index size increase.
regress=# -- Big-ish, but whether you care depends on your application. Now:
regress=# REINDEX INDEX composite_idx ;
REINDEX
Time: 342.283 ms
regress=# SELECT pg_size_pretty(pg_indexes_size('demo'::regclass));
pg_size_pretty
----------------
2208 kB
(1 row)
regress=# -- Index is back to same size, but total time for insert with progressive
regress=# -- index build plus reindex at the end us up to 1084.283, twice as long as
regress=# -- dropping the indexes, inserting the data, and re-creating the indexes took.
Так:
Для OLAP: удаление индексов, вставка, повторное создание индексов.
Для OLTP вы, вероятно, просто захотите придерживаться прогрессивных построений индекса. Рассмотрите возможность использования не 100% коэффициента заполнения индексов, чтобы снизить затраты на вставку.
Избегайте вставки с прогрессивными построениями индекса с последующим повторным индексированием, это худшее из обоих миров.
Конечно, размеры, используемые в этом тесте, соответствуют размерам игрушечных столов, поэтому вам следует повторить это тестирование на выборке ваших реальных данных и индексов чтобы получить четкое представление о том, насколько это важно для тебя. Я повторил эти тесты с коэффициентом масштабирования на 100 больше, чем указано выше, и последовательно обнаружил, что индекс почти в два раза больше, если строить инкрементально, хотя относительная разница во времени сборки фактически упала для этого конкретного теста.
Итак: Протестируйте свои данные и схему.