まだ話してない大事なポイントが一つあるんだ、それはウィンドウ関数付きクエリのパフォーマンス。どんなにイケてるクエリでも、最適化を考えないとカメみたいに遅くなっちゃう。今日はまさにその話をしよう！
ウィンドウ関数ってめっちゃ柔軟でパワフル。でもその柔軟さは、ギフトであると同時にパフォーマンスの脅威でもある。PostgreSQLは「魔法」じゃないから、データ処理にはリソースが必要。でっかいテーブルでウィンドウ関数を使うと、クエリがまるでその場でマラソンしてるみたいになることもあるよ。
最適化するとこんなメリットがある：
- 大量データを扱うクエリが速くなる。
- データベースへの負荷を最小限にできる。
- クエリがサーバー（と、同じDBを使ってる同僚たち）にやさしくなる。
じゃあ、どうやったらクエリがレースカーみたいに速くなるか、一緒に見ていこう！
ウィンドウ関数の基本動作
最適化する前に、何がクエリを遅くしてるのか知っておこう。PostgreSQLはウィンドウ関数をこんな感じで処理する：
OVER()の中にORDER BYがあれば、データをソートする。- 指定されたウィンドウフレームやグループごとに各行を処理する。
- 各行ごとに結果を返す。
例えば、salesテーブルに1,000万行あるとしよう。クエリでフィルタを使わなければ、PostgreSQLはその全行を処理することになる。これ、もはやマラソンじゃなくて、終わりのないランニングマシンだよ。
ウィンドウ関数を速くするには？
- ソートを速くするためのインデックス利用
ほとんどのウィンドウ関数はOVER()の中でORDER BYを使って行の順序を制御する。つまり、PostgreSQLはウィンドウ関数を実行する前にデータをソートしなきゃいけない。
ORDER BYで使うカラム（または複数カラム）にインデックスがあれば、PostgreSQLはこのソートをかなり速くできる。
例
CREATE INDEX idx_sales_date ON sales (sale_date);
これで、sale_dateでソートするクエリを書くと、インデックスが効いてくる：
SELECT
sale_date,
product_id,
SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;
sale_dateにインデックスがなければ、毎回クエリ実行時に重いソートが発生して、PostgreSQLが「どうやって速く並べ替えよう…」ってパニックになる。
WHEREでフィルタを使う
データ量を絞るのは最適化のキーテクニック。全部の1,000万行を処理する必要がないなら、例えば直近1年だけでいいなら、WHEREで範囲を絞ろう！
例
SELECT
sale_date,
product_id,
SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales
WHERE sale_date >= '2023-01-01';
これは、汚れた水をふるいにかけて、必要な情報だけ残す感じだね。
- 適切なウィンドウフレームの選択
SUM()みたいな集約系ウィンドウ関数を使うときは、正しいウィンドウフレームを選ぶのが大事。デフォルトのフレーム（RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW）だと、PostgreSQLは現在行までの全行を含めちゃう。これは大きなテーブルだと非効率。
例：ROWSを使う
もし直前の数行だけ含めたいなら、ROWSで明示しよう：
SELECT
sale_date,
product_id,
SUM(amount) OVER (
PARTITION BY product_id
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS rolling_sum
FROM sales;
この場合、PostgreSQLは各行ごとに3行（2つ前＋現在）だけ処理する。デフォルトで何百行も処理するよりずっと効率的。
- ウィンドウ関数の数を最小限に
ウィンドウ関数はPostgreSQLが個別に処理する。複数使うと、それぞれでソートが発生することも。でも、ウィンドウのパラメータ（PARTITION BYやORDER BY）が同じなら、PostgreSQLはもっと効率的にやってくれる。
例：同じウィンドウでの最適化
SELECT
product_id,
sale_date,
SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date) AS row_num
FROM sales;
両方の関数（SUM()とROW_NUMBER()）が同じフレームを使ってる。PostgreSQLは1回だけソートすればOK。これ、めっちゃイイ。
- テーブルのパーティショニング
テーブルがデカすぎるなら、物理的に小さく分けるのもアリ。PostgreSQLはパーティショニングテーブルを作れるから、データを別セグメントに分けられる。これで処理がかなり速くなることも。
パーティショニングテーブル作成例
CREATE TABLE sales_partitioned (
sale_date DATE NOT NULL,
product_id INT NOT NULL,
amount NUMERIC NOT NULL
) PARTITION BY RANGE (sale_date);
その後、例えば年ごとにパーティションを作る：
CREATE TABLE sales_2022 PARTITION OF sales_partitioned
FOR VALUES FROM ('2022-01-01') TO ('2022-12-31');
CREATE TABLE sales_2023 PARTITION OF sales_partitioned
FOR VALUES FROM ('2023-01-01') TO ('2023-12-31');これでWHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31'を使うと、PostgreSQLは自動で該当パーティションだけ見に行く。
パーティショニングの詳細はコースの後半でまたやるからお楽しみに :P
- 不要なデータを避ける（
SELECTは必要なものだけ）
関数や結果に必要なカラムだけ選ぼう。ウィンドウ関数にproduct_id、sale_date、amountだけ必要なら、顧客のバイオデータとか全部引っ張ってくる必要はないよ。
「節約」クエリの例
SELECT
product_id,
sale_date,
SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;
データが少なければ、PostgreSQLの仕事も減る。
- マテリアライズ（
MATERIALIZED VIEW）の利用
同じウィンドウ関数の計算を何度もやるなら、結果をマテリアライズドビューに保存しよう。Materialized Viewはディスクにデータを保存するから、重いクエリを毎回やらなくて済む。
マテリアライズドビュー作成例
CREATE MATERIALIZED VIEW sales_running_total AS
SELECT
product_id,
sale_date,
SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;
これで、データは簡単にクエリできる：
SELECT * FROM sales_running_total WHERE product_id = 10;
EXPLAINやEXPLAIN ANALYZEでクエリプランをチェック
SQLの他の場面と同じく、EXPLAINやEXPLAIN ANALYZEを使えば、PostgreSQLがクエリをどう実行してるか、どこがボトルネックか分かるよ。
クエリアナライズ例
EXPLAIN ANALYZE
SELECT
product_id,
sale_date,
SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date) AS running_total
FROM sales;
このツールで、PostgreSQLがどこに一番時間を使ってるか分かるから、ボトルネックを最適化できる。
ウィンドウ関数はデータ分析の強力な武器だけど、使い方には注意が必要。速さが欲しい？インデックスを選んで、フィルタを追加して、パーティションも活用、マテリアライズドビューも遠慮なく使おう。PostgreSQLは、ちゃんと考えて使ってくれる人が大好きだよ！
GO TO FULL VERSION