Как создать индекс в PostgreSQL и проверить его пользу

За несколько минут вы создадите индекс в PostgreSQL 16 и проверите, изменился ли план запроса. В результате станет понятно не только, какую команду выполнить, но и почему сам факт создания индекса ещё не гарантирует ускорения.

Что нужно иметь до начала

• PostgreSQL 16 или совместимую версию. Если у вас PostgreSQL 15 или более ранний выпуск, базовый синтаксис ниже тот же, но детали планировщика и доступные возможности стоит сверить с документацией вашей версии.
• Доступ к базе данных с правом создавать индексы в нужной схеме.
• Таблицу и запрос, который нужно ускорить. В примерах используется таблица orders с полем customer_id.
• Клиент для подключения к PostgreSQL, например psql, либо SQL-консоль в вашей IDE.

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

Индекс создают не «на всякий случай», а под конкретный запрос, который нужно измерить до и после изменения.

Пошаговая инструкция

1. Проверьте версию и подключение

   Сначала убедитесь, что запрос выполняется именно в той базе, которую вы собираетесь менять. Версия важна: поведение планировщика и синтаксис отдельных возможностей могут отличаться.

   SELECT version();
   
   SELECT current_database(), current_schema();

   Проверка: в результате видны версия PostgreSQL, имя базы и схема. Если вы подключились не к той базе, индекс окажется бесполезным для приложения.

2. Найдите запрос, который действительно нужно ускорить

   Индекс имеет смысл, когда запрос регулярно фильтрует, соединяет или сортирует данные по одному и тому же полю. Для первого опыта возьмите выборку заказов конкретного клиента.

   EXPLAIN (ANALYZE, BUFFERS)
   SELECT order_id, created_at, total_amount
   FROM orders
   WHERE customer_id = 42;

   Проверка: PostgreSQL покажет план, фактическое время выполнения и количество прочитанных блоков. До создания индекса запишите значение Execution Time и посмотрите, сколько строк прочитано.

3. Создайте индекс на поле фильтрации

   Для условия WHERE customer_id = 42 подходит обычный B-tree-индекс. Это стандартный тип для сравнений, диапазонов и сортировки по числовым, строковым и временным значениям.

   CREATE INDEX idx_orders_customer_id
   ON orders (customer_id);

   Проверка: команда завершилась сообщением CREATE INDEX без ошибки. Если таблица большая и работает под нагрузкой, обычная команда может блокировать часть операций с таблицей; вариант для работающей системы разбирается ниже.

4. Убедитесь, что индекс появился

   Проверять результат лучше через системный каталог PostgreSQL, а не по памяти названия индекса.

   SELECT schemaname, tablename, indexname, indexdef
   FROM pg_indexes
   WHERE tablename = 'orders';

   Проверка: в списке есть idx_orders_customer_id, а в определении указано поле customer_id. Если строк нет, проверьте схему, имя таблицы и подключение.

5. Повторите тот же замер

   Запустите исходный запрос без изменений. Иначе вы не сравните два одинаковых сценария.

   EXPLAIN (ANALYZE, BUFFERS)
   SELECT order_id, created_at, total_amount
   FROM orders
   WHERE customer_id = 42;

   Проверка: в плане может появиться Index Scan или Bitmap Index Scan. Сравните фактическое время и число блоков с первым замером. На маленькой таблице PostgreSQL может оставить Seq Scan: прочитать её целиком иногда дешевле, чем обращаться к индексу.

6. Проверьте сценарий с сортировкой или диапазоном

   Индекс на одном поле не решает все запросы. Если приложение выбирает заказы клиента и сразу сортирует их по дате, проверьте составной индекс.

   CREATE INDEX idx_orders_customer_created
   ON orders (customer_id, created_at DESC);

   После этого измерьте запрос:

   EXPLAIN (ANALYZE, BUFFERS)
   SELECT order_id, created_at, total_amount
   FROM orders
   WHERE customer_id = 42
   ORDER BY created_at DESC
   LIMIT 20;

   Проверка: план может использовать новый индекс и отказаться от отдельной сортировки. Порядок полей критичен: индекс (customer_id, created_at) хорошо подходит для фильтра по клиенту, но не является универсальной заменой индексу только на created_at.

Почему индекс может не ускорить запрос

PostgreSQL не обязан использовать каждый созданный индекс. Планировщик оценивает стоимость вариантов и выбирает тот, который считает дешевле. На решение влияют размер таблицы, число подходящих строк, статистика и объём данных, который нужно вернуть.

• Таблица маленькая. Последовательное чтение всей таблицы может быть быстрее индексного доступа.
• Условие возвращает большую часть строк. Если подходит значительная доля таблицы, переход через индекс добавляет лишние обращения к данным.
• Статистика устарела. После массовой загрузки данных планировщику может потребоваться обновить статистику. Выполните ANALYZE orders;, затем повторите замер.
• Запрос меняет значение поля. Условие вроде WHERE lower(email) = 'user@example.com' не всегда использует обычный индекс на email. Для выражений нужен отдельный дизайн индекса, а не слепое копирование примера.
• В запросе применено преобразование типа. Сравнение поля и параметра разных типов может привести к плану, который не использует ожидаемый индекс. Типы параметров лучше проверять на стороне приложения.

Что ломается и как это исправить

Ошибка: permission denied for table orders

У пользователя нет нужных прав на таблицу или схему. Подключитесь ролью, которой разрешено создавать индекс, либо попросите администратора выполнить операцию. Не выдавайте приложению расширенные права только ради одного индекса.

Ошибка: relation "orders" does not exist

PostgreSQL не нашёл таблицу в текущем поисковом пути. Проверьте базу и схему, например запросом к information_schema.tables, а затем укажите имя схемы явно: public.orders, если таблица действительно находится в public.

Ошибка: relation "idx_orders_customer_id" already exists

Индекс с таким именем уже создан. Сначала посмотрите его определение в pg_indexes. Не создавайте второй индекс на тех же полях без причины: он займёт место и добавит работу при вставке и изменении строк.

В плане остался Seq Scan

Это не обязательно ошибка. Проверьте размер таблицы, долю совпадающих строк и свежесть статистики. Запустите ANALYZE orders; и повторите EXPLAIN (ANALYZE, BUFFERS), но не отключайте последовательное сканирование в рабочей базе ради красивого плана.

Как создавать индекс на рабочей базе

Обычный CREATE INDEX может мешать операциям с таблицей во время построения. В PostgreSQL есть конкурентное создание:

CREATE INDEX CONCURRENTLY idx_orders_customer_id
ON orders (customer_id);

Такой режим обычно уменьшает блокировку обычных операций, но строит индекс дольше и имеет ограничения: команду нельзя выполнять внутри транзакционного блока, а сбой может оставить недействительный индекс, который потребуется отдельно проверить и удалить. Перед запуском уточните состояние транзакций и окно нагрузки.

После создания следите не только за временем одного запроса. Важны размер индекса, скорость вставок и обновлений, блокировки, использование диска и планы самых частых запросов. Удалять индекс стоит только после проверки зависимостей и наблюдения за нагрузкой: приложение могло использовать его для другого запроса.

Что делать дальше

Соберите несколько реальных запросов из логов или мониторинга и для каждого сохраните план до изменения. Затем создавайте индекс под конкретный фильтр, сортировку или соединение и повторяйте замер на данных, близких к рабочему объёму. Следующим шагом изучите составные и частичные индексы, но не объединяйте поля в один индекс без проверки порядка условий и фактического плана.