Skip to content

Лабораторная работа 11: Сравнение типов индексов (B-tree, GIN, GiST)

Теоретические сведения

1. Индекс B-tree (Balanced Tree)

  • Описание: Стандартный индекс, используемый по умолчанию в PostgreSQL. Работает по принципу сбалансированного дерева.
  • Особенности:
  • Поддерживает операции сравнения (=, >, <, BETWEEN).
  • Эффективен для уникальных значений и сортировки.
  • Оптимален для данных с высокой селективностью (например, первичные ключи).
  • Примеры использования:
  • Поиск по диапазону дат: WHERE date BETWEEN '2023-01-01' AND '2023-12-31'.
  • Уникальные идентификаторы: WHERE id = 123.
  • Сортировка: ORDER BY name.

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

  • Описание: Индекс для работы с составными данными (массивы, JSONB, полнотекстовый поиск).
  • Особенности:
  • Хранит отдельные элементы значений (например, слова текста или элементы массива).
  • Эффективен для поиска вхождения элемента (@>, <@, &&).
  • Медленнее при вставке/обновлении данных из-за сложной структуры.
  • Примеры использования:
  • Поиск по массивам: WHERE tags @> '{postgres}'.
  • JSONB-поля: WHERE metadata->'author' = 'John'.
  • Полнотекстовый поиск: WHERE to_tsvector('english', text) @@ to_tsquery('sql').

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

  • Описание: Универсальный индекс для сложных типов данных и нечеткого поиска.
  • Особенности:
  • Поддерживает геометрические данные, диапазоны, полнотекстовый поиск.
  • Позволяет определять собственные стратегии индексирования.
  • Может быть менее точен, чем GIN, но быстрее для некоторых операций.
  • Примеры использования:
  • Геопространственные данные: WHERE geom_point <@ 'BOX(10,20,30,40)'.
  • Поиск по временным диапазонам: WHERE period && '[2023-01-01, 2023-12-31]'.
  • Полнотекстовый поиск с ранжированием.

Практические задания

1. Создание таблицы и индексов

-- Создаем таблицу для тестирования
CREATE TABLE test_data (
    id SERIAL PRIMARY KEY,
    content TEXT,
    json_data JSONB,
    geo_data GEOMETRY(Point)
);

-- Заполняем таблицу тестовыми данными (например, 100 000 строк)
INSERT INTO test_data (content, json_data, geo_data)
SELECT 
    md5(random()::text),
    jsonb_build_object('tags', array[md5(random()::text), md5(random()::text)]),
    ST_MakePoint(random()*100, random()*100)
FROM generate_series(1, 100000);

-- Создаем индексы
CREATE INDEX btree_idx ON test_data USING btree (id);
CREATE INDEX gin_idx ON test_data USING gin (json_data);
CREATE INDEX gist_idx ON test_data USING gist (geo_data);

2. Сравнение размера индексов

SELECT 
    indexname, 
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes 
WHERE tablename = 'test_data';

Пример вывода:

 indexname  | size
------------+-------
 btree_idx  | 2208 kB
 gin_idx    | 5120 kB
 gist_idx   | 4096 kB

3. Тестирование скорости выборки

Для B-tree:

EXPLAIN ANALYZE SELECT * FROM test_data WHERE id = 50000;
-- Время выполнения: ~0.1 мс

Для GIN (JSONB):

EXPLAIN ANALYZE SELECT * FROM test_data WHERE json_data @> '{"tags": ["abc"]}';
-- Время выполнения: ~2 мс

Для GiST (геоданные):

EXPLAIN ANALYZE SELECT * FROM test_data 
WHERE ST_DWithin(geo_data, ST_MakePoint(50, 50), 10);
-- Время выполнения: ~5 мс

4. Тестирование полнотекстового поиска

-- Добавляем tsvector-колонку
ALTER TABLE test_data ADD COLUMN content_tsv TSVECTOR;
UPDATE test_data SET content_tsv = to_tsvector('english', content);

-- Создаем индексы GIN и GiST для полнотекста
CREATE INDEX gin_tsv_idx ON test_data USING gin (content_tsv);
CREATE INDEX gist_tsv_idx ON test_data USING gist (content_tsv);

-- Тестируем скорость
EXPLAIN ANALYZE SELECT * FROM test_data 
WHERE content_tsv @@ to_tsquery('english', 'abc');
-- GIN: ~1 мс, GiST: ~3 мс

Анализ результатов

  1. B-tree:
  2. Минимальный размер, максимальная скорость для точных запросов.

  3. Идеален для первичных ключей и сортировки.

  4. GIN:

  5. Большой размер, но высокая скорость для составных данных.

  6. Лучший выбор для JSONB, массивов и полнотекста.

  7. GiST:

  8. Универсален для геоданных и нестандартных типов.
  9. Медленнее GIN для полнотекста, но поддерживает больше операций.

Рекомендации: - Используйте B-tree для простых запросов. - Выбирайте GIN для работы с массивами и JSONB. - Применяйте GiST для геоданных или комбинированных условий.