Чотири місця для підзапиту
Підзапит можна поставити туди, де очікується значення, список або таблиця. стоїть замість одного значення: salary_max - (SELECT AVG(salary_max) FROM vacancies). Підзапит в IN чи EXISTS відповідає на «чи є такий рядок». У FROM підзапит стає , з якою працюють як зі звичайною. І в SELECT поруч з колонками, наприклад кількість активних вакансій компанії.
підзапит посилається на рядок зовнішнього запиту: WHERE v.company_id = c.id. Логічно він виконується для кожного рядка заново. Насправді планувальник часто переписує IN і EXISTS у semi-join чи anti-join, і тоді різниці з JOIN немає. Агрегат у корельованому підзапиті переписати важче, і там він справді рахується рядок за рядком.
, тобто WITH name AS (SELECT ...), дає підзапиту імʼя і виносить його нагору, і довгий запит читається зверху вниз. WITH RECURSIVE додає те, чого звичайний підзапит не вміє: обійти дерево категорій чи ланцюжок «хто кого запросив» на невідому глибину. CTE є в MySQL з 8.0, у PostgreSQL і в SQLite з 3.8.3.
Що з цього перепише планувальник
Питання «що швидше, підзапит чи JOIN» без плану не має відповіді. Деякі записи бази зводять до одного й того самого плану, деякі ні. Нижче поведінка MySQL 8.0 і PostgreSQL 16 на 200 000 вакансій.
WHERE c.id IN (SELECT company_id FROM vacancies WHERE ...)
semi-join
Обидві бази виконують це як зʼєднання з унікальними company_id: PostgreSQL через HashAggregate і Hash Join, MySQL через Materialize with deduplication. Дублів компаній немає.
JOIN vacancies v ON v.company_id = c.id WHERE ...
дублі
Компанія приходить стільки разів, скільки в неї підхожих вакансій. DISTINCT лікує це сортуванням чи хешуванням усього результату. Для «компанії, у яких є» IN чи EXISTS чесніші.
WHERE EXISTS (SELECT 1 FROM vacancies v WHERE v.company_id = c.id)
semi-join
Те саме, що IN, і без пастки з NULL у NOT-версії. NOT EXISTS стає anti-join в обох базах.
WHERE c.id NOT IN (SELECT company_id FROM vacancies)
пастка NULL
Один NULL у company_id, і результат порожній. MySQL робить з NOT IN anti-join лише для колонок NOT NULL, PostgreSQL ніколи.
salary_max > (SELECT AVG(salary_max) ... WHERE v2.company_id = v.company_id)
на кожен рядок
Корельований агрегат жодна база не переписала: MySQL виконав його 160 000 разів, PostgreSQL 200 000. 7,3 с і 4,4 с проти 0,25 с з AVG() OVER (PARTITION BY company_id).
(SELECT COUNT(*) FROM vacancies v WHERE v.company_id = c.id) AS n
на кожен рядок
Підзапит у SELECT виконується для кожної компанії, але з індексом це швидкий пошук. Так працює withCount() в Eloquent. Для десяти лічильників по тих самих рядках одна похідна таблиця з GROUP BY дешевша.
WITH a AS (SELECT ...) SELECT ... FROM a WHERE company_id = 42
вбудовано
CTE, на який послалися один раз, обидві бази зливають із зовнішнім запитом, і умова company_id = 42 йде в індекс. PostgreSQL так робить з 12-ї версії.
Час і кількість виконань узято з EXPLAIN ANALYZE на MySQL 8.0.46 і PostgreSQL 16 (з вимкненим JIT); дані ті самі, що в розділі EXPLAIN нижче.
Дерево категорій одним запитом
Категорії зберігаються списком суміжності: кожна знає лише parent_id. Щоб отримати повний шлях Backend > PHP > Laravel, рекурсивний CTE починає з коренів (parent_id IS NULL) і на кожному кроці приєднує дітей уже знайдених рядків, поки нові не перестануть зʼявлятися. Між двома частинами стоїть UNION ALL.
Пастка з типами. Колонку path задає перша, нерекурсивна частина. У MySQL її довжина береться з name, VARCHAR(50), і довший шлях дасть помилку Data too long. PostgreSQL вимагає, щоб тип збігався в обох частинах. SQLite типи не перевіряє. Тому першу частину явно приводять: CAST(name AS CHAR(200)) у MySQL і CAST(name AS TEXT) у PostgreSQL.
+------+-------+-------------------------+ | id | depth | path | +------+-------+-------------------------+ | 1 | 0 | Backend | | 2 | 1 | Backend > PHP | | 3 | 2 | Backend > PHP > Laravel | | 4 | 2 | Backend > PHP > Symfony | | 5 | 0 | Frontend | | 6 | 1 | Frontend > Vue | +------+-------+-------------------------+ -- без CAST і з довгою назвою кореня: ERROR 1406 (22001): Data too long for column 'path' at row 1
Рекурсія в MySQL обмежена змінною cte_max_recursion_depth, за замовчуванням 1000 кроків. Для дерева категорій цього вдосталь, а цикл у даних (категорія сама собі предок) зупиниться помилкою, а не зависанням.
id | depth | path ----+-------+------------------------- 1 | 0 | Backend 2 | 1 | Backend > PHP 3 | 2 | Backend > PHP > Laravel 4 | 2 | Backend > PHP > Symfony 5 | 0 | Frontend 6 | 1 | Frontend > Vue (6 rows) -- без CAST: ERROR: recursive query "tree" column 3 has type character varying(50) in non-recursive term but type character varying overall
Межі глибини PostgreSQL не має: цикл у даних означає нескінченний запит. Захищаються умовою на depth, масивом уже пройдених id або конструкцією CYCLE, яка зʼявилася в PostgreSQL 14.
id depth path -- ----- ----------------------- 1 0 Backend 2 1 Backend > PHP 3 2 Backend > PHP > Laravel 4 2 Backend > PHP > Symfony 5 0 Frontend 6 1 Frontend > Vue
CAST не потрібен: SQLite не тримає довжину VARCHAR і не звіряє типи частин. Тому запит без CAST, що проходить тести на SQLite, на PostgreSQL падає.
Коли підзапит повернув два рядки
Скалярний підзапит обіцяє одне значення. Якщо умова в ньому неточна і рядків виявилося кілька, MySQL і PostgreSQL зупиняють запит з помилкою. SQLite мовчки бере перший рядок, і помилка, яку тести на SQLite не помітили, вилазить у продакшні.
ERROR 1242 (21000): Subquery returns more than 1 row
У Kvitka Soft три вакансії. Щоб отримати одну конкретну, підзапиту потрібні ORDER BY і LIMIT 1 або агрегат.
ERROR: more than one row returned by a subquery used as an expression
Нуль рядків помилкою не є: скалярний підзапит без рядків дає NULL.
title ---------- Senior PHP
Помилки немає. Який рядок «перший», без ORDER BY не визначено.
CTE вбудований і CTE окремо
До PostgreSQL 12 кожен CTE виконувався окремо і повністю, і WITH був «барʼєром оптимізації». З 12-ї версії CTE, на який послалися один раз і який не змінює дані, вбудовується в зовнішній запит. Ключові слова MATERIALIZED і NOT MATERIALIZED змінюють це рішення вручну. MySQL 8.0 так само зливає такий CTE, як і похідну таблицю.
Третя вкладка про інше: корельований підзапит з агрегатом проти віконної функції на тих самих 200 000 вакансій.
WITH active AS (SELECT * FROM vacancies WHERE status = 'active') SELECT * FROM active WHERE company_id = 42;Bitmap Heap Scan on vacancies (cost=4.46..85.21 rows=17 width=37) (actual time=0.018..0.052 rows=22 loops=1)
Recheck Cond: (company_id = 42)
Filter: ((status)::text = 'active'::text)
-> Bitmap Index Scan on vacancies_company_id_index (actual time=0.009..0.009 rows=22 loops=1)
Index Cond: (company_id = 42)
Execution Time: 0.114 ms WITH active AS MATERIALIZED (SELECT * FROM vacancies WHERE status = 'active') SELECT * FROM active WHERE company_id = 42;CTE Scan on active (cost=4165.00..7531.16 rows=748 width=192) (actual time=0.774..77.998 rows=22 loops=1)
Filter: (company_id = 42)
Rows Removed by Filter: 149978
CTE active
-> Seq Scan on vacancies (actual time=0.003..36.332 rows=150000 loops=1)
Filter: ((status)::text = 'active'::text)
Execution Time: 83.037 ms PostgreSQL 16, 200 000 вакансій, три чверті з них активні. Вивід знято зі справжньої бази, частину оцінок і Planning Time прибрано.
WITH active AS (SELECT * FROM vacancies WHERE status = 'active') SELECT * FROM active WHERE company_id = 42;-> Filter: (vacancies.`status` = 'active') (actual time=0.131..0.182 rows=22 loops=1)
-> Index lookup on vacancies using vacancies_company_id_index (company_id=42)
(actual time=0.127..0.172 rows=22 loops=1) SELECT c.id, c.name FROM companies c WHERE c.id IN (SELECT v.company_id FROM vacancies v WHERE v.salary_max > 5900);-> Nested loop inner join (actual time=84.5..93.5 rows=720 loops=1)
-> Table scan on c (actual time=0.183..4.03 rows=10000 loops=1)
-> Single-row index lookup on <subquery2> using <auto_distinct_key> (company_id=c.id)
-> Materialize with deduplication (actual time=84.3..84.3 rows=720 loops=1)
-> Filter: (v.salary_max > 5900) (actual time=0.129..82.1 rows=3200 loops=1)
-> Table scan on v (actual time=0.109..67.4 rows=200000 loops=1) MySQL 8.0.46, InnoDB, ті самі дані. Оцінки cost прибрано.
SELECT count(*) FROM vacancies v WHERE salary_max > (SELECT AVG(salary_max) FROM vacancies v2 WHERE v2.company_id = v.company_id);Aggregate (actual time=4410.105..4410.107 rows=1 loops=1)
-> Seq Scan on vacancies v (actual time=1.459..4399.646 rows=77005 loops=1)
Filter: ((salary_max)::numeric > (SubPlan 1))
SubPlan 1
-> Aggregate (actual time=0.021..0.021 rows=1 loops=200000)
-> Bitmap Heap Scan on vacancies v2 (actual time=0.005..0.018 rows=22 loops=200000)
Recheck Cond: (company_id = v.company_id)
Execution Time: 4410.205 ms SELECT count(*) FROM (SELECT salary_max, AVG(salary_max) OVER (PARTITION BY company_id) AS a FROM vacancies) t WHERE salary_max > a;Aggregate (actual time=245.598..245.600 rows=1 loops=1)
-> Subquery Scan on t (actual time=0.203..241.942 rows=77005 loops=1)
Filter: ((t.salary_max)::numeric > t.a)
-> WindowAgg (actual time=0.198..215.477 rows=200000 loops=1)
-> Index Scan using vacancies_company_id_index on vacancies (rows=200000 loops=1)
Execution Time: 245.733 ms PostgreSQL 16, 200 000 вакансій. JIT вимкнено (SET jit = off), щоб час не включав компіляцію; оцінки cost прибрано. MySQL 8.0 на тому самому запиті: Select #2 dependent, loops=160000, 7,25 с.
- CTE Scan
- Читання з уже обчисленого CTE. Якщо цього вузла в плані немає, CTE вбудовано, і план такий, ніби WITH не було.
- CTE active
- Окремий крок: усі 150 000 активних вакансій зібрано в тимчасовий результат. Індексу на ньому немає, тож company_id = 42 перевіряється перебором.
- MATERIALIZED
- Корисний, коли CTE дорогий і на нього посилаються двічі, або щоб зафіксувати порядок обчислень. NOT MATERIALIZED навпаки вбудовує CTE, навіть якщо на нього посилаються кілька разів.
- Index lookup
- CTE злито із зовнішнім запитом: імені active у плані вже немає, а company_id = 42 шукається в індексі.
- Materialize with deduplication
- IN з підзапитом став semi-join: підзапит виконано один раз, 3 200 вакансій зведено до 720 унікальних company_id, і з ними зʼєднано компанії.
- Nested loop inner join
- Звичайне зʼєднання, яке написав би й JOIN з DISTINCT. Різниця лише в тому, що дублі прибрано до зʼєднання, а не після.
- SubPlan
- Корельований підзапит, який не вдалося переписати. loops=200000: середня зарплата компанії рахується заново для кожної вакансії, хоча різних company_id менше 9 000.
- WindowAgg
- Віконна функція рахує середнє по кожній компанії один раз за прохід по відсортованих рядках. Результат той самий, 77 005 вакансій, у 18 разів швидше.
EXISTS у SELECT замість запиту на кожен профіль
Картка розробника показує, чи є в нього проєкти, які редактори не приховали. Перевіряти це окремим запитом для кожного профілю означало б N+1 у списку. Тому репозиторій додає до вибірки корельований EXISTS прямо в SELECT: одна вибірка профілів, і для кожного база сама дивиться в projects. Результат приходить як shows_projects; PostgreSQL повертає boolean, SQLite 1 або 0, тож код приводить його через (bool).
src/Hiring/Infrastructure/Catalog/EloquentCompanyDirectory.phpКаталог компаній: whereHas('jobs', ...) це WHERE EXISTS з корельованим підзапитом, withCount(['jobs as open_jobs' => ...]) це скалярний підзапит у SELECT. Обидва Eloquent будує сам.src/Hiring/Infrastructure/Catalog/EloquentJobCatalog.phpwhereNotIn('vacancies.company_id', ...) тут зі списком значень, а не з підзапитом, і з окремим whereNull: пастку NOT IN з NULL обійдено явно.src/Analytics/Infrastructure/Persistence/EloquentTrafficReport.phpCTE в коді сайту немає. Ближче всього звіт аналітики: «перший перегляд відвідувача» рахується в PHP, а в SQL був би похідною таблицею чи CTE з ROW_NUMBER().Читабельно, але не те або не так
NOT IN з підзапитом
Компанії без вакансій через id NOT IN (SELECT company_id FROM vacancies) повертають порожній результат, щойно зʼявляється вакансія без компанії.
Натомість: NOT EXISTS. Він не залежить від NULL і в обох базах стає anti-join.
Корельований агрегат на великій таблиці
salary_max > (SELECT AVG(...) WHERE v2.company_id = v.company_id) рахує середнє заново для кожного рядка: 200 000 разів, 4,4 с.
Натомість: Віконна функція AVG() OVER (PARTITION BY company_id) або похідна таблиця з GROUP BY company_id, зʼєднана з вакансіями.
CTE як спосіб «закешувати» результат
Розрахунок на те, що WITH обчислиться один раз, працює не завжди. PostgreSQL 12+ вбудовує CTE з одним посиланням, і він виконується як частина запиту; MySQL так само.
Натомість: Якщо потрібна саме окрема матеріалізація, у PostgreSQL є AS MATERIALIZED. Для повторного використання між запитами потрібна тимчасова таблиця, а не CTE.
Типи в рекурсивному CTE
Запит з деревом, перевірений на SQLite, падає на PostgreSQL через різні типи частин або на MySQL з Data too long, коли шлях довший за найдовшу назву кореня.
Натомість: Приводьте колонки першої частини явно: CAST(... AS TEXT) у PostgreSQL, CAST(... AS CHAR(n)) у MySQL.
Скалярний підзапит без гарантії одного рядка
Підзапит у SELECT повертає кілька рядків. MySQL і PostgreSQL падають, SQLite мовчки бере перший, і тести це пропускають.
Натомість: Умова на унікальний ключ, агрегат або ORDER BY ... LIMIT 1 у підзапиті.
- Скалярний підзапит
- Підзапит, що повертає рівно одну колонку й не більше одного рядка. Нуль рядків дає NULL. Більше одного дає помилку в MySQL і PostgreSQL, а в SQLite перше значення.
- Похідна таблиця
- Підзапит у FROM з аліасом: FROM (SELECT ...) t. MySQL називає це derived table. Планувальник може злити її із зовнішнім запитом або виконати окремо в тимчасову таблицю.
- Корельований підзапит
- Підзапит, що використовує колонку зовнішнього запиту. Без переписування виконується для кожного рядка зовні, з переписуванням стає semi-join або anti-join.
- CTE
- Common Table Expression: іменований підзапит у WITH на початку запиту. На один CTE можна послатися кілька разів, а рекурсивний CTE посилається сам на себе.
Питання зі співбесід
IN порівнює значення з набором, який повертає підзапит; EXISTS перевіряє, чи повертає корельований підзапит хоч один рядок. Для позитивної перевірки результат однаковий, і MySQL та PostgreSQL виконують обидва як semi-join. Різниця зʼявляється в NOT IN: NULL у підзапиті робить результат порожнім, а NOT EXISTS від NULL не залежить.
Це підзапит, що посилається на колонки зовнішнього запиту. Логічно він виконується для кожного зовнішнього рядка. IN і EXISTS планувальник зазвичай переписує в зʼєднання, а підзапит з агрегатом часто ні, і тоді в плані SubPlan чи dependent subquery з loops, рівним кількості рядків. Переписати можна через JOIN з агрегованою похідною таблицею або віконну функцію.
WITH RECURSIVE: перша частина бере саму категорію Backend, рекурсивна приєднує категорії, у яких parent_id дорівнює id уже знайдених, через UNION ALL. Запит закінчується, коли крок не знайшов нових рядків. У MySQL глибину обмежує cte_max_recursion_depth, у PostgreSQL від циклів захищаються умовою на глибину або CYCLE.
З 12-ї версії CTE з одним посиланням вбудовується в зовнішній запит. Звіт, де зовні стояла вибіркова умова, виграв: умова дійшла до індексу всередині CTE. Звіт, який розраховував на одноразове обчислення дорогого CTE або на порядок обчислень, міг програти, бо тепер планувальник оптимізує все разом і може обрати гірший план. Повернути стару поведінку для конкретного CTE можна через AS MATERIALIZED.