Индексы в базах данных работают по тому же принципу, что и оглавление в книге. Без них приходится просматривать каждую страницу в поисках нужной информации. И если в книге это просто неудобно, то в базе данных такой полный перебор может растянуться на часы.

PostgreSQL предлагает несколько типов индексов, и каждый подходит для своих сценариев. B-tree индексы — стандартный выбор для большинства случаев. Они эффективны для поиска по точному совпадению и для диапазонных запросов. GiST индексы незаменимы для геоданных и полнотекстового поиска. Hash индексы работают только с равенством, зато делают это максимально быстро.

Создание индекса — это только начало пути. Планировщик запросов не всегда использует доступные индексы, особенно если статистика устарела или запрос написан неоптимально. Команда EXPLAIN ANALYZE показывает реальный план выполнения запроса. Если вместо Index Scan видите Sequential Scan — самое время разобраться, почему индекс игнорируется.

При создании составных индексов порядок колонок критически важен. Индекс по (last_name, first_name) поможет найти всех однофамильцев, а вот индекс по (first_name, last_name) для такого запроса будет бесполезен. Первая колонка должна быть самой селективной — той, что лучше всего сужает результаты поиска.

Не стоит индексировать все подряд — это замедляет операции вставки и обновления. Каждый индекс нужно поддерживать в актуальном состоянии, что требует дополнительных ресурсов. Особенно осторожно нужно подходить к индексам на часто изменяемых колонках.

Частичные индексы — мощный инструмент оптимизации. Вместо того чтобы индексировать всю таблицу, можно создать индекс только для нужного подмножества данных. Например, для активных пользователей или для заказов за последний месяц. Это экономит место и ускоряет обновление индекса.

Покрывающие индексы позволяют получить все нужные данные прямо из индекса, без обращения к таблице. Для этого в INCLUDE нужно добавить все колонки, которые встречаются в SELECT. Такие индексы занимают больше места, но могут радикально ускорить запросы.

Регулярная переиндексация помогает бороться с фрагментацией. Со временем индексы "разбухают" из-за операций вставки и удаления. REINDEX перестраивает индекс заново, что улучшает его эффективность. Только не забудьте выбрать подходящее окно для обслуживания — операция требует блокировки.

Мониторинг использования индексов не менее важен, чем их создание. pg_stat_user_indexes показывает, как часто используется каждый индекс. Если индекс не используется месяцами, а места занимает много — стоит задуматься о его удалении.

🏴‍☠️ @happy_devops