Функциональные индексы в PostgreSQL: ускоряем запросы с функциями

Обычный индекс работает когда вы фильтруете по значению столбца напрямую. Но что если в WHERE всегда используется функция? Функциональный (expression) индекс — решение.

Проблема: функция в WHERE убивает индекс

-- Индекс на email есть, но НЕ используется!
WHERE LOWER(email) = 'user@example.com'
WHERE DATE(created_at) = '2026-03-15'
WHERE EXTRACT(YEAR FROM created_at) = 2026

Причина: B-tree индекс хранит значения столбца email, а не LOWER(email). Это разные вещи.

-- Проверка: индекс игнорируется
EXPLAIN SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- Seq Scan on users (cost=...) Filter: (lower(email) = 'alice@example.com')
-- ← Нет Index Scan!

Решение: функциональный индекс

-- Индекс на выражение LOWER(email)
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Теперь запрос использует индекс!
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- Index Scan using idx_users_email_lower on users

PostgreSQL создаёт и хранит вычисленное значение LOWER(email) для каждой строки.

Примеры функциональных индексов

Case-insensitive поиск

-- Индекс
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
CREATE INDEX idx_products_name_lower ON products (LOWER(name));

-- Запросы используют индекс
WHERE LOWER(email) = LOWER($1)
WHERE LOWER(name) LIKE 'ноутбук%'  -- только с начала строки

Поиск по дате (без времени)

-- Индекс на дату без времени
CREATE INDEX idx_orders_date ON orders (created_at::date);
-- или
CREATE INDEX idx_orders_date ON orders (DATE(created_at));

-- Запросы
WHERE created_at::date = '2026-03-15'
WHERE DATE(created_at) BETWEEN '2026-03-01' AND '2026-03-31'

Индекс по году/месяцу

CREATE INDEX idx_orders_month ON orders (DATE_TRUNC('month', created_at));

WHERE DATE_TRUNC('month', created_at) = '2026-03-01'

JSON/JSONB поля

-- Индекс на конкретное поле в JSONB
CREATE INDEX idx_users_city ON users ((data->>'city'));
CREATE INDEX idx_products_brand ON products ((metadata->>'brand'));

-- Запросы
WHERE data->>'city' = 'Москва'
WHERE metadata->>'brand' = 'Apple'

Вычисляемые значения

-- Индекс на длину строки
CREATE INDEX idx_articles_title_len ON articles (LENGTH(title));
WHERE LENGTH(title) > 100

-- Индекс на абсолютное значение
CREATE INDEX idx_transactions_abs ON transactions (ABS(amount));
WHERE ABS(amount) > 1000

-- Индекс на конкатенацию
CREATE INDEX idx_users_fullname ON users (first_name || ' ' || last_name);
WHERE (first_name || ' ' || last_name) = 'Иван Иванов'

Составной функциональный индекс

-- Составной индекс: функция + обычный столбец
CREATE INDEX idx_orders_status_date ON orders (status, created_at::date);

WHERE status = 'completed' AND created_at::date = '2026-03-15'

Частичный функциональный индекс

Комбинация функционального и частичного индекса:

-- Индекс только для активных пользователей, без учёта регистра
CREATE INDEX idx_active_users_email
ON users (LOWER(email))
WHERE is_active = true;

-- Использует индекс только когда оба условия выполнены
WHERE LOWER(email) = 'user@example.com' AND is_active = true

Индекс для pg_trgm (нечёткий поиск)

-- Установить расширение
CREATE EXTENSION pg_trgm;

-- GIN-индекс для поиска по подстроке
CREATE INDEX idx_products_name_trgm ON products USING gin (name gin_trgm_ops);

-- Теперь LIKE с % в начале использует индекс!
WHERE name LIKE '%ноутбук%'
WHERE name ILIKE '%notebook%'
WHERE name % 'ноутбок'  -- нечёткий поиск (similarity)

Обычный B-tree не поддерживает LIKE '%text%'. pg_trgm + GIN — решение.

Проверка через EXPLAIN

-- До индекса
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- Seq Scan on users (cost=0.00..1850.00 rows=5) (actual time=2.3..18.5 rows=1)
-- Buffers: shared hit=850

-- После CREATE INDEX idx_users_email_lower ON users (LOWER(email))
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- Index Scan using idx_users_email_lower on users (cost=0.43..8.45 rows=1) (actual time=0.04..0.05 rows=1)
-- Buffers: shared hit=3

Ускорение в 100+ раз на больших таблицах.

Когда функциональный индекс не поможет

-- Функция применяется к разным столбцам каждый раз
WHERE CONCAT(first_name, last_name) = $1  -- непредсказуемо

-- Разные функции в запросах (индекс для одной, запрос с другой)
-- Индекс: LOWER(email)
-- Запрос: UPPER(email) = 'ALICE@EXAMPLE.COM'  ← не совпадает!

-- Кардинальность слишком низкая
CREATE INDEX ON orders (EXTRACT(YEAR FROM created_at));  -- только 1-5 уникальных значений
-- Планировщик выберет Seq Scan

Стоимость функциональных индексов

Функциональный индекс:

• Замедляет INSERT/UPDATE/DELETE (нужно вычислить функцию и обновить индекс)
• Занимает место (дополнительно к самому индексу)
• Требует чтобы функция была IMMUTABLE (детерминированной)

-- LOWER — IMMUTABLE, подходит
CREATE INDEX ON users (LOWER(email));  -- ✅

-- NOW() — VOLATILE, нельзя!
CREATE INDEX ON orders (NOW() - created_at);  -- ❌ Ошибка

Практический чеклист

1. Найдите медленный запрос с функцией в WHERE через pg_stat_statements
2. Проверьте через EXPLAIN ANALYZE — есть ли Seq Scan там где не должно быть
3. Создайте функциональный индекс с той же функцией что в WHERE
4. Проверьте через EXPLAIN ANALYZE — появился ли Index Scan
5. Оцените размер индекса: SELECT pg_size_pretty(pg_relation_size('idx_name'))

Итог

Функциональные индексы — простой и мощный инструмент. Добавляйте их когда видите частые Seq Scan по столбцу с функцией в WHERE.