Индексы, план выполнения, типичные проблемы и техники оптимизации
Производительность JOIN критична для быстрых запросов. В этой теме изучим индексы, план выполнения, алгоритмы JOIN и техники оптимизации.
EXPLAIN показывает план выполнения запроса без его выполнения:
EXPLAIN SELECT * FROM users u
JOIN orders o ON u.id = o.user_id;Пример вывода:
Hash Join (cost=1.15..155.00 rows=5000 width=72)
Hash Cond: (o.user_id = u.id)
-> Seq Scan on orders o (cost=0.00..140.00 rows=10000 width=40)
-> Hash (cost=1.10..1.10 rows=100 width=36)
-> Seq Scan on users u (cost=0.00..1.10 rows=100 width=36)
EXPLAIN ANALYZE выполняет запрос и показывает фактическое время:
EXPLAIN ANALYZE SELECT * FROM users u
JOIN orders o ON u.id = o.user_id;Пример вывода:
Hash Join (cost=1.15..155.00 rows=5000 width=72) (actual time=0.050..1.200 rows=5000 loops=1)
Hash Cond: (o.user_id = u.id)
-> Seq Scan on orders o (cost=0.00..140.00 rows=10000 width=40) (actual time=0.010..0.500 rows=10000 loops=1)
-> Hash (cost=1.10..1.10 rows=100 width=36) (actual time=0.020..0.020 rows=100 loops=1)
-> Seq Scan on users u (cost=0.00..1.10 rows=100 width=36) (actual time=0.010..0.015 rows=100 loops=1)
Planning Time: 0.200 ms
Execution Time: 1.500 ms
| Метрика | Описание |
|---|---|
cost | Оценка стоимости (в единицах дисковых операций) |
rows | Оценка количества строк |
width | Средняя ширина строки в байтах |
actual time | Фактическое время в миллисекундах |
loops | Количество выполнений операции |
PostgreSQL использует три основных алгоритма для выполнения JOIN.
Перебирает строки одной таблицы и для каждой ищет совпадения в другой.
Nested Loop (cost=0.29..8.31 rows=1 width=0) (actual time=0.050..0.080 rows=10 loops=1)
-> Seq Scan on users u (cost=0.00..1.10 rows=100 width=4)
-> Index Scan using idx_orders_user_id on orders o (cost=0.29..0.31 rows=1 width=8)
Index Cond: (user_id = u.id)
Когда используется:
Производительность:
Строит хэш-таблицу из одной таблицы и проверяет совпадения в другой.
Hash Join (cost=1.15..155.00 rows=5000 width=72) (actual time=0.050..1.200 rows=5000 loops=1)
Hash Cond: (o.user_id = u.id)
-> Seq Scan on orders o (cost=0.00..140.00 rows=10000 width=40)
-> Hash (cost=1.10..1.10 rows=100 width=36)
-> Seq Scan on users u (cost=0.00..1.10 rows=100 width=36)
Когда используется:
Производительность:
Сливает две отсортированные таблицы, проходя по ним один раз.
Merge Join (cost=100.00..200.00 rows=5000 width=72) (actual time=2.000..3.500 rows=5000 loops=1)
Merge Cond: (u.id = o.user_id)
-> Index Scan using users_pkey on users u (cost=0.29..50.00 rows=100 width=36)
-> Sort (cost=90.00..95.00 rows=10000 width=40)
Sort Key: o.user_id
-> Seq Scan on orders o (cost=0.00..140.00 rows=10000 width=40)
Когда используется:
Производительность:
Самое важное правило: всегда создавайте индексы на внешних ключах.
-- Плохо: нет индекса на foreign key
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id) -- Нет индекса!
);
-- Хорошо: явный индекс
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id)
);
CREATE INDEX idx_orders_user_id ON orders(user_id);Для JOIN по нескольким колонкам:
-- Для условия ON a.id = b.a_id AND a.type = b.type
CREATE INDEX idx_b_a_id_type ON b(a_id, type);Порядок колонок важен:
Индекс, включающий все колонки запроса, позволяет избежать обращения к таблице:
-- Запрос:
SELECT user_id, amount FROM orders WHERE user_id = 1;
-- Покрывающий индекс:
CREATE INDEX idx_orders_user_id_amount ON orders(user_id, amount);
-- Index Only Scan вместо Index Scan + Heap FetchИндексы только для подмножества строк:
-- Индекс только для активных заказов
CREATE INDEX idx_orders_user_id_active
ON orders(user_id, id)
WHERE status = 'active';
-- Используется в запросе:
SELECT * FROM orders
WHERE user_id = 1 AND status = 'active';Обновление статистики критично для оптимального плана:
-- Обновить статистику для таблицы
ANALYZE orders;
-- Обновить статистику для конкретной колонки
ANALYZE orders(user_id);
-- Обновить статистику для всех таблиц
ANALYZE;-- Увеличить точность статистики для важной колонки
ALTER TABLE orders ALTER COLUMN user_id SET STATISTICS 1000;
ANALYZE orders;-- Статистика по таблице
SELECT * FROM pg_stat_user_tables WHERE relname = 'orders';
-- Статистика по колонкам
SELECT * FROM pg_stats WHERE tablename = 'orders';Проблема:
-> Seq Scan on orders o (cost=0.00..140.00 rows=10000 width=40)
Решение:
CREATE INDEX idx_orders_user_id ON orders(user_id);Проблема: Неявное приведение типов предотвращает использование индекса.
-- Плохо: text vs integer
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id::TEXT;
-- Хорошо: одинаковые типы
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id;Проблема:
-- Индекс на lower(name) не используется
SELECT * FROM users u
JOIN orders o ON LOWER(u.name) = LOWER(o.customer_name);Решение:
-- Создать функциональный индекс
CREATE INDEX idx_users_name_lower ON users(LOWER(name));
CREATE INDEX idx_orders_customer_name_lower ON orders(LOWER(customer_name));Проблема: JOIN один-ко-многим создаёт больше строк, чем ожидается.
-- users (1000) × orders (10000) = 10000+ строк
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;Решение: Агрегируйте до JOIN:
SELECT u.name, os.total_amount
FROM users u
JOIN (
SELECT user_id, SUM(amount) AS total_amount
FROM orders
GROUP BY user_id
) os ON u.id = os.user_id;Проблема:
-- 1000 × 1000 = 1 000 000 строк!
SELECT * FROM table1 CROSS JOIN table2;Решение:
LIMIT для тестированияПроблема:
-- OR предотвращает использование индексов
SELECT * FROM t1
JOIN t2 ON t1.a = t2.a OR t1.b = t2.b;Решение:
-- UNION отдельных JOIN
SELECT * FROM t1 JOIN t2 ON t1.a = t2.a
UNION
SELECT * FROM t1 JOIN t2 ON t1.b = t2.b;Память для операций сортировки и хэш-таблиц:
-- Увеличить для сессионной настройки
SET work_mem = '256MB';
-- Глобальная настройка в postgresql.conf
-- work_mem = 256MBОценка доступной памяти для кэша:
-- Установить в 50-75% от доступной RAM
-- effective_cache_size = 4GBСтоимость случайного доступа к диску:
-- Для SSD уменьшить с 4.0 до 1.1-1.5
-- random_page_cost = 1.1Временное отключение алгоритмов для тестирования:
-- Отключить Hash Join для тестирования
SET enable_hashjoin = off;
-- Отключить Nested Loop
SET enable_nestloop = off;
-- Сбросить настройки
RESET enable_hashjoin;
RESET enable_nestloop;Расширение для отслеживания запросов:
-- Включить в postgresql.conf
-- shared_preload_libraries = 'pg_stat_statements'
-- Найти медленные запросы с JOIN
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE '%JOIN%'
ORDER BY total_exec_time DESC
LIMIT 10;-- Логировать запросы медленнее 1 секунды
-- log_min_duration_statement = 1000
-- Авто EXPLAIN для медленных запросов
-- auto_explain.log_min_duration = 1000-- После создания таблицы с FK
CREATE INDEX idx_<table>_<fk_column> ON <table>(<fk_column>);EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;Опция BUFFERS показывает использование кэша:
shared hit — данные в кэшеshared read — данные прочитаны с диска-- Плохо: агрегация после JOIN
SELECT u.name, SUM(o.amount)
FROM users u
JOIN orders o ON u.id = o.user_id
GROUP BY u.id;
-- Лучше: агрегация в подзапросе
SELECT u.name, os.total
FROM users u
JOIN (
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id
) os ON u.id = os.user_id;-- Быстрое тестирование структуры результата
SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
LIMIT 10;-- Плохо: выбирает все колонки
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
-- Хорошо: только нужные колонки
SELECT u.name, o.amount, o.created_at
FROM users u JOIN orders o ON u.id = o.user_id;EXPLAIN ANALYZE для анализа плана выполненияANALYZE для оптимального планаwork_mem и random_page_cost для вашей системыpg_stat_statements для мониторинга медленных запросовВопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.