プロシージャを作るとき、それがDBの「ハート」になって色々な操作をやることが多いよね。でも、同時に「ボトルネック」にもなりがち。特にこんな場合は：
- 無駄な操作をしてる（例えば、同じデータに何度もアクセスしてるとか）。
- インデックスをうまく使えてない。
- 1つのトランザクションで操作が多すぎる。
ある賢いエンジニアが言ってたけど、「ダメなコードを速くするのは、怠け者の友達にもっと速く走れって言うようなもんだ」って。だからプロシージャの最適化は、単なるスピードアップじゃなくて、土台から良くすることなんだ！
トランザクション内の操作数を最小限にする
PostgreSQLのトランザクションは、操作ごとにオーバーヘッドが発生する。トランザクションが大きいほどロックも長くなって、他のユーザーもブロックされやすい。これを減らすには：
- 1つのトランザクションに操作を詰め込みすぎない。
EXCEPTION ENDを使って、ローカルに変更を制限する。部分的なロールバックが必要なときに便利。- 大きなトランザクションは、アプリのロジックが許せば小さく分割しよう。
例：大量データの挿入を「バッチ」に分ける：
-- 例：段階的コミット付きバッチロード用プロシージャ
CREATE PROCEDURE batch_load()
LANGUAGE plpgsql
AS $$
DECLARE
r RECORD;
batch_cnt INT := 0;
BEGIN
FOR r IN SELECT * FROM staging_table LOOP
BEGIN
INSERT INTO target_table (col1, col2) VALUES (r.col1, r.col2);
batch_cnt := batch_cnt + 1;
EXCEPTION
WHEN OTHERS THEN
-- エラーをログする；この要素の変更はロールバックされる
INSERT INTO load_errors(msg) VALUES (SQLERRM);
END;
IF batch_cnt >= 1000 THEN
COMMIT; -- 1000件ごとにコミット
batch_cnt := 0;
END IF;
END LOOP;
COMMIT; -- 最後のコミット
END;
$$;
ヒント：それぞれのCOMMITで変更が確定するから、トランザクション分割でデータの整合性が崩れないか事前に確認しよう。
インデックスでクエリを高速化する
例えば、ordersテーブルに100万件あって、customer_idでよく検索する場合。インデックスがなければ全件スキャンになる：
CREATE INDEX idx_customer_id ON orders(customer_id);
これで、こんなクエリ：
SELECT * FROM orders WHERE customer_id = 42;
が、全テーブルスキャンせずにめっちゃ速くなるよ。
重要：プロシージャ作るときは、使うカラムがインデックスに含まれてるか確認しよう。特にフィルタ、ソート、JOIN条件で使うやつ。
EXPLAIN ANALYZEでパフォーマンス分析
EXPLAINはクエリの実行プラン（PostgreSQLがどうやって実行するか）を見せてくれるし、ANALYZEを付けると実際の実行統計（どれくらい時間かかったか等）も出る。例えば：
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;
プロシージャ内でどう使う？
複雑なクエリは、EXPLAIN ANALYZEで個別に実行して「分解」してみよう：
DO $$
BEGIN
RAISE NOTICE 'クエリプラン: %',
(
SELECT query_plan
FROM pg_stat_statements
WHERE query = 'SELECT * FROM orders WHERE customer_id = 42'
);
END $$;
分析と改善の例
元のプロシージャ（遅いバージョン）:
CREATE OR REPLACE FUNCTION update_total_sales()
RETURNS VOID AS $$
BEGIN
UPDATE sales
SET total = (
SELECT SUM(amount)
FROM orders
WHERE orders.sales_id = sales.id
);
END $$ LANGUAGE plpgsql;
何が起きてる？ salesテーブルの各行ごとにSUM(amount)のサブクエリが実行されて、めっちゃ操作が多くて遅い。
改善バージョン：
CREATE OR REPLACE FUNCTION update_total_sales()
RETURNS VOID AS $$
BEGIN
UPDATE sales as s
SET total = o.total_amount
FROM (
SELECT sales_id, SUM(amount) as total_amount
FROM orders
GROUP BY sales_id
) o
WHERE o.sales_id = s.id;
END $$ LANGUAGE plpgsql;
これならSUMのサブクエリは1回だけで、全部まとめて更新できる。
エラー時のデータロールバック
プロシージャ内で何か失敗したら、トランザクションの一部だけロールバックできる。例えば：
BEGIN
-- データ挿入
INSERT INTO inventory(product_id, quantity) VALUES (1, -5);
EXCEPTION
WHEN OTHERS THEN
-- このブロックは内部savepointへのロールバックと同じ！
RAISE WARNING 'データ更新時のエラー: %', SQLERRM;
END;
実践：堅牢な注文処理プロシージャの実装
例えば、注文を処理するタスクがあるとする。途中でエラー（在庫不足など）が起きたら、注文はキャンセルされてエラーがログに残る。
CREATE OR REPLACE PROCEDURE process_order(p_order_id INT)
LANGUAGE plpgsql
AS $$
DECLARE
v_in_stock INT;
BEGIN
-- 在庫チェック
SELECT stock INTO v_in_stock FROM products WHERE id = p_order_id;
BEGIN
IF v_in_stock < 1 THEN
RAISE EXCEPTION '在庫がありません';
END IF;
UPDATE products SET stock = stock - 1 WHERE id = p_order_id;
-- ... 他の操作
EXCEPTION
WHEN OTHERS THEN
-- このブロック内の変更は全部ロールバック！
INSERT INTO order_logs(order_id, log_message)
VALUES (p_order_id, '処理エラー: ' || SQLERRM);
RAISE NOTICE '注文処理エラー: %', SQLERRM;
END;
-- エラーがなければこの後のコードが続く
-- ログに「注文処理成功」とかも書ける
END;
$$;- エラーがあっても注文は処理されず、
order_logsテーブルにログが残る。 - エラー時は内部savepointが効いて、全体の文脈は失われない。
プロシージャ最適化＆堅牢化の基本ルール
- プロシージャ内のクエリにはインデックスを使おう。
- 大きな操作はバッチ（小分け）にして段階的に処理しよう。
- エラーはちゃんとログしよう ― 大量操作用のエラーログテーブルを作ると便利。
- 「部分的」ロールバックには
EXCEPTION付きのネストブロックだけ使おう。 - PL/pgSQL内で
ROLLBACK TO SAVEPOINTは使わない ― シンタックスエラーになるよ。 - プロシージャではCOMMIT/SAVEPOINTはautocommitモードの接続時だけ使おう！
- 重いクエリの実行プラン（
EXPLAIN ANALYZE）は、プロシージャに組み込む前に外で分析しよう。
GO TO FULL VERSION