Создание и удаление индексов

Создание и удаление индексов

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

CREATE INDEX: базовый синтаксис

CREATE INDEX имя_индекса ON имя_таблицы (колонка);

Пример: индекс на колонку внешнего ключа (которую PostgreSQL не создаёт автоматически):

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

Соглашение по именованию: idx_<таблица>_<колонки>. PostgreSQL допускает любое имя, но осмысленное имя незаменимо при диагностике: EXPLAIN показывает имя индекса, и idx_orders_customer_id сразу объясняет, зачем он.

UNIQUE INDEX

CREATE UNIQUE INDEX uq_users_email ON users(email);

Уникальный индекс гарантирует уникальность значений — как ограничение UNIQUE, но создаётся отдельно. На практике ограничение UNIQUE в CREATE TABLE и отдельный CREATE UNIQUE INDEX дают одинаковый результат. Разница в том, что через CREATE UNIQUE INDEX можно создать частичный уникальный индекс:

-- Уникальный email среди активных пользователей (NULL допускается для удалённых)
CREATE UNIQUE INDEX uq_users_active_email ON users(email) WHERE is_active = TRUE;

Это позволяет иметь несколько строк с одним email при is_active = FALSE — например, для мягкого удаления с возможностью повторной регистрации.

Составной индекс

CREATE INDEX idx_events_user_type ON events(user_id, event_type);

Оптимизирует запросы, фильтрующие по обоим полям или только по первому:

-- Использует индекс:
WHERE user_id = 5 AND event_type = 'click'
WHERE user_id = 5

-- Не использует (только второе поле):
WHERE event_type = 'click'

Если запросы по event_type без user_id тоже важны — нужен второй индекс idx_events_type ON events(event_type).

Частичный индекс

Индекс с условием WHERE:

-- Индекс только для незавершённых заказов
CREATE INDEX idx_orders_pending ON orders(created_at) WHERE status = 'pending';

-- Индекс для поиска неподтверждённых пользователей
CREATE INDEX idx_users_unverified ON users(created_at) WHERE is_verified = FALSE;

Частичный индекс меньше полного и обновляется только для строк, удовлетворяющих условию. Если 90% заказов имеют status = 'completed', индекс для pending будет в 10 раз меньше.

Функциональный индекс (expression index)

Иногда запросы применяют функцию к колонке:

-- Поиск без учёта регистра
SELECT * FROM users WHERE LOWER(email) = 'anna@example.com';

Обычный индекс на email не поможет — функция LOWER() применяется к значению при сравнении, а индекс хранит исходные значения. Решение — индекс по выражению:

CREATE INDEX idx_users_email_lower ON users(LOWER(email));

Теперь PostgreSQL использует этот индекс при WHERE LOWER(email) = 'anna@example.com'. Индекс хранит уже вычисленные значения LOWER(email).

Другой пример — индекс на часть JSONB-документа:

-- Часто запрашиваемое поле из JSONB
CREATE INDEX idx_events_meta_type ON events((metadata->>'type'));

CONCURRENTLY: создание без блокировки

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

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

CONCURRENTLY создаёт индекс без блокировки записи — таблица остаётся доступной для INSERT, UPDATE, DELETE. Индекс строится дольше (два прохода вместо одного), но безопасен для production.

Ограничения CONCURRENTLY:

  • Нельзя запустить в транзакции (BEGIN...COMMIT)
  • Занимает больше времени
  • Если создание прерывается — остаётся «инвалидный» индекс, который нужно удалить и пересоздать

IF NOT EXISTS

CREATE INDEX IF NOT EXISTS idx_orders_customer_id ON orders(customer_id);

Пропускает создание, если индекс с таким именем уже существует. Полезно в миграционных скриптах для идемпотентности.

DROP INDEX: удаление

DROP INDEX idx_orders_customer_id;

С безопасной проверкой:

DROP INDEX IF EXISTS idx_orders_customer_id;

Для удаления без блокировки (как при создании):

DROP INDEX CONCURRENTLY idx_orders_customer_id;

Удаление индекса немедленное — данные из таблицы не затрагиваются, удаляется только структура индекса.

REINDEX: перестроить индекс

Со временем индексы могут «раздуваться» — хранить пустые страницы после многократных удалений. Для перестройки:

-- Перестроить один индекс
REINDEX INDEX idx_orders_customer_id;

-- Перестроить все индексы таблицы
REINDEX TABLE orders;

REINDEX блокирует таблицу. Для production используйте REINDEX CONCURRENTLY (PostgreSQL 12+):

REINDEX INDEX CONCURRENTLY idx_orders_customer_id;

Просмотр существующих индексов

В psql:

\d orders
-- Показывает таблицу с колонками, ограничениями и индексами

\di orders*
-- Показывает только индексы, начинающиеся с "orders"

Через SQL:

-- Все индексы в текущей базе
SELECT indexname, tablename, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

-- Размер индексов
SELECT indexname,
       pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'orders';

Статистика использования индексов:

SELECT indexrelname AS index_name,
       idx_scan     AS times_used,
       idx_tup_read AS rows_read,
       pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

idx_scan = 0 означает, что индекс никогда не использовался с момента последнего сброса статистики. Это кандидат на удаление.

Индекс покрытия (covering index)

Обычный индекс хранит значения индексируемых колонок + указатель на строку в таблице. При Index Scan PostgreSQL сначала находит указатель в индексе, затем идёт за остальными колонками в таблицу — это называется heap fetch (обращение к куче).

Covering index включает все нужные колонки прямо в индекс — тогда heap fetch не нужен совсем:

-- Запрос часто запрашивает только customer_id, status, total_amount
SELECT customer_id, status, total_amount FROM orders WHERE customer_id = 42;

-- Покрывающий индекс: все нужные колонки в индексе
CREATE INDEX idx_orders_cover ON orders(customer_id) INCLUDE (status, total_amount);

Клауза INCLUDE добавляет колонки в «leaf» уровень индекса без включения в поиск. PostgreSQL не ищет по ним, но и за heap fetch не идёт. Результат — Index Only Scan вместо Index Scan:

Index Only Scan using idx_orders_cover on orders
  Index Cond: (customer_id = 42)
  Heap Fetches: 0  -- ни одного обращения к таблице!

INCLUDE добавлен в PostgreSQL 11. Он экономит операции ввода-вывода на запросах, которые обращаются к одному-двум «горячим» колонкам вместе с колонкой фильтра.

Практический пример: индексация интернет-магазина

-- Основные FK (PostgreSQL не создаёт автоматически)
CREATE INDEX idx_orders_customer_id    ON orders(customer_id);
CREATE INDEX idx_order_items_order_id  ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
CREATE INDEX idx_products_category_id  ON products(category_id);

-- Поиск по статусу заказа (часто в WHERE)
CREATE INDEX idx_orders_status ON orders(status);

-- Фильтрация по дате создания
CREATE INDEX idx_orders_created_at ON orders(created_at);

-- Составной: поиск заказов клиента с фильтром по статусу
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);

-- Частичный: незавершённые заказы (их немного, но обращаются часто)
CREATE INDEX idx_orders_open ON orders(created_at) WHERE status IN ('pending', 'processing');

Типичные ошибки

1. Не создать индекс на FK-колонку:

-- После создания таблицы проверить:
SELECT * FROM pg_indexes WHERE tablename = 'orders';
-- Если customer_id нет — JOIN по нему будет медленным

2. Слишком много индексов на часто изменяемой таблице: Каждый индекс замедляет INSERT/UPDATE. Таблица событий (events) с 10 индексами будет вставлять строки в несколько раз медленнее, чем с 2-3.

3. Индекс на низко-кардинальную колонку без частичного условия:

-- Мало пользы: планировщик всё равно выберет Seq Scan
CREATE INDEX idx_orders_is_paid ON orders(is_paid);
-- Лучше частичный (если 95% заказов оплачены, индекс нужен для неоплаченных):
CREATE INDEX idx_orders_unpaid ON orders(created_at) WHERE is_paid = FALSE;

Краткий итог

  • CREATE INDEX [UNIQUE] name ON table(col) — создать индекс
  • CREATE INDEX CONCURRENTLY — без блокировки (production-безопасно)
  • IF NOT EXISTS — идемпотентное создание
  • DROP INDEX [CONCURRENTLY] [IF EXISTS] name — удалить индекс
  • REINDEX INDEX/TABLE — перестроить раздувшийся индекс
  • pg_indexes и pg_stat_user_indexes — просмотр и статистика использования
  • Составной индекс: первая колонка — самая часто фильтруемая
  • Частичный индекс: экономит место, если фильтр применяется постоянно

Диагностика: найти «неиспользуемые» индексы

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

-- Индексы с нулевым использованием (кандидаты на удаление)
SELECT schemaname, relname AS table_name, indexrelname AS index_name,
       idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pk_%'    -- не удалять первичные ключи
  AND indexrelname NOT LIKE 'uq_%'    -- не удалять уникальные
ORDER BY pg_relation_size(indexrelid) DESC;

idx_scan = 0 после нескольких недель работы production — сигнал, что индекс не используется. Однако перед удалением убедитесь: статистика сбрасывается при рестарте PostgreSQL, а некоторые индексы используются только при периодических отчётах.

Перед удалением «подозрительного» индекса: выполните EXPLAIN для всех запросов, которые работают с этой таблицей. Если ни один не использует индекс — удаляйте.

Что дальше

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

Попробуйте интерактивную версию

Практические задачи, квизы и AI-наставник — бесплатный старт без карты

Перейти к практике