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

Question

[beduin01]

Не могу найти в документации.
Проблема такая. Есть таблица на 100 млн записей. Мне потребовалось добавить одно поле и потом я решил заполнить его сделав

UPDATE (SET a = 'foo' where field и LIKE '%pattern%')

по нему.
Перед UPDATE я индекс по нему сделал, и как понимаю это была большая ошибка.

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

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

Answer 1

[d'Ivan]

UPDATE (SET a = 'foo' where field и LIKE '%pattern%')

Нужно было перед командой добавить "EXPLAIN ANALYSE ", тогда можно было узнать план запроса, не выполняя его.

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

См. выше.

Особенно когда

Есть таблица на 100 млн записей.

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

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

Comments

[beduin01]

Кстати, а какие индексы быстрее строятся? btree или hash? И умеют ли они параллелиться? Или только на одном ядре

[beduin01]

Проверил статус работы в pg activity. Пишет что запрос в статусе active.
Сделал count по выполненным данным, через 5 минут снова -- результат не изменился. Такое чувство ничего не происходит. Перестартовал postgres. Из консоли снова выполнил:

UPDATE "xml_files" set action_type = 'data_insert' where action_type is null;

Все тормозит, процессор загружен, но count опять возвращает одни и те же значения как если бы
where action_type is null был тот же самый и ничего не заапдейтилось

[d'Ivan]

beduin01, не имеет смысла добавлять индексы, которые не несут никакой пользы. При запросах LIKE '%pattern%' они бесполезны.
А что вообще находится в том поле? Значения, разделенные пробелами или запятыми?

[d'Ivan]

beduin01,

Такое чувство ничего не происходит. Перестартовал postgres.

В этом не было никакой необходимости.
Если
SELECT * FROM pg_stat_activity показывает что запрос еще выполняется, то не нужно ожидать, что изменения будут выполнены до его окончания.

Сделал count по выполненным данным

Просто SELECT Count(*)? Ведь это сканирование всех 100М записей. БД не жалко?

[ScriptKiddo]

Нужно было перед командой добавить "EXPLAIN ANALYSE ", тогда можно было узнать план запроса, не выполняя его.

EXPLAIN ANALYZE выполняет запрос, EXPLAIN - не выполняет

UPD.

Исправил ANALYZE на EXPLAIN

[d'Ivan]

ScriptKiddo, я ввел в заблуждение.
Спасибо за уточнение.
Только все же не

ANALYZE - не выполняет

А EXPLAIN (без ANALYZE)

Answer 2

[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 на куски.

Comments

[d'Ivan]

Потом VACUUM ANALYZE на таблицу.

Наверное, это тоже займет много времени на 100М, стоит учесть. Такое стоит выполнять ночью или рано утром.

[beduin01]

Проверил статус работы в pg activity. Пишет что запрос в статусе active.
Сделал count по выполненным данным, через 5 минут снова -- результат не изменился. Такое чувство ничего не происходит. Перестартовал postgres. Из консоли снова выполнил:

UPDATE "xml_files" set action_type = 'data_insert' where action_type is null;

Все тормозит, процессор загружен, но count опять возвращает одни и те же значения как если бы
where action_type is null был тот же самый и ничего не заапдейтилось

[galaxy]

beduin01,

Сделал count по выполненным данным, через 5 минут снова -- результат не изменился

ну здрасьте называется. Про транзакции слышали? Вы не увидите изменений, пока операция не завершится

[galaxy]

UPDATE "xml_files" set action_type = 'data_insert' where action_type is null;

Есть id какой-нибудь в этой таблице?
Если да, разбейте на куски:

UPDATE "xml_files" set action_type = 'data_insert' where action_type is null and id < 1000000;
UPDATE "xml_files" set action_type = 'data_insert' where action_type is null and id >= 1000000 and id < 2000000;
...

[beduin01]

galaxy, а разве update будет транзакционной операцией? И или вся таблица или ничего? Можно ли его поведение сделать не транзакционным?

[galaxy]

beduin01, любой запрос в pg, даже если транзакция явно не объявлена, работает в рамках своей транзакции. Нельзя увидеть результаты незафиксированной (comitted) транзакции извне ее (т.е. например, как вы пытаетесь в течение запроса из другого окна прочитать).
Изменить это поведение нельзя - это основа архитектуры.
Да я и не понимаю, зачем оно вам нужно. Разбейте большой UPDATE на много маленьких, будет много небольших транзакций, результаты которых вы сможете увидеть.

[beduin01]

galaxy, я удалил-добавил заново индексы на этот раз btree вместо hash, и разбил запрос на части, как вы и предлагали. В итоге он отработал успешно суммарно за 2 часа. Я понять не могу. Почему тот то больше суток висел?

[galaxy]

beduin01, да мало ли почему, теперь уже чего разбираться (если только вы не хотите еще неделю экспериментировать). Просто теперь будете знать, как делать не стоит

[beduin01]

galaxy, спасибо, а примерно какое количество записей апдейтить за раз можно? Или только опытным путем?

[galaxy]

beduin01, в общем случае опытным, конечно, - слишком много факторов.
А так, пытайтесь оценить, сколько времени займет апдейт, сколько параллельных транзацкий за это время у вас обычно проходит. Сколько дополнительно дискового пространства потребуется (апдейт - это удвоение записи + запись в WAL)