Что такое индексы в PostgreSQL, какие типы индексов существуют и для каких операций они применяются

Что проверяют

Знаете ли вы, что типов больше одного, и понимаете ли, какой под какую задачу.

Как отвечать

Индекс это отдельная структура, ускоряющая поиск ценой места и замедления записи.

• B-tree, по умолчанию: равенство, диапазоны, сортировка, префикс строки (LIKE 'abc%'). Покрывает девяносто процентов случаев;
• Hash: только равенство. С версии 10 журналируется и потому пригоден, но выигрыш перед B-tree невелик;
• GIN: когда в одном значении много элементов. Полнотекстовый поиск, массивы, jsonb. Быстрый поиск, дорогая вставка;
• GiST: геометрия, диапазоны, поиск ближайших соседей, нечёткий поиск через расширения;
• SP-GiST: несбалансированные структуры, префиксные деревья, точки;
• BRIN: очень большие таблицы, где данные физически упорядочены по колонке (обычно по времени). Хранит границы блоков, поэтому крошечный по размеру.

Помимо типов важны разновидности: составной (работает слева направо), частичный (where is_active, меньше и дешевле), покрывающий (INCLUDE, чтобы не ходить в таблицу), функциональный (по lower(email)), уникальный как ограничение целостности.

Практика: строить в проде через CREATE INDEX CONCURRENTLY, чтобы не блокировать запись; проверять пользу по EXPLAIN ANALYZE; удалять неиспользуемые, их видно в pg_stat_user_indexes.

Где спотыкаются

Знают только B-tree.

Заводят индекс на колонку с двумя значениями и удивляются, что планировщик его игнорирует.

Не знают, что функция над колонкой отключает обычный индекс.

Строят индексы обычной командой на большой боевой таблице и блокируют запись.