Медленный простой запрос на обновление базы данных PostgreSQL с 3 миллионами строк

Я пытаюсь простую UPDATE table SET column1 = 0 в таблице с ~ 3 миллионами строк в Postegres 8.4, но это продолжается навсегда. Он работает более 10 минут. теперь в моей последней попытке.

Раньше я пытался запускать команды VACUUM и ANALYZE в этой таблице, а также пытался создать некоторые индексы (хотя я сомневаюсь, что это будет иметь значение в этом случае), но никто не помогает.

Любые другие идеи?

Спасибо, Рикардо

Update:

Это структура таблицы:

CREATE TABLE myTable
(
  id bigserial NOT NULL,
  title text,
  description text,
  link text,
  "type" character varying(255),
  generalFreq real,
  generalWeight real,
  author_id bigint,
  status_id bigint,
  CONSTRAINT resources_pkey PRIMARY KEY (id),
  CONSTRAINT author_pkey FOREIGN KEY (author_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT c_unique_status_id UNIQUE (status_id)
);

Я пытаюсь запустить UPDATE myTable SET generalFreq = 0;

Ответы

Ответ 1

Взгляните на этот ответ: PostgreSQL замедляется на большой таблице с массивами и множеством обновлений

Сначала начните с лучшего FILLFACTOR, сделайте VACUUM FULL, чтобы заставить таблицу переписать и проверить HOT-обновления после вашего UPDATE-запроса:

SELECT n_tup_hot_upd, * FROM pg_stat_user_tables WHERE relname = 'myTable';

ГОРЯЧИЕ обновления намного быстрее, когда у вас много записей для обновления. Более подробную информацию о HOT можно найти в этой статье.

Ps. Вам нужна версия 8.3 или лучше.

Ответ 2

Мне нужно обновить таблицы из 1 или 2 миллиарда строк с различными значениями для каждой строки. Каждый прогон составляет ~ 100 миллионов изменений (10%). Моя первая попытка состояла в том, чтобы сгруппировать их при транзакции 300K обновлений непосредственно на определенном разделе, поскольку Postgresql не всегда оптимизирует подготовленные запросы, если вы используете разделы.

  • Транзакции группы "UPDATE myTable SET myField = значение ГДЕ myId = id "
    Дает 1500 обновлений/сек., что означает, что каждый прогон будет возьмите не менее 18 часов.
  • ГОРЯЧИЕ обновления, как описано здесь, с FILLFACTOR = 50. дает 1,600 обновлений/сек. Я использую SSD, поэтому это дорогостоящее улучшение, поскольку оно удваивает размер хранилища.
  • Вставить во временную таблицу обновленного значения и объединить их после с UPDATE... FROM Дает 18 000 обновлений/сек. если я делаю ВАКУУМ для каждого раздела; 100 000 в секунду. Cooool. последовательность операций:
CREATE TEMP TABLE tempTable (id BIGINT NOT NULL, field(s) to be updated,
CONSTRAINT tempTable_pkey PRIMARY KEY (id));

Накопить множество обновлений в буфере в зависимости от доступной ОЗУ Когда он заполняется или требуется изменить таблицу/раздел или завершен:

COPY tempTable FROM buffer;
UPDATE myTable a SET field(s)=value(s) FROM tempTable b WHERE a.id=b.id;
COMMIT;
TRUNCATE TABLE tempTable;
VACUUM FULL ANALYZE myTable;

Это означает, что запуск теперь занимает 1,5 часа вместо 18 часов за 100 миллионов обновлений, включая вакуум.

Ответ 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 мин. для обработки плюс дополнительное время для воссоздания индексов и ограничений. Но это помогло!:)

Конечно, это работало только потому, что никто не использовал базу данных. Сначала мне нужно было бы заблокировать таблицу, если бы это было в производственной среде.

Ответ 4

Сегодня я провел много часов с подобной проблемой. Я нашел решение : удалить все ограничения/индексы перед обновлением. Независимо от того, индексируется ли обновляемый столбец или нет, похоже, что psql обновляет все индексы для всех обновленных строк. По завершении обновления добавьте ограничения/индексы назад.

Ответ 5

Попробуйте это (обратите внимание, что generalFreq начинается как тип REAL и остается неизменным):

ALTER TABLE myTable ALTER COLUMN generalFreq TYPE REAL USING 0;

Это перепишет таблицу, аналогичную DROP + CREATE, и перестроит все индексы. Но все в одной команде. Гораздо быстрее (около 2x), и вам не нужно иметь дело с зависимостями и воссозданием индексов и других вещей, хотя он блокирует таблицу (доступ к эксклюзивному - например, полная блокировка) на время. Или, может быть, это то, что вы хотите, если хотите, чтобы все остальное стояло в очереди за ним. Если вы не обновляете "слишком много" строк, этот путь медленнее, чем просто обновление.

Ответ 6

Как вы его используете? Если вы зацикливаете каждую строку и выполняете инструкцию обновления, вы запускаете потенциально миллионы отдельных обновлений, поэтому она будет работать невероятно медленно.

Если вы используете единую инструкцию по обновлению для всех записей в одном из операторов, она будет работать намного быстрее, и если этот процесс будет медленным, то, вероятно, это будет возможно для вашего оборудования больше всего на свете. 3 миллиона - это много записей.

Ответ 7

Первое, что я предлагаю (от 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). Затем вы будете обновлять меньшее количество строк. Хотя нет, если значения уже не равны нулю, но обновление меньше строк "может помочь", поскольку в противном случае оно обновляет их и все индексы независимо от того, изменилось ли значение или нет.

Другой вариант: если звезды выравниваются по значениям по умолчанию и непустым ограничениям, вы можете удалить старый столбец и создать еще один, просто отрегулировав метаданные, мгновенное время.

Ответ 8

попробуйте

UPDATE myTable SET generalFreq = 0.0;

Возможно, это проблема кастинга