Замедлите простой запрос на обновление на базе данных PostgreSQL с 3 миллионами строк
Я бы использовал SortedList , но я не думаю, что это сильно повлияет на производительность, просто, вероятно, будет легче читать.
7 ответов
Взгляните на этот ответ: PostgreSQL медленно работает с большой таблицей с массивами и множеством обновлений
Сначала начните с лучшего FILLFACTOR, сделайте VACUUM FULL, чтобы принудительно перезаписать таблицу и проверьте HOT-обновления после вашего UPDATE-запроса:
SELECT n_tup_hot_upd, * FROM pg_stat_user_tables WHERE relname = 'myTable';
HOT-обновления выполняются намного быстрее, когда у вас есть много записей для обновления. Более подробную информацию о HOT можно найти в этой статье .
Пс. Вам нужна версия 8.3 или лучше.
Сегодня я провел много часов с подобной проблемой. Я нашел решение : отбросить все ограничения / индексы перед обновлением . Независимо от того, проиндексирован ли обновляемый столбец или нет, похоже, что psql обновляет все индексы для всех обновленных строк. После завершения обновления добавьте ограничения / индексы обратно.
Первое, что я бы предложил (из https://dba.stackexchange.com/questions/118178/does-updating-a-row-with-the-same-value-actually-update-the -row ) предназначен только для обновления строк, которые «нуждаются» в этом, например:
UPDATE myTable SET generalFreq = 0 where generalFreq != 0;
(может также потребоваться индекс для generalFreq). Тогда вы обновите меньше строк. Хотя не в том случае, если все значения уже ненулевые, но обновление меньшего количества строк «может помочь», так как в противном случае оно обновляет их и все индексы независимо от того, изменилось ли значение или нет.
Другой вариант: если звезды совпадают с точки зрения значений по умолчанию и ненулевых ограничений, вы можете удалить старый столбец и создать еще один , просто изменив метаданные, мгновенное время.
В своих тестах я заметил, что большое обновление, более 200 000 строк, медленнее, чем 2 обновления из 100 000 строк, даже с временной таблицей.
Мое решение состоит в цикле, в каждом цикле создайте временную таблицу из 200 000 строк, в этой таблице я вычисляю свои значения, а затем обновляю основную таблицу новыми значениями также ...
Каждые 2 000 000 строк, которые я вручную «VACUUM ANALYZE mytable», замечал, что автоматический пылесос не выполняет свою работу для таких обновлений.
Как вы его запускаете? Если вы перебираете каждую строку и выполняете оператор обновления, вы выполняете потенциально миллионы отдельных обновлений, поэтому процесс будет выполняться невероятно медленно.
Если вы выполняете один оператор обновления для всех записей в одном операторе, он будет работать намного быстрее, и если этот процесс медленный, то это, вероятно, связано с вашим оборудованием, а не с чем-то еще. 3 миллиона - это очень много записей.
Выждав 35 мин. чтобы мой запрос UPDATE завершился (и все еще не сделал), я решил попробовать что-то другое. Я сделал команду:
CREATE TABLE table2 AS
SELECT
all the fields of table1 except the one I wanted to update, 0 as theFieldToUpdate
from myTable
Затем добавил индексы, затем отбросил старую таблицу и переименовал новую, чтобы она заняла ее место. Это заняло всего 1,7 мин. для обработки плюс дополнительное время для воссоздания индексов и ограничений. Но это помогло! :)
Конечно, это сработало только потому, что никто другой не использовал базу данных. Мне нужно было бы сначала заблокировать таблицу, если бы это было в производственной среде.
попробуйте
UPDATE myTable SET generalFreq = 0.0;
Возможно, это проблема с приведением