Postgres 8.4.4 (x32 на Win7 x64) очень медленное ОБНОВЛЕНИЕ на маленькой таблице

У меня есть очень простой оператор обновления:

UPDATE W SET state='thing'
WHERE state NOT IN ('this','that') AND losttime < CURRENT_TIMESTAMP;

В таблице W всего 90 строк, хотя столбцы времени и состояния для каждой строки обновляются каждый примерно каждые ~ 10 секунд. Существуют индексы состояния и потери времени (а также первичный индекс).

Я заметил, что с большими базами данных (т. Е. В других таблицах много записей, а не в таблице W) в течение определенного периода времени запрос получает все медленнее, медленнее и медленнее. После работы в течение 48 часов я рассчитываю его, запустив его в окне запроса PqAdminIII, и для его выполнения потребовалось 17 минут!

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

UPDATE H SET release='1' 
WHERE a NOT IN (SELECT id from A WHERE state!='done') AND release!='1';

H действительно не имеют индексов, но я попытался поставить и удалить индекс на H (выпуск) без каких-либо изменений в поведении. Этот запрос после того, как база данных проработала 48 часов и таблица H содержит ~ 100 тыс. Строк, занимает 27 минут. Сервер Postgres будет иметь поток, полностью привязанный (100% использование ЦП) на время выполнения запроса, поэтому не похоже, что есть какая-либо конкуренция за сеть, диск и т. Д.

Итак, в общих чертах поведение, которое я вижу заключается в том, что моя база данных работает, как ожидалось, около 5 минут, а затем постепенно все останавливается, поскольку выполнение основных команд UPDATE, связанных с обслуживанием, становится все дольше и дольше. На второй день требуется час, чтобы выполнить простой цикл обслуживания (несколько ОБНОВЛЕНИЙ), который вначале длился ~ 100 мс. Мне кажется очевидным, что снижение производительности суперлинейно в зависимости от количества информации в базе данных - может быть, N ^ 2 или что-то подобное.

Автоочистка включена с использованием значений по умолчанию. Я прочитал руководство (еще раз) и не увидел ничего, что бросалось бы на меня.

Я чешу здесь затылок. Я не вижу никаких исправлений ошибок, которые кажутся актуальными в примечаниях к выпуску 9.0.1 и 9.0.2. Может ли кто-нибудь помочь мне понять, что происходит? Спасибо, M

-xxxx-

Хорошо, у меня могут быть две проблемы.

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

Второе обновление продолжает работать медленно. План запроса предполагает, что индексы используются неэффективно и что происходит перекрестный переход 80k * 30k, что может объяснить сверхлинейное время выполнения, которое я, кажется, наблюдаю. (Все согласны с такой интерпретацией плана?)

Я могу преобразовать UPDATE в SELECT:

SELECT * from H
where a not in (SELECT id from A where state='done') AND release!='1';

с аналогичным временем выполнения (27 минут).

Если я не доверяю оптимизатору postgres и выполняю это:

WITH r as (select id from A where state='done')
SELECT a from H 
JOIN on H.a=r.id 
WHERE H.released='0';

тогда запрос выполняется примерно через 500 мсек.

Как мне преобразовать эти знания обратно в ОБНОВЛЕНИЕ, которое выполняется с приемлемой скоростью? Моя попытка:

UPDATE H SET release='1'
FROM A
where A.state!='done' AND release!='1' AND A.id=H.a;

выполняется примерно за 140 секунд, что быстрее, но все же очень-очень медленно.

Куда я могу перейти?

-xxxx-

ВАКУУМНЫЙ АНАЛИЗ был добавлен как часть " регулярное обслуживание », при котором приложение будет запускать его примерно раз в минуту или около того независимо от работающей автоочистки.

Кроме того, был переписан второй запрос, чтобы исключить заведомо медленное условие NOT IN, заменив его предложением "Left Anti-Semi Join" (а?)

UPDATE H SET release='1' 
WHERE release='0' AND NOT EXISTS (SELECT * FROM A WHERE id=H.a AND state!='done');