Блокирует ли процесс построения индексов таблицу?

Не могу найти в документации.
Проблема такая. Есть таблица на 100 млн записей. Мне потребовалось добавить одно поле и потом я решил заполнить его сделав
UPDATE (SET a = 'foo' where field и LIKE '%pattern%')
по нему.
Перед UPDATE я индекс по нему сделал, и как понимаю это была большая ошибка.

В итоге прошли сутки, у меня загружено одно ядро и все другие UPDATE запросы (не связанные с этим полем обрабатываются по 10 минут).

Отсюда вопрос:
1. Правильно ли я понимаю что hash индексы не могут использовать сразу все ядра
1.1 а btree могут?
2. Как быть в текущей ситуации. Я понимаю что индекс нужно было после UPDATE делать, но как понять через сколько процесс закончится? Может подождать стоит?
3. Почему UPDATE не связанный с этим полем так тормозит?
  • Вопрос задан
  • 417 просмотров
Пригласить эксперта
Ответы на вопрос 2
2ord
@2ord
продвинутый чайник

UPDATE (SET a = 'foo' where field и LIKE '%pattern%')
Нужно было перед командой добавить "EXPLAIN ANALYSE ", тогда можно было узнать план запроса, не выполняя его.
3. Почему UPDATE не связанный с этим полем так тормозит?
См. выше.

Особенно когда
Есть таблица на 100 млн записей.

Кроме того, что за индекс был добавлен на поле, по которому выполняется LIKE? Если B-tree, Hash, то это имеет нулевую ценность для ускорения запроса. По этому полю возможно нужен GIN индекс (pg_trgm). Или даже необходимо перепроектировать таблицы, вынеся это поле, разбив на множество записей и связью N:M. Мы не знаем что за данные в том поле.

Для экспериментов лучше создавать ограниченный набор данных, приближенный к продакшн и тестировать на нем, вместо на 100М записей.
Ответ написан
@galaxy
Блокирует ли процесс построения индексов таблицу?

и тут же
Перед UPDATE я индекс по нему сделал

Ну и как сам думаешь?

Мне потребовалось добавить одно поле
добавление поля без DEFAULT, допускающего NULL значение, пройдет быстро. Если его надо заполнять (есть DEFAULT, или как вы руками), это уже апдейт на 100 млн строк.

1. Правильно ли я понимаю что hash индексы не могут использовать сразу все ядра
1.1 а btree могут?

Вообще ни при чем ни hash, ни btree индексы.
В postgres есть только parallel queries - параллельные запросы, которые могут выполняться несколькими потоками. Параллельных апдейтов нет.

3. Почему UPDATE не связанный с этим полем так тормозит?

Основной апдейт грузит машину + если другие апдейты пытаются изменить те же строки в таблице, они у вас, скорее всего, не тормозят, а просто отваливаются по таймауту. Ибо строки заблокированы основной бесконечной транзакцией.
Какое поле изменяется (то же самое или нет), значения не имеет.

2. Как быть в текущей ситуации. Я понимаю что индекс нужно было после UPDATE делать, но как понять через сколько процесс закончится? Может подождать стоит?

Прибейте этот апдейт для начала. Ничего страшного не произойдет. Никто вам тут не скажет, сколько еще он будет висеть. Может, он сам ждет какой-то блокировки.
Потом VACUUM ANALYZE на таблицу.

Что касается заполнения поля, из вопроса непонятны условия. Вы во всей таблице (или большей ее части) хотите это сделать? Или вот это: where field и LIKE '%pattern%' - выборка все же малого числа строк (сколько примерно?)?
* вообще-то стоило бы до UPDATE дернуть SELECT с тем же условием и посмотреть, сколько времени уйдет на него*
Простой индекс не сработает с запросами вида ilike '%pattern%'. Поставьте расширение pg_trgm и сделайте GIN индекс. Опять же, если надо заполнить полтаблицы, индекс не спасет, надо будет разбивать UPDATE на куски.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы