クエリチューニング: GROUP BY から LATERAL への書き換え

「ユニークキーの GROUP BY」 を 「LATERAL」 に書き換えることで、クエリを性能改善できる可能性があります。
ここでは、非常にシンプルな書き換えの例を示します。

テーブルの準備

まずは、以下のような「部署」、「書籍」、「部署ごとの書籍在庫数」を管理する3つのテーブルを準備します。
なお、「書籍」テーブルは、データベースの状況をイメージしやすいように用意しているだけで、以降のクエリ書き換えでは特に使いません。

-- 部署のIDと名前を管理するテーブル department を作成
CREATE TABLE department (dept_id INTEGER PRIMARY KEY, name TEXT);

-- 部署の情報を5件登録
COPY department (dept_id, name) FROM stdin;
1	総務部
2	開発部
3	財務部
4	企画部
5	購買部
\.

-- 書籍のIDと名前を管理するテーブル book を作成
CREATE TABLE book (book_id INTEGER PRIMARY KEY, name TEXT);

-- 書籍の情報を6件登録
COPY book (book_id, name) FROM stdin;
1	AAA
2	BBB
3	CCC
4	DDD
5	EEE
6	FFF
\.

-- 部署ごとの書籍の在庫数を管理するテーブル book を作成
CREATE TABLE book_stock (
    dept_id INTEGER REFERENCES department(dept_id),
    book_id INTEGER REFERENCES book(book_id),
    stock_num INTEGER,
    PRIMARY KEY(dept_id, book_id)
);

-- 書籍の在庫情報を20件登録 (総務部2件、開発部4件、財務部4件、企画部6件、購買部4件)
COPY book_stock (dept_id, book_id, stock_num) FROM stdin;
1	2	7
1	3	3
2	5	2
2	4	7
2	3	3
2	2	8
3	4	10
3	2	10
3	1	1
3	6	4
4	1	5
4	2	7
4	3	10
4	4	10
4	5	5
4	6	4
5	3	6
5	5	5
5	2	8
5	1	10
\.

クエリの書き換え例

ここでは、「部署ごとの総在庫数」を取得するクエリを取り上げます。
このクエリは、部署のIDをキーに GROUP BY で実現するのが一般的だと思います。

SELECT
    d.name 部署名,
    sum(bs.stock_num) 総在庫数
FROM
    department d,
    book_stock bs
WHERE
    d.dept_id = bs.dept_id
GROUP BY
    d.dept_id;

この「ユニークキーの GROUP BY」 は、以下のような 「LATERAL」 に書き換えられます。

SELECT
    d.name 部署名,
    hoge.sum 総在庫数
FROM
    department d,
    LATERAL (
        SELECT sum(bs.stock_num) sum
        FROM book_stock bs
        WHERE d.dept_id = bs.dept_id
    ) hoge;

データ件数が少ないときは、GROUP BY でも LATERAL でも性能に大差はありません。
しかし、データ件数 (特に LATERAL 内でアクセスするテーブルのデータ件数) が多くなると、
この書き換えにより大幅に性能を改善できる可能性があります。