Postgres 9 супер медленное простое удаление

У меня есть существенная база данных... не очень большая - всего около 1 ГБ данных.

Мне нужно удалить некоторые строки из нескольких таблиц. Например, у меня есть таблица

 Order
 id     | ... | status   | ...
 1      | ... | 1        | ...
 ...
 40     | ... | 20       | ...
 41     | ... | 1        | ...
 ...
 470000 | ... | 12       | ...

Теперь я хочу удалить все заказы, которые имеют status=1

Я предполагаю, что делаю это с помощью:

УДАЛИТЬ ИЗ Заказа, ГДЕ статус=1

Все красиво и просто казалось бы, но на это уходят годы! Когда я запустил этот запрос, он все еще работал со 100% загрузкой ЦП через 40 минут... когда я убил процесс, ничего не было удалено.

Когда я попытался ограничить область действия с помощью

УДАЛИТЬ ИЗ Заказа, ГДЕ статус=1 И идентификатор‹1000

потребовалось пару минут, чтобы удалить около 200 строк....

Есть ли что-то, чего мне не хватает в моей конфигурации? Что я должен искать/проверить/изменить? Любые идеи, почему это так чертовски неэффективно?

Позвольте мне добавить, что я обычно работаю с MySQL и мне нужно управлять этой базой данных postgres, но на самом деле у меня нет никакого опыта работы с postgres, поэтому это может быть что-то очень простое.

Индексы есть как в столбцах id, так и в столбцах состояния.

В таблице около 500 тыс. строк, около половины необходимо удалить.

План выполнения:

Delete  (cost=0.00..19474.19 rows=266518 width=6)
->  Seq Scan on Orders  (cost=0.00..19474.19 rows=266518 width=6)
Filter: (statusid = 1)

Нет никаких триггеров или правил любого рода. Более того, я не добавил, что это свежая копия таблицы, я имею в виду, что она была перемещена с другого сервера с помощью экспорта/импорта. Может быть, это как-то играет роль?

Поможет ли удаление индексов?


postgresql sql database-administration
person RandomWhiteTrash    schedule 24.10.2011    source источник
comment
У вас есть индекс в столбце status? Как выглядит план выполнения оператора удаления?   -  person a_horse_with_no_name    schedule 24.10.2011
comment
Выполнив команду EXPLAIN: postgresql.org/docs/current/static/ sql-explain.html   -  person a_horse_with_no_name    schedule 24.10.2011
comment
Сколько строк в таблице? И как у них может быть статус=1? В любом случае удаление 266518 строк не должно занимать 40 минут...   -  person a_horse_with_no_name    schedule 24.10.2011
comment
Тогда имеет смысл, что индекс не используется. Но все же удаление не должно занять так много времени. Я понятия не имею, как узнать, что замедляет вас, хотя. Вы, вероятно, получите лучшую помощь, разместив это в списке рассылки PG.   -  person a_horse_with_no_name    schedule 24.10.2011
comment
1) Определены ли в таблице какие-либо триггеры (или правила)? 2) Вы можете удалить все индексы и затем создать их заново (вам не нужен индекс для удаления, удаление половины таблицы в любом случае приведет к последовательному сканированию таблицы.   -  person Erwin Brandstetter    schedule 24.10.2011
comment
Было бы хорошо, если бы вы опубликовали определение таблицы, а также если есть другие таблицы с любым FOREIGN KEY, который ссылается на эту таблицу и имеет определенное действие ON DELETE.   -  person ypercubeᵀᴹ    schedule 24.10.2011

Ответы (2)


arrow_upward
10
arrow_downward

Ничего не удалено после того, как вы убили процесс, это ИМЕННО то, что вы должны увидеть.

Удаление происходит как транзакция, что означает, что либо все удаляется, либо ничего. Чтобы убедиться, что это может произойти, строки нужно скопировать куда-то перед их удалением. Это означает, что удаление 250 тыс. строк занимает примерно столько же времени, сколько и вставка такого количества строк. В некоторых случаях может быть быстрее создать новую таблицу, в которой все НЕ удалено, и переименовать новую таблицу в старую.

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

(Это общая мудрость СУБД, а не специфичная для postgresql - детали того, как работает MVCC postgres, могут значительно отличаться.)

person evil otto    schedule 24.10.2011
comment
Привет, Это именно то, что я начал подозревать ... я могу переключить транзакцию на время удаления? В конце концов, мне все равно, удалит ли он только 100 КБ, прежде чем что-то произойдет, потому что я всегда могу запустить удаление снова. - person RandomWhiteTrash; 25.10.2011
comment
Как насчет использования какого-то инкрементного подхода (псевдокода): for (my_id = 0; my_id ‹ max(id); my_id += 1000) { удалить из заказа, где статус = 1 и id между my_id и my_id + 1000; совершить } - person Frank Schmitt; 01.11.2011

arrow_upward
3
arrow_downward

Гораздо быстрее использовать COPY FROM/TURNCATE/COPY TO на больших таблицах.

Но, конечно, нужно быть осторожным со ссылками и по возможности отключать триггеры.

person Petr    schedule 24.04.2015