Решение: какой тип индекса?

Правильный тип индекса зависит от паттерна доступа запроса (равенство, диапазон или текстовый/JSON-поиск), кардинальности столбца и баланса чтения/записи. Неверный индекс и тратит место, и замедляет запись. Это дерево исходит из формы запроса, ведёт к выбору между B-tree, Hash, Bitmap, GIN/GiST, частичным и покрывающим индексами и напоминает, что порядок столбцов в составном индексе критичен.

Как ваш запрос использует этот столбец?

Паттерн доступа — самый определяющий фактор при выборе индекса. Вы только сравниваете столбец через '=', используете диапазон/сортировку (>, <, BETWEEN, ORDER BY) или ищете по полному тексту, полю JSON либо геометрическим данным/массивам? Начните отсюда; не выбирайте тип индекса, пока потребность не ясна.

• Смешанные равенство и диапазон/сортировка (=, >, <, BETWEEN, ORDER BY)
• Только равенство (=), без диапазона и сортировки
• Полнотекстовый поиск, поле JSON/JSONB, массив или геометрический поиск
• Запрос всегда нацелен на одно и то же небольшое подмножество строк (например, WHERE status='active')

Какова кардинальность столбца (число различных значений)?

Если нужны диапазон/сортировка, по умолчанию используется B-tree; однако для столбцов с очень малым числом различных значений (низкая кардинальность: пол, статус, да/нет) в хранилище данных с преобладанием чтения лучше может подойти индекс Bitmap. У столбца много уникальных значений или несколько повторяющихся?

• Высокая кардинальность (много уникальных значений) или OLTP-нагрузка
• Низкая кардинальность и преобладание чтения / хранилище данных (DWH)

Читает ли запрос только столбцы, входящие в индекс?

Паттерн доступа подходит для B-tree. Последняя оптимизация: если все столбцы, которые запрос возвращает и по которым фильтрует, есть в индексе, СУБД может ответить только по индексу, не обращаясь к таблице (index-only / покрывающее сканирование). Ваш запрос читает несколько столбцов или ему нужна большая часть строки?

• Да, несколько столбцов; можно ответить без обращения к таблице
• Нет, достаточно стандартного доступа

Рекомендация: B-tree (по умолчанию)

B-tree — сбалансированная древовидная структура, поддерживающая широкий спектр паттернов доступа: равенство, диапазон, сортировку (ORDER BY), сопоставление по префиксу с LIKE 'abc%' и MIN/MAX. Это тип индекса по умолчанию в большинстве реляционных СУБД и правильная отправная точка при сомнениях.

Рекомендация: хеш-индекс (только равенство)

Хеш-индекс хранит хеш значения столбца и очень быстр только для поиска по точному равенству ('='). Он не поддерживает поиск по диапазону, сортировку и поиск по префиксу, поэтому область его применения узка.

Рекомендация: индекс Bitmap (низкая кардинальность / DWH)

Индекс Bitmap хранит битовый массив для каждого различного значения и очень быстро объединяет несколько условий с низкой кардинальностью побитовыми AND/OR. Он силён в нагрузках с преобладанием чтения, таких как хранилища данных и отчётность.

Рекомендация: GIN / GiST (текст, JSON, массивы, геометрия)

В PostgreSQL есть специализированные семейства индексов для типов доступа, с которыми стандартный B-tree не справляется. GIN указывает на множество компонентов внутри значения (слова, ключи JSONB, элементы массива); GiST подходит для геометрических/диапазонных запросов и поиска ближайших соседей.

Рекомендация: частичный / фильтрованный индекс

Частичный индекс индексирует только строки, удовлетворяющие условию (WHERE ...), а не всю таблицу. Если запросы всегда нацелены на одно и то же небольшое подмножество, индекс становится намного меньше и быстрее, а стоимость записи снижается.

Рекомендация: покрывающий (INCLUDE) индекс

Покрывающий индекс содержит все столбцы, нужные запросу, что позволяет СУБД ответить только по индексу, вообще не обращаясь к таблице (heap) (index-only scan). Даёт значительное ускорение для часто выполняемых запросов, возвращающих мало столбцов.