Какие типы индексов поддерживает PostgreSQL и для каких сценариев они предназначены?

PostgreSQL предоставляет несколько типов индексов, каждый оптимизирован под определённые виды запросов и данных:

Основные типы:

1. B-tree (дерево) — стандартный индекс для большинства операций.

   • Для: равенства (=), диапазонов (<, >, BETWEEN), сортировки (ORDER BY), LIKE 'pattern%'.
   • Пример:

     CREATE INDEX idx_users_email ON users(email);

2. Hash (хэш) — быстрый поиск строгого равенства.

   • Для: только операций = (не поддерживает диапазоны или сортировку).
   • Пример:

     CREATE INDEX idx_users_id_hash ON users USING HASH(id);

3. GIN (Generalized Inverted Index) — для индексирования составных значений.

   • Для: полнотекстового поиска, массивов (@>), JSONB (?, @>), hstore.
   • Пример (полнотекстовый поиск):

     CREATE INDEX idx_docs_content ON documents USING GIN(to_tsvector('english', content));

4. GiST (Generalized Search Tree) — для данных, где расстояние или пересечение имеют значение.

   • Для: геопространственных данных (PostGIS), полнотекстового поиска, диапазонов.
   • Пример (геометрия):

     CREATE INDEX idx_places_geom ON places USING GIST(geom);

5. SP-GiST (Space-Partitioned GiST) — для неоднородных или иерархических данных.

   • Для: IP-адресов (inet, cidr), геометрических типов, текстовых префиксов.
   • Пример:

     CREATE INDEX idx_net_ip ON network USING SPGiST(ip_range);

6. BRIN (Block Range INdex) — для очень больших, естественно отсортированных таблиц.

   • Для: временных рядов, логов, где данные физически упорядочены (например, по timestamp).
   • Пример:

     CREATE INDEX idx_sensor_brin ON sensor_data USING BRIN(created_at);

Ключевой выбор:

• B-tree — универсальный выбор для большинства столбцов.
• GIN/GiST — для сложных типов данных (JSON, массивы, текст, геоданные).
• BRIN — для огромных таблиц с низкой избирательностью запросов, где важен размер индекса.