PostgreSQL 7.4 のインデックスと smallint と JDBC

2007/08/03 5:41am

java
postgres

PostgreSQL 7.4 のマニュアル「8.1.1. 整数データ型」には以下のような記述がある。

注意: インデックスが付けられた smallint あるいは bigint の列がテーブルにある場合、システムがそのインデックスを使用しようとした時に問題を引き起こすことがあります。例えば句が次のような形式の場合、

… WHERE smallint_column = 42

システムはインデックスを使用しません。なぜなら42という定数にシステムが integer を割り当てるからです。今の所PostgreSQLは2つの異なるデータ型が混在している時には、インデックスを使うことができません。問題を回避するには以下のように定数部分を単一引用符で括ります。

… WHERE smallint_column = ‘42’

こうすると、システムは型分析を後廻しにしてこの定数に正しいデータ型を割り振ります。

（なお、PostgreSQL 8.x のドキュメントからは該当する部分が削除されているので、バージョン 8 以降にはこの制限はないのかもしれない。未確認）

なんにせよ、PostgreSQL 7.4 では smallint のカラムを条件に含める場合は、 smallint_column = '42' と単一引用符で囲むか、smallint_column = 42::smallint みたいに型を指定しないと、インデックスが使われないらしい。

こういう些細な注意事項は忘れてしまうのが常なので、よほどサイズが重要でないかぎりは int を使うのが安全。どうしても smallint を使う場合は、ちゃんと SQL を explain してみて引用符で囲み、理由をコメントなりで書いておくようにするべきだろう。

ただ、問題は JDBC とかの抽象層をかます場合で、このときにどんな SQL が発行されるかを把握しておく必要がある。

PostgreSQL 側で、実際に発行されている SQL をログに記録して確認してみた。Java のコードと PostgreSQL のログに残されていた SQL ステートメントの両方を載せてある。

java.sql.PreparedStatement.setInt(int, int) を使った場合、

int type = ...;
stmt.setInt(1, type);
...
statement: SELECT * FROM example WHERE smallint_column = 1

java.sql.PreparedStatement.setShort(int, short) を使った場合、

int type = ...;
stmt.setShort(1, (short)type);
...
statement: SELECT * FROM example WHERE smallint_column = 1

java.sql.PreparedStatement.setObject(int, Object, int) を使った場合、

int type = ...;
stmt.setObject(3, Integer.valueOf(type), java.sql.Types.SMALLINT);
...
statement: SELECT * FROM example WHERE smallint_column = 1

java.sql.PreparedStatement.setString(int, String) を使った場合、

int type = ...;
stmt.setString(1, Integer.toString(type));
...
statement: SELECT * FROM example WHERE smallint_column = '1&#x27;

setShort や setObject で SQL の型を指定しても、実際に発行される SQL では型指定がないのでインデックスが使用されない。setString を使えば、引用符に囲まれ、インデックスが使われるようになる。

最後に、今回の検証に使用した環境は以下のとおり。

PostgreSQL 7.4.7
pg74.216.jdbc3.jar