Основные виды индексов: B-TREE, HASH, GIN, GiST

Итак, в мире PostgreSQL есть несколько видов индексов, и каждый из них создан для выполнения своей уникальной роли. Это как выбирать транспортное средство: вы можете использовать велосипед для прогулки по парку, но для поездки на другую сторону города, вероятно, выберете автомобиль. Точно так же разные индексы подходят для разных задач.

В PostgreSQL основные виды индексов включают:

• B-TREE индексы: универсальные индексы для большинства задач.
• HASH индексы: оптимизированы для точного сравнения.
• GIN индексы: идеальны для поиска по массивам и JSONB.
• GiST индексы: используются для сложных типов данных, таких как географические.

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

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

Индексы B-TREE

B-TREE (сокращение от "balanced tree") — это наиболее распространённый тип индекса, основа работы PostgreSQL. Этот индекс создаёт древовидную структуру, в которой данные организованы таким образом, чтобы ускорить поиск, сортировку и фильтрацию.

Представьте себе библиотеку с полками, где каждая полка содержит книги, разложенные в алфавитном порядке. Если вы ищете книгу на букву "М", вам не нужно просматривать все книги подряд — достаточно начать с середины. На похожих принципах работают отбалансированные деревья.

Когда их использовать?

Да почти всегда! B-TREE индексы особенно полезны для:

• Поиска по диапазонам: WHERE price > 100.
• Сортировки: ORDER BY name ASC.
• Поиска по равенству: WHERE id = 42.

Приведём пример создания:

-- Создаём индекс B-TREE для колонки price таблицы products:
CREATE INDEX idx_price ON products(price);

Когда в запросе вы напишете что-то вроде WHERE price > 100, PostgreSQL сможет обратиться к этому индексу и не будет просматривать всю таблицу.

Индексы HASH

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

Это как картотека, где каждая карточка имеет чёткий номер. Вы ищете номер карточки 42, и библиотекарь мгновенно вам её находит. Но если вы скажете: «покажите мне карточки с номерами от 40 до 50», вам откажут.

HASH индексы подходят только для точного поиска:

• WHERE email = 'user@example.com'.
• SELECT ... WHERE id = 123.

Если вам нужны диапазоны или сортировка, то HASH не подойдёт.

Пример создания:

-- Создаём хэш-индекс для колонки email таблицы users:
CREATE INDEX idx_email_hash ON users USING HASH (email);

Теперь PostgreSQL будет использовать данный индекс для запросов вида WHERE email = 'user@example.com'.

Обратите внимание: HASH индексы подходят для специфических случаев и используются реже, чем B-TREE.

Индексы GIN (Generalized Inverted Index)

GIN — это специализированный индекс, который делает настоящую магию с массивами, JSONB и текстовыми данными. Представьте, что у вас есть шкаф с тысячами ящиков, и каждый ящик подписан. Например, в ящике с надписью "яблоки" лежат все яблоки, в ящике "бананы" — бананы. Чтобы найти яблоки или бананы, вам не нужно рыться во всех ящиках — вы идёте сразу к нужному.

GIN индексы нужны для:

• Поиска по массивам: @> (содержит), <@ (содержится).
• JSONB данных: WHERE jsonb_data @> '{"key": "value"}'.

Пример создания

-- Создаём GIN-индекс для колонки tags, содержащей массивы:
CREATE INDEX idx_tags_gin ON products USING GIN (tags);

Теперь PostgreSQL сможет эффективно находить продукты, где тегами являются, например, "электроника" и "рекомендуемые".

Индексы GiST (Generalized Search Tree)

GiST индексы — это мощный инструмент для работы с более сложными типами данных, включая географические координаты и диапазоны. Они строят деревья, которые оптимизированы для пространственного поиска и поиска по диапазонам.

Представьте себе карту города, где каждая точка отмечена на основе её координат. Вы можете быстро найти все точки в радиусе 5 км от текущей.

GiST подходят для:

• Географических данных: SELECT ... FROM locations WHERE ST_DWithin(geom, point, distance).
• Поиска по диапазонам: WHERE date_range && '[2023-01-01, 2023-12-31]'.

Пример:

-- Создаём GiST-индекс для колонки location, содержащей географические данные:
CREATE INDEX idx_location_gist ON places USING GiST (location);

Теперь можно выполнять сложные географические запросы, такие как поиск ближайших точек.

Таблица сравнения индексов

Теперь вы знакомы с основными типами индексов в PostgreSQL, а также их применением. Запомните: выбор индекса — это стратегический шаг, от которого зависит скорость работы ваших запросов. Шах и мат, тормоза!