Добавьте индекс в ДБ

Баг-репорты
1

Здравствуйте.

При удалении пользователей через:

POST /api/users/bulk/delete

удаление одного пользователя занимает около 5 секунд, а удаление пачек может приводить к 504 Gateway Time-out.

Проверка через EXPLAIN ANALYZE показала, что почти всё время уходит на каскадный внешний ключ:

nodes_user_usage_history_user_id_fkey

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

WHERE user_id = ?

выполняют полный просмотр таблицы.

Предполагаемое решение:

CREATE INDEX CONCURRENTLY
    nodes_user_usage_history_user_id_idx
ON public.nodes_user_usage_history (user_id);

image
image1070×280 18.1 KB

2

Провели проверку и добавили индекс вручную:

CREATE INDEX CONCURRENTLY
    nodes_user_usage_history_user_id_idx
ON public.nodes_user_usage_history (user_id);

Индекс успешно построен без остановки базы:

indisvalid = true
indisready = true
indislive  = true
size       = 553 MB

Результат до добавления индекса:

  • удаление одного пользователя: около 4,72 секунды;

  • около 4,67 секунды занимал FK nodes_user_usage_history_user_id_fkey;

  • удаление 100 пользователей приводило к 504 Gateway Time-out.

Результат после добавления индекса:

  • пачки по 100 пользователей выполняются за 5,1–6,2 секунды;

  • среднее время первых семи пачек — около 5,52 секунды;

  • успешно удалено 700 пользователей без ошибок и 504;

  • среднее время на одного пользователя снизилось примерно до 55 мс;

  • ускорение составило около 85 раз.

Затраты:

  • дополнительное место на диске: 553 MB;

  • база продолжала работать во время CREATE INDEX CONCURRENTLY;

  • влияние индекса на скорость дальнейших записей в таблицу пока отдельно не измерялось.

Таким образом, отдельный индекс по user_id полностью устраняет основную проблему каскадного удаления. Просьба рассмотреть его добавление в официальную Prisma-схему и миграцию.

3

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

4

Да, такое решение выглядит верным: индекс (user_id, created_at) подходит и для каскадного удаления по user_id, и для запросов статистики за диапазон дат.

Спасибо за ответ. Будем ждать появления этой миграции в продакшен-версии.