Индекс в PostgreSQL: создать и не ждать чуда

В PostgreSQL 16 индекс можно создать одной командой, но это не гарантирует ускорение: на маленькой таблице планировщик часто выберет последовательное чтение. Ниже вы добавите индекс для поиска по email, проверите план запроса и поймёте, почему индекс может не использоваться. На работу с готовой базой заложите около 10 минут.

Что нужно до начала

Понадобятся доступ к базе PostgreSQL 16, клиент для выполнения SQL и таблица, в которой есть столбец с тем же именем, что в примере. Для создания индекса нужны права на изменение таблицы. Если таблицы customers и столбца email у вас нет, замените их названия в запросах на свои.

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

Создайте индекс и проверьте запрос

1. Посмотрите, как называется таблица и какие столбцы в ней есть. В клиенте базы выполните:

   \d customers

   Это команда клиента psql, а не SQL; в другом интерфейсе откройте структуру таблицы через его панель. Проверка: в списке столбцов есть email, а таблица содержит данные.

2. Сначала посмотрите план запроса без индекса:

   EXPLAIN SELECT *
   FROM customers
   WHERE email = 'reader@example.com';

   EXPLAIN показывает план, но сам запрос не выполняет. Проверка: в результате есть строка с типом операции, например Seq Scan. Это последовательное чтение таблицы, не ошибка.

3. Создайте обычный индекс на столбце, по которому ищете:

   CREATE INDEX customers_email_idx ON customers (email);

   Если команда завершилась без ошибки, индекс создан. Проверить его наличие в psql можно командой \d customers: в выводе появится индекс customers_email_idx.

4. Попросите PostgreSQL обновить статистику о таблице и её данных:

   ANALYZE customers;

   Проверка: команда завершилась без сообщения об ошибке. Статистика помогает планировщику оценивать, сколько строк вернёт запрос.

5. Повторите тот же запрос с планом:

   EXPLAIN SELECT *
   FROM customers
   WHERE email = 'reader@example.com';

   На достаточно большой таблице и при подходящих данных план может содержать Index Scan или Bitmap Index Scan. Если снова видите Seq Scan, это не доказывает, что индекс сломан: планировщик мог решить, что так дешевле.

Ошибки, которые чаще всего мешают

Индекс создали, а запрос не ускорился

На небольшой таблице прочитать все строки может быть дешевле, чем обращаться к индексу, а затем отдельно получать нужные данные. Ещё одна причина — условие запроса не соответствует индексу: индекс стоит на email, а запрос фильтрует по другому столбцу. Сравнивайте план до и после, а не наличие индекса в списке.

Запрос отличается от поиска по столбцу

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

Индексируют каждый столбец подряд

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

Повторно создают индекс с тем же именем

PostgreSQL ответит ошибкой вроде relation "customers_email_idx" already exists. Сначала проверьте, нет ли такого индекса; не удаляйте его, пока не выяснили, кто и для чего его использует. Имя индекса в базе должно быть уникальным для схемы.

Создают уникальный индекс, не проверив данные

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

Что важно знать об индексе

Индекс¹ — отдельная структура данных, которая помогает находить строки по заданным условиям. В PostgreSQL тип по умолчанию — B-tree: он подходит для многих сравнений и упорядоченных значений, но не является универсальным ускорителем для каждого запроса.

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

Если хотите оценить фактическое время, используйте EXPLAIN ANALYZE только для запроса, который безопасно выполнить: в отличие от EXPLAIN, эта команда запускает его. Для запроса на чтение это обычно приемлемо, но он всё равно потребляет ресурсы и может долго работать. Сравнивайте одинаковые запросы на тех же данных; учитывайте, что первый запуск может отличаться от последующих из-за кеширования.

1. Индекс — вспомогательная структура в базе данных, которая ускоряет некоторые способы поиска и сортировки. Она не заменяет таблицу и требует дополнительного места и обслуживания.

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

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