B-tree, GIN, GiST, EXPLAIN ANALYZE, оптимизация запросов
Индексы — это структуры данных, которые ускоряют поиск, сортировку и соединение данных ценой дополнительной дисковой памяти и замедления операций записи (INSERT, UPDATE, DELETE).
💡 Аналогия: Индекс в книге позволяет быстро найти нужную страницу, не просматривая всю книгу подряд. В PostgreSQL индексы работают аналогично.
Запрос: SELECT * FROM users WHERE email = 'test@example.com';
Таблица users (1 000 000 строк):
┌─────────────────────────────────────────┐
│ row 1: id=1, email=alice@mail.com │
│ row 2: id=2, email=bob@gmail.com │
│ row 3: id=3, email=test@example.com ← │ ← Ищем эту
│ row 4: id=4, email=charlie@yahoo.com │
│ ... │
│ row 1000000: id=..., email=... │
└─────────────────────────────────────────┘
❌ PostgreSQL читает ВСЕ 1 000 000 строк
⏱️ Время: ~500-1000 мс
Запрос: SELECT * FROM users WHERE email = 'test@example.com';
Индекс idx_users_email:
┌─────────────────────────────────────────┐
│ email: alice@mail.com → row 1 │
│ email: bob@gmail.com → row 2 │
│ email: test@example.com → row 3 ← │ ← Нашли!
│ email: charlie@yahoo.com → row 4 │
│ ... │
└─────────────────────────────────────────┘
✅ PostgreSQL читает только нужную строку
⏱️ Время: ~0.1-1 мс (в 1000 раз быстрее!)
┌─────────────────────────────────────────────────────────────┐
│ ПРЕИМУЩЕСТВА │
├─────────────────────────────────────────────────────────────┤
│ ✅ Быстрый поиск по столбцу (SELECT с WHERE) │
│ ✅ Ускорение сортировки (ORDER BY) │
│ ✅ Ускорение соединений (JOIN) │
│ ✅ Уникальность данных (UNIQUE индекс) │
└─────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────┐
│ НЕДОСТАТКИ │
├─────────────────────────────────────────────────────────────┤
│ ❌ Занимает дисковое пространство (10-30% от таблицы) │
│ ❌ Замедляет INSERT (нужно обновлять индекс) │
│ ❌ Замедляет UPDATE (если индексированный столбец) │
│ ❌ Замедляет DELETE (нужно помечать в индексе) │
│ ❌ Требует обслуживания (VACUUM, REINDEX) │
└─────────────────────────────────────────────────────────────┘
Правило: Создавайте индексы только на столбцах, которые часто используются в WHERE, JOIN, ORDER BY.
PostgreSQL поддерживает 6 типов индексов:
| Тип | Описание | Лучшее применение | Размер |
|---|---|---|---|
| B-tree | Балансированное дерево | =, >, <, BETWEEN, LIKE 'prefix%', сортировка | Средний |
| Hash | Хэш-таблица | Только = (точное совпадение) | Малый |
| GIN | Inverted index | Массивы, JSONB, полнотекстовый поиск | Большой |
| GiST | Balanced tree | Геоданные, диапазоны, нечёткий поиск | Большой |
| SP-GiST | Space-partitioning | Префиксные деревья, IP-адреса | Средний |
| BRIN | Block range | Большие отсортированные таблицы (миллионы строк) | Очень малый |
┌──────────────────────────────────────────────────────────────┐
│ ВЫБОР ТИПА ИНДЕКСА │
├──────────────────────────────────────────────────────────────┤
│ │
│ Столбец с уникальными значениями? │
│ └─> B-tree (по умолчанию) │
│ │
│ Только точное равенство (=)? │
│ └─> Hash (быстрее B-tree для =) │
│ │
│ Массивы или JSONB? │
│ └─> GIN │
│ │
│ Геометрические данные или диапазоны? │
│ └─> GiST │
│ │
│ Таблица > 1 млн строк и данные отсортированы? │
│ └─> BRIN (в 100 раз меньше B-tree) │
│ │
│ IP-адреса или префиксные данные? │
│ └─> SP-GiST │
│ │
└──────────────────────────────────────────────────────────────┘
B-tree (Balanced Tree) — сбалансированное дерево поиска. Индекс по умолчанию в PostgreSQL.
Корень
/ \
/ \
Узел 1 Узел 2
/ | \ / | \
1 2 3 4 5 6 7 8 9 10 11 12
┌─────────────────────────────────────────┐
│ Высота дерева: log₂(N) │
│ Для 1 млн строк: ~20 уровней │
│ Поиск: 20 операций (мгновенно) │
└─────────────────────────────────────────┘
-- Простой индекс
CREATE INDEX idx_users_email ON users(email);
-- Уникальный индекс (гарантирует уникальность)
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Композитный индекс (несколько столбцов)
CREATE INDEX idx_users_city_age ON users(city, age);
-- Индекс по выражению
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Частичный индекс (только для подмножества строк)
CREATE INDEX idx_users_active ON users(id)
WHERE is_active = TRUE;
-- Индекс с сортировкой
CREATE INDEX idx_users_created_at_desc
ON users(created_at DESC);| Оператор | Пример | Эффективность |
|---|---|---|
= | WHERE email = 'test@example.com' | ✅ Отлично |
> / < | WHERE age > 25 | ✅ Отлично |
BETWEEN | WHERE age BETWEEN 20 AND 30 | ✅ Отлично |
IN | WHERE status IN ('active', 'pending') | ✅ Отлично |
IS NULL | WHERE deleted_at IS NULL | ✅ Хорошо |
LIKE 'prefix%' | WHERE name LIKE 'Alex%' | ✅ Хорошо |
ORDER BY | ORDER BY created_at DESC | ✅ Отлично |
LIKE '%suffix%' | WHERE name LIKE '%alex%' | ❌ Бесполезно |
FUNCTION(column) | WHERE YEAR(created_at) = 2026 | ❌ Бесполезно* |
*Используйте индекс по выражению:
CREATE INDEX idx_year ON users(YEAR(created_at))
Порядок столбцов критически важен!
-- Индекс: (city, age)
CREATE INDEX idx_users_city_age ON users(city, age);┌─────────────────────────────────────────────────────────────┐
│ ПРАВИЛО ЛЕВОГО НАЧАЛА │
├─────────────────────────────────────────────────────────────┤
│ │
│ Индекс (city, age) работает для: │
│ ✅ WHERE city = 'Moscow' │
│ ✅ WHERE city = 'Moscow' AND age = 25 │
│ ✅ WHERE city = 'Moscow' AND age > 25 │
│ │
│ НЕ работает для: │
│ ❌ WHERE age = 25 (нет city) │
│ ❌ WHERE age = 25 AND city = 'Moscow' (порядок не важен, │
│ но индекс не │
│ будет использован │
│ оптимально) │
│ │
└─────────────────────────────────────────────────────────────┘
Индекс (city, age):
Сначала сортировка по city:
┌─────────────┬─────┐
│ Moscow │ 20 │
│ Moscow │ 25 │
│ Moscow │ 30 │
│ London │ 22 │
│ London │ 28 │
│ Paris │ 24 │
└─────────────┴─────┘
Затем по age внутри каждого city:
┌─────────────┬─────┐
│ Moscow │ 20 │ ← Сначала все Moscow
│ Moscow │ 25 │ отсортированные по age
│ Moscow │ 30 │
│ London │ 22 │ ← Потом все London
│ London │ 28 │ отсортированные по age
│ Paris │ 24 │
└─────────────┴─────┘
Индексируют только строки, удовлетворяющие условию:
CREATE INDEX idx_users_active ON users(email)
WHERE is_active = TRUE;Преимущества:
Использование:
-- ✅ Будет использован
SELECT * FROM users
WHERE is_active = TRUE AND email = 'test@example.com';
-- ❌ Не будет использован
SELECT * FROM users
WHERE email = 'test@example.com'; -- нет условия is_active-- Поиск без учёта регистра
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
-- Использование:
SELECT * FROM users
WHERE LOWER(email) = 'test@example.com';
-- Индекс по дате из timestamp
CREATE INDEX idx_orders_date ON users((created_at::date));
-- Использование:
SELECT * FROM orders
WHERE created_at::date = '2026-03-23';GIN (Generalized Inverted Index) — хранит отображение «значение → строки».
Исходные данные (массивы):
┌─────────┬─────────────────────┐
│ id │ tags │
├─────────┼─────────────────────┤
│ 1 │ {python, sql} │
│ 2 │ {python, django} │
│ 3 │ {sql, postgres} │
└─────────┴─────────────────────┘
Индекс GIN:
┌──────────┬──────────┐
│ django │ → id: 2 │
│ postgres │ → id: 3 │
│ python │ → id: 1, 2 │
│ sql │ → id: 1, 3 │
└──────────┴──────────┘
-- Для массивов
CREATE INDEX idx_tags ON products USING GIN(tags);
-- Для JSONB
CREATE INDEX idx_data ON users USING GIN(data);
-- Для полнотекстового поиска
CREATE INDEX idx_search ON documents
USING GIN(to_tsvector('russian', content));
-- Для hstore
CREATE INDEX idx_meta ON products USING GIN(meta);| Тип данных | Оператор | Описание |
|---|---|---|
| Массив | @> | Содержит массив |
| Массив | <@ | Содержится в массиве |
| Массив | && | Пересекается |
| JSONB | @> | Содержит ключ-значение |
| JSONB | ? | Имеет ключ |
| JSONB | `? | ` |
| JSONB | ?& | Имеет все ключи |
-- Поиск в массиве: продукты с тегом 'postgresql'
SELECT * FROM products
WHERE tags @> ARRAY['postgresql'];
-- Пересечение массивов
SELECT * FROM products
WHERE tags && ARRAY['python', 'django'];
-- Поиск в JSONB: пользователи со статусом 'active'
SELECT * FROM users
WHERE data @> '{"status": "active"}';
-- Проверка наличия ключа
SELECT * FROM users
WHERE data ? 'email';
-- Проверка наличия любого ключа
SELECT * FROM users
WHERE data ?| ARRAY['email', 'phone'];
-- Проверка наличия всех ключей
SELECT * FROM users
WHERE data ?& ARRAY['email', 'phone'];-- Обычный GIN (поддерживает все операторы)
CREATE INDEX idx_data ON users USING GIN(data);
-- GIN с jsonb_path_ops (быстрее, но меньше операторов)
CREATE INDEX idx_data_path ON users USING GIN(data jsonb_path_ops);
-- Работает с:
-- ✅ data @> '{"key": "value"}'
-- ❌ data ? 'key'
-- ❌ data ?| ARRAY['key1', 'key2']Совет: Используйте
jsonb_path_opsдля больших JSONB, если нужны только операторы@>.
GiST (Generalized Search Tree) — расширяемый индекс для сложных типов данных.
-- Индекс для геометрических данных
CREATE INDEX idx_location ON places USING GiST(geom);
-- Поиск точек в радиусе 1 км
SELECT * FROM places
WHERE ST_DWithin(
geom,
ST_MakePoint(37.6173, 55.7558)::geography,
1000
);
-- Поиск ближайших объектов
SELECT * FROM places
ORDER BY geom <-> ST_MakePoint(37.6173, 55.7558)
LIMIT 10;-- Индекс для диапазонов дат
CREATE INDEX idx_booking_period ON bookings USING GiST(period);
-- Поиск пересекающихся диапазонов
SELECT * FROM bookings
WHERE period && '[2026-03-01, 2026-03-31]'::daterange;
-- Поиск диапазонов, содержащих дату
SELECT * FROM bookings
WHERE period @> '2026-03-15'::date;-- Включить расширение
CREATE EXTENSION pg_trgm;
-- Индекс для нечёткого поиска
CREATE INDEX idx_name_trgm ON users USING GiST(name gist_trgm_ops);
-- Поиск похожих значений
SELECT * FROM users
WHERE name % 'Aleksandr'; -- похоже на 'Alexander'
-- Поиск с расстоянием Левенштейна
SELECT * FROM users
ORDER BY name <-> 'Aleksandr'
LIMIT 5;BRIN (Block Range Index) — хранит метаданные о блоках, а не о отдельных строках.
Таблица (отсортирована по created_at):
Блок 1: [2026-01-01 ... 2026-01-10] ← BRIN хранит: min=2026-01-01, max=2026-01-10
Блок 2: [2026-01-11 ... 2026-01-20] ← BRIN хранит: min=2026-01-11, max=2026-01-20
Блок 3: [2026-01-21 ... 2026-01-31] ← BRIN хранит: min=2026-01-21, max=2026-01-31
Запрос: WHERE created_at = '2026-01-15'
→ PostgreSQL читает только Блок 2 (пропускает Блоки 1 и 3)
-- Для больших отсортированных таблиц
CREATE INDEX idx_logs_created_at ON logs USING BRIN(created_at);
-- С указанием размера диапазона (по умолчанию 128)
CREATE INDEX idx_logs_created_at ON logs USING BRIN(created_at)
WITH (pages_per_range = 256);| Критерий | B-tree | BRIN |
|---|---|---|
| Размер таблицы | < 1 млн строк | > 1 млн строк |
| Данные отсортированы | Не важно | ✅ Обязательно |
| Размер индекса | 10-30% от таблицы | 0.1-1% от таблицы |
| Скорость создания | Медленно | Быстро |
| Скорость поиска | Быстро | Средне |
-- Таблица: 100 млн строк, 50 ГБ
-- B-tree индекс: ~5 ГБ, создание: 30 минут
-- BRIN индекс: ~50 МБ, создание: 2 минуты
CREATE INDEX idx_logs_created_at_brin
ON logs USING BRIN(created_at);
-- Запрос сканирует только нужные блоки
SELECT * FROM logs
WHERE created_at >= '2026-01-01'
AND created_at < '2026-02-01';-- Только план (без выполнения)
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- План с выполнением (реальные метрики)
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
-- Подробный план
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM users WHERE email = 'test@example.com';EXPLAIN ANALYZE
SELECT * FROM users
WHERE email = 'test@example.com';QUERY PLAN
─────────────────────────────────────────────────────────────
Index Scan using idx_users_email on users
(cost=0.29..8.30 rows=1 width=36) (actual time=0.021..0.023 rows=1 loops=1)
Index Cond: (email = 'test@example.com'::text)
Planning Time: 0.182 ms
Execution Time: 0.045 ms
┌─────────────────────────────────────────────────────────────┐
│ Index Scan using idx on users │
│ (cost=0.29..8.30 rows=1 width=36) │
│ (actual time=0.021..0.023 rows=1 loops=1) │
└─────────────────────────────────────────────────────────────┘
│
┌─────────────────┴─────────────────┐
▼ ▼
┌───────────────────┐ ┌───────────────────┐
│ ОЦЕНКА │ │ ФАКТИЧЕСКИ │
├───────────────────┤ ├───────────────────┤
│ cost=0.29..8.30 │ │ actual 0.021..0.023│
│ └─ старт..всего │ │ └─ старт..всего │
│ rows=1 │ │ rows=1 │
│ └─ оценочно строк │ │ └─ реально строк │
│ width=36 │ │ loops=1 │
│ └─ байт на строку │ │ └─ повторов │
└───────────────────┘ └───────────────────┘
Seq Scan on users
(cost=0.00..35.50 rows=10 width=36)
(actual time=0.050..0.100 rows=10 loops=1)
Filter: (age > 25)
Rows Removed by Filter: 990
Когда используется:
Index Scan using idx_users_email on users
(cost=0.29..8.30 rows=1 width=36)
(actual time=0.020..0.022 rows=1 loops=1)
Index Cond: (email = 'test@example.com'::text)
Когда используется:
Index Only Scan using idx_users_email on users
(cost=0.29..8.30 rows=1 width=8)
(actual time=0.015..0.016 rows=1 loops=1)
Index Cond: (email = 'test@example.com'::text)
Heap Fetches: 0
Когда используется:
-- Index Only Scan будет использован:
SELECT email FROM users WHERE email = 'test@example.com';
-- Index Scan (нужно обращаться к таблице):
SELECT email, name FROM users WHERE email = 'test@example.com';Bitmap Heap Scan on users
(cost=4.32..20.50 rows=100 width=36)
(actual time=0.050..0.200 rows=100 loops=1)
Recheck Cond: (status = 'active')
Heap Blocks: exact=50
-> Bitmap Index Scan on idx_users_status
(cost=0.00..4.30 rows=100 width=0)
(actual time=0.030..0.030 rows=100 loops=1)
Index Cond: (status = 'active')
Когда используется:
Простой план:
┌─────────────────────────────────────┐
│ Index Scan using idx_users_email │
│ └─ читает индекс + таблицу │
└─────────────────────────────────────┘
Сложный план (с JOIN):
┌─────────────────────────────────────┐
│ Hash Join │
│ ├─ Index Scan (users) │
│ └─ Hash │
│ └─ Seq Scan (orders) │
└─────────────────────────────────────┘
EXPLAIN (ANALYZE, FORMAT JSON)
SELECT * FROM users WHERE email = 'test@example.com';Полезно для программного анализа планов.
-- ❌ ОБЫЧНОЕ СОЗДАНИЕ (блокирует запись)
CREATE INDEX idx_users_email ON users(email);
-- Таблица заблокирована для INSERT/UPDATE/DELETE
-- Время блокировки: секунды-минуты
-- ✅ CONCURRENTLY (без блокировки)
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);
-- Таблица доступна для записи
-- Время создания: 2-3 раза дольшеЭтап 1: Сканирование таблицы
┌─────────────────────────────────────┐
│ Чтение таблицы без блокировки │
│ INSERT/UPDATE/DELETE разрешены │
└─────────────────────────────────────┘
Этап 2: Построение индекса
┌─────────────────────────────────────┐
│ Сортировка и создание индекса │
│ INSERT/UPDATE/DELETE разрешены │
└─────────────────────────────────────┘
Этап 3: Применение изменений
┌─────────────────────────────────────┐
│ Краткая блокировка для синхронизации│
│ Применение изменений, сделанных │
│ во время этапов 1-2 │
└─────────────────────────────────────┘
-- ❌ Нельзя в транзакции
BEGIN;
CREATE INDEX CONCURRENTLY idx ON users(email); -- ОШИБКА!
COMMIT;
-- ✅ Только так:
CREATE INDEX CONCURRENTLY idx ON users(email);
-- ❌ Нельзя если таблица удалена
DROP TABLE users;
CREATE INDEX CONCURRENTLY idx ON users(email); -- ОШИБКА!
-- ✅ Проверка существования
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx ON users(email);-- Если создание зависло, найдите PID:
SELECT pid, query, state
FROM pg_stat_activity
WHERE query LIKE 'CREATE INDEX CONCURRENTLY%';
-- Отмените:
SELECT pg_cancel_backend(pid);
-- Или убейте процесс:
SELECT pg_terminate_backend(pid);-- Обычное удаление
DROP INDEX idx_users_email;
-- Безопасное удаление (если существует)
DROP INDEX IF EXISTS idx_users_email;
-- Удаление нескольких индексов
DROP INDEX IF EXISTS idx1, idx2, idx3;1. Проанализируйте медленные запросы
└─> pg_stat_statements, slow query log
2. Изучите план выполнения (EXPLAIN ANALYZE)
└─> Найдите Seq Scan на больших таблицах
3. Создайте индекс
└─> CREATE INDEX CONCURRENTLY
4. Проверьте результат
└─> EXPLAIN ANALYZE (должен быть Index Scan)
5. Мониторьте использование индекса
└─> pg_stat_user_indexes
-- Foreign keys должны быть индексированы!
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_product_id ON orders(product_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);Без индекса на FK:
┌─────────────────────────────────────┐
│ Hash Join │
│ ├─ Seq Scan users (1 млн строк) │ ← Медленно!
│ └─ Seq Scan orders (10 млн строк) │ ← Медленно!
└─────────────────────────────────────┘
С индексом на FK:
┌─────────────────────────────────────┐
│ Nested Loop │
│ ├─ Index Scan users │ ← Быстро!
│ └─ Index Scan orders │ ← Быстро!
└─────────────────────────────────────┘
-- Частый запрос:
SELECT email, name FROM users WHERE city = 'Moscow';
-- Создайте покрывающий индекс (включающий все столбцы):
CREATE INDEX idx_users_city_covering ON users(city)
INCLUDE (email, name);
-- Index Only Scan будет использован!| Проблема | Решение |
|---|---|
LIKE '%text%' не использует индекс | Используйте GIN с trigram: CREATE INDEX idx_name_trgm ON users USING GIN(name gin_trgm_ops) |
FUNCTION(column) не использует индекс | Создайте индекс по выражению: CREATE INDEX idx_lower ON users(LOWER(email)) |
column != value не использует индекс | Перепишите как column IN (другие_значения) или используйте частичный индекс |
OR между столбцами | Создайте индексы на оба столбца или используйте UNION |
| Сортировка по нескольким столбцам | Создайте композитный индекс с тем же порядком |
-- Исходный медленный запрос (500 мс)
EXPLAIN ANALYZE
SELECT u.name, u.email, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
AND u.created_at >= '2026-01-01'
AND o.total_amount > 1000
GROUP BY u.id, u.name, u.email
ORDER BY order_count DESC
LIMIT 10;
-- План показывает Seq Scan на users и orders
-- Создаём индексы:
CREATE INDEX CONCURRENTLY idx_users_status_created
ON users(status, created_at);
CREATE INDEX CONCURRENTLY idx_orders_user_amount
ON orders(user_id, total_amount);
-- Новый план: Index Scan, время: 5 мс (в 100 раз быстрее!)-- Включите в postgresql.conf:
-- shared_preload_libraries = 'pg_stat_statements'
-- pg_stat_statements.track = all
-- Создайте расширение
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- Топ-10 самых медленных запросов
SELECT
query,
calls,
ROUND(mean_exec_time::numeric, 2) AS mean_ms,
ROUND(total_exec_time::numeric, 0) AS total_ms,
rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
-- Топ-10 самых частых запросов
SELECT
query,
calls,
ROUND(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;
-- Топ-10 запросов по общему времени
SELECT
query,
calls,
ROUND(total_exec_time::numeric, 0) AS total_ms,
ROUND(mean_exec_time::numeric, 2) AS mean_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- Сброс статистики
SELECT pg_stat_statements_reset();-- Какие индексы используются чаще всего
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 index_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
ORDER BY pg_relation_size(indexrelid) DESC;
-- Удаление неиспользуемых индексов (освободит место!)
DROP INDEX IF EXISTS unused_index_name;-- Размер всех индексов таблицы
SELECT
indexname,
pg_size_pretty(pg_relation_size(indexname::regclass)) AS size
FROM pg_indexes
WHERE tablename = 'users'
ORDER BY pg_relation_size(indexname::regclass) DESC;
-- Общий размер индексов в базе
SELECT
pg_size_pretty(SUM(pg_relation_size(indexrelid))) AS total_index_size
FROM pg_stat_user_indexes;
-- Таблицы с самыми большими индексами
SELECT
relname AS table_name,
pg_size_pretty(SUM(pg_relation_size(indexrelid))::bigint) AS index_size
FROM pg_stat_user_indexes
GROUP BY relname
ORDER BY SUM(pg_relation_size(indexrelid)) DESC
LIMIT 10;-- Seq Scan на больших таблицах (возможно, нужен индекс)
SELECT
relname AS table_name,
seq_scan,
seq_tup_read,
idx_scan,
idx_tup_fetch
FROM pg_stat_user_tables
WHERE seq_scan > 0
AND seq_tup_read > 10000
ORDER BY seq_tup_read DESC;
-- Таблицы с большим количеством Seq Scan чем Index Scan
SELECT
relname,
seq_scan,
idx_scan,
CASE
WHEN idx_scan > 0
THEN ROUND(100.0 * seq_scan / (seq_scan + idx_scan), 1)
ELSE 100.0
END AS seq_scan_pct
FROM pg_stat_user_tables
WHERE seq_scan > idx_scan
AND seq_scan > 100
ORDER BY seq_scan DESC;-- Включите логирование медленных запросов (postgresql.conf):
-- log_min_duration_statement = 1000 -- логирует запросы > 1 секунды
-- Или используйте расширение auto_explain:
-- shared_preload_libraries = 'auto_explain'
-- auto_explain.log_min_duration = 1000
-- auto_explain.log_analyze = on-- 1. Создавайте индексы на foreign keys
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- 2. Используйте композитные индексы для частых комбинаций WHERE
CREATE INDEX idx_users_status_city ON users(status, city);
-- 3. Ставьте более селективные столбцы первыми
CREATE INDEX idx_users_email_status ON users(email, status);
-- email уникальнее status → ставим первым
-- 4. Используйте CONCURRENTLY в продакшене
CREATE INDEX CONCURRENTLY idx ON users(email);
-- 5. Регулярно обновляйте статистику
ANALYZE users;
VACUUM ANALYZE users;
-- 6. Проверяйте план выполнения перед оптимизацией
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- 7. Используйте частичные индексы для флагов
CREATE INDEX idx_users_active ON users(id)
WHERE is_active = TRUE;
-- 8. Мониторьте неиспользуемые индексы
SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0;-- 1. Не создавайте индексы на маленькие таблицы (< 1000 строк)
-- Seq Scan будет быстрее
-- 2. Не создавайте избыточные индексы
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_email_active ON users(email, is_active);
-- Второй индекс делает первый избыточным!
-- 3. Не индексируйте столбцы с низкой селективностью
CREATE INDEX idx_users_gender ON users(gender);
-- gender имеет только 2-3 значения → индекс бесполезен
-- 4. Не используйте функции в WHERE без индекса по выражению
SELECT * FROM users WHERE YEAR(created_at) = 2026;
-- Создайте: CREATE INDEX idx_year ON users((YEAR(created_at)));
-- 5. Не создавайте индексы внутри транзакций с CONCURRENTLY
BEGIN;
CREATE INDEX CONCURRENTLY idx ON users(email); -- ОШИБКА!
COMMIT;
-- 6. Не забывайте ANALYZE после массовой загрузки данных
COPY large_table FROM '/data.csv';
ANALYZE large_table; -- Обязательно!□ Запрос выполняется медленно (> 100 мс)?
□ Таблица достаточно большая (> 10 000 строк)?
□ Столбец используется в WHERE, JOIN, ORDER BY?
□ Столбец имеет достаточную селективность?
□ Индекс не дублирует существующий?
□ Вы готовы к замедлению записи?
□ Используете CONCURRENTLY для продакшена?
□ Проверили план с EXPLAIN ANALYZE?
-- Обновление статистики (ежедневно)
ANALYZE;
-- Очистка мёртвых строк (автоматически через autovacuum)
VACUUM users;
-- Перестроение индекса (если фрагментирован)
REINDEX INDEX idx_users_email;
-- Перестроение без блокировки (PostgreSQL 12+)
REINDEX INDEX CONCURRENTLY idx_users_email;
-- Проверка фрагментации индекса
SELECT
indexrelname,
pg_size_pretty(pg_relation_size(indexrelid)) AS size,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND pg_relation_size(indexrelid) > 10000000; -- > 10 МБ| Что индексируем? | Тип |
|---|---|
| Текст, числа, даты (по умолчанию) | B-tree |
| Только точное равенство | Hash |
| Массивы, JSONB | GIN |
| Геоданные, диапазоны | GiST |
| Полнотекстовый поиск | GIN + tsvector |
| Нечёткий поиск | GiST + trigram |
| Большие отсортированные таблицы | BRIN |
-- План выполнения
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
-- Медленные запросы
SELECT * FROM pg_stat_statements ORDER BY mean_exec_time DESC;
-- Использование индексов
SELECT * FROM pg_stat_user_indexes ORDER BY idx_scan DESC;
-- Неиспользуемые индексы
SELECT indexrelname FROM pg_stat_user_indexes WHERE idx_scan = 0;
-- Размер индексов
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes WHERE tablename = 'users';Индексы — это ключевой инструмент оптимизации PostgreSQL. Правильное использование индексов может ускорить запросы в тысячи раз.
Запомните:
Следующий шаг: Проверьте знания в разделе вопросов по индексам!
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.