psqlでバインド変数を利用する方法

本記事の概要

本記事では psql でバインド変数を利用する方法を説明しています。また、バインド変数そのものについても簡単に解説を行なっています。

環境

検証に利用した環境は下記となります。

• RockyLinux 9.4
• PostgreSQL 16.6

psqlでバインド変数を利用する方法

バインド変数とは

そもそもバインド変数とは何かというお話ですが、バインド変数とは「SQLに記述された値に対して、外部からパラメータとして値を渡す」仕組みとなっています。
簡単な例を下記に記載します。

SELECT * FROM test_table WHERE id = $1;

上記の $1 に該当する部分がバインド変数に該当し、後から $1 に対して値を当てはめて実行するという流れになります。
ちなみに、この $1 は、データベースやインターフェースなどによって記述方法が違う場合があります。
例えば、JDBC では ? を利用しますので

SELECT * FROM test_table WHERE id = ?;

といった形になります。
他にも %s などを使うインターフェースがあるようです。

バインド変数のメリット

バインド変数を利用するメリットとしては

1. セキュリティ対策
2. SQLの記述が楽になる

という 2 点が挙げられます。

セキュリティ対策としてバインド変数を利用する

バインド変数を利用しない場合、どのようなリスクがあるのかを解説します。
WEB 画面で入力された値を、検索条件に利用する SQL があったとします。

-- 「検索文字列」の部分が WEB 画面で入力された値
SELECT * FROM test_table WHERE value = '検索文字列';

例えば、WEB 画面で「a' OR '1' = '1」と入力された場合、実際に実行される SQL は

-- 悪意のある入力により SQL の構文が変わる
SELECT * FROM test_table WHERE value = 'a' OR '1' = '1';

というものになります。
上記の SQL を実行した場合には、test_table の全てのデータが対象となります。
といった具合に、外部から値を受け取った値をそのまま SQL に埋め込む方法にはリスクがあります。
バインド変数を利用すると、上記のように SQL の構文自体が変動することがなくなり、安全に SQL を実行できます。

SQLの記述が楽になるのでバインド変数を利用する

SQL をプログラムなどで記述する場合、値に該当する箇所を変数で用意することが多いと思います。
例えば、下記のような id と value という変数を条件式の値として埋め込むプログラムがあったとします。

/* バインド変数を利用しない */
"SELECT * FROM test_table WHERE id = " + id + " AND value = '" + value "';"

/* バインド変数を利用する */
"SELECT * FROM test_table WHERE id = ? AND value ?;"

簡単な SQL ですが、バインド変数をしない場合は可読性が高いとはいえません。
条件が複雑になったり、if 文が追加されたりすると、より可読性や保守性が悪くなります。
バインド変数を利用した場合では、余計な + や ' や " が含まれないため、記述しやすく、かつ読みやすいものとなっています。

実際に挙動を確認

前置きが長くなりましたが、実際に psql でバインド変数を利用する方法を下記に記載します。
psql でバインド変数を利用したい場合、PREPARE 文を利用します。
具体的には下記のような流れとなります。

-- PREPARE 文を作成
postgres=# PREPARE test_bind (integer) AS SELECT aid, bid, abalance FROM pgbench_accounts WHERE aid = $1;
PREPARE

-- バインド変数に設定したい値を引数に指定して、PREPARE 文を実行
postgres=# EXECUTE test_bind(100);
 aid | bid | abalance 
-----+-----+----------
 100 |   1 |        0
(1 row)

意外と簡単にバインド変数を利用できることを確認できました。

バインド変数を利用することで実行計画が変動するか

バインド変数の利用方法とは少し外れるのですが、バインド変数を利用するメリットとして「実行計画を再利用できるから」というお話もあります。
ただ、バインド変数を利用したからといって必ず実行計画が再利用されるというわけではありません。PostgreSQL では plan_cache_mode パラメータによって、実行計画を再利用するかどうかが制御されます。詳細については、公式ドキュメントの説明を参照してください（該当の部分を下記に引用します）。

【 PostgreSQL 16.4文書 - PREPARE - 】
https://www.postgresql.jp/document/16/html/sql-prepare.html

デフォルト(すなわち、plan_cache_modeがautoに設定されている場合)では、パラメータのあるプリペアド文に対して、汎用的な計画を使うか独自の計画を使うかを、サーバは自動的に選択します。 これに対する現在の規則は、最初の5回が独自の計画で実行され、その計画の推定コストの平均が計算される、というものです。 それから汎用的な計画が作成され、その推定コストが独自の計画のコストの平均と比較されます。 再計画を繰り返すことが望ましいと思えるほどにはそのコストが独自の計画の平均コストよりも高くなければ、その後の実行は汎用的な計画を使います。

バインド変数を利用しても実行計画が変わることを確認する

実際に SQL を実行し、バインド変数を利用しても実行計画が変わることを確認します。
pgbench_accounts テーブルの aid を検索条件にした SQL を利用して、挙動を確認した結果を以下に記載します。

-- (1) PREPARE 文を用意します
postgres=# PREPARE test_bind (integer) AS SELECT aid, bid, abalance FROM pgbench_accounts WHERE aid < $1;
PREPARE

-- (2) バインド変数に 1 を指定して PREPARE 文を実行します
postgres=# EXPLAIN (ANALYZE) EXECUTE test_bind(1);
                                                               QUERY PLAN                                                                
-----------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.42..4.44 rows=1 width=12) (actual time=0.006..0.007 rows=0 loops=1)
   Index Cond: (aid < 1)
 Planning Time: 0.786 ms
 Execution Time: 0.023 ms
(4 rows)

-- (3) 今度はバインド変数に 100000000 を指定して PREPARE 文を実行します
postgres=# EXPLAIN (ANALYZE) EXECUTE test_bind(100000000);
                                                         QUERY PLAN                                                          
-----------------------------------------------------------------------------------------------------------------------------
 Seq Scan on pgbench_accounts  (cost=0.00..28894.00 rows=1000000 width=12) (actual time=0.017..181.650 rows=1000000 loops=1)
   Filter: (aid < 100000000)
 Planning Time: 0.193 ms
 Execution Time: 214.466 ms
(4 rows)

上記の (2) では IndexScan が選択されていますが、(3) では SeqScan が選択されています。このように PostgreSQL では、バインド変数を利用したからといって実行計画が固定化されるわけではないことを確認しました。
また、公式ドキュメントの説明通り、6 回目以降の PREPARE 文の実行では、実行計画が変動しました。

-- (4) 6 回目の実行では SeqScan ではなく IndexScan が選択された
postgres=# EXPLAIN (ANALYZE) EXECUTE test_bind(100000000);
                                                                        QUERY PLAN                                                                        
----------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using pgbench_accounts_pkey on pgbench_accounts  (cost=0.42..14961.75 rows=333333 width=12) (actual time=0.111..269.975 rows=1000000 loops=1)
   Index Cond: (aid < $1)
 Planning Time: 0.130 ms
 Execution Time: 302.414 ms
(4 rows)

まとめ

本記事では psql でバインド変数を利用する方法について解説しました。
psql でバインド変数を利用する必要性は多くないと思いますが、プログラムから実行している SQL をチューニングしたい場合など、 psql でバインド変数を利用したいケースもあります。
psql で SQL を実行するのが最も手軽な方法だと思いますので、psql を便利に利用できるように、引き続き情報をお伝えできればと思います。