[PsotgreSQL]JDBC経由でサーバサイドでの解析結果を使わないようにする

PostgreSQLにPreparedStatementを使用して繰り返しSQLを実行すると、効率化のためにサーバサイドでの解析結果が使用されるようになる。
これによって、条件に埋め込む値が変わっても、実行計画が作り直されないので想定外の実行計画になってしまう場合があるらしい。

動きを確認するために、以下のデータを持つテーブルを使って検証してみる。

select flg, count(*) from test group by flg;
 flg |  count  
-----+---------
 0   | 1000000
 1   |       1
(2 rows)

条件に固定値を指定した場合の実行計画は、"1"を指定した場合はインデックスが使われ"0"を指定した場合はフルスキャンとなる。

#EXPLAIN select * from test where flg = '1';
                              QUERY PLAN                              
----------------------------------------------------------------------
 Index Scan using test_idx on test  (cost=0.42..4.44 rows=1 width=17)
   Index Cond: (flg = '1'::bpchar)
(2 rows)

# EXPLAIN select * from test where flg = '0';
                          QUERY PLAN                           
---------------------------------------------------------------
 Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17)
   Filter: (flg = '0'::bpchar)
(2 rows)

これをPreparedStatementを使って実行する。
最初の10回は条件に"0"を指定しているのでフルスキャンとなり、最後実行では条件に"1"を指定しているのでインデックスが使われるはず。

try (Connection connection = dataSource.getConnection()) {
    try (Statement statement = connection.createStatement()) {
        statement.execute("load 'auto_explain'");
        statement.execute("set auto_explain.log_min_duration = 0");
        statement.execute("set auto_explain.log_analyze = true");
    }
    try (PreparedStatement statement = connection.prepareStatement("select * from test where flg = ?")) {
        for (int i = 0; i < 10; i++) {
            statement.setString(1, "0");
            statement.executeQuery();
        }
    }
    try (PreparedStatement statement = connection.prepareStatement("select * from test where flg = ?")) {
        statement.setString(1, "1");
        statement.executeQuery();
    }
}

実際の実行結果はこのようになる。
最後のSQL実行は、固定値を指定した時とは異なりインデックスが使われずフルスキャンとなる。
デフォルトだと5回繰り返してPreparedStatementが使われるとサーバサイドの解析結果が使われるらしいけど、Filterのflg=の部分が固定値じゃなくなったのは5回目じゃないが不思議…実行計画からは判断できないのだろうか。
デフォルト値から変えたい場合は、接続プロパティのprepareThresholdを使う。

LOG:  duration: 880.555 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.011..250.647 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 965.254 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.013..263.084 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 713.326 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.015..216.970 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 603.937 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.010..189.935 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 565.579 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.008..175.277 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 582.891 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.007..183.025 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 604.002 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.010..187.921 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 590.842 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.007..185.131 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 589.730 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.007..183.538 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 610.742 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.009..192.248 rows=1000000 loops=1)
          Filter: (flg = ($1)::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 114.769 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=114.755..114.756 rows=1 loops=1)
          Filter: (flg = ($1)::bpchar)
          Rows Removed by Filter: 1000000

サーバサイドの解析を使わないようにするには、接続プロパティのprepareThresholdに0を指定する。
例えば、「jdbc:postgresql://localhost/db?prepareThreshold=0」みたいに指定する。
prepareThreshold=0を指定した結果は、上の結果とは異なり最後の実行がインデックススキャンとなっている。

G:  duration: 813.314 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.011..248.204 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 769.790 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.013..236.008 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 663.801 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.013..198.553 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 562.532 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.009..177.537 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 599.577 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.009..188.422 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 611.355 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.008..192.423 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 626.638 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.010..197.469 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 639.909 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.012..200.366 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 590.332 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.007..187.930 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 587.067 ms  plan:
        Query Text: select * from test where flg = $1
        Seq Scan on test  (cost=0.00..18860.01 rows=1000001 width=17) (actual time=0.012..185.482 rows=1000000 loops=1)
          Filter: (flg = '0'::bpchar)
          Rows Removed by Filter: 1
LOG:  duration: 0.018 ms  plan:
        Query Text: select * from test where flg = $1
        Index Scan using test_idx on test  (cost=0.42..4.44 rows=1 width=17) (actual time=0.014..0.014 rows=1 loops=1)
          Index Cond: (flg = '1'::bpchar)

試した環境

• サーバ:9.5
• JDBC:org.postgresql:postgresql:42.1.4
  

psql上でOracleのSQL*Plusのeditみたいなことをする

psql上では、\eで前回実行したSQL文をエディタ上で編集できるようになる。
エディターは、EDITOR環境変数に設定されたパスのものが開かれる。

PostgreSQLのpsqlでテーブル定義を確認する

psqlメタコマンドの\d テーブル名で確認することができる。

実行例

=> \d hoge
 テーブル "public.hoge"
  列  |   型   | 修飾語
------+--------+--------
 id   | bigint |
 name | text   |

PostgreSQLのsleepファンクション

pg_sleepを使うことでデータベースサーバ側でsleep処理ができる。
pg_sleepの引数には、sleepしたい秒数を指定する。

-- 5秒sleep
select pg_sleep(5);
 pg_sleep
----------

(1 行)

時間: 5016.963 ms

-- 3秒sleep
select pg_sleep(3);

 pg_sleep
----------

(1 行)

時間: 3001.497 ms

IS DISTINCT FROM演算子

IS DISTINCT FROM(IS NOT DISTINCT FROM)演算子の使い方。PostgreSQLでは使うことができる。Oracleは12c時点では使うことができない。
この演算子を使うと、一方がnullの可能性がある場合の条件をcol1 = :col1 or col1 is nullのようにnullを考慮する必要がなくなる。

使い方

IS NOT DISTINCT FROMは、以下の条件と同じ動きとなる。

(expo IS NOT NULL
AND exp2 IS NOT NULL
AND exp1 = exp2)
OR (exp1 IS NULL AND exp2 IS NULL)

例

検索対象のデータ

データの内容(@は、nullを示しています)

select * from test;
 col
-----
   1
   2
   3
   @
(4 行)

IS NOT DISTINCT FROM

is not distinct from nullとするとnullのレコードのみ取得できる。

select * from test where col is not distinct from null;
 col
-----
   @

条件を指定すると、そのレコードのみ取得できる。

select * from test where col is not distinct from 3;
 col
-----
   3
(1 行)

[PostgreSQL]単一のINSERT文で複数レコードを登録

INSERT文のVALUES区に登録対象のレコード情報をカンマ(,)区切りで複数列挙することで一括登録ができる

SQL例

使い方は簡単で、INSERTのVALUE句に設定する。

INSERT INTO users (id, name) VALUES
    (1, 'hoge'),
    (2, 'fuga')

[PostgreSQL]ドメインを定義する

ドメイン定義を使用することで、よく利用する列定義をテーブルごとにするのではなくドメインの定義に集約することができる。

ドメインの定義方法

create domainを使用してドメインを作成する。

例

-- not null制約のついた、varchar(5)のドメインを作成
CREATE DOMAIN test_domain VARCHAR(5) NOT NULL;

-- ドメインの定義内容の確認
                              ドメイン一覧
  スキーマ   |    名前     |          型          |  修飾語  | チェック
-------------+-------------+----------------------+----------+----------
 test_schema | test_domain | character varying(5) | not null |

ドメイン型のカラムの作成

-- テーブルの作成
CREATE TABLE TEST_TABLE (
    ID   SERIAL NOT NULL,
    NAME TEST_DOMAIN,   -- 型にドメインを指定したカラム
    PRIMARY KEY (ID)
)

-- テーブルの定義の確認
-- ドメインに設定しているnot null制約は確認できない
                      テーブル "test_schema.test_table"
  列  |     型      |                         修飾語
------+-------------+---------------------------------------------------------
 id   | integer     | not null default nextval('test_table_id_seq'::regclass)
 name | test_domain |
インデックス:
    "test_table_pkey" PRIMARY KEY, btree (id)

データをインサートしてみる

-- 有効なデータの場合正しくinsertできる
insert into test_table (name) values ('12345');
INSERT 0 1

-- 桁数オーバの場合エラーになる
insert into test_table (name) values ('123456');
ERROR:  value too long for type character varying(5)

-- 制約違反の場合エラーになる
insert into test_table (name) values (null);
ERROR:  domain test_domain does not allow null values

[PostgreSQL]JDBCの接続URLでカレントスキーマを指定する

JDBCで接続先のURLを指定するときに、接続パラメータでカレントのスキーマを指定する。

URLのフォーマット

カレントスキーマの接続パラメータは、currentSchemaなので、以下のようにURLの最後に指定する。

jdbc:postgresql://localhost/test?currentSchema

実行例

// 以下のコードの場合、カレントスキーマはtest_schemaとなる
dataSource.setUrl("jdbc:postgresql://localhost/test?currentSchema=test_schema");
try (Connection connection = dataSource.getConnection()) {
    final String schema = connection.getSchema();
    System.out.println("schema = " + schema);       // -> schema = test_schema
}

PostgreSQLでシーケンスを使う

PostgreSQLでのシーケンス作成やシーケンスの操作関数の纏め。

シーケンスの作成

create sequence文を使ってシーケンスを作成する。

例

全てデフォルトの設定でシーケンスを作成する。(初期値は1、増分値は1、キャッシュはなしなどになる)

create sequence test_seq;

一時的な（今のセッションだけに関連づいた）シーケンスを作成する。
シーケンス作成時に、temp(temporary)を指定すると今のセッションに関連づいた一時的なシーケンスが作られる。(セッションが終了すると、自動的に破棄される。)
既に同名のシーケンスが存在していた場合、一時的なシーケンスが優先されて既にあったシーケンスは見えなくなる。

create temp sequence test_seq;

シーケンスの情報を確認する

シーケンスを検索することで、今の状態が確認できる。

select * from シーケンス名

シーケンスを操作するための関数

nextvalで次の値を取得する。

select nextval('シーケンス名')

currvalで最後にnextvalされた値を取得できる。

select currval('シーケンス名')

setvalで現在の値を変更できる。この例だと、値を999に変更している。

select setval('シーケンス名', 999)

PostgreSQLはStatement.RETURN_GENERATED_KEYSで全カラムの値が返される

PostgresSQLは、Statement生成時に、Statement.RETURN_GENERATED_KEYSを指定するとStatement#getGeneratedKeysでそのテーブルの全カラムの情報が取得される。

これは、Statement.RETURN_GENERATED_KEYSでStatementを生成すると、実行されるINSERT文に「RETURNING *」が付加されるため。

例

ID、NAME、BIRTHDAYの3つのカラムを持つテーブルにINSERT処理を行っています。
INSERT対象のカラムはNAMEのみで、ID列は自動採番カラム(SERIAL)となっています。

PreparedStatement statement = connection.prepareStatement("insert into USER_INFO (name) VALUES (?)",
        Statement.RETURN_GENERATED_KEYS);
statement.setString(1, "なまえ");

statement.executeUpdate();
ResultSet generatedKeys = statement.getGeneratedKeys();
if (generatedKeys.next()) {
    System.out.println(MessageFormat.format("カラム数 = [{0}]",
            statement.getMetaData().getColumnCount()));
    System.out.println("generatedKeys.getDate(3) = " + generatedKeys.getDate(3));
}
connection.commit();

実行結果

実行すると、GeneratedKeysで取得したResultSetのカラム数は3となっていることがわかります。
また、INSERT時に列挙していないカラムのデータもとれているのがわかります。

カラム数 = [3]
generatedKeys.getDate(3) = null

実際に実行されたSQL文

ログを見てみると、実行されたINSERT文にRETURNING *が付加されているのがわかります。
これが理由で、getGeneratedKeysが全カラムの情報を戻して来ています。

2015-01-08 12:48:11 JST LOG:  duration: 0.000 ms  parse : insert into USER_INFO (name) VALUES ($1) RETURNING *
2015-01-08 12:48:11 JST LOG:  duration: 0.000 ms  bind : insert into USER_INFO (name) VALUES ($1) RETURNING *
2015-01-08 12:48:11 JST DETAIL:  parameters: $1 = 'なまえ'
2015-01-08 12:48:11 JST LOG:  duration: 1.000 ms  execute : insert into USER_INFO (name) VALUES ($1) RETURNING *
2015-01-08 12:48:11 JST DETAIL:  parameters: $1 = 'なまえ'
2015-01-08 12:48:11 JST LOG:  duration: 0.000 ms  parse : insert into USER_INFO (name) VALUES ($1) RETURNING *

カラムインデックスやカラム名指定の場合は？

Statement生成時に自動生成カラムのインデックスや名前を指定した場合は、指定したカラムの情報のみ取得することが出来ます。
例えば、ID列のみを指定した場合実行されるSQL文は以下のようになります。

2015-01-08 12:55:28 JST LOG:  duration: 34.000 ms  parse : insert into USER_INFO (name) VALUES ($1) RETURNING "id"
2015-01-08 12:55:28 JST LOG:  duration: 0.000 ms  bind : insert into USER_INFO (name) VALUES ($1) RETURNING "id"
2015-01-08 12:55:28 JST DETAIL:  parameters: $1 = 'なまえ'
2015-01-08 12:55:28 JST LOG:  duration: 0.000 ms  execute : insert into USER_INFO (name) VALUES ($1) RETURNING "id"
2015-01-08 12:55:28 JST DETAIL:  parameters: $1 = 'なまえ'
2015-01-08 12:55:28 JST LOG:  duration: 0.000 ms  parse : insert into USER_INFO (name) VALUES ($1) RETURNING "id"