Какой тип индекса лучше применять для поля varchar в PostgreSQL?

Типы индексов для поля varchar в PostgreSQL

Это важный вопрос о оптимизации баз данных. В PostgreSQL есть несколько типов индексов, каждый с своими преимуществами для varchar полей.

Основные типы индексов в PostgreSQL

PostgreSQL поддерживает несколько типов индексов через параметр USING:

CREATE INDEX idx_name ON table_name USING index_type (column_name);

1. B-Tree Index (по умолчанию) — УНИВЕРСАЛЬНЫЙ ВЫБОР

-- Явное указание (хотя это и по умолчанию)
CREATE INDEX idx_users_email ON users USING BTREE (email);

-- Или просто (B-Tree по умолчанию)
CREATE INDEX idx_users_email ON users (email);

Когда использовать B-Tree для varchar:

-- Точный поиск
SELECT * FROM users WHERE email = 'john@example.com';

-- Диапазонный поиск
SELECT * FROM users WHERE username >= 'a' AND username < 'b';

-- Сортировка
SELECT * FROM users ORDER BY email;

-- LIKE с префиксом
SELECT * FROM users WHERE email LIKE 'john@%';

Преимущества:

• Универсальный
• Работает для всех типов сравнений (=, <, >, <=, >=)
• Поддерживает сортировку
• Хороший баланс между скоростью и памятью

Производительность:

• Поиск: O(log n)
• Диапазон: O(log n + результаты)
• Память: Средняя

2. Hash Index — Только точный поиск

CREATE INDEX idx_users_email_hash ON users USING HASH (email);

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

-- Только точные совпадения
SELECT * FROM users WHERE email = 'john@example.com';

-- БЫСТРО! O(1) вместо O(log n)

НЕ подходит для:

-- LIKE поиск — не работает с HASH индексом
SELECT * FROM users WHERE email LIKE 'john@%';

-- Диапазонный поиск
SELECT * FROM users WHERE email > 'john@example.com';

-- Сортировка
SELECT * FROM users ORDER BY email;

Примечание: В современных версиях PostgreSQL Hash индексы поддерживают индексные сканирования, но их используют редко.

3. GIN Index — Для полнотекстового поиска

-- Для полнотекстового поиска (tsvector)
CREATE INDEX idx_articles_content ON articles USING GIN (to_tsvector('english', content));

-- Использование
SELECT * FROM articles 
WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database');

Когда использовать GIN для varchar:

• Полнотекстовый поиск по большим текстовым полям
• Поиск в JSON массивах
• Поиск подстрок в больших текстах

Примеры:

-- Индекс для полнотекстового поиска
CREATE INDEX idx_posts_search ON posts USING GIN (
    to_tsvector('english', title || ' ' || content)
);

-- Использование
SELECT * FROM posts 
WHERE to_tsvector('english', title || ' ' || content) @@ 
      to_tsquery('english', 'database & search');

-- Для JSON
ALTER TABLE products ADD COLUMN tags TEXT[];
CREATE INDEX idx_products_tags ON products USING GIN (tags);

SELECT * FROM products WHERE tags @> ARRAY['electronics'];

4. GiST Index — Для пространственных данных

-- Для географических данных
CREATE INDEX idx_locations_geom ON locations USING GIST (coordinates);

-- Для диапазонов (range types)
CREATE INDEX idx_events_range ON events USING GIST (date_range);

Когда использовать GiST для varchar:

• Редко используется для varchar напрямую
• Лучше для пространственных данных (PostGIS)

5. BRIN Index — Для очень больших таблиц

CREATE INDEX idx_logs_timestamp ON logs USING BRIN (created_at);

Преимущества BRIN:

• Очень маленький размер индекса
• Хорош для очень больших таблиц (млн строк)
• Медленнее чем B-Tree, но занимает намного меньше памяти

Когда использовать для varchar:

• Редко, так как varchar обычно не монотонно растёт
• Лучше для временных меток (которые обычно упорядочены)

6. SP-GiST Index — Вещественные данные

CREATE INDEX idx_data_spgist ON data USING SPGIST (value);

Редко используется для varchar.

Сравнительная таблица

Практические примеры для varchar

Пример 1: Email (точный поиск + LIKE)

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(100) NOT NULL
);

-- B-Tree для email (лучший выбор)
CREATE INDEX idx_users_email ON users (email);

-- Использование
SELECT * FROM users WHERE email = 'john@example.com';      -- O(log n)
SELECT * FROM users WHERE email LIKE 'john@%';             -- O(log n) + результаты
SELECT * FROM users ORDER BY email;                        -- Использует индекс

Пример 2: Поиск в статьях (полнотекстовый)

CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(500),
    content TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- GIN индекс для полнотекстового поиска
CREATE INDEX idx_articles_full_search ON articles USING GIN (
    to_tsvector('english', title || ' ' || content)
);

-- Использование
SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || content) @@ 
      to_tsquery('english', 'database:* & search:*');

-- LIKE поиск (менее эффективен)
SELECT * FROM articles WHERE content LIKE '%database%';

Пример 3: Username (точный + диапазон)

CREATE TABLE profiles (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL
);

-- B-Tree
CREATE INDEX idx_profiles_username ON profiles (username);

-- Использование
SELECT * FROM profiles WHERE username = 'john_doe';        -- O(log n)
SELECT * FROM profiles WHERE username LIKE 'john_%';       -- Диапазон
SELECT * FROM profiles ORDER BY username;                  -- Сортировка

Пример 4: Быстрый поиск по полному совпадению

CREATE TABLE codes (
    id BIGSERIAL PRIMARY KEY,
    code VARCHAR(20) NOT NULL
);

-- Если ТОЛЬКО точный поиск, можно использовать Hash
CREATE INDEX idx_codes_hash ON codes USING HASH (code);

-- Использование (O(1))
SELECT * FROM codes WHERE code = 'ABC12345';

Оптимизация с выражениями

-- Индекс по выражению (часто для CI поиска)
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Использование
SELECT * FROM users 
WHERE LOWER(email) = LOWER('John@Example.COM');

-- Индекс для LIKE поиска (с prefix)
CREATE INDEX idx_users_email_prefix ON users 
WHERE email LIKE '%@gmail.com';  -- Частичный индекс

Рекомендации для varchar в PostgreSQL

🏆 Лучший выбор для varchar:

1. B-Tree — универсальный, используй по умолчанию

   • Точный поиск: = ✓
   • Диапазоны: < > ✓
   • LIKE с префиксом: ✓
   • Сортировка: ✓

2. Hash — только если ОЧЕНЬ частые точные поиски

   • Очень редко используется в реальных проектах

3. GIN — для полнотекстового поиска

   • to_tsvector() для английского текста
   • Более гибкий поиск слов

Что выбрать:

Если ищешь: точно → LIKE → диапазоны → сортировка
           → B-Tree ✓

Если ТОЛЬКО точные поиски и O(1) критично
           → Hash (редко)

Если полнотекстовый поиск
           → GIN + to_tsvector()