Индексы PostgreSQL: B-tree, GIN, GiST, BRIN, hash

При переполнении страница делится (split), разделитель поднимается вверх; high key и right link дают параллельный доступ. Дедупликация хранит повтор один раз с posting list; suffix truncation обрезает разделители; INCLUDE покрывает запрос.

B-tree - сбалансированное дерево из 8-КБ страниц: meta page указывает на корень, внутренние страницы хранят разделители и downlink, листья - ключ и ctid. Большой fanout даёт 3-4 уровня на миллиарды строк.

BRIN хранит сводку min/max на диапазон блоков (по умолчанию 128 страниц), а не запись на строку - индекс крошечный. Работает только при физической корреляции (append-only время); на перемешанных данных бесполезен.

GIN - инвертированный индекс для данных, где у строки много элементов: массивы, JSONB, полнотекст. Хранит «элемент → posting list из ctid». Быстр на чтение, дорог на запись; B-tree бессилен против LIKE '%x%', GIN решает.

GiST - каркас деревьев для пересечений, содержания и расстояний (&&, @>, <->): геометрия, диапазоны, exclusion constraint. SP-GiST делит пространство на непересекающиеся части (точки, IP, префиксы). Оба lossy.

Hash поддерживает только оператор равенства и ищет по хешу за одно-два обращения. С PostgreSQL 10 он журналируется (WAL) и безопасен. Не умеет диапазоны, сортировку и многоколоночность - по умолчанию бери B-tree.

Индекс работает не «на колонке», а для конкретных операторов над ней. Связь тип+оператор+метод доступа задаёт operator class. Через каталог pg_amop можно узнать, какой AM поддерживает оператор: @> знают gin и gist, не btree.

Условие sargable, если индекс по колонке к нему применим. Каст или функция над колонкой (created::date, lower(email)) делают его не-sargable - индекс игнорируется, план уходит в Seq Scan. Лечение - переписать или индекс по выражению.

Индекс - отсортированный список. Составной (a,b,c) работает по префиксу слева направо, не по средней колонке. Покрывающие (INCLUDE), частичные (WHERE) и по выражению сужают работу; лишние индексы - налог на запись.