В прошлый раз мы говорили вот о чем. Когда мы изучаем план запроса, то смотрим, было ли попадание в индекс. Однако бывает подвох: индекс был, да не тот.

Скажем, есть запрос: показать топ-100 активных заказов по убыванию даты. Пожалуйста:

select * from orders
where status = 'active'
order by created_at desc
limit 100

Для поля status создан индекс idx_orders_status, для created_at — idx_orders_created_at (по убыванию).

Смотрим EXPLAIN, а там примерно такое:

limit: 100
  filter scan condition: status = 'active'
    index scan idx_orders_created_at: desc

Разработчик смотрит: index scan был? Был. Вот и ладушки.

На самом деле произошло вот что. Вместо того, чтобы взять только активные заказы и отсортировать их, Postgres сделал обратное. Он пошел по всей таблице (full scan), просто не в случайном порядке (как записи лежат на диске), а согласно индексу. При этом записи были отобраны вручную: для каждой из них выполнялась проверка status = ‘active’. Фактически это был full scan, только в другом порядке.

В идеале нам нужен другой план:

limit: 100
  in-memory sort, key: created_at (desc)
    index scan idx_orders_status condition: status = 'active'

Этот план читают так. Сначала Postgres выбирает активные заказы по индексу. Ожидается, что в таблице много завершенных заказов, поэтому у активных будет высокая селективность. Далее эти заказы сортируются в памяти по created_at (уже без всякого индекса) и берутся первые сто.

Почему был первый план, а не второй? Во-первых, индекса на status может не быть. Если он есть, у значения active низкая селективность: доля активных заказов высокая. Может быть, настало время обновить статистику командой analyze, и значение active уйдет из массива частых значений, которые нельзя брать в индекс. Наконец, можно признать, что тут ничего не поделаешь и мы согласы жить с первым планом, а не вторым. Возможны и другие варианты, их смотрят по ситуации.

Общий принцип такой. Часто фильтры и сортировка тянут одеяло на себя. Postgres должен решить, что выгоднее: отсортировать по индексу и пройтись вручную или наоборот: сначала отсечь по индексу и отсортировать в памяти. Второй вариант предпочтительней: когда выборка мала, любая операция над ней дешева: сортировка, группировка и так далее. Стремитесь к тому, чтобы WHERE отсекал как можно больше записей, и только потом вступали в действие другие операции.

← Совет дня №19