Оператор CREATE INDEX в PostgreSQL используется для создания индексов, которые ускоряют выполнение запросов за счёт оптимизации поиска данных в таблицах.
Основы оператора CREATE INDEX
Базовый синтаксис:
CREATE INDEX index_name ON table_name (column_name);Пример создания индекса для ускорения поиска по столбцу email в таблице users:
CREATE INDEX users_email_idx ON users (email);Уникальный индекс
Создание индекса, который гарантирует уникальность значений:
CREATE UNIQUE INDEX users_email_unique ON users (email);Индекс для нескольких столбцов
Можно создать индекс сразу для нескольких столбцов:
CREATE INDEX users_name_age_idx ON users (name, age);Использование индексов с функциями
Индексы могут использовать функции для хранения вычисляемых значений:
CREATE INDEX users_lower_email_idx ON users (LOWER(email));Полнотекстовый индекс (GIN)
Для полнотекстового поиска используется индекс GIN:
CREATE INDEX users_bio_gin_idx ON users USING GIN(to_tsvector('russian', bio));Частичный индекс
Можно создать индекс только для части данных, удовлетворяющих определённому условию:
CREATE INDEX active_users_idx ON users (email) WHERE is_active = true;Создание индекса без блокировки таблицы
При создании индекса стандартным способом таблица блокируется на запись. Чтобы избежать блокировки, используется CONCURRENTLY:
CREATE INDEX CONCURRENTLY users_email_concurrent_idx ON users (email);Этот способ позволяет продолжать операции вставки, обновления и удаления данных во время создания индекса, но требует большего времени выполнения и двух проходов по таблице.