大量データ処理のための関数最適化

PostgreSQLで関数の最適化って話になると、だいたい2つの重要なポイントがあるんだ：インデックス化とパーティショニング。この2つのテクニックで、余計な計算を減らして、データに「ピンポイント」でアクセスできるようになるから、大量データでも速く処理できるんだ。詳しく見ていこう！

データベースのインデックスは、本の索引と同じ感じ。欲しい情報を本で探すとき、全部のページを順番に読むんじゃなくて、索引を開いてテーマを見つけて、すぐそのページに飛ぶでしょ？PostgreSQLのインデックスもまさにそんな感じ。

インデックスの作成

インデックスはCREATE INDEXコマンドで作るよ。シンプルな例を見てみよう：

-- usersテーブルのidカラムにインデックスを作って検索を速くする
CREATE INDEX idx_users_id ON users (id);

これで、例えばこんなクエリを実行すると：

SELECT * FROM users WHERE id = 42;

PostgreSQLは作ったインデックスを使って、必要な行をサクッと見つけてくれるよ。

例：インデックスを使った関数の最適化

例えば、ordersテーブルからユーザーごとの注文データを取ってくる関数があるとする：

CREATE OR REPLACE FUNCTION get_user_orders(user_id INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE user_id = user_id;
END; 
$$ LANGUAGE plpgsql;

ordersテーブルに何百万行もあったら、この関数は遅くなっちゃう。どうする？user_idにインデックスを作ろう：

CREATE INDEX idx_orders_user_id ON orders (user_id);

これで関数内のクエリがめっちゃ速くなる。PostgreSQLがインデックスを使って行を探してくれるからね。

インデックスの種類

PostgreSQLはいろんなタイプのインデックスをサポートしてるけど、よく使うのはB-TREEとGIN。ざっくり比較すると：

もっとインデックスについて深く知りたかったら、PostgreSQL公式ドキュメントを見てみて！

データのパーティショニング

インデックスが検索を速くするものなら、パーティショニングはテーブルをもっと小さい「かたまり」（パーティション）に分ける方法。1つのテーブルに大量のデータがあるときに便利だよ。

例えば、ordersテーブルがあって、過去10年分の注文が全部入ってるとする。もし「先月の注文だけ欲しい」ってクエリを投げても、PostgreSQLは全部のデータを見に行っちゃうから重い。パーティショニングなら、例えば年ごとにデータを分けておけるんだ。

パーティションテーブルの作成

パーティションテーブルはこんな感じで作れるよ：

-- 親パーティションとしてordersテーブルを作る
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    user_id INT NOT NULL
) PARTITION BY RANGE (order_date);

-- 各年ごとの子テーブルを作る
CREATE TABLE orders_2023 PARTITION OF orders FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE orders_2022 PARTITION OF orders FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

これで、例えばこんなクエリを実行すると：

SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2023-02-01';

PostgreSQLはorders_2023だけを見ればいいってすぐ分かるから、全体をチェックしなくて済むよ。

関数でのパーティショニング利用

例えば、特定の年の注文を取ってくる関数があるとする。パーティショニングのおかげで、関数内のクエリも速くなる。PostgreSQLが必要な子テーブルだけ見てくれるからね。

CREATE OR REPLACE FUNCTION get_orders_by_year(year INT)
RETURNS TABLE(order_id INT, order_date DATE) AS $$
BEGIN
    RETURN QUERY 
    SELECT id, order_date 
    FROM orders 
    WHERE order_date >= make_date(year, 1, 1) 
      AND order_date < make_date(year + 1, 1, 1);
END;
$$ LANGUAGE plpgsql;

実践ケース

1. インデックスのケース

文字列検索：商品テーブルがあって、商品名でよく検索するなら、nameカラムにインデックスを作ろう：

CREATE INDEX idx_products_name ON products (name);

ソートの高速化：クエリで日付順ソートをよく使うなら、インデックスを作ろう：

CREATE INDEX idx_orders_date ON orders (order_date);

2. パーティショニングのケース

履歴データ：テーブルにタイムスタンプ付きデータがあるなら、日・月・年ごとにパーティション分けするとクエリがかなり速くなるよ。

地理データ：国ごとのデータがあるなら、国ごとにパーティションを作るのもアリ。

ありがちなミスとその対策

インデックスを作りすぎるのはよくあるミス。インサートやアップデートのたびにPostgreSQLが全部のインデックスを更新しなきゃいけなくなるから、逆にパフォーマンスが落ちるんだ。アドバイス：本当に条件やソートでよく使うカラムだけにインデックスを作ろう。

もう一つのよくあるミスは、パーティショニングの切り方がイマイチなこと。例えば日ごとに細かく分けすぎると、管理コストが増えて逆効果になることもあるよ。