Совет дня №16

Разберем ситуацию, когда индекс есть, но не используется.

Предположим, в системе есть пользователи, и каждый указывает город:

create table users (
  id uuid primary key,
  name text,
  city text
)

В реальности поле city ссылается на таблицу городов, но сейчас это не важно – оставим текст. Разработчик создал индекс на город:

create index idx_user_city
using btree on users(city);

Выполняет запрос, чтобы найти пользователей из Москвы:

select * from users where city = 'Moscow'

Смотрит план, а там full scan. Почему?

Самое важное: Postgres никогда не использует индекс просто потому, что он есть. Индекс берется в работу, только если а) по нему собрана статистика и б) она показывает, что индекс дешевле full scan.

Разберем оба условия. Статистика по каждой таблице, ее колонке и индексу хранится в каталоге pg_statistics. Это очень низкоуровневые данные. В них записаны наиболее частые значения колонок, их гистограмма, приблизительные диапазоны значений. Поверх pg_statistics создана вьюха pg_stats, которая удобнее в работе и проверяет права на таблицы.

Статистику собирает специальный процесс по расписанию. Если вы только что создали индекс, статистики для него нет. Обновите ее командой:

analyze users

В идеале analyze нужно добавлять в миграции, которые создают или меняют индексы, чтобы сразу после их применения индекс подхватился.

Итак, статистика есть, но индекс “мигает” – то используется, то нет. Например, ищем пользователей из Читы, попадаем в индекс:

explain
select * from users where city = 'Chita'
/* index scan on idx_user_city ... */

А если из Москвы, будет full scan:

explain
select * from users where city = 'Moscow'
/* full seq scan on users ... */

В чем дело? Ответ – селективность, она же избирательность. Так называют долю найденных записей относительно их общего числа. С селективностью есть путаница: разные учебники по-разному трактуют этот показатель. Одни говорят: если процент малый (1-5%), то селективность высокая, а если большой (30 и выше) – то низкая. Другие наоборот: малый процент – низкая селективность, высокий — большая. Я предпочитаю первый (обратный) вариант: чем меньше записей охватывает условие, тем точнее (выше) селективность.

Если проверить, сколько в базе пользователей из какого города, то окажется следующее. Из Читы – три человека (высокая селективность), а из Москвы – триста тысяч (крайне низкая). Москва окажется в каталоге pg_statistics в колонке “частые значения” – сигнал к тому, чтобы не брать индекс в работу. При анализе запроса Postgres это проверит и возьмет full scan. Москвичи пролетают!

Какова должна быть селективность значения? Реальность такова, что Postgres берет в работу индекс, если селективность условия не превышает 5-10 процентов. Это довольно мало! Именно поэтому нет смысла создавать индексы на логические флаги (true/false), статусы (active, pending, done) и другие значения с малым разбросом. Их селективность слишком низкая (высокая доля выборки).

Для таких значений используйте частичные индексы (см. прошлый совет). Например, только активные заказы, только москвичи и так далее. И потом ищите уже внутри этого подмножества.

Почему Postgres предпочитает full scan для условия, которое покрывает 30% таблицы? Ведь прочитать треть таблицы быстрее, чем всю? Дело в том, что обход индекса возвращает не сами строки, а номера блоков, где они находятся. Блоки идут не один за другим, а разбросаны по всему файлу. Даже если отсортировать номера по возрастанию, между ними могут быть большие промежутки. В результате будет много дисковых операций. На крутящихся жестких дисках это приводило к частому переносу головки.

Напротив, чтение всех блоков подряд относительно дешево. При таком методе Postgres читает сразу много блоков, а не по одному.

Итого: выполняйте analyze, проверяйте селективность условия.