Оптимизация SQL в PostgreSQL: IN против EXISTS против ANY / ALL против JOIN
Это один из наиболее распространенных вопросов, задаваемых разработчиками, которые пишут SQL-запросы к базе данных PostgreSQL. Существует несколько способов, которыми подвыбор или поиск могут быть вставлены в оператор SQL. Оптимизатор PostgreSQL очень умен в оптимизации запросов, и многие запросы могут быть переписаны / преобразованы для повышения производительности.
Давайте обсудим тему с примером, для которого я использую схему, созданную pgbench.
Примечание: для тех, кто не знаком с pgbench, это инструмент для микро-бенчмаркинга, поставляемый с PostgreSQL. Пример схемы pgbench может быть инициализирован с некоторыми данными следующим образом:
pgbench -i -s 10Для этого примера я обновил баланс ветвей пары ветвей:
1
update pgbench_branches set bbalance=4500000 where bid in (4,7);Запросы на включение
Задача SQL для этого примера: узнать количество учетных записей на ветку из pgbench_accounts для тех ветвей, где баланс на уровне филиала больше нуля. Этот запрос может быть написан четырьмя различными способами в соответствии со стандартами ANSI SQL.
1. Использование предложения IN
SELECT count(aid),bid FROM pgbench_accounts WHERE
bid in (SELECT bid FROM pgbench_branches WHERE bbalance > 0)
GROUP BY bid;2. Используя предложение ANY
SELECT count(aid),bid FROM pgbench_accounts WHERE
bid = ANY(SELECT bid FROM pgbench_branches WHERE bbalance > 0)
GROUP BY bid;3. Использование предложения EXISTS
SELECT count(aid),bid
FROM pgbench_accounts WHERE EXISTS
(SELECT bid FROM pgbench_branches WHERE bbalance > 0
AND pgbench_accounts.bid = pgbench_branches.bid)
GROUP BY bid;4. Использование INNER JOIN
SELECT count(aid),a.bid
FROM pgbench_accounts a
JOIN pgbench_branches b ON a.bid = b.bid
WHERE b.bbalance > 0
GROUP BY a.bid;При написании запроса можно предположить, что EXISTS и INNER JOIN могут быть лучше, потому что они могут использовать всю логику и оптимизацию для объединения двух таблиц, тогда как предложения IN и ANY должны иметь дело с подзапросами. Тем не менее, PostgreSQL (по крайней мере, PG 10 и выше) достаточно умен, чтобы создать один и тот же план выполнения для всех четырех вариантов!
Все вышеперечисленные запросы будут генерировать один и тот же план выполнения следующим образом:
HashAggregate (cost=31132.65..31132.75 rows=10 width=12) (actual time=279.625..279.626 rows=2 loops=1)
Group Key: a.bid
-> Hash Join (cost=1.15..30132.65 rows=200000 width=8) (actual time=63.686..242.956 rows=200000 loops=1)
Hash Cond: (a.bid = b.bid)
-> Seq Scan on pgbench_accounts a (cost=0.00..26394.00 rows=1000000 width=8) (actual time=0.012..86.250 rows=1000000 loops=1)
-> Hash (cost=1.12..1.12 rows=2 width=4) (actual time=0.016..0.016 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pgbench_branches b (cost=0.00..1.12 rows=2 width=4) (actual time=0.010..0.012 rows=2 loops=1)
Filter: (bbalance > 0)
Rows Removed by Filter: 8
Planning Time: 0.257 ms
Execution Time: 279.703 ms
(12 rows)Примечание: Примечание: подавите параллельное выполнение для лучшей читабельности и простого плана выполнения. Даже при параллельном плане выполнения все запросы создают один и тот же план выполнения.
Итак, можем ли мы заключить, что мы можем написать запрос так, как нам удобно, а ум PostgreSQL позаботится обо всем остальном? Подождите! Все может пойти по-другому, если мы возьмем сценарий исключения.
Запросы исключения
Задача SQL выглядит следующим образом : узнать количество учетных записей на ветку из pgbench_accounts EXCEPT для тех ветвей, где баланс на уровне ветви больше нуля.
Таким образом, четыре способа написания запросов становятся:
1. Использование NOT IN
SELECT count(aid),bid FROM pgbench_accounts WHERE
bid NOT IN (SELECT bid FROM pgbench_branches WHERE bbalance > 0)
GROUP BY bid;2. Использование <> ALL
SELECT count(aid),bid FROM pgbench_accounts WHERE
bid <> ALL(SELECT bid FROM pgbench_branches WHERE bbalance > 0)
GROUP BY bid;3. Использование NOT EXISTS
SELECT count(aid),bid
FROM pgbench_accounts WHERE NOT EXISTS
(SELECT bid FROM pgbench_branches WHERE bbalance > 0
AND pgbench_accounts.bid = pgbench_branches.bid)
GROUP BY bid;4. Использование LEFT JOIN и NULL
SELECT count(aid),a.bid
FROM pgbench_accounts a
LEFT JOIN pgbench_branches b ON a.bid = b.bid AND b.bbalance > 0
WHERE b.bid IS NULL
GROUP BY a.bid;«NOT IN» и «<> ALL» создают план выполнения с подзапросами (SubPlan). Они соответственно:
HashAggregate (cost=31395.13..31395.23 rows=10 width=12) (actual time=395.297..395.299 rows=8 loops=1)
Group Key: pgbench_accounts.bid
-> Seq Scan on pgbench_accounts (cost=1.13..28895.13 rows=500000 width=8) (actual time=0.042..250.086 rows=800000 loops=1)
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 200000
SubPlan 1
-> Seq Scan on pgbench_branches (cost=0.00..1.12 rows=2 width=4) (actual time=0.010..0.012 rows=2 loops=1)
Filter: (bbalance > 0)
Rows Removed by Filter: 8
Planning Time: 0.197 ms
Execution Time: 395.363 ms
(11 rows)и
HashAggregate (cost=601394.00..601394.10 rows=10 width=12) (actual time=731.987..731.989 rows=8 loops=1)
Group Key: pgbench_accounts.bid
-> Seq Scan on pgbench_accounts (cost=0.00..598894.00 rows=500000 width=8) (actual time=0.041..579.264 rows=800000 loops=1)
Filter: (SubPlan 1)
Rows Removed by Filter: 200000
SubPlan 1
-> Materialize (cost=0.00..1.14 rows=2 width=4) (actual time=0.000..0.000 rows=2 loops=1000000)
-> Seq Scan on pgbench_branches (cost=0.00..1.12 rows=2 width=4) (actual time=0.010..0.012 rows=2 loops=1)
Filter: (bbalance > 0)
Rows Removed by Filter: 8
Planning Time: 0.203 ms
Execution Time: 732.142 ms
(12 rows)Хотя NOT EXISTS и LEFT JOIN создают тот же план выполнения без подплана, как показано ниже:
HashAggregate (cost=41245.15..41245.25 rows=10 width=12) (actual time=500.193..500.195 rows=8 loops=1)
Group Key: a.bid
-> Hash Anti Join (cost=1.15..37245.15 rows=800000 width=8) (actual time=0.041..344.845 rows=800000 loops=1)
Hash Cond: (a.bid = b.bid)
-> Seq Scan on pgbench_accounts a (cost=0.00..26394.00 rows=1000000 width=8) (actual time=0.013..110.645 rows=1000000 loops=1)
-> Hash (cost=1.12..1.12 rows=2 width=4) (actual time=0.018..0.018 rows=2 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on pgbench_branches b (cost=0.00..1.12 rows=2 width=4) (actual time=0.011..0.012 rows=2 loops=1)
Filter: (bbalance > 0)
Rows Removed by Filter: 8
Planning Time: 0.248 ms
Execution Time: 500.266 ms
(12 rows)Эти прямые хэш (анти) объединения между таблицами - самый разумный способ ответить на запрос. Таким образом, это является веской причиной для рекомендации синтаксиса EXISTS или синтаксиса JOIN. Таким образом, общее правило в пользу EXISTS / JOINs остается в силе.
Но подождите! Видим ли мы лучшее время выполнения с предложением NOT IN даже с подпланом? Да. PostgreSQL провел отличную оптимизацию, тем самым подготовив хэш подплана NOT (hashed SubPlan 1) . Таким образом, PostgreSQL лучше понимает, как обращаться с предложением IN, что является логическим способом мышления, поскольку многие люди склонны писать с предложением IN. Но у нас очень мало строк (две), возвращаемых подпланом. То же самое происходит, даже если подзапрос возвращает несколько сотен строк.
Но что, если подзапросом возвращено большое количество строк (несколько сотен тысяч строк)? Давайте попробуем простой пример:
CREATE TABLE t1 AS
SELECT * FROM generate_series(0, 500000) id;
CREATE TABLE t2 AS
SELECT (random() * 4000000)::integer id
FROM generate_series(0, 4000000);
ANALYZE t1;
ANALYZE t2;
EXPLAIN SELECT id
FROM t1
WHERE id NOT IN (SELECT id FROM t2);В этом случае план выполнения:
Seq Scan on t1 (cost=0.00..2583268004.52 rows=250000 width=4)
Filter: (NOT (SubPlan 1))
SubPlan 1
-> Materialize (cost=0.00..9333.01 rows=400001 width=4)
-> Seq Scan on t2 (cost=0.00..5770.01 rows=400001 width=4)
(5 rows)В этом случае план выполнения переключается на материализацию результата подплана, и расчетная стоимость возрастает до 25831564501.02! (При настройках по умолчанию в PostgreSQL, если количество строк из t2 меньше, чем приблизительно 100 КБ, используется хэшированный подплан, как мы уже обсуждали.)
Это приведет к существенному снижению производительности. Таким образом, предложение IN прекрасно работает, если подплан выбирает меньшее количество строк.
Подвох здесь в том, что когда происходит разработка, в таблицах будет меньше строк, и они будут работать по-разному с увеличением количества строк, так как план выполнения смещается и может привести к большим проблемам с производительностью в реальном производстве.
Есть ли еще сложности, о которых мы должны знать?
Да, могут быть преобразования типов данных, когда мы пишем запрос другим способом.
Например, утверждение типа:
Shell
EXPLAIN ANALYZE SELECT * FROM emp WHERE gen = ANY(ARRAY['M','F']);
1
EXPLAIN ANALYZE SELECT * FROM emp WHERE gen = ANY(ARRAY['M','F']);приводит к неявному преобразованию типов данных значений полей в текст.
Seq Scan on emp (cost=0.00..1.04 rows=2 width=43) (actual time=0.023..0.026 rows=3 loops=1)
Filter: ((gen)::text = ANY ('{M,F}'::text[]))Обратите внимание на преобразование типа данных: ( gen ) :: text . В большой таблице этот тип преобразования будет иметь дополнительные издержки, тогда как PostgreSQL лучше справляется с предложением IN.
EXPLAIN ANALYZE SELECT * FROM emp WHERE gen IN ('M','F');
Seq Scan on emp (cost=0.00..1.04 rows=3 width=43) (actual time=0.030..0.034 rows=3 loops=1)
Filter: (gen = ANY ('{M,F}'::bpchar[]))Несмотря на то, что предложение IN преобразуется в предложение ANY, преобразование типа данных поля «gen» не выполняется. И указанные значения 'M', 'F' конвертируются в bpchar, который является внутренним эквивалентом CHAR.
Резюме
При написании этого поста я намерен не отдавать предпочтение какому-либо конкретному способу написания запроса, а пролить некоторый свет на то, где что-то может пойти не так и что следует учитывать.
В общем, я привык предлагать разработчикам, что ключ к написанию хорошего оператора SQL - следовать пошаговому процессу.
- Сначала составьте список таблиц, из которых должны быть получены данные.
- Затем подумайте, как присоединиться к этим таблицам.
- Подумайте, как сделать так, чтобы минимальные записи участвовали в условии соединения.
Старайтесь не думать от «Как разбить логику» на подзапросы.
Никогда не предполагайте, что запрос работает хорошо с небольшим количеством данных в таблице.
Используйте план EXPLAIN, чтобы понять, что происходит в фоновом режиме.
В общем случае EXISTS и прямое соединение таблиц часто приводят к хорошим результатам. PostgreSQL во многих случаях оптимизирует предложение IN для хешированного подплана. «IN» может привести к лучшему плану и выполнению в некоторых определенных ситуациях. Опять же, все зависит от того, как запрос переписан / преобразован внутри. Для лучшей оптимизации стоит потратить время на переписывание запросов.
