Какой тип индексов поддерживает PostgreSQL?

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

PostgreSQL поддерживает несколько типов индексов, каждый из которых оптимизирован для определённых типов запросов и данных. Понимание различных типов индексов критично для оптимизации производительности базы данных в Java приложениях.

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

1. B-Tree (индекс по умолчанию)

B-Tree — самый универсальный и часто используемый тип индекса. Он работает с любыми сравниваемыми данными.

Характеристики:

• Поддерживает операторы: <, <=, =, >=, >
• Отличен для диапазонных запросов
• Сортирует данные в дерево, где каждый узел содержит несколько ключей
• Сбалансированное дерево гарантирует O(log n) доступ

-- Создание B-Tree индекса (по умолчанию)
CREATE INDEX idx_users_email ON users(email);

-- Эквивалентно
CREATE INDEX idx_users_email ON users USING btree(email);

-- B-Tree для диапазонных запросов
CREATE INDEX idx_users_age ON users(age);

SELECT * FROM users WHERE age > 18 AND age < 65; -- Быстрый запрос

// Java код для работы с B-Tree индексированной таблицей
public class UserRepository {
    private JdbcTemplate jdbcTemplate;
    
    // Этот запрос использует индекс idx_users_email
    public User findByEmail(String email) {
        String sql = "SELECT * FROM users WHERE email = ?";
        return jdbcTemplate.queryForObject(sql, new UserRowMapper(), email);
    }
    
    // Этот запрос использует индекс idx_users_age для диапазона
    public List<User> findByAgeRange(int minAge, int maxAge) {
        String sql = "SELECT * FROM users WHERE age >= ? AND age <= ?";
        return jdbcTemplate.query(sql, new UserRowMapper(), minAge, maxAge);
    }
}

2. Hash индекс

Hash — используется для точного совпадения (equality) с быстрым доступом.

Характеристики:

• Поддерживает только оператор =
• O(1) среднее время доступа
• Используется для точного поиска
• НЕ работает с диапазонами
• НЕ сортирует результаты

-- Создание Hash индекса
CREATE INDEX idx_users_id_hash ON users USING hash(id);

SELECT * FROM users WHERE id = 1; -- Быстро (Hash индекс)
SELECT * FROM users WHERE id > 1; -- Медленно (Hash не поддерживает диапазоны)

public class FastLookupRepository {
    // Hash индекс оптимален для точного поиска по ID
    public User findById(Long id) {
        String sql = "SELECT * FROM users WHERE id = ?";
        return jdbcTemplate.queryForObject(sql, new UserRowMapper(), id);
    }
}

3. GiST (Generalized Search Tree)

GiST — универсальный тип индекса для многомерных данных, полнотекстового поиска и геометрических типов.

Характеристики:

• Поддерживает многие типы данных
• Используется для текстового поиска, IP адресов, диапазонов
• Более медленный чем B-Tree для простых операций
• Идеален для специализированных типов данных

-- GiST индекс для полнотекстового поиска
CREATE INDEX idx_documents_search ON documents 
USING gist(document_text);

-- GiST для IPv4 адресов
CREATE INDEX idx_ips ON network_logs USING gist(ip_address inet_ops);

-- Полнотекстовый поиск
SELECT * FROM documents 
WHERE document_text @@ to_tsquery('english', 'database & search');

public class DocumentRepository {
    // Полнотекстовый поиск использует GiST индекс
    public List<Document> searchByContent(String searchTerm) {
        String sql = "SELECT * FROM documents " +
                     "WHERE document_text @@ to_tsquery('english', ?)";
        return jdbcTemplate.query(sql, new DocumentRowMapper(), searchTerm);
    }
}

4. GIN (Generalized Inverted Index)

GIN — индекс для поиска по составным значениям (массивы, JSON, hstore).

Характеристики:

• Очень быстрый поиск по элементам
• Медленная вставка/обновление (требует перестройки)
• Идеален для JSON и массивов
• Занимает много памяти

-- GIN индекс для JSON данных
CREATE INDEX idx_users_metadata_gin ON users USING gin(metadata);

-- GIN индекс для массивов
CREATE INDEX idx_tags_gin ON posts USING gin(tags);

-- Поиск по JSON
SELECT * FROM users 
WHERE metadata @> '{"premium": true}';

-- Поиск в массиве
SELECT * FROM posts 
WHERE 'java' = ANY(tags);

public class MetadataRepository {
    // Поиск по JSON использует GIN индекс
    public List<User> findPremiumUsers() {
        String sql = "SELECT * FROM users " +
                     "WHERE metadata @> ?::jsonb";
        return jdbcTemplate.query(sql, new UserRowMapper(), 
                                  "{\"premium\": true}");
    }
    
    // Поиск в массиве
    public List<Post> findPostsByTag(String tag) {
        String sql = "SELECT * FROM posts WHERE ? = ANY(tags)";
        return jdbcTemplate.query(sql, new PostRowMapper(), tag);
    }
}

5. BRIN (Block Range INdex)

BRIN — компактный индекс для больших таблиц с последовательными данными.

Характеристики:

• Очень маленький размер
• Идеален для больших отсортированных таблиц
• Медленнее B-Tree
• Требует меньше памяти

-- BRIN индекс для большой таблицы логов
CREATE INDEX idx_logs_timestamp_brin ON logs 
USING brin(created_at);

SELECT * FROM logs 
WHERE created_at > '2024-01-01' AND created_at < '2024-01-31';

6. SP-GiST (Space-Partitioned GiST)

SP-GiST — оптимизированный для адресных структур данных (деревья, точки, диапазоны).

Используется для:

• Частичного совпадения текста
• Географических координат
• Иерархических данных

CREATE INDEX idx_locations_spgist ON locations USING spgist(coordinates);

Сравнительная таблица индексов

Практические рекомендации для Java разработчика

public class IndexSelectionGuide {
    // 1. Email, ID, статусы → B-Tree (по умолчанию)
    // CREATE INDEX ON users(email);
    
    // 2. Диапазонные запросы (age, date) → B-Tree
    // CREATE INDEX ON orders(created_at);
    
    // 3. Точный поиск с высокой нагрузкой → Hash
    // CREATE INDEX ON users USING hash(id);
    
    // 4. JSON/JSONB поля → GIN
    // CREATE INDEX ON users USING gin(metadata);
    
    // 5. Массивы → GIN
    // CREATE INDEX ON posts USING gin(tags);
    
    // 6. Очень большие таблицы с последовательными данными → BRIN
    // CREATE INDEX ON events USING brin(timestamp);
}

Заключение

ПостреSQL предоставляет 6 основных типов индексов, каждый оптимизирован для определённых сценариев. B-Tree — самый универсальный и рекомендуется по умолчанию. Для специализированных задач (JSON, полнотекстовый поиск, большие таблицы) используй GIN, GiST или BRIN. Правильный выбор индекса может кратно улучшить производительность запросов.