Пошук уроків, статей та іншого контенту
Оптимізуйте умови з’єднання, фільтрацію, порядок таблиць і вибір відповідного алгоритму JOIN.
JOIN може бути найдорожчою частиною запиту, оскільки планувальник має зіставити рядки з двох або більше наборів даних. Вартість залежить від:
кількості рядків після фільтрації;
наявності індексів на умовах з’єднання;
типів даних у ключах;
розподілу значень і актуальності статистики;
алгоритму з’єднання;
порядку фактичного виконання операцій;
обсягу пам’яті, доступної для операції.
Оптимізація JOIN починається не з примусового вибору алгоритму, а з аналізу фактичного плану виконання.
Нехай є користувачі, замовлення та позиції замовлень:
CREATE TABLE customers (
customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL,
country_code char(2) NOT NULL,
is_active boolean NOT NULL DEFAULT true
);
CREATE TABLE orders (
order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customers(customer_id),
status text NOT NULL,
created_at timestamptz NOT NULL,
total_amount numeric(12, 2) NOT NULL
);
CREATE TABLE order_items (
order_item_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id bigint NOT NULL REFERENCES orders(order_id),
product_id bigint NOT NULL,
quantity integer NOT NULL,
unit_price numeric(12, 2) NOT NULL
);
-- Тестові дані для демонстрації планів
INSERT INTO customers (email, country_code, is_active)
SELECT
'customer' || number || '@example.com',
CASE number % 4
WHEN 0 THEN 'UA'
WHEN 1 THEN 'PL'
WHEN 2 THEN 'DE'
ELSE 'US'
END,
number % 10 <> 0
FROM generate_series(1, 10000) AS numbers(number);
INSERT INTO orders (customer_id, status, created_at, total_amount)
SELECT
((number - 1) % 10000) + 1,
CASE number % 4
WHEN 0 THEN 'paid'
WHEN 1 THEN 'shipped'
WHEN 2 THEN 'cancelled'
ELSE 'pending'
END,
now() - (number % 365) * interval '1 day',
(number % 500) + 10
FROM generate_series(1, 100000) AS numbers(number);
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT
order_id,
((order_id + item_number) % 1000) + 1,
item_number,
10 + item_number
FROM orders
CROSS JOIN generate_series(1, 3) AS items(item_number);
CREATE INDEX orders_customer_id_idx
ON orders (customer_id);
CREATE INDEX order_items_order_id_idx
ON order_items (order_id);
ANALYZE customers;
ANALYZE orders;
ANALYZE order_items;Для аналізу використовуйте:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
c.customer_id,
c.email,
count(o.order_id) AS paid_orders
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE c.country_code = 'UA'
AND c.is_active = true
AND o.status = 'paid'
GROUP BY c.customer_id, c.email;EXPLAIN показує оцінений план, а EXPLAIN ANALYZE фактично виконує запит і додає реальні вимірювання.
Особливо важливі поля:
cost — оцінена вартість плану;
rows — оцінена кількість рядків;
actual rows — фактична кількість рядків;
actual time — фактичний час виконання;
loops — кількість повторень вузла;
Buffers: shared hit — сторінки, прочитані з кешу PostgreSQL;
Buffers: shared read — сторінки, прочитані зі сховища.
Велика різниця між rows і actual rows означає, що планувальник неправильно оцінює селективність. Через це він може вибрати невідповідний алгоритм JOIN.
Після зміни індексів або значного оновлення даних оновлюйте статистику:
ANALYZE customers;
ANALYZE orders;
ANALYZE order_items;PostgreSQL може вибрати один із трьох основних алгоритмів.
Для кожного рядка зовнішньої таблиці PostgreSQL шукає відповідні рядки у внутрішній таблиці.
Спрощено:
для кожного рядка з A:
знайти відповідні рядки в BNested Loop ефективний, коли:
зовнішній набір малий;
внутрішня таблиця має індекс на ключі з’єднання;
умова з’єднання швидко звужує результат;
потрібен лише невеликий набір рядків через LIMIT.
Наприклад, пошук замовлень одного конкретного клієнта:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
o.order_id,
o.created_at,
o.total_amount
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE c.customer_id = 42
ORDER BY o.created_at DESC;Для такого запиту індекс на orders(customer_id) дає змогу швидко виконати пошук замовлень.
Якщо зовнішня таблиця містить мільйони рядків, а внутрішня таблиця щоразу сканується повністю, Nested Loop може стати дуже дорогим. Це часто видно за великим значенням loops.
Hash Join спочатку будує хеш-таблицю для однієї сторони, а потім шукає збіги для рядків іншої сторони.
Спрощено:
побудувати хеш за ключем для меншого набору
для кожного рядка іншого набору:
знайти збіг у хеш-таблиціЦей алгоритм часто підходить для з’єднання великих таблиць за умовою рівності:
SELECT *
FROM orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_id;Hash Join потребує пам’яті. Якщо хеш-таблиця не вміщується у доступну пам’ять, операція може розбитися на частини та використовувати тимчасові файли. У плані це може бути видно за параметрами, пов’язаними з кількістю batch-операцій.
Збільшення work_mem іноді допомагає конкретному запиту, але глобальне необережне збільшення цього параметра небезпечне: одночасно кілька операцій і кілька сесій можуть використати значний обсяг пам’яті.
Merge Join проходить два набори, відсортовані за ключем з’єднання, і порівнює їх послідовно.
Він може бути ефективним, коли:
обидві сторони вже відсортовані;
є відповідні індекси;
з’єднуються великі набори;
результат потрібен у тому самому порядку.
Якщо дані ще не відсортовані, PostgreSQL може додати операції Sort. Вартість сортування іноді робить Merge Join невигідним порівняно з Hash Join.
Не слід вибирати алгоритм лише за його назвою. Для одного запиту Nested Loop може бути оптимальним, а для іншого на тих самих таблицях — дуже повільним.
Колонки в умові JOIN повинні мати однаковий або сумісний тип:
JOIN orders AS o
ON o.customer_id = c.customer_idНебажаний варіант:
JOIN orders AS o
ON o.customer_id::text = c.customer_id::textЯвне перетворення може завадити використанню індексу або збільшити вартість операції. Ключі зовнішніх і первинних ключів краще проєктувати з однаковими типами від самого початку.
Умова:
JOIN customers AS c
ON lower(c.email) = lower(o.customer_email)може вимагати функціонального індексу, інакше звичайний індекс на email не допоможе для цього виразу.
Краще нормалізувати значення під час запису або створити відповідний індекс, якщо така умова справді необхідна:
CREATE INDEX customers_lower_email_idx
ON customers (lower(email));Індекс має відповідати виразу, який використовується в запиті.
Якщо з’єднання використовує кілька колонок:
JOIN order_items AS oi
ON oi.order_id = o.order_id
AND oi.product_id = 100індекс можна побудувати відповідно до найчастішого шаблону фільтрації:
CREATE INDEX order_items_order_product_idx
ON order_items (order_id, product_id);Порядок колонок має значення. Індекс (order_id, product_id) найкраще підходить, коли умова починається з order_id. Він не є еквівалентом індексу (product_id, order_id) для всіх типів запитів.
Чим менше рядків доходить до JOIN, тим менше роботи потрібно виконати. PostgreSQL часто сам переносить фільтри ближче до джерела даних. Проте запит слід формулювати однозначно.
SELECT
c.customer_id,
c.email,
o.order_id,
o.created_at
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE c.country_code = 'UA'
AND o.status = 'paid';Фільтри для окремих таблиць логічно належать до відповідних джерел. Еквівалентний запис через підзапити іноді робить намір очевиднішим:
SELECT
c.customer_id,
c.email,
o.order_id,
o.created_at
FROM (
SELECT customer_id, email
FROM customers
WHERE country_code = 'UA'
) AS c
JOIN (
SELECT order_id, customer_id, created_at
FROM orders
WHERE status = 'paid'
) AS o
ON o.customer_id = c.customer_id;У сучасних версіях PostgreSQL прості підзапити зазвичай можуть бути вбудовані в основний план, тому сам факт використання підзапиту не гарантує прискорення. Вирішальним є фактичний план.
ON і WHERE для INNER JOINДля INNER JOIN такі варіанти зазвичай логічно еквівалентні:
SELECT *
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status = 'paid';SELECT *
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.status = 'paid';Планувальник може отримати однаковий план. Вибирайте розташування, яке найкраще описує логіку запиту.
Для LEFT JOIN розташування фільтра може змінити результат.
Зберегти всіх клієнтів і приєднати лише оплачені замовлення:
SELECT
c.customer_id,
c.email,
o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
AND o.status = 'paid';Усі клієнти залишаться в результаті, навіть якщо оплачених замовлень немає.
Інший варіант:
SELECT
c.customer_id,
c.email,
o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.status = 'paid';Тут рядки без замовлення мають o.status IS NULL і відкидаються. Фактично результат стає подібним до INNER JOIN.
Тому не переміщуйте умови між ON і WHERE механічно під час оптимізації. Спочатку перевірте семантику.
Порядок таблиць у тексті запиту не визначає порядок їх фактичного читання:
FROM customers AS c
JOIN orders AS o
ON o.customer_id = c.customer_idабо:
FROM orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_idДля внутрішніх з’єднань PostgreSQL може переставляти таблиці та обирати інший порядок виконання. Він оцінює різні варіанти на основі статистики та вартості операцій.
Для великої кількості таблиць пошук усіх варіантів може бути дорогим. У таких випадках PostgreSQL може перейти до генетичного планувальника. Але зазвичай не варто вручну переписувати порядок таблиць, доки не проаналізовано план і статистику.
Для LEFT JOIN, FULL JOIN та інших зовнішніх з’єднань можливості перестановки обмежені семантикою запиту.
Зовнішній ключ не створює індекс автоматично. Первинний ключ індексується автоматично, але колонка зовнішнього ключа — ні.
У прикладі індекси потрібні на дочірніх колонках:
CREATE INDEX orders_customer_id_idx
ON orders (customer_id);
CREATE INDEX order_items_order_id_idx
ON order_items (order_id);Це особливо важливо для:
пошуку дочірніх рядків для одного батьківського рядка;
Nested Loop;
видалення або оновлення рядків у таблиці, на яку посилається зовнішній ключ;
запитів із LEFT JOIN, де треба знайти відсутні дочірні записи.
Для одночасної фільтрації та з’єднання може бути корисний складений індекс:
CREATE INDEX orders_customer_status_idx
ON orders (customer_id, status);Він може допомогти запиту:
SELECT o.order_id, o.created_at
FROM orders AS o
WHERE o.customer_id = 42
AND o.status = 'paid';Але не кожен JOIN потребує індексу на обох сторонах. Якщо PostgreSQL обирає Hash Join і сканує обидві таблиці послідовно, індекс може не використовуватися — це не обов’язково проблема.
Якщо запити майже завжди працюють лише з певним станом, можна індексувати підмножину рядків:
CREATE INDEX orders_paid_customer_created_idx
ON orders (customer_id, created_at DESC)
WHERE status = 'paid';Такий індекс менший за повний індекс і може бути швидшим для запитів із відповідним предикатом:
SELECT
o.order_id,
o.created_at,
o.total_amount
FROM orders AS o
JOIN customers AS c
ON c.customer_id = o.customer_id
WHERE c.customer_id = 42
AND o.status = 'paid'
ORDER BY o.created_at DESC;Умова запиту має бути сумісною з предикатом часткового індексу. Якщо PostgreSQL не може довести, що запит працює лише з рядками індексу, він може його не використати.
Якщо потрібно приєднати агреговані дані, спочатку обмежте набір рядків, які беруть участь в агрегації:
SELECT
c.customer_id,
c.email,
COALESCE(paid.order_count, 0) AS order_count
FROM customers AS c
LEFT JOIN (
SELECT
customer_id,
count(*) AS order_count
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
) AS paid
ON paid.customer_id = c.customer_id
WHERE c.country_code = 'UA';Такий підхід зберігає клієнтів без замовлень і не переносить непотрібні статуси в агрегацію.
Перевіряйте план: інколи планувальник сам знаходить еквівалентне перетворення, а інколи форма запиту впливає на можливість оптимізації.
Для повільного JOIN використовуйте послідовність:
Запустіть запит із EXPLAIN (ANALYZE, BUFFERS).
Знайдіть вузол із найбільшим actual time.
Порівняйте rows та actual rows.
Перевірте, чи не повторюється дорогий вузол тисячі разів через Nested Loop.
Перевірте умови з’єднання та типи колонок.
Перевірте індекси на ключах і фільтрах.
Оновіть статистику через ANALYZE.
Повторіть вимірювання на даних, близьких до production.
Порівняйте не лише час, а й кількість прочитаних буферів та тимчасових файлів.
Порівнюйте плани після кожної зміни. Індекс, який прискорює один запит, може збільшити вартість запису та не допомогти іншим запитам.
Для діагностики можна тимчасово вимкнути певний тип з’єднання в поточній сесії:
SET LOCAL enable_hashjoin = off;Або:
SET LOCAL enable_nestloop = off;Це корисно, щоб перевірити, чи альтернативний алгоритм потенційно швидший. Такі параметри не слід використовувати як постійний спосіб оптимізації без розуміння причини. Зазвичай правильніше виправити статистику, індекси або умови запиту.
PostgreSQL може змінити порядок внутрішніх з’єднань. Орієнтуйтеся на EXPLAIN, а не на порядок запису таблиць.
Індекси займають місце, уповільнюють INSERT, UPDATE і DELETE. Створюйте індекси для реальних умов з’єднання та фільтрації.
Для великої частини таблиці послідовне читання та Hash Join можуть бути дешевшими за багато випадкових звернень через індекс.
Для зовнішніх з’єднань це може змінити результат, перетворивши LEFT JOIN на логіку INNER JOIN.
Якщо планувальник очікує 10 рядків, а отримує 1 000 000, він може обрати невдалий алгоритм. Спочатку перевірте статистику та розподіл даних.
Nested Loop не є універсально швидшим. Для великих наборів без вузького індексованого пошуку Hash Join або Merge Join часто підходить краще.
Час залежить від кешу, навантаження та стану системи. Аналізуйте також BUFFERS, кількість рядків, loops і тимчасові операції.
Оптимізуйте JOIN на основі фактичного плану EXPLAIN (ANALYZE, BUFFERS).
Відфільтровуйте дані до з’єднання, коли це можливо.
Використовуйте сумісні типи ключів і не приховуйте їх непотрібними приведеннями.
Індексуйте колонки зовнішніх ключів та складені умови відповідно до реальних запитів.
Nested Loop ефективний для малого зовнішнього набору та індексованого пошуку.
Hash Join часто підходить для великих з’єднань за рівністю.
Merge Join корисний для великих відсортованих наборів.
Порядок таблиць у FROM не гарантує порядок виконання.
Для OUTER JOIN різниця між ON і WHERE може змінити результат.
Актуальна статистика важлива не менше за індекси.
Не примушуйте PostgreSQL використовувати алгоритм JOIN без вимірювання та пояснення причини.