Ответ
Индексы в 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) = 'иванов';
Ответ 18+ 🔞
А, индексы в постгресе! Ну это ж классика, про которую все слышали, но половина нихуя не понимает, как они реально работают. Сейчас разжуёшь.
Представь себе библиотеку, блядь. Табличка в базе — это как стопка книг, скинутых в кучу в подвале. Чтобы найти «Войну и мир», тебе придётся перерыть всё, страницу за страницей, до охуения. Это Sequential Scan, полное сканирование, пиздец и тоска.
А индекс — это, сука, картотечный ящик. Отдельная хуйня, где на карточках написано, например, «Автор: Толстой Л.Н.» и сбоку мелкими буковками: «лежит в подвале, на третьей полке слева, пятая книга сверху». Подбежал к ящику, быстренько нашёл карточку (потому что карточки отсортированы по алфавиту, ёпта!) — и сразу знаешь, куда идти. Вот и вся магия.
Какие бывают эти ящики-индексы, и на какой случай какой брать:
| Тип индекса | На что похож | Когда юзать | Как создать |
|---|---|---|---|
| B-tree (дерево, ёбаное) | Алфавитный указатель в конце учебника. Самый обычный, дефолтный. | Когда ищешь по равенству (=), по диапазону (>, <, BETWEEN), сортируешь (ORDER BY) или ищешь что-то вроде LIKE 'Ивано%'. |
CREATE INDEX idx_email ON users(email); |
| Hash (Хэш, блядь) | Телефонная книжка, где имена отсортированы не по алфавиту, а по какой-то своей, ебучей формуле. | ТОЛЬКО для точного совпадения, когда пишешь WHERE id = 5. Быстрее дерева, но если нужно найти всех, у кого id больше 10 — нихуя не поможет, сорян. |
CREATE INDEX idx_hash ON table USING HASH (column); |
| GIN (Джин, чтоб он вылетел из бутылки) | Предметный указатель в конце толстой книги. «Яблоко: с. 5, 10, 45, 231». | Для сложных хуйнёнок: массивов (есть ли в списке тегов 'база данных'), JSONB-документов (есть ли поле 'active'), полнотекстового поиска. | CREATE INDEX idx_tags ON posts USING GIN (tags); |
| GiST/SP-GiST (Джист, блядь) | Карта местности с разными слоями: реки, дороги, леса. | Для геоданных (найти ближайшие к мне бары), для поиска по интервалам, для текста (как альтернатива GIN). | CREATE INDEX idx_coord ON bars USING GiST (coordinates); |
| BRIN (Брин, как актриса) | Оглавление учебника, где написано: «Страницы 50-100: глава про интегралы». Очень грубо, зато мало места жрёт. | Для ОГРОМНЫХ, просто овердохуищных таблиц, где данные уже лежат в каком-то порядке (например, логи по дате). Быстро отсечёт ненужные куски, но внутри куска будет тупить. | CREATE INDEX idx_date ON logs USING BRIN (created_at); |
А как база решает, юзать индекс или нет?
А там, внутри, сидит такой планировщик, хитрая жопа. Он прикидывает: «Так, если пойти через индекс, нужно прочитать 100 карточек, а потом сходить за 100 книгами. А если просто всю кучу перебрать — нужно прочитать 10 000 книг. Окей, по индексу дешевле». И выбирает быстрый путь. Чтобы подсмотреть его мысли — юзай EXPLAIN ANALYZE.
-- Вот так подглядываешь в его мозги
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 123 AND status = 'shipped';
-- Смотри в выводе — если увидишь "Index Scan", значит, ящик-картотека был использован, ура!Практические советы, чтоб не обосраться:
- Индексируй то, по чему ищешь. Столбцы в
WHERE,JOINиORDER BY— главные кандидаты. Индекс на столбецпол, где значения «М» и «Ж» — это обычно идиотизм, потому что отфильтруется нихуя. - Составные индексы — штука мощная, но с подвохом. Индекс
(фамилия, имя)поможет найти «Иванов Иван» и даже просто всех «Ивановых», но нихуя не поможет найти всех «Иванов» по имени. Порядок столбцов — всё, ёпта! - Частичные индексы — гениальная вещь. Зачем индексировать ВСЕХ пользователей, если ты 90% запросов делаешь только по активным? Проиндексируй только их и сэкономь место со скоростью.
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true; - Не наеби себя. Каждый новый индекс — это доп. работа для базы при вставке или обновлении (надо ведь все ящики-картотеки поправить) и доп. место на диске. Делай их с умом, а не как попало.
- Индекс по выражению. Бывает, что ищешь не
WHERE name = 'Иванов', аWHERE LOWER(name) = 'иванов'. Так вот, можно сделать индекс сразу по результату функцииLOWER(name), и тогда поиск будет быстрым.CREATE INDEX idx_lower_name ON users(LOWER(last_name)); -- Теперь запрос `SELECT ... WHERE LOWER(last_name) = 'иванов'` полетит.
Вот и вся наука, если без академического бреда. Главное — понимать, что индекс не волшебная таблетка, а инструмент. Им можно и гвоздь забить, и себе по ебалу получить, если использовать неправильно.