Пошук уроків, статей та іншого контенту
Розгляньте підзапити, що залежать від поточного рядка зовнішнього запиту, та оцініть їхню продуктивність.
Корельований підзапит — це підзапит, який використовує значення з поточного рядка зовнішнього запиту.
На відміну від звичайного підзапиту, корельований підзапит не є повністю незалежним. Його результат залежить від конкретного рядка, який обробляє зовнішній запит.
Загальна структура:
SELECT ...
FROM outer_table AS outer_row
WHERE outer_row.column оператор (
SELECT ...
FROM inner_table AS inner_row
WHERE inner_row.column = outer_row.column
);Посилання outer_row.column усередині підзапиту і створює кореляцію.
Створимо навчальні таблиці:
CREATE TEMP TABLE departments (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TEMP TABLE employees (
id integer PRIMARY KEY,
name text NOT NULL,
department_id integer NOT NULL REFERENCES departments(id),
salary numeric(10, 2) NOT NULL
);
INSERT INTO departments (id, name) VALUES
(1, 'Розробка'),
(2, 'Аналітика'),
(3, 'Підтримка');
INSERT INTO employees (id, name, department_id, salary) VALUES
(1, 'Олена', 1, 70000),
(2, 'Максим', 1, 90000),
(3, 'Ірина', 1, 80000),
(4, 'Андрій', 2, 60000),
(5, 'Марія', 2, 75000),
(6, 'Петро', 3, 50000);Тепер знайдемо працівників, чия зарплата вища за середню зарплату в їхньому відділі:
SELECT
e.name,
e.salary,
e.department_id
FROM employees AS e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees AS e2
WHERE e2.department_id = e.department_id
);Підзапит використовує значення e.department_id із поточного рядка зовнішнього запиту:
WHERE e2.department_id = e.department_idДля кожного працівника обчислюється середня зарплата саме його відділу.
Результат:
name | salary | department_id
-------+---------+---------------
Максим | 90000 | 1
Марія | 75000 | 2Для працівника Максим підзапит обчислює середню зарплату працівників відділу 1. Для Марії — середню зарплату відділу 2.
Підзапит у попередньому прикладі використовується як одне значення. Такий підзапит називають скалярним.
Скалярний підзапит повинен повернути:
один стовпець;
не більше одного рядка.
Функція AVG завжди повертає один рядок, тому цей приклад коректний.
Якщо підзапит поверне кілька рядків, PostgreSQL повідомить про помилку:
ERROR: more than one row returned by a subquery used as an expressionНаприклад, такий запит некоректний:
SELECT
e.name
FROM employees AS e
WHERE e.salary = (
SELECT e2.salary
FROM employees AS e2
WHERE e2.department_id = e.department_id
);Для одного відділу підзапит може повернути кілька зарплат, а зовнішній оператор = очікує одне значення.
У такій ситуації потрібно використовувати інший оператор, наприклад IN, EXISTS, ANY або агрегатну функцію — залежно від умови задачі.
EXISTSEXISTS перевіряє, чи повертає підзапит хоча б один рядок.
Розглянемо таблицю замовлень:
CREATE TEMP TABLE orders (
id integer PRIMARY KEY,
employee_id integer NOT NULL REFERENCES employees(id),
total numeric(10, 2) NOT NULL
);
INSERT INTO orders (id, employee_id, total) VALUES
(1, 1, 1200),
(2, 1, 800),
(3, 2, 2500),
(4, 4, 900);Знайдемо працівників, які мають хоча б одне замовлення:
SELECT
e.id,
e.name
FROM employees AS e
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.employee_id = e.id
);Умова:
o.employee_id = e.idпорівнює замовлення з поточним працівником зовнішнього запиту.
SELECT 1 усередині EXISTS — поширений стиль запису. Значення 1 не має значення: EXISTS перевіряє лише наявність рядка.
EXISTS зручнийEXISTS добре підходить для перевірки факту:
чи має користувач замовлення;
чи існує пов’язаний запис;
чи є хоча б один платіж;
чи відповідає пов’язаний рядок певній умові.
Після знаходження першого відповідного рядка PostgreSQL може припинити перевірку для поточного рядка зовнішнього запиту.
NOT EXISTSЗа допомогою NOT EXISTS можна знайти рядки без пов’язаних записів.
Наприклад, знайдемо працівників, які ще не мають замовлень:
SELECT
e.id,
e.name
FROM employees AS e
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.employee_id = e.id
);NOT EXISTS зазвичай є надійним способом перевірити відсутність пов’язаних рядків, зокрема коли в даних можливі значення NULL.
JOIN і агрегуваннямКорельований підзапит часто можна переписати через JOIN і GROUP BY.
Попередній запит про зарплату можна записати так:
SELECT
e.name,
e.salary,
e.department_id
FROM employees AS e
JOIN (
SELECT
department_id,
AVG(salary) AS average_salary
FROM employees
GROUP BY department_id
) AS department_salary
ON department_salary.department_id = e.department_id
WHERE e.salary > department_salary.average_salary;Тут середня зарплата кожного відділу обчислюється один раз у похідній таблиці, після чого результат приєднується до працівників.
Обидва варіанти логічно еквівалентні:
корельований підзапит безпосередньо описує умову для поточного рядка;
JOIN спочатку формує агреговані дані, а потім з’єднує їх з основною таблицею.
Не можна наперед стверджувати, що один варіант завжди швидший. PostgreSQL аналізує запит і може перетворити його внутрішній план. Продуктивність потрібно перевіряти на реальних даних.
Для аналізу плану виконання використовуйте:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
e.name,
e.salary,
e.department_id
FROM employees AS e
WHERE e.salary > (
SELECT AVG(e2.salary)
FROM employees AS e2
WHERE e2.department_id = e.department_id
);EXPLAIN показує план, який PostgreSQL планує використати.
ANALYZE додатково виконує запит і показує фактичну статистику:
фактичний час виконання;
кількість оброблених рядків;
кількість повторних запусків вузла плану;
відмінність між оціненою та фактичною кількістю рядків.
BUFFERS допомагає побачити використання буферів shared memory і диска.
Серед важливих полів у результаті:
Planning Time — час побудови плану;
Execution Time — час виконання;
rows — кількість рядків;
loops — кількість виконань вузла;
Seq Scan — послідовне сканування таблиці;
Index Scan — використання індексу;
Nested Loop — з’єднання вкладеними циклами.
План потрібно читати з урахуванням розміру даних. Для маленької таблиці послідовне сканування часто є нормальним і може бути швидшим за використання індексу.
Для запиту з EXISTS PostgreSQL часто вигідно мати індекс на колонці, яка використовується для зв’язку:
CREATE INDEX orders_employee_id_idx
ON orders (employee_id);Після створення індексу можна знову перевірити план:
EXPLAIN (ANALYZE, BUFFERS)
SELECT
e.id,
e.name
FROM employees AS e
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.employee_id = e.id
);Індекс особливо важливий, коли:
зовнішня таблиця містить багато рядків;
пов’язана таблиця містить багато рядків;
умова кореляції використовує селективну колонку;
підзапит часто перевіряє наявність пов’язаного запису.
Індекс не гарантує прискорення кожного запиту. PostgreSQL може вибрати послідовне сканування, якщо вважає його вигіднішим.
Корельований підзапит логічно перевіряється в контексті рядків зовнішнього запиту. Якщо зовнішній запит повертає багато рядків, підзапит може виконуватися багато разів.
Наприклад, у такій формі:
SELECT
e.name
FROM employees AS e
WHERE (
SELECT COUNT(*)
FROM orders AS o
WHERE o.employee_id = e.id
) > 10;для кожного працівника визначається кількість його замовлень.
На великих таблицях це може бути дорого, якщо:
немає індексу orders(employee_id);
зовнішній запит обробляє багато працівників;
підзапит виконує складі обчислення;
оптимізатор не може ефективно перетворити запит.
Той самий запит можна переписати через агрегування:
SELECT
e.name
FROM employees AS e
JOIN (
SELECT
employee_id
FROM orders
GROUP BY employee_id
HAVING COUNT(*) > 10
) AS order_counts
ON order_counts.employee_id = e.id;Порівнювати ці варіанти потрібно за допомогою EXPLAIN (ANALYZE, BUFFERS) на даних, наближених до продуктивного середовища.
Важливо: корельований підзапит не обов’язково буде повільним. PostgreSQL може оптимізувати його, перетворити на напівз’єднання або вибрати інший ефективний план. Проблемою є не сам синтаксис, а фактичний план і обсяг даних.
Цей підзапит не є корельованим:
SELECT
e.name
FROM employees AS e
WHERE e.salary > (
SELECT AVG(salary)
FROM employees
);Він обчислює одну середню зарплату для всієї таблиці та не посилається на зовнішній рядок.
Кореляція з’являється лише після додавання посилання на зовнішній псевдонім:
WHERE e2.department_id = e.department_idОператор = зі скалярним підзапитом вимагає не більше одного рядка. Якщо можливі кілька результатів, використовуйте відповідний оператор або агрегатну функцію.
COUNT там, де достатньо EXISTSТакий варіант рахує всі відповідні рядки:
WHERE (
SELECT COUNT(*)
FROM orders AS o
WHERE o.employee_id = e.id
) > 0Для перевірки самого факту наявності зазвичай достатньо:
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.employee_id = e.id
)Вигляд SQL-запиту не показує всі рішення оптимізатора. Для оцінювання використовуйте:
EXPLAIN (ANALYZE, BUFFERS)і перевіряйте запит на достатньому обсязі даних.
NULLПорівняння із NULL не повертає TRUE, воно повертає UNKNOWN.
Наприклад, якщо підзапит AVG поверне NULL, умова:
e.salary > NULLне буде істинною для жодного рядка.
Корельований підзапит залежить від значень поточного рядка зовнішнього запиту.
Зовнішній псевдонім таблиці можна використовувати всередині підзапиту.
Скалярний підзапит повинен повертати не більше одного рядка.
EXISTS перевіряє наявність хоча б одного пов’язаного рядка.
NOT EXISTS перевіряє відсутність пов’язаних рядків.
Корельований підзапит іноді можна переписати через JOIN, GROUP BY або похідну таблицю.
PostgreSQL може оптимізувати корельовані підзапити, тому продуктивність потрібно оцінювати за фактичним планом.
Для аналізу використовуйте EXPLAIN (ANALYZE, BUFFERS).
Індекси на колонках кореляції можуть суттєво вплинути на продуктивність.