Индекс добавили, а запрос не ускорился и загрузка стала дольше
Таблица фактов в PostgreSQL — 200 млн строк. Чтобы ускорить запрос WHERE is_test = false, построили индекс по колонке is_test. У колонки два значения, и 99% строк — false.
После выкатки: запрос не ускорился, план по-прежнему показывает Seq Scan, а ночная загрузка стала заметно дольше.
Что здесь происходит?
- Индекс создавался под нагрузкой и остался невалидным, поэтому планировщик его не рассматривает; после REINDEX план переключится на чтение по индексу, а лишняя работа при загрузке уйдёт вместе с недостроенным индексом.
- PostgreSQL не использует B-tree по boolean-колонке: для поля с двумя значениями индекс имеет смысл только в составе составного, поэтому его надо пересобрать как индекс по паре (is_test, дата загрузки).
- Селективность условия почти нулевая: читать 99% таблицы через индекс дороже, чем последовательно, а вот при вставках индекс всё равно обновляется — отсюда замедление загрузки.
- Индексу мешает физический порядок строк: пока таблица не упорядочена по is_test, корреляция низкая и обращения к страницам считаются случайными — после CLUSTER по этому индексу запрос пойдёт по индексу, а загрузка вернётся к прежней скорости.