Проектирование под выполняемые запросы — Примеры

Пример 1 — Индексы, выведенные из списка запросов

Прежде чем что-то добавлять, команда выписывает четыре чтения, которые выполняет функция заказов. У таблицы заказов нет индекса, кроме первичного ключа. Назовите индекс, нужный каждому запросу, и скажите, какой запрос ни один индекс по этой таблице обслужить не может.

1. страница аккаунта: заказы одного клиента, сначала новые
2. «найти мой заказ»: один заказ по идентификатору из письма-подтверждения
3. экран склада: открытые заказы одного склада, сначала старые
4. месячный отчёт: выручка, суммированная по товарам через все заказы

Решение

create index orders_customer_recent on orders (customer_id, placed_at desc);
create unique index orders_public_id on orders (public_id);
create index orders_warehouse_open on orders (warehouse_id, placed_at)
  where status = 'open';
-- запрос 4 отвечается из сводной таблицы, обновляемой ночью, а не индексом.

Каждый индекс называет запрос, который за него платит, а столбец сортировки включён, чтобы сортировка не была отдельным шагом. Отчёт — тот запрос, которому индекс не поможет: он агрегирует данные по всем заказам, поэтому ответ приходит из сводной таблицы, записываемой по расписанию, а не вычисляется при каждом чтении. Обычно ошибаются так: добавляют по индексу на каждый упомянутый в этих запросах столбец, однако потом платят за все эти индексы на каждой вставке.

Пример 2 — Два изменения, которые не оправдывают своей стоимости

После выпуска функции предлагают два изменения: хранимый итог в строке клиента и индекс по статусу заказа. Одно оправдано измерением, другое нет — решите каждое по приведённым данным.

предложение A
  alter table customers add column orders_total numeric not null default 0;
предложение B
  create index orders_status_idx on orders (status);

данные
  страница аккаунта читает итог клиента на каждом запросе   (измерено)
  экран открытых заказов фильтрует по складу и статусу      (измерено)
  ни один запрос не фильтрует только по статусу             (не найдено)

Решение

-- A: оставлено, потому что измеренное чтение в нём нуждается, и названо производной величиной
alter table customers add column orders_total_derived numeric not null default 0;

-- B: убрано, потому что ни один запрос в нём не нуждается
drop index orders_status_idx;

Хранимый итог убирает агрегацию из самого частого чтения, поэтому он заслуживает обязанности держать его правильным на каждой записи, а суффикс _derived не даёт позднему читателю принять его за источник истины. За индексом по статусу нет запроса, поэтому он лишь добавляет работы каждой вставке, тогда как запрос открытых заказов уже обслуживается своим составным индексом. Ошибка — оставить оба: неоправданный индекс стоит на каждой вставке, а непомеченный производный столбец тихо принимают за истину.