Создали индекс, чтобы ускорить чтение. В итоге база стала работать медленнее

Запрос выполнялся 800 мс. После добавления индекса – 20 мс. Вроде бы всё отлично. Метрика улучшилась в 40 раз, задачу можно закрывать.

Через пару недель выясняется, что INSERT стал тяжелее, массовые UPDATE тоже просели, а индексы на таблице уже занимают приличный объём) И оптимизация, которая выглядела очевидно успешной, внезапно перестаёт быть такой однозначной 😁

Возьмём обычную таблицу orders. В неё постоянно добавляются новые заказы, меняются статусы, обновляется информация об оплате. На таблице уже есть несколько индексов, потом появляется ещё один – ради того самого медленного SELECT.

С чтением всё хорошо. А вот при каждой вставке PostgreSQL теперь приходится обновлять ещё одну индексную структуру. И чем больше индексов висит на активно изменяемой таблице, тем дороже обходится запись...

С UPDATE ещё интереснее. PostgreSQL из-за MVCC создаёт новую версию строки. Если изменившаяся колонка входит в индекс, индекс тоже приходится обновлять. А если часто изменяемое поле проиндексировано, для такого изменения уже не получится использовать HOT update.

Плюс индекс нужно хранить на диске и обслуживать. На одной маленькой таблице это может быть вообще незаметно. На большой write-heavy таблице – уже нет.

Допустим, запрос, который мы ускорили, выполняется 200 раз в минуту. А INSERT и UPDATE по этой же таблице – 20 000 раз.

После этого цифры 800 мс → 20 мс выглядят уже чуть иначе.

Поэтому перед созданием индекса я бы сначала посмотрел, как вообще живёт эта таблица: сколько на ней чтения и записи, какие индексы уже есть, насколько дорог конкретный запрос и как часто он реально выполняется. Иногда новый индекс действительно оправдан. Иногда дешевле переписать запрос. А бывает, что нужный индекс уже существует, просто PostgreSQL по какой-то причине его не выбирает 🤷‍♂️

На курсе по PostgreSQL как раз будем разбирать индексы на таких кейсах: смотреть на запрос и нагрузку, выбирать решение и потом проверять по EXPLAIN, что изменилось до и после.