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 или что-то в этом роде.

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

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

-x-x-x-x-

Итак, у меня могут быть две проблемы.

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

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

Куда я могу пойти отсюда?

-x-x-x-x-

VACUUM ANALYZE был добавлен как часть «планового обслуживания», когда приложение будет запускать его примерно раз в минуту или около того независимо от любой запущенной автоматической очистки.

Кроме того, переписали второй запрос, убрав заведомо медленное предложение NOT IN, заменив его на «левое антиполусоединение» (да?)

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