Ответ
Индексы в PostgreSQL — это отдельные структуры данных (чаще всего B-деревья), которые хранят отсортированные или специальным образом организованные значения одного или нескольких столбцов таблицы вместе с указателями на соответствующие строки (TID). Их цель — позволить СУБД быстро находить строки, соответствующие условию WHERE, без полного сканирования таблицы (Sequential Scan).
Основные типы индексов и их применение:
| Тип индекса | Структура | Оптимальное использование | Пример создания |
|---|---|---|---|
| B-tree (по умолчанию) | Сбалансированное дерево | Операции сравнения (=, <, >, BETWEEN, IN), сортировка (ORDER BY), LIKE 'pattern%'. |
CREATE INDEX idx_user_email ON users(email); |
| Hash | Хэш-таблица | Только операция точного равенства (=). Быстрее B-tree для простых равенств, но не поддерживает диапазоны и сортировку. |
CREATE INDEX idx_hash ON table USING HASH (column); |
| GIN (Generalized Inverted Index) | Обратный индекс | Составные типы: массивы (@>), JSONB (?, @>), полнотекстовый поиск (to_tsvector). Хорош, когда значение содержит множество элементов. |
CREATE INDEX idx_gin_tags ON posts USING GIN (tags); |
| GiST/SP-GiST | Деревья для сложных данных | Геометрические типы (<->, &&), поиск по диапазонам, полнотекстовый поиск (альтернатива GIN). |
CREATE INDEX idx_gist_point ON locations USING GiST (coord); |
| BRIN (Block Range INdex) | Сводка по блокам | Очень большие таблицы, где данные физически отсортированы (например, по timestamp). Минимальный размер, но менее точен. | CREATE INDEX idx_brin_date ON logs USING BRIN (created_at); |
Как планировщик запросов использует индекс?
Планировщик оценивает стоимость операций. Индекс будет использован, если он сокращает количество просматриваемых строк и стоимость доступа по индексу + чтения строк меньше стоимости полного сканирования таблицы. Используйте EXPLAIN ANALYZE для анализа.
-- Пример анализа плана запроса
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped';
-- В выводе ищите "Index Scan" или "Bitmap Index Scan"Практические рекомендации:
- Индексируйте столбцы в
WHERE,JOIN,ORDER BY. - Рассматривайте составные индексы (multicolumn): Порядок столбцов важен. Индекс
(a, b)полезен для поиска поaи по(a, b), но не по одномуb. - Используйте частичные индексы (Partial Indexes) для подмножества строк, что экономит место и ускоряет запросы.
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true; - Помните о компромиссах: Индексы ускоряют
SELECT, но замедляютINSERT,UPDATE,DELETE(так как нужно обновлять и саму таблицу, и индексы) и занимают место на диске. - Выраженные индексы (Indexes on Expressions): Можно индексировать результат функции.
CREATE INDEX idx_lower_name ON users(LOWER(last_name)); -- Запрос сможет использовать индекс: SELECT ... WHERE LOWER(last_name) = 'иванов';