Индексы и селективность (PostgreSQL)

Индекс по полю в БД потенциально может ускорить SELECT операцию с условием по данному полю, может ускорить запрос вида: ORDER BY поле LIMIT 20, но индекс существенно замедляет операции изменения таблицы и т.п.

Когда нужен индекс, когда он поможет и будет использован при SELECTах? Всё зависит от селективности индекса, т.е. от кол-ва строк, которые мы получим если зададим условие:

проиндексированное_поле = значение

Отличный кандидат для индексирования — селективность 1, т.е. уникальный индекс (например, id), когда по указанному значению мы найдем максимум одну запись.

Рассмотрим в качестве примера таблицу пользователей с полями информации о регионе: страна (country_id) и город (city_id). Хорошо, когда селективность составляет < 5% (например, поле city_id у пользователя). При этом PostgreSQL умён, он считает не селективность “вообще” по полю, а селективность в виде гистограммы по отдельным значениям поля. Т.е. если мы задаем условие вида

страна = Россия

то получим 10% записей из БД, а если условие

страна = Уругвай

то получим 2 записи, и это PostgreSQL понимает. (Конечно, здесь мы предлполагаем, что пользователей из Уругвая на нашем сервере гораздо меньше, чем пользователей из России).

Так вот, если селективность плохая (получаем много записей), PostgreSQL предпочтёт выполнить полное сканирование БД, не используя индекс. И такой индекс только мешает.

P.S. Кросс-пост из моего блога