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

「ユニークキーの 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 内でアクセスするテーブルのデータ件数) が多くなると、
この書き換えにより大幅に性能を改善できる可能性があります。