Анализ планов выполнения, поиск узких мест, оптимизация запросов
EXPLAIN — главный инструмент анализа производительности запросов в PostgreSQL. В этой теме вы научитесь читать планы выполнения, находить узкие места и оптимизировать запросы.
-- Показать план без выполнения
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';
-- Показать план с фактическим выполнением
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
-- Подробный вывод со статистикой буферов
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = 'test@example.com';| Опция | Описание |
|---|---|
ANALYZE | Выполнить запрос и показать фактическое время |
BUFFERS | Показать статистику использования буферов |
VERBOSE | Показать дополнительную информацию |
COSTS | Показать оценку стоимости (по умолчанию) |
SETTINGS | Показать настройки планировщика |
FORMAT TEXT/JSON/XML/YAML | Формат вывода |
-- Полный пример
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, FORMAT JSON)
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
| Метрика | Описание |
|---|---|
cost=0.29..8.30 | Оценка стоимости (startup..total) |
rows=1 | Оценка количества строк |
width=32 | Средняя ширина строки в байтах |
Actual Time=0.025..0.026 | Фактическое время (startup..total) в мс |
rows=1 | Фактическое количество строк |
loops=1 | Сколько раз выполнен узел |
Buffers: shared hit=2 | Чтение из памяти (hit) и диска (read) |
Seq Scan on users (cost=0.00..35.50 rows=1000 width=32)
Actual Time=0.010..0.150 rows=1000 loops=1
Filter: (age > 25)
Rows Removed by Filter: 5000
Когда используется:
Проблема: O(n) — читает всю таблицу
Решение: Создать индекс для фильтра
Index Scan using idx_users_email on users (cost=0.29..8.30 rows=1 width=32)
Actual Time=0.020..0.021 rows=1 loops=1
Index Cond: (email = 'test@example.com'::text)
Когда используется:
Преимущество: O(log n) — быстрее для селективных запросов
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
Когда используется:
Преимущество: Самый быстрый тип сканирования
Требование: VACUUM для поддержания видимости строк
Bitmap Heap Scan on users (cost=4.32..20.50 rows=100 width=32)
Actual Time=0.050..0.100 rows=100 loops=1
Recheck Cond: (age > 25)
Heap Blocks: exact=50
-> Bitmap Index Scan on idx_users_age (cost=0.00..4.30 rows=100 width=0)
Actual Time=0.030..0.030 rows=100 loops=1
Index Cond: (age > 25)
Когда используется:
Механизм:
Nested Loop (cost=0.29..50.00 rows=10 width=64)
Actual Time=0.050..0.200 rows=10 loops=1
-> Index Scan using idx_users_id on users (cost=0.29..8.30 rows=1 width=32)
Actual Time=0.020..0.021 rows=1 loops=10
Index Cond: (id = orders.user_id)
-> Seq Scan on orders (cost=0.00..40.00 rows=1000 width=32)
Actual Time=0.005..0.015 rows=100 loops=1
Когда используется:
Сложность: O(n * m) в худшем случае
Hash Join (cost=50.00..150.00 rows=1000 width=64)
Actual Time=1.0..5.0 rows=1000 loops=1
Hash Cond: (orders.user_id = users.id)
-> Seq Scan on orders (cost=0.00..80.00 rows=5000 width=32)
Actual Time=0.5..3.0 rows=5000 loops=1
-> Hash (cost=40.00..40.00 rows=1000 width=32)
Actual Time=0.4..0.4 rows=1000 loops=1
Buckets: 1024 Batches: 1 Memory Usage: 64kB
-> Seq Scan on users (cost=0.00..40.00 rows=1000 width=32)
Actual Time=0.1..0.2 rows=1000 loops=1
Когда используется:
Преимущество: O(n + m) — эффективно для больших данных
Merge Join (cost=30.00..100.00 rows=500 width=64)
Actual Time=0.5..2.0 rows=500 loops=1
Merge Cond: (orders.user_id = users.id)
-> Index Scan using idx_orders_user on orders (cost=0.29..60.00 rows=5000 width=32)
Actual Time=0.1..1.0 rows=5000 loops=1
-> Index Scan using idx_users_id on users (cost=0.29..30.00 rows=1000 width=32)
Actual Time=0.1..0.5 rows=1000 loops=1
Когда используется:
Преимущество: O(n + m) при отсортированных данных
Seq Scan on users (cost=0.00..100.00 rows=1 width=32)
Actual Time=0.5..50.0 rows=1 loops=1
Filter: (email = 'test@example.com')
Rows Removed by Filter: 99999
Проблема: 99999 строк отфильтровано, найдена 1
Решение: Создать индекс
CREATE INDEX idx_users_email ON users(email);Nested Loop (cost=0.29..50000.00 rows=10000 width=64)
Actual Time=0.1..5000.0 rows=10000 loops=1
-> Seq Scan on large_table (cost=0.00..40000.00 rows=100000 width=32)
Actual Time=0.05..4000.0 rows=100000 loops=1
-> Index Scan using idx_small_id on small_table (cost=0.29..0.10 rows=1 width=32)
Actual Time=0.005..0.005 rows=1 loops=100000
Проблема: Внешний цикл — большая таблица
Решение: Обновить статистику
ANALYZE large_table;
ANALYZE small_table;Seq Scan on users (cost=0.00..100.00 rows=1 width=32)
Actual Time=0.5..50.0 rows=1 loops=1
Filter: (LOWER(email) = 'test@example.com')
Проблема: Индекс не используется из-за функции
Решение: Функциональный индекс
CREATE INDEX idx_users_email_lower ON users(LOWER(email));Index Scan using idx_users_id on users (cost=0.29..8.30 rows=1 width=32)
Actual Time=0.5..0.5 rows=0 loops=1
Index Cond: (id = '123')
Filter: (id = '123'::integer)
Проблема: Неявное приведение типов
Решение: Использовать правильный тип
-- Плохо
SELECT * FROM users WHERE id = '123';
-- Хорошо
SELECT * FROM users WHERE id = 123;Seq Scan on users (cost=0.00..100.00 rows=5000 width=32)
Actual Time=0.5..50.0 rows=1 loops=1
Filter: (is_active = true)
Проблема: Планировщик ошибся в оценке (ожидал 5000, получил 1)
Решение: Обновить статистику
ANALYZE users;
-- Или для конкретных столбцов
ANALYZE users(is_active);-- Плохо: выбирает все столбцы
SELECT * FROM users WHERE id = 1;
-- Хорошо: только нужные столбцы
SELECT id, name, email FROM users WHERE id = 1;Преимущество: Index Only Scan возможен
-- Медленно: IN с подзапросом
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);
-- Быстрее: EXISTS
SELECT * FROM users
WHERE EXISTS (
SELECT 1 FROM orders
WHERE orders.user_id = users.id AND total > 1000
);-- Без LIMIT: сканирует всю таблицу
SELECT * FROM orders ORDER BY created_at DESC;
-- С LIMIT: останавливается после N строк
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10;-- Медленно: DISTINCT требует сортировки
SELECT DISTINCT user_id FROM orders;
-- Быстрее: GROUP BY
SELECT user_id FROM orders GROUP BY user_id;
-- Или EXISTS для проверки существования
SELECT u.* FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);-- Индекс не используется: LIKE с % в начале
SELECT * FROM users WHERE name LIKE '%john%';
-- Индекс используется: LIKE с константой в начале
SELECT * FROM users WHERE name LIKE 'john%';
-- Решение для поиска по подстроке: расширение pg_trgm
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_users_name_trgm ON users USING gin(name gin_trgm_ops);
SELECT * FROM users WHERE name LIKE '%john%'; -- использует индексSHOW random_page_cost;
SHOW seq_page_cost;
SHOW effective_cache_size;
SHOW work_mem;| Параметр | Описание | Рекомендация |
|---|---|---|
random_page_cost | Стоимость случайного чтения страницы | 1.1-1.5 для SSD, 4.0 для HDD |
seq_page_cost | Стоимость последовательного чтения | 1.0 (по умолчанию) |
effective_cache_size | Оценка размера кэша ОС | 50-75% от RAM |
work_mem | Память для сортировок и хэшей | 4-64MB в зависимости от нагрузки |
shared_buffers | Размер буферов PostgreSQL | 25% от RAM |
SET random_page_cost = 1.1;
SET work_mem = '64MB';
-- Выполнить запрос
EXPLAIN ANALYZE SELECT ...;
-- Сбросить
RESET random_page_cost;
RESET work_mem;-- Запрос выполняется 5 секунд
EXPLAIN ANALYZE
SELECT
u.name,
u.email,
COUNT(o.id) as order_count,
SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days'
AND o.status = 'completed'
GROUP BY u.id, u.name, u.email
ORDER BY total_spent DESC
LIMIT 100;Sort (cost=5000.00..5000.25 rows=100 width=64)
Actual Time=5000.0..5000.5 rows=100 loops=1
Sort Key: (sum(o.total)) DESC
Sort Method: top-N heapsort Memory: 32kB
-> HashAggregate (cost=4500.00..5000.00 rows=1000 width=64)
Actual Time=4900.0..4950.0 rows=10000 loops=1
Group Key: u.id
-> Hash Join (cost=100.00..4000.00 rows=50000 width=64)
Actual Time=1.0..4000.0 rows=50000 loops=1
Hash Cond: (o.user_id = u.id)
-> Seq Scan on orders o (cost=0.00..3500.00 rows=50000 width=20)
Actual Time=0.5..3500.0 rows=50000 loops=1
Filter: (status = 'completed')
-> Hash (cost=80.00..80.00 rows=1000 width=44)
Actual Time=0.3..0.3 rows=1000 loops=1
Buckets: 1024 Batches: 1 Memory Usage: 64kB
-> Seq Scan on users u (cost=0.00..80.00 rows=1000 width=44)
Actual Time=0.1..0.2 rows=1000 loops=1
Filter: (created_at > NOW() - '30 days'::interval)
-- 1. Создать индексы
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_orders_user_status ON orders(user_id, status) INCLUDE (total);
-- 2. Обновить статистику
ANALYZE users;
ANALYZE orders;
-- 3. Оптимизированный запрос
EXPLAIN ANALYZE
SELECT
u.name,
u.email,
COUNT(o.id) as order_count,
SUM(o.total) as total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.id, u.name, u.email
ORDER BY total_spent DESC
LIMIT 100;Limit (cost=100.00..100.25 rows=100 width=64)
Actual Time=50.0..50.1 rows=100 loops=1
-> Sort (cost=100.00..100.25 rows=100 width=64)
Actual Time=49.0..49.5 rows=100 loops=1
Sort Key: (sum(o.total)) DESC
Sort Method: top-N heapsort Memory: 32kB
-> HashAggregate (cost=80.00..90.00 rows=1000 width=64)
Actual Time=40.0..45.0 rows=1000 loops=1
Group Key: u.id
-> Nested Loop (cost=0.50..70.00 rows=5000 width=64)
Actual Time=0.5..35.0 rows=5000 loops=1
-> Index Scan using idx_users_created_at on users u (cost=0.29..20.00 rows=1000 width=44)
Actual Time=0.2..10.0 rows=1000 loops=1
Index Cond: (created_at > NOW() - '30 days'::interval)
-> Index Scan using idx_orders_user_status on orders o (cost=0.21..0.05 rows=5 width=20)
Actual Time=0.02..0.02 rows=5 loops=1000
Index Cond: (user_id = u.id AND status = 'completed')
Результат: 5000 мс → 50 мс (100x ускорение)
Теперь вы умеете анализировать и оптимизировать запросы. В следующей теме вы изучите безопасность и управление доступом — роли, привилегии и Row Level Security.
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.