はじめに
SQLでは複数のテーブルからデータを取得する場合が多いです。
その際、結合を使うのが定番ですがコスト面ではどうなのでしょうか？
それを確認するために、同じ結果を返す結合クエリとサブクエリを書いて実行計画を比較してみたいと思います。
実行計画の確認方法がわからない方は「PostgreSQLで実行計画を確認してみる」を参考にしてください🙌
準備
まずクエリを叩く為のテーブルを準備します☝️
テーブル作成
-- 部署テーブル
CREATE TABLE departments (
id SERIAL PRIMARY KEY,
name VARCHAR(50)
);
-- 従業員テーブル
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
department_id INT,
name VARCHAR(50),
salary INT
);
テーブル作成
INSERT INTO departments (name) VALUES
('営業'),
('開発'),
('人事');
INSERT INTO employees (department_id, name, salary) VALUES
(1, '佐藤', 300),
(1, '鈴木', 400),
(1, '高橋', 500),
(2, '田中', 600),
(2, '伊藤', 700),
(2, '渡辺', 800),
(3, '山本', 300),
(3, '中村', 350),
(3, '小林', 400);
結合クエリとサブクエリを使用して同じ結果を取得する
全従業員の中から平均給与より高い人を探します。
結合
SELECT e.name, e.salary
FROM employees e
JOIN departments d
ON e.department_id = d.id
WHERE e.salary > (
SELECT AVG(salary) FROM employees
);
サブクエリ
SELECT name, salary
FROM employees
WHERE salary > (
SELECT AVG(salary) FROM employees
);
実行結果（結合クエリ / サブクエリ）
実行結果はどちらも同じなります。
name | salary
------+--------
高橋 | 500
田中 | 600
伊藤 | 700
渡辺 | 800
実行計画
結合
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Hash Join (cost=38.54..56.63 rows=170 width=122) (actual time=0.864..0.870 rows=4 loops=1)
Hash Cond: (e.department_id = d.id)
InitPlan 1 (returns $0)
-> Aggregate (cost=16.38..16.39 rows=1 width=32) (actual time=0.075..0.075 rows=1 loops=1)
-> Seq Scan on employees (cost=0.00..15.10 rows=510 width=4) (actual time=0.003..0.005 rows=9 loops=1)
-> Seq Scan on employees e (cost=0.00..17.65 rows=170 width=126) (actual time=0.475..0.478 rows=4 loops=1)
Filter: ((salary)::numeric > $0)
Rows Removed by Filter: 5
-> Hash (cost=15.40..15.40 rows=540 width=4) (actual time=0.141..0.142 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on departments d (cost=0.00..15.40 rows=540 width=4) (actual time=0.111..0.113 rows=3 loops=1)
Planning Time: 1.599 ms
Execution Time: 1.426 ms
(13 rows)
サブクエリ
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Seq Scan on employees (cost=16.39..34.04 rows=170 width=122) (actual time=0.417..0.420 rows=4 loops=1)
Filter: ((salary)::numeric > $0)
Rows Removed by Filter: 5
InitPlan 1 (returns $0)
-> Aggregate (cost=16.38..16.39 rows=1 width=32) (actual time=0.067..0.067 rows=1 loops=1)
-> Seq Scan on employees employees_1 (cost=0.00..15.10 rows=510 width=4) (actual time=0.011..0.012 rows=9 loops=1)
Planning Time: 1.073 ms
Execution Time: 1.831 ms
(8 rows)
今回の例では結合クエリが若干速いですね！
結合クエリとサブクエリを使用して同じ結果を取得する２
次は部署ごとに平均給与より高い人を探します。
結合
SELECT e.name, e.salary, e.department_id
FROM employees e
JOIN (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) avg_table
ON e.department_id = avg_table.department_id
WHERE e.salary > avg_table.avg_salary;
サブクエリ
SELECT name, salary, department_id
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM employees
WHERE department_id = e.department_id
);
実行結果（結合クエリ / サブクエリ）
実行結果はどちらも同じなります。
name | salary | department_id
------+--------+---------------
高橋 | 500 | 1
渡辺 | 800 | 2
小林 | 400 | 3
実行計画
結合
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=22.65..39.10 rows=170 width=126) (actual time=0.340..0.345 rows=3 loops=1)
Hash Cond: (e.department_id = employees.department_id)
Join Filter: ((e.salary)::numeric > (avg(employees.salary)))
Rows Removed by Join Filter: 6
-> Seq Scan on employees e (cost=0.00..15.10 rows=510 width=126) (actual time=0.192..0.193 rows=9 loops=1)
-> Hash (cost=20.15..20.15 rows=200 width=36) (actual time=0.112..0.113 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> HashAggregate (cost=17.65..20.15 rows=200 width=36) (actual time=0.107..0.110 rows=3 loops=1)
Group Key: employees.department_id
Batches: 1 Memory Usage: 40kB
-> Seq Scan on employees (cost=0.00..15.10 rows=510 width=8) (actual time=0.004..0.005 rows=9 loops=1)
Planning Time: 1.232 ms
Execution Time: 0.705 ms
(13 rows)
サブクエリ
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Seq Scan on employees e (cost=0.00..8379.10 rows=170 width=126) (actual time=0.094..0.134 rows=3 loops=1)
Filter: ((salary)::numeric > (SubPlan 1))
Rows Removed by Filter: 6
SubPlan 1
-> Aggregate (cost=16.39..16.40 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=9)
-> Seq Scan on employees (cost=0.00..16.38 rows=3 width=4) (actual time=0.002..0.003 rows=3 loops=9)
Filter: (department_id = e.department_id)
Rows Removed by Filter: 6
Planning Time: 0.305 ms
Execution Time: 0.237 ms
(10 rows)
今回はdepartmentsテーブルと結合しているクエリよりも、シンプルにemployeesテーブルだけを参照しているサブクエリの方が実行時間が短くなりました！
上記からわかること
実行計画を比較すると、結合が速いケースとサブクエリが速いケースの両方があることがわかります。
-
不要な結合を避けられる場合
サブクエリは必要なテーブルだけを参照するため、余計な処理がなく効率的です。 -
複数テーブルや集計を組み合わせる場合
結合を利用することで、一度のスキャンで効率的に処理できることがあります。
結論として、「どちらが速いか」はケースバイケースであり、取得したい情報・必要なテーブル数・データ量に応じて最適な書き方を選ぶことが重要です。
まとめ
「結合とサブクエリのどちらが速いか」は一概には言えず、
取得したい情報や必要なテーブル数、データ量に応じて最適解が変わる というのがポイントです。
開発時には「このクエリは本当に結合が必要なのか？」「サブクエリで済むのではないか？」を意識して書き分けることが重要だと思います。