Селективность SQL

Селективность

Селективность предиката показывает, какая доля строк ему соответствует. В разговоре термин используют в двух обратных смыслах, поэтому лучше называть конкретную величину:

fraction_selected = matched_rows / total_rows

Чем меньше доля выбранных строк, тем предикат избирательнее.

Пример

В таблице миллион заказов:

• id = 42 выбирает примерно одну строку;
• state = 'PAID' выбирает 700 000;
• user_id = 42 AND created_at >= ... может выбрать 20.

Индекс по id почти наверняка полезен. Отдельный индекс по частому значению state может быть невыгоден: чтение таблицы окажется дешевле множества случайных обращений.

Кардинальность и распределение

Высокая кардинальность столбца не гарантирует избирательность конкретного условия. У email много разных значений, у status мало, но важна реальная частота каждого значения.

Оптимизатор оценивает ее по статистике:

• число разных значений;
• доля NULL;
• наиболее частые значения;
• гистограмма;
• иногда расширенная статистика по корреляции столбцов.

Составной индекс

CREATE INDEX idx_orders_user_state_created
    ON orders (user_id, state, created_at);

Порядок выбирают по паттернам запросов, равенствам/диапазонам, сортировке и покрытию — не только по правилу «самый селективный первым».

Ловушки

• На маленькой таблице scan может быть оптимален даже при селективном предикате.
• Параметры с разной селективностью могут нуждаться в разных планах.
• Коррелированные столбцы ломают предположение о независимости: country и city связаны.
• Устаревшая статистика ведет к неверной оценке.

Связано: Индексы SQL, SQL оценка производительности, Условный оператор WHERE.

Вопросы

Что значит селективный предикат? Он оставляет малую долю строк.

Почему индекс по boolean часто не помогает? Каждому значению соответствует большая доля таблицы; исключение — частичный индекс для редкого состояния.

Нужно ли ставить самый селективный столбец первым? Не всегда; учитывают реальные запросы, равенство, диапазон, сортировку и возможности СУБД.

Откуда оптимизатор знает распределение? Из статистики, которая должна быть достаточно актуальной.