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

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

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


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

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

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


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

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


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

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

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

    Комментарии 4

      +1
      В статье все очень сильно упрощено.

      К примеру можно рассмотреть такой случай:

      SELECT * FROM humans
      where age = 18
      and birthday_year = 1990

      и аналогично

      SELECT * FROM humans
      where age = 18
      and birthday_year = 1980

      как селективность будет рассчитана в таких случаях.

      Ну и конечно ссылку. Это про Оракл. Но лучше все равно ничего не написано. А работают индексы везде при мерно одинаково.
        0
        В приведенном примере у БД нет шансов увидеть корреляцию между значениями двух полей таблицы. И, соответственно, план запроса будет строиться из общих соображений. Увидев селективность по каждому из индексов, общее количество строк в таблице и другие параметры, БД может:

        1. не использовать ни один из индексов;
        2. использовать один из индексов;
        3. использовать оба индекса.
          0
          То есть если кратко может быть все что угодно :)
          И любое поведение будет неправильным.
          Обзор подобных случаев был бы гораздо интереснее чем простенькое описание поведения в штатной ситуации.
            0
            Мой пост ни в коем случае не претендовал на полноту охвата вопроса, а просто поднимал проблему, которую многие (на моем личном опыте) совсем не понимают, считая наличие индекса панацеей от всех проблем.

            То что касается моего ответа на предыдущий пост — да, может быть всё, что угодно, и это зависит не только от запроса/схемы базы данных, но и от данных таблицы, а они могут быть разными. Также влияет настройка оптимизатора и т.п. Тут нет четкой границы вида «100» записей и индекс начнет работать или наоборот. С другой стороный, очевидно, что в таблице из двух строк база данных никогда не будет использовать ни один индекс (это крайний случай).

      Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

      Самое читаемое