[pgsql-jp: 41466] Re: AutoCommitをfalseにするとレスポンスが遅くなる

2013年 7月 24日 (水) 13:29:46 JST

mlusさん、笠原さん

宮本です。
ご連絡ありがとうございます。

> Connection.setReadOnly(true)   とか
> createStatement(ResultSet.TYPE_FORWARD_ONLY,
>   ResultSet.CONCUR_READ_ONLY ,
>   ResultSet.HOLD_CURSORS_OVER_COMMIT)
> 
> みたいな感じで、Statementの調整とかもありますが、関係ないですかね。

Connection.setReadOnly(true)をしたり、Commitしてみましたが、結果は
変わりませんでした。（遅い）

> もしそうであれば、ExecuteQuery前に
> st.setPrepareThreshold(1)
> を実行して、プランを比較してみていただけませんか？

PrepareThresholdはデフォルト値のままでしたので、
st.setPrepareThreshold(1)を実行し、SQLを３回実行して
比較してみました。
が、残念ながら、３回ともレスポンス、実行計画は
変わりませんでした。（遅いまま）

1回目：182080 msec
2回目：182218 msec
3回目：182006 msec

追加情報なのですが、以下のように、Javaで実行するSQLに
"explain analyze"をつけて実行すると、レスポンスは早くなり、
explainの結果で出力される実行計画も"hash join"が使用されます。
（期待通りの実行計画になります。）

String sql = "explain analyze SELECT COUNT(*) FROM (SELECT A,B・・・ （省略）"

よろしくお願いします。

以上

---- kasaharatt ＠ nttdata.co.jp さんは書きました: 
> 笠原と申します。
> 
> >> Q1 SQL分の実行には、StatementとPreparedStatementのどちらを使ったでしょうか。
> > 
> > PreparedStatementを使用しています。
> > String sql = "SELECT COUNT(*) FROM (SELECT A,B・・・ （省略）"
> > PreparedStatement st = conn.prepareStatement(sql);
> PrepareThreshold はデフォルト値でしょうか？
> 
> もしそうであれば、ExecuteQuery前に
> st.setPrepareThreshold(1)
> を実行して、プランを比較してみていただけませんか？
> # できればst.setPrepareThreshold(1)設定後に3回程度連続でSQLを実行してみて欲しいです。
> 
> もしかしたら PrepareThreshold が影響しているかもしれません・・
> http://jdbc.postgresql.org/documentation/head/server-prepare.html
> 
> 
> > -----Original Message-----
> > From: pgsql-jp-bounces ＠ ml.postgresql.jp [mailto:pgsql-jp-bounces ＠ ml.postgresql.jp] On Behalf Of
> > pascalpascal ＠ snow.plala.or.jp
> > Sent: Wednesday, July 24, 2013 10:33 AM
> > To: PostgreSQL Japanese Mailing List
> > Subject: [pgsql-jp: 41463] Re: AutoCommitをfalseにするとレスポンスが遅くなる
> > 
> > MauMauさん
> > 
> > ご連絡ありがとうございます。
> > 
> > > 問い合わせ計画は自動コミットが有効かどうかによって変わらないように思うのですが、次のことを教えてください。
> > 
> > 私も変わらないと思っていましたので、今回の現象は驚いています。
> > 
> > > Q1 SQL分の実行には、StatementとPreparedStatementのどちらを使ったでしょうか。
> > 
> > PreparedStatementを使用しています。
> > 
> > String sql = "SELECT COUNT(*) FROM (SELECT A,B・・・ （省略）"
> > PreparedStatement st = conn.prepareStatement(sql);
> > 
> > > Q2 setAutoCommit()で自動コミットを有効/無効にしたそれぞれの場合で、まったく同じSQL分を実行されているでしょうか。
> > 
> > SQL、where句の定数、バインド変数の値もまったく同じです。
> > 
> > バインド変数は、例えば、
> > st.setObject(1, "00060000");
> > のような感じで指定しています。
> > 
> > 簡単にいうと、同じプログラムで、
> > conn.setAutoCommit(false)　を入れるか、入れないかで、
> > レスポンス、実行計画が違います。
> > 
> > > Q3 それぞれの場合において、テーブル内のデータや統計情報に違いはなかったでしょうか。
> > 
> > AutoCommit(false)ありのプログラムを実行したすぐ後に
> > AutoCommit(false)なしのプログラムを実行しても、
> > レスポンスや実行計画が違います。getAutoCommit()して
> > 確認していますが、AutoCommit(false)なしの場合はtrueになっています。
> > 実行計画はauto_explain.log_min_durationを設定して、Postgresqlログで
> > 確認しています。
> > 
> > アプリの仕様上、AutoCommitはfalseにしないといけないので、
> > AutoCommitはfalseにしたままで、この問題を解決できるような
> > 回避策がないかと思っています。
> > 
> > よろしくお願いします。
> > 
> > ---- MauMau <maumau307 ＠ gmail.com> さんは書きました:
> > > MauMauといいます。
> > >
> > > 問い合わせ計画は自動コミットが有効かどうかによって変わらないように思うのですが、次のことを教えてください。
> > >
> > > Q1 SQL分の実行には、StatementとPreparedStatementのどちらを使ったでしょうか。
> > >
> > > Q2 setAutoCommit()で自動コミットを有効/無効にしたそれぞれの場合で、まったく同じSQL分を実行されているでしょうか。
> > > 同じとは、WHERE句などで指定する定数や、PreparedStatement#setXXX()で
> > > 設定する値も完全に一致する、ということです。
> > >
> > > Q3 それぞれの場合において、テーブル内のデータや統計情報に違いはなかったでしょうか。
> > >
> > >
> > > 以上です。
> > >
> > > ----- Original Message -----
> > > From: <pascalpascal ＠ snow.plala.or.jp>
> > > To: <pgsql-jp ＠ ml.postgresql.jp>
> > > Sent: Tuesday, July 23, 2013 5:29 PM
> > > Subject: [pgsql-jp: 41453] AutoCommitをfalseにするとレスポンスが遅くなる
> > >
> > >
> > > > 宮本と申します。
> > > >
> > > > 現在、PostgreSQL(9.1.6)を運用しております。
> > > >
> > > > jdbc経由でPostgresqlに接続して、selectを実行するJavaのプログラムで
> > > > レスポンスが遅いという現象があり、調査したところ、
> > > > AutoCommitをfalseにすると、遅いということがわかりました。
> > > >
> > > > AutoCommitをfalseにすると実行計画も違います。長いSQL文で結合が多く、
> > > > データ件数も多いため、Hash joinを使った方が早いのですが、
> > > > AutoCommitをfalseにすると、なぜか、Nested Loopが使われてしまいます。
> > > >
> > > > AutoCommit  レスポンス      実行計画
> > > > ---------------------------------------------
> > > > false        遅い（約3分）  Nested loop
> > > > true         早い（約6秒）  Hash join
> > > >
> > > > 環境
> > > >  OS: Redhat Enterprise Linux 5.7
> > > >  PostgreSQL: 9.1.6
> > > >  jdbc: postgresql-9.1-901.jdbc3.jar
> > > > （jdbcをpostgresql-9.1-903.jdbc3.jarやpostgresql-9.2-1003.jdbc3.jarにしても同じでした。）
> > > >
> > > > これは仕様なのでしょうか。
> > > > それともバグなのでしょうか。
> > > > ネット上でいろいろ調べましたが、わかりませんでした。
> > > > どなたか情報をお持ちの方は教えていただけないでしょうか。
> > > > よろしくお願いします。
> > > >
> > >
>