Типы индексов, создание и анализ, влияние на производительность
Индексы — это мощный инструмент ускорения запросов, но они не бесплатны. В этой теме вы изучите типы индексов, принципы их работы и когда индексы могут навредить производительности.
Индекс — это дополнительная структура данных, которая ускоряет поиск строк в таблице.
Без индекса (Seq Scan):
┌─────────────────────────────────┐
│ Строка 1 → Проверка условия │
│ Строка 2 → Проверка условия │
│ Строка 3 → Проверка условия │
│ ... │ O(n)
│ Строка N → Проверка условия │
└─────────────────────────────────┘
С индексом (Index Scan):
┌─────────────────┐ ┌─────────────────┐
│ Индекс │ │ Таблица │
│ ┌───────────┐ │ │ ┌───────────┐ │
│ │ Значение │ │─────▶│ │ Строка │ │
│ │ → Строка │ │ │ └───────────┘ │
│ └───────────┘ │ └─────────────────┘
└─────────────────┘
O(log n)
Универсальный индекс для большинства случаев.
-- Создание индекса
CREATE INDEX idx_users_email ON users(email);
-- Составной индекс
CREATE INDEX idx_users_name_email ON users(last_name, first_name);
-- Индекс с сортировкой
CREATE INDEX idx_orders_created ON orders(created_at DESC);
-- Частичный индекс (только для активных пользователей)
CREATE INDEX idx_users_active_email
ON users(email)
WHERE is_active = true;Поддерживаемые операторы: =, <, >, <=, >=, BETWEEN, IN, IS NULL, LIKE (с ограничениями)
Когда использовать:
Только для поиска по точному совпадению.
CREATE INDEX idx_sessions_token
ON sessions USING hash(token);Поддерживаемые операторы: только =
Особенности:
=REINDEX)Для индексирования составных значений (массивы, JSONB, полнотекстовый поиск).
-- Индекс для JSONB
CREATE INDEX idx_documents_properties
ON documents USING gin(properties);
-- Запросы с индексом GIN
SELECT * FROM documents
WHERE properties ? 'color'; -- есть ли ключ
SELECT * FROM documents
WHERE properties->'tags' ? 'sale'; -- есть ли значение в массиве
SELECT * FROM documents
WHERE properties @> '{"color": "red"}'; -- содержит ли объект
-- Индекс для массивов
CREATE TABLE articles (
id SERIAL,
tags TEXT[]
);
CREATE INDEX idx_articles_tags
ON articles USING gin(tags);
-- Поиск по массиву
SELECT * FROM articles
WHERE tags && ARRAY['sql', 'postgres']; -- пересечение массивовДля сложных типов данных: геометрия, диапазоны, полнотекстовый поиск.
-- Индекс для диапазонов дат
CREATE TABLE reservations (
id SERIAL,
room_id INTEGER,
during TSRANGE(check_in, check_out)
);
CREATE INDEX idx_reservations_during
ON reservations USING gist(during);
-- Поиск пересекающихся диапазонов
SELECT * FROM reservations
WHERE room_id = 1
AND during && TSRANGE('2026-03-01', '2026-03-07');
-- Индекс для полнотекстового поиска
CREATE INDEX idx_articles_search
ON articles USING gin(to_tsvector('russian', title || ' ' || content));Для больших таблиц с естественным порядком данных (временные ряды).
-- Индекс для больших таблиц с данными по времени
CREATE TABLE events (
id BIGSERIAL,
event_time TIMESTAMPTZ,
data JSONB
);
CREATE INDEX idx_events_time
ON events USING brin(event_time);Особенности BRIN:
-- Индекс: (last_name, first_name)
CREATE INDEX idx_users_name
ON users(last_name, first_name);
-- Использует индекс (левый префикс)
SELECT * FROM users WHERE last_name = 'Ivanov'; -- ✓
SELECT * FROM users WHERE last_name = 'Ivanov' AND first_name = 'Ivan'; -- ✓
-- НЕ использует индекс (пропущен левый столбец)
SELECT * FROM users WHERE first_name = 'Ivan'; -- ✗Правило: Самый селективный столбец (с наибольшим числом уникальных значений) должен быть первым.
-- Плохо: gender имеет только 2 значения
CREATE INDEX idx_users_bad ON users(gender, email);
-- Хорошо: email имеет много уникальных значений
CREATE INDEX idx_users_good ON users(email, gender);-- Индекс включает дополнительные столбцы через INCLUDE
CREATE INDEX idx_orders_user_date
ON orders(user_id, created_at)
INCLUDE (total);
-- Запрос использует только индекс (Index Only Scan)
SELECT user_id, created_at, total
FROM orders
WHERE user_id = 1;Преимущество: Все данные есть в индексе, не нужно обращаться к таблице.
Индекс только для подмножества строк.
-- Индекс только для активных заказов
CREATE INDEX idx_orders_active
ON orders(created_at)
WHERE status = 'active';
-- Индекс для важных пользователей
CREATE INDEX idx_users_vip
ON users(email)
WHERE is_vip = true;
-- Индекс для новых записей
CREATE INDEX idx_events_recent
ON events(event_time)
WHERE event_time > NOW() - INTERVAL '30 days';Преимущества:
-- Включить анализ выполнения
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM users WHERE email = 'test@example.com';
-- Пример вывода:
-- Index Scan using idx_users_email on users (cost=0.29..8.30 rows=1 width=32)
-- Actual Time=0.025..0.026 rows=1 loops=1
-- Index Cond: (email = 'test@example.com'::text)
-- Buffers: shared hit=2-- Какие индексы используются чаще всего
SELECT
schemaname,
relname as table_name,
indexrelname as index_name,
idx_scan as index_scans,
idx_tup_read as tuples_read,
pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
-- Индексы, которые никогда не используются
SELECT
schemaname,
relname as table_name,
indexrelname as index_name
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%_pkey'; -- исключаем первичные ключи-- Размер всех индексов таблицы
SELECT
tablename,
indexname,
pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;
-- Общий размер индексов
SELECT pg_size_pretty(sum(pg_relation_size(indexrelid)))
FROM pg_stat_user_indexes;-- Для таблицы с 100 строками индекс не нужен
-- Seq Scan будет быстрее из-за накладных расходов
SELECT * FROM small_table WHERE id = 1;-- Индекс по столбцу с 2-3 уникальными значениями
CREATE INDEX idx_users_gender ON users(gender);
-- Неэффективно: 50% строк удовлетворяют условию
SELECT * FROM users WHERE gender = 'M'; -- Seq Scan вероятнее-- Индекс НЕ используется
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- Решение: функциональный индекс
CREATE INDEX idx_users_email_lower ON users(LOWER(email));
-- Или: хранить данные в нормализованном виде
UPDATE users SET email = LOWER(email);
CREATE INDEX idx_users_email ON users(email);-- Индекс обычно не используется для NOT и <>
SELECT * FROM users WHERE status != 'deleted';
-- Лучше: частичный индекс для нужных значений
CREATE INDEX idx_users_active ON users(id) WHERE status != 'deleted';-- Индекс используется
SELECT * FROM users WHERE name LIKE 'Ivan%';
-- Индекс НЕ используется
SELECT * FROM users WHERE name LIKE '%anov';
-- Решение: индекс с reversed строкой или GIN с trigram
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin(name gin_trgm_ops);
SELECT * FROM users WHERE name LIKE '%anov'; -- теперь использует индексКаждый индекс замедляет INSERT, UPDATE, DELETE:
INSERT без индекса:
1. Записать строку в таблицу
INSERT с 3 индексами:
1. Записать строку в таблицу
2. Обновить индекс 1
3. Обновить индекс 2
4. Обновить индекс 3
Рекомендация: Для таблиц с частой записью создавайте только необходимые индексы.
CREATE TABLE orders (
user_id INTEGER REFERENCES users(id)
);
-- PostgreSQL не создаёт индекс автоматически!
CREATE INDEX idx_orders_user_id ON orders(user_id);-- Всегда проверяйте план выполнения
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE user_id = 1
AND created_at > NOW() - INTERVAL '7 days';-- Удаляйте неиспользуемые индексы
DROP INDEX IF EXISTS idx_unused;-- После массовой загрузки данных
ANALYZE table_name;
-- Для конкретной таблицы
ANALYZE VERBOSE orders;-- Блокирует таблицу на время создания
CREATE INDEX idx_large ON large_table(column);
-- Не блокирует запись (медленнее)
CREATE INDEX CONCURRENTLY idx_large ON large_table(column);-- Запрос выполняется 5 секунд
EXPLAIN ANALYZE
SELECT o.*, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
AND o.created_at > NOW() - INTERVAL '30 days'
ORDER BY o.created_at DESC
LIMIT 100;
-- План: Seq Scan на orders, Hash Join-- Создаём составной частичный индекс
CREATE INDEX CONCURRENTLY idx_orders_pending_recent
ON orders(created_at DESC, user_id)
WHERE status = 'pending';
-- Индекс на FK
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);
-- Запрос выполняется 50 мс
-- План: Index Scan на orders, Index Lookup на usersТеперь вы умеете создавать и анализировать индексы. В следующей теме вы изучите основы PL/pgSQL — процедурного языка PostgreSQL для написания хранимых процедур и функций.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.