Что такое индексы в PostgreSQL?

Индексы в PostgreSQL — это структуры данных, ускоряющие поиск и выборку данных из таблиц. Они работают как предметный указатель в книге: вместо полного сканирования таблицы система использует индекс для быстрого перехода к нужным строкам.

Типы индексов по умолчанию:

• B-tree: Наиболее распространенный тип, используется для широкого спектра запросов, включая сравнения (=, <, >) и поиск по диапазонам.
• Hash: Используется для сравнений на равенство (=). Менее гибок, чем B-tree, и не поддерживается в транзакциях.
• GiST (Generalized Search Tree): Обобщенный фреймворк для создания различных типов индексов, поддерживает сложные типы данных и операции (например, геометрические данные, полнотекстовый поиск).
• SP-GiST (Space-Partitioned GiST): Еще один фреймворк для создания индексов, оптимизированный для данных с несимметричной структурой (например, деревья, графы).
• GIN (Generalized Inverted Index): Используется для индексирования столбцов, содержащих массивы или списки значений (например, jsonb типы, текстовые поля для полнотекстового поиска).
• BRIN (Block Range Index): Индекс для очень больших таблиц, хранящий информацию о диапазонах значений в блоках данных. Эффективен для столбцов с естественным порядком данных (например, временные метки).

Преимущества использования индексов:

• Ускорение запросов SELECT: Значительно сокращают время выполнения запросов при поиске по проиндексированным столбцам.
• Оптимизация JOIN операций: Помогают быстрее находить соответствующие строки при объединении таблиц.
• Ускорение сортировки: Сортировка по проиндексированным столбцам может выполняться быстрее.

Недостатки использования индексов:

• Замедление операций INSERT, UPDATE, DELETE: При изменении данных в таблице необходимо также обновить индекс, что требует дополнительных накладных расходов.
• Занимают место на диске: Индексы являются отдельными структурами данных и требуют дискового пространства.
• Необходимость обслуживания: Индексы могут фрагментироваться со временем и требовать периодической перестройки (REINDEX).

Создание индекса:

-- Создание B-tree индекса на столбце user_id в таблице users
CREATE INDEX users_user_id_idx ON users (user_id);

Создание составного индекса:

-- Создание B-tree индекса на столбцах order_date и customer_id в таблице orders
CREATE INDEX orders_date_customer_idx ON orders (order_date, customer_id);

Удаление индекса:

-- Удаление индекса users_user_id_idx
DROP INDEX users_user_id_idx;