PL/pgSQLはなぜ「ふつうのSQL」より遅いのか

同じことをするなら1本のSQLよりもPL/pgSQLほうがその仕組み上遅くなるということを、ソースコードを解析して説明しています。

Transcript

1. はなぜ「ふつうのSQL」 より遅いのか PL/pgSQL PostgreSQL 18 の内部実装から読み解く アンカンファレンス #57 PostgreSQL 1
2. はなぜSQLより遅いのか PL/pgSQL 自己紹介 名前: まぐろ（旧Twitter（現X）： @tameguro） 所属: 都内某SI勤務のSE PostgreSQLとの関わり: PostgreSQLの設計・導入・保守な どをしたりしなかったりしています 今年の4月に「PL/pgSQL完全ガイド」を出しました アンカンファレンス #57 PostgreSQL 2
3. はなぜSQLより遅いのか PL/pgSQL 皆さんPL/pgSQL使ってますか？ アンカンファレンス #57 PostgreSQL 3
4. はなぜSQLより遅いのか PL/pgSQL いろんなところで使われますが、Oracleからの移植で PL/SQLをPL/pgSQLに置き換えるときによく使われます アンカンファレンス #57 PostgreSQL 4
5. はなぜSQLより遅いのか PL/pgSQL でも移植はなかなか一筋縄ではいかないし、同じようなこ としているのにPL/SQLよりも遅くなった、ということもし ばしば むしろPL/SQLとは無関係に、同じことを通常のSQL1本で実 行するよりも遅くなります アンカンファレンス #57 PostgreSQL 5
6. はなぜSQLより遅いのか PL/pgSQL なぜ遅いのでしょう、速くすることはできないのでしょう か？ アンカンファレンス #57 PostgreSQL 6
7. はなぜSQLより遅いのか PL/pgSQL 今日話すこと 同じことをするなら、PL/pgSQLは通常のSQL1本より一般的に遅い。 …ということをPostgreSQL 18のソースコードを読みながら、内部構造から証 明します。 1. CREATE FUNCTION した時に何が起きるか 2. 初めて呼び出した時のコンパイルとキャッシュ 3. 実行エンジンはどう動いているか(ここに遅さの本質がある) 4. 式(expression)はどう評価されるか 5. 例外処理( EXCEPTION )の裏側 6. では、いつPL/pgSQLを使うべきか アンカンファレンス #57 PostgreSQL 7
8. はなぜSQLより遅いのか PL/pgSQL この資料の作り方について 本資料は、Claude Code に PostgreSQL 18( REL_18_STABLE )のソースコー ドを実際にダウンロード・解析させて作成しました src/pl/plpgsql/src/ ( pl_handler.c / pl_comp.c / pl_exec.c など) src/backend/utils/cache/funccache.c 実ソースの該当関数・該当行まで遡って裏付けを取っています（Claude Codeが） スライド中のコード引用はすべて実際のソースからの抜粋(要約・簡略化 した箇所は明記) アンカンファレンス #57 PostgreSQL 8
9. はなぜSQLより遅いのか PL/pgSQL 結論を先に 同じ結果を1本のSQLで書けるなら、 PL/pgSQLで書くより速い。 -- ふつうのSQL(1本のクエリ、セットベース) UPDATE accounts SET balance = balance + 100 WHERE id = 1; -- PL/pgSQLでも同じことはできるが… BEGIN UPDATE accounts SET balance = balance + 100 WHERE id = 1; END; なぜ後者の方が(一般に)遅くなるのか? この後、内部の仕組みを1つずつ見てい きます。 アンカンファレンス #57 PostgreSQL 9
10. はなぜSQLより遅いのか PL/pgSQL とは(おさらい) PL/pgSQL 標準搭載の手続き型言語 PostgreSQL で定義 変数・制御構文( IF / LOOP / FOR )・例外処理を備える 中身はどう動いているのか? コンパイル言語のように機械語になるのか? それともインタプリタなのか? CREATE FUNCTION ... LANGUAGE plpgsql アンカンファレンス #57 PostgreSQL 10
11. はなぜSQLより遅いのか PL/pgSQL 全体像 ① パース CREATE FUNCTION 時 構文解析のみ ② コンパイル + キャッシュ 初回呼び出し時 ASTを構築しメモリに保持 ③ 実行 呼び出しの都度 ツリーウォーク型 インタプリタ この3段階を、ソースコードを見ながら順に追っていきます。 アンカンファレンス #57 PostgreSQL 11
12. はなぜSQLより遅いのか PL/pgSQL フェーズ1 CREATE FUNCTION アンカンファレンス #57 PostgreSQL 時 12
13. はなぜSQLより遅いのか PL/pgSQL 時に呼ばれるもの CREATE FUNCTION src/pl/plpgsql/src/pl_handler.c plpgsql_validator(PG_FUNCTION_ARGS) の関数を作成すると、まずバリデータが呼ばれる バリデータは中で plpgsql_compile(fcinfo, forValidator=true) を呼ぶ ここでやっているのは構文解析だけ LANGUAGE plpgsql アンカンファレンス #57 PostgreSQL 13
14. はなぜSQLより遅いのか PL/pgSQL 構文解析だけ、とはどういうことか CREATE FUNCTION broken() RETURNS void AS $$ BEGIN SELECT * FROM this_table_does_not_exist; END; $$ LANGUAGE plpgsql; このテーブルは存在しない それでも CREATE FUNCTION は成功する 中の SELECT 文はただのクエリ文字列として保持されるだけで、 この時点ではテーブルの存在確認もSQLのプランニングも行われない エラーになるのは、実際に関数を実行し、このクエリが実行された瞬間 アンカンファレンス #57 PostgreSQL 14
15. はなぜSQLより遅いのか PL/pgSQL フェーズ2 初回呼び出し時のコンパイル アンカンファレンス #57 PostgreSQL 15
16. はなぜSQLより遅いのか PL/pgSQL 呼び出し時のエントリポイント src/pl/plpgsql/src/pl_handler.c plpgsql_call_handler(PG_FUNCTION_ARGS) 関数が実際に呼ばれると、 plpgsql_compile(fcinfo, forValidator=false) が呼 ばれる。 アンカンファレンス #57 PostgreSQL 16
17. はなぜSQLより遅いのか PL/pgSQL キャッシュの仕組み キャッシュキー = 関数OID + 引数の型 + トリガーコンテキスト 同じ関数でも多態的(polymorphic)なら型ごとに別エントリ ハッシュテーブルは TopMemoryContext → セッションが続く限り保持される 別セッション(新規接続)では最初からコンパイルし直し #define FUNCS_PER_USER 128 /* initial table size */ static HTAB *cfunc_hashtable = NULL; アンカンファレンス #57 PostgreSQL 17
18. はなぜSQLより遅いのか PL/pgSQL 回目以降はもっと速い: fn_extra 2 function = (PLpgSQL_function *) cached_function_compile(fcinfo, fcinfo->flinfo->fn_extra, /* ← ここ */ ...); fcinfo->flinfo->fn_extra = function; fcinfo->flinfo->fn_extra に直接コンパイル済みの構造体ポインタを保 存 同じクエリ内・同じプリペアド文での再呼び出しはハッシュ検索すら行わ ずキャッシュを再利用 キャッシュミス時 plpgsql_compile_callback が呼ばれ、フル解析を行う アンカンファレンス #57 PostgreSQL 18
19. はなぜSQLより遅いのか PL/pgSQL コンパイル本体: plpgsql_compile_callback pl_comp.c: plpgsql_compile_callback() で関数本体をパース できあがった抽象構文木（AST）を PLpgSQL_function 構造体に格納 関数専用のメモリコンテキスト( fn_cxt )に確保 成功したら CacheMemoryContext の下にぶら下げて長期保持 失敗時は自動的に破棄されリークしない設計 ここまでが「コンパイル」。この後は実行のたびに このASTを何度も辿ることになる。 pl_scanner.c / pl_gram.y (flex/bison) アンカンファレンス #57 PostgreSQL 19
20. はなぜSQLより遅いのか PL/pgSQL コンパイルの結果できあがるAST PLpgSQL_function コンパイル結果として得られるASTのルート action PLpgSQL_stmt_block BEGIN ... END ブロック body[0] PLpgSQL_stmt_execsql 1個のSQL⽂をラップするノード sqlstmt sqlstmt(⽂字列のまま保持・この時点では未パース) UPDATE accounts SET balance = balance + 100 WHERE id = 1; 埋め込みSQLはASTノードにならず、実⾏時(フェーズ3)に初めてSPI経由でパース・プランニングされる アンカンファレンス #57 PostgreSQL 20
21. はなぜSQLより遅いのか PL/pgSQL フェーズ3 実行 アンカンファレンス #57 PostgreSQL 21
22. はなぜSQLより遅いのか PL/pgSQL plpgsql_exec_function pl_exec.c: plpgsql_exec_function() 実行状態( PLpgSQL_execstate )をセットアップ 2. 呼び出し引数をローカル変数にコピー 3. 特殊変数 FOUND を false に初期化 1. 4. exec_toplevel_block(&estate, func->action) → ここから実際の文の実行が始まる を呼ぶ アンカンファレンス #57 PostgreSQL 22
23. はなぜSQLより遅いのか exec_stmts — PL/pgSQL 文リストを辿る pl_exec.c: exec_stmts() foreach(s, stmts) { PLpgSQL_stmt *stmt = (PLpgSQL_stmt *) lfirst(s); switch (stmt->cmd_type) { case PLPGSQL_STMT_ASSIGN: rc = exec_stmt_assign(estate, ...); break; case PLPGSQL_STMT_IF: rc = exec_stmt_if(estate, ...); break; ... アンカンファレンス #57 PostgreSQL 23
24. はなぜSQLより遅いのか PL/pgSQL exec_stmts — 文リストを辿る を foreach で走査し、 switch で分岐。約20種類の文タイプ (ASSIGN/IF/LOOP/FORI/EXECSQL/RAISE/OPEN/FETCH…)ごとに専用のハンドラ 関数へディスパッチする。 あまりにも力技 List アンカンファレンス #57 PostgreSQL 24
25. はなぜSQLより遅いのか PL/pgSQL これは「ツリーウォーク型インタプリタ」 コンパイルフェーズで作ったASTを、実行フェーズでそのまま辿る 機械語やバイトコードへの変換は一切行わない IF ・ LOOP ・ FOR なども、対応する exec_stmt_* 関数が 再帰的/反復的に exec_stmts を呼び出すことで実現している つまりPL/pgSQLは、素朴な意味でのインタプリタ言語。 このディスパッチは文を実行するたびに発生する。 ループの中なら、ループ回数だけ繰り返される。 ふつうのSQLエグゼキュータには、そもそもこの層が存在しない。 アンカンファレンス #57 PostgreSQL 25
26. はなぜSQLより遅いのか PL/pgSQL 式の評価 expression はどう動くのか アンカンファレンス #57 PostgreSQL 26
27. はなぜSQLより遅いのか PL/pgSQL PLpgSQL_expr の正体 x := a + 1; IF x > 10 THEN ... や x > 10 のような式は、生のSQLテキストのまま PLpgSQL_expr 構造体に保持されている つまりPL/pgSQL自身は式を評価する独自の計算エンジンを持たない 実際には、コアのSQLパーサ・プランナ・エグゼキュータに SPI(Server Programming Interface)経由で丸投げしている a + 1 アンカンファレンス #57 PostgreSQL 27
28. はなぜSQLより遅いのか PL/pgSQL とは SPI の略 バックエンド内部のCコードから、通常のクライアントSQLと同じパー サ・プランナ・エグゼキュータを呼び出すためのAPI PL/pgSQLに限らず、PL/Python・PL/Perlなど他の手続き言語や拡張機能(C 言語関数)からも使われる共通の仕組み PL/pgSQLの式評価もSQL文の実行も、結局はSPI経由でこのAPIを呼んで いる。 ここまで見てきた exec_eval_expr や exec_stmt_execsql の正体は、 「SPIを介してコアのSQLエンジンを毎回呼び出すラッパー」である。 Server Programming Interface アンカンファレンス #57 PostgreSQL 28
29. はなぜSQLより遅いのか PL/pgSQL exec_eval_expr の流れ pl_exec.c: exec_eval_expr() if (expr->plan == NULL) exec_prepare_plan(estate, expr, CURSOR_OPT_PARALLEL_OK); if (exec_eval_simple_expr(estate, expr, &result, isNull, rettype, rettypmod)) return result; /* Else do it the hard way via exec_run_select */ rc = exec_run_select(estate, expr, 0, NULL); アンカンファレンス #57 PostgreSQL 29
30. はなぜSQLより遅いのか PL/pgSQL exec_eval_expr の流れ 初回: exec_prepare_plan → SPI_prepare_extended でプラン作成 まず「簡単な式」として高速評価を試み、ダメなら通常のSELECT実行に 回す アンカンファレンス #57 PostgreSQL 30
31. はなぜSQLより遅いのか PL/pgSQL つまり x := a + 1; は… 内部的には、こういうクエリを実行しているのと(ほぼ)同じ。 SELECT a + 1; コアのSQLパーサ・プランナが呼ばれる PostgreSQL本体が持つ型システム・演算子解決・関数呼び出しの 仕組みをそのまま利用できる(独自実装しなくて済む) 一方で、素朴にやると式1つ評価するたびに毎回SQL実行のフルコースが走 ってしまう アンカンファレンス #57 PostgreSQL 31
32. はなぜSQLより遅いのか PL/pgSQL simple expression 最適化 pl_exec.c: exec_eval_simple_expr() 単純なスカラー式(集約もSRFも副問い合わせも伴わないもの)は SPIのポータル/スナップショット管理を丸ごと迂回 キャッシュ済みプランから直接 ExecEvalExpr() を呼ぶ 通常のSPI実行に比べて大幅に軽量 /* * exec_eval_simple_expr - Evaluate a simple expression * returning a Datum by directly calling ExecEvalExpr(). */ アンカンファレンス #57 PostgreSQL 32
33. はなぜSQLより遅いのか PL/pgSQL simple expression のキャッシュと再プラン 判定結果・プランは式ごとに保持 expr->expr_simple_plan (キャッシュされたプラン) expr->expr_simple_state (実行用の ExprState ) スキーマ変更などでプランが無効化されたら CachedPlanIsSimplyValid() が検知し、自動的に再プラン 一度「simple」と判定された式が「非simple」に変わることはない (逆も同様)という前提で最適化されている アンカンファレンス #57 PostgreSQL 33
34. はなぜSQLより遅いのか PL/pgSQL 最速の経路にも、消えないコストがある exec_eval_simple_expr() は毎回呼ばれるたびに、こういう処理を行う。 LocalTransactionId curlxid = MyProc->vxid.lxid; ... EnsurePortalSnapshotExists(); ... if (likely(CachedPlanIsSimplyValid(...))) expr->expr_simple_plan_lxid = curlxid; ... paramLI->parserSetupArg = expr; econtext->ecxt_param_list_info = paramLI; アンカンファレンス #57 PostgreSQL 34
35. はなぜSQLより遅いのか PL/pgSQL 最速の経路にも、消えないコストがある プラン自体はキャッシュされ再利用されるが、 有効性チェック・パラメータリストの張り替えは式評価のたびに毎回発生 1本のSQLならN行分をまとめて1回のexecutor起動で処理するところを、 PL/pgSQLは式1つにつき1回、これを繰り返す アンカンファレンス #57 PostgreSQL 35
36. はなぜSQLより遅いのか PL/pgSQL 文もすべてSPI任せ SQL 動的 EXECUTE — これらもすべて SPI経由でコアのSQLエグゼキュータに委譲される。 PL/pgSQLは「制御構文のインタプリタ」ではあるが、 SQLエンジンとしては何も持っていない。 SQLに関することは常にPostgreSQL本体に投げている。 SELECT INTO / INSERT / UPDATE / DELETE / アンカンファレンス #57 PostgreSQL 36
37. はなぜSQLより遅いのか PL/pgSQL FOR ループは行を1件ずつ処理する pl_exec.c: exec_for_query() /* Fetch the initial tuple(s). */ SPI_cursor_fetch(portal, true, prefetch_ok ? 10 : 1); ... while (n > 0) { for (i = 0; i < n; i++) { exec_move_row(estate, var, tuptab->vals[i], tuptab->tupdesc); rc = exec_stmts(estate, stmt->body); /* ← 1行ごとにインタプリタへ */ } SPI_cursor_fetch(portal, true, prefetch_ok ? 50 : 1); /* 続きを取得 */ } アンカンファレンス #57 PostgreSQL 37
38. はなぜSQLより遅いのか PL/pgSQL FOR ループは行を1件ずつ処理する はカーソルからバッチでフェッチしつつ、 1行ごとにループ本体(exec_stmts)を呼び直す。 セットベースの1本のSQLがエグゼキュータの中で完結するのに対し、 PL/pgSQLは行数分だけインタプリタとSQLエグゼキュータを往復する。 FOR rec IN SELECT ... LOOP アンカンファレンス #57 PostgreSQL 38
39. はなぜSQLより遅いのか PL/pgSQL 比較: 「同じ更新」を2通りで書くと -- ふつうのSQL: エグゼキュータが内部で全行を一括処理 UPDATE accounts SET balance = balance * 1.01; -- PL/pgSQL: 行数ぶんだけ interpreter ⇄ SPI を往復 FOR rec IN SELECT id FROM accounts LOOP UPDATE accounts SET balance = balance * 1.01 WHERE id = rec.id; END LOOP; 後者は行ごとに: カーソルフェッチ → exec_stmts のswitchディスパッチ → exec_eval_expr / exec_stmt_execsql のパラメータリスト構築 → SPI経由のUPDATE実行、を繰り返す。 これまで見てきた各レイヤーのオーバーヘッドが、行数分だけ積み重なる。 アンカンファレンス #57 PostgreSQL 39
40. はなぜSQLより遅いのか PL/pgSQL 例外処理 ブロックの裏側 EXCEPTION アンカンファレンス #57 PostgreSQL 40
41. はなぜSQLより遅いのか PL/pgSQL BEGIN...EXCEPTION...END はどう実装されているか BEGIN ... 本体 ... EXCEPTION WHEN division_by_zero THEN ... END; アンカンファレンス #57 PostgreSQL 41
42. はなぜSQLより遅いのか PL/pgSQL BEGIN...EXCEPTION...END pl_exec.c: exec_stmt_block() はどう実装されているか の中で: BeginInternalSubTransaction(NULL); PG_TRY(); { rc = exec_stmts(estate, block->body); ReleaseCurrentSubTransaction(); } PG_CATCH(); { ... RollbackAndReleaseCurrentSubTransaction(); 節を持つブロックは、サブトランザクションの中で実行される。 EXCEPTION アンカンファレンス #57 PostgreSQL 42
43. はなぜSQLより遅いのか PL/pgSQL 実務上の注意点 EXCEPTION 節があるブロックは、サブトランザクション開始のコストを 毎回払う ループの中で毎回 BEGIN...EXCEPTION...END を使うと、ループ回数分サブ トランザクションが積まれる → パフォーマンスに影響しうる 例外処理が不要な箇所では EXCEPTION 節を書かないほうが軽い、 という理由がソースレベルで裏付けられる アンカンファレンス #57 PostgreSQL 43
44. はなぜSQLより遅いのか PL/pgSQL では、いつPL/pgSQLを使うべき か アンカンファレンス #57 PostgreSQL 44
45. はなぜSQLより遅いのか PL/pgSQL 「遅い」の意味を正確に 今日の主張は 「同じ結果を1本のSQLで書けるのに、わざわざPL/pgSQLで 書くと一般的に遅い」 ということ 逆に言えば、次のようなケースはそもそも比較対象が違う 手続き的な分岐・繰り返し・複数SQLの組み合わせが本質的に必要 アプリケーション側と何度も往復するより、サーバ内で完結させたほ うがネットワークコストで有利 EXCEPTION による部分的なエラーハンドリングが要件そのもの 「遅い」は「使うな」ではなく、「代替できるならSQLを選べ」 というト レードオフの話 アンカンファレンス #57 PostgreSQL 45
46. はなぜSQLより遅いのか PL/pgSQL まとめ アンカンファレンス #57 PostgreSQL 46
47. はなぜSQLより遅いのか PL/pgSQL まとめ: なぜPL/pgSQLは遅くなりうるのか ①インタプリタ層 exec_stmtsのswitchディスパッチが文の実行のたびに発生。ふつうのSQLエグ ゼキュータにはない層。 ②式評価のたびのコスト simple expressionでもパラメータリスト構築・有効性チェックを毎回実施。 ③行単位の処理 FORループはカーソルで1行ずつフェッチし、その都度exec_stmtsへ戻る。セッ トベースでない。 ④EXCEPTIONのコスト サブトランザクション開始/終了が毎回のブロック実行に付随する。 アンカンファレンス #57 PostgreSQL 47
48. はなぜSQLより遅いのか PL/pgSQL まとめ: なぜPL/pgSQLは遅くなりうるのか は独自SQLエンジンを持たず、すべてSPI経由でコア実行エンジンに 委譲している。 そのぶん、1本のSQLでは発生しない「往復」のコストを、呼び出し・式・行 の単位で積み重ねている。 だから、同じことができるなら通常のSQLの方が一般的に速い。 PL/pgSQL アンカンファレンス #57 PostgreSQL 48
49. はなぜSQLより遅いのか : AI 裏テーマ のおかげでOSS解析のハードルが劇的に下がっ た PL/pgSQL 読んだことのない数十万行のCコードでも、 AIと一緒になら「実際に読んで確かめる」が現実的な選択肢になる。 今回のスライドも、Claude CodeにPostgreSQL 18本体のソースを実際に読 ませて作成 以前なら、この規模のCコードベースを裏付けとして読むには相応の経験 と時間が必要だった 今は「なぜこの挙動になるのか」を、その場でソースの該当関数まで遡っ て検証できる みんなもPostgreSQLのソースを読んでみよう！ アンカンファレンス #57 PostgreSQL 49
50. はなぜSQLより遅いのか PL/pgSQL 参考資料 PostgreSQL 18 ソースコード( REL_18_STABLE ) src/pl/plpgsql/src/pl_handler.c src/pl/plpgsql/src/pl_comp.c src/pl/plpgsql/src/pl_exec.c src/backend/utils/cache/funccache.c https://github.com/postgres/postgres Portions Copyright (c) 1996-2025, PostgreSQL Global Development Group. Portions Copyright (c) 1994, Regents of the University of California. アンカンファレンス #57 PostgreSQL 50
51. はなぜSQLより遅いのか PL/pgSQL ご清聴ありがとうございました 51