gav21Привет.
Имеется БД ~200GB с одной основной таблицей около 110ГБ , с интенсивным инсертом + апдейтом (суммарно около 2-3 млн модификаций в сутки)
ВНЕЗАПНО :) начал тормозить процесс update (конструкция вида UPDATE t SET status='z' WHERE id in (SELECT id FROM t where status = 'x' and cond1=false and cond2=1 FOR UPDATE)

Первым делом сделали vacuum+analyze на таблицу. Значения автовакума cost_limit , cost_delay выставил более агрессивным. Таблица начала практически непрерывно вакумиться.

Подзапрос из SELECT идет по индексу (partial), но делает очень большое кол-во чтений (в т.ч физического). При том что индекс занимает 300МБ, чтений делается на 2-3ГБ (реальная выборка несколько сотен строк). При пересоздании индекса (после пересоздания занимает 5 МБ), кол-во чтений резко падает все работает быстро.
1. Я правильно понимаю, что индекс пухнет из за неполного удаления вакумом из него строк?
2. Даже если индекс распухший, но 99% его это пустые и или почти пустые страницы, откуда так много чтений?
3. Почему апдейт в индекс не добавляет строки на свободные страницы, уменьшая распухание индекса?

PostgreSQL 9.5, RedHat

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

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

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

проблема может возбуждаться наличием длинных транзакций -- растягивающих псевдо--"андо" т.е. жизнь зомби записей и не дающих их отвакуумить. начать с отстрела висяков.(т.е. конкурентно висеть в создании альтернативы вредно по определению)

когда-то замечал большие проблемы с инд-сами на тостах (после очистки тостов индексы висели многомеговые на !пустых тостах)

qwwq,

ещё -- если активных записей -- на 5-меговый индекс -- нет ли возможности вытащить их все в отдельную партицию? типа ротацию забубенить.