[pgsql-jp: 40890] Re: auto_explainの実行計画について
nozawakz ＠ nttdata.co.jp
nozawakz ＠ nttdata.co.jp
2011年 8月 19日 (金) 13:42:02 JST
板垣さま
ご回答いただき、ありがとうございました。
下記、長文になってしまい、申し訳ありません。
＞auto_explain は実際に使用されたプランを出力するので、
＞ご希望の事象を確認するためには十分利用できるはずです。
auto_explain.log_min_duration = 0
とし、実行計画をすることができました。
運用のシーンを考えますと、
再起動なしで時間を変えることができるので
欠かせないライブラリだと感じています。
＞ただ、こちらについては ANALYZE で再プランニングされるかは疑問です。
＞確かに ALTER TABLE 等、テーブル構成が変化するケースでは
＞再プランニングされるのですが、8.3 以降であっても ANALYZE だけでは
＞されないような気がします。
当方の試験手順に問題があるためか
再プランニングされておりました。
下記、試験内容となっております。
■試験内容
ANALYZEで実行計画が再プランニング（＝最新化）されない可能性があり、
その場合レコード件数が増加するテーブルに対するSQLは性能劣化する恐れがある。
最新化されるかどうか確定情報がないため、実機（postgreSQL 8.4.4）で確認する
■試験結果＆考察
実行計画は最新化されていたため、
板垣様の見解と異なるため、
当方の試験手順に問題があると思われる。
ただし、コネクションプーリングでなく、
sleep文で代用しているため、問題があるのでしょうか。
PostgreSQLとは直接関係のない質問になってしまうため、
もしも、ご存知でしたらご教授いただきたいです。
■試験内容詳細
①対象テーブル
postgres=# \d t_test02;
テーブル "public.t_test02"
カラム | 型 | 修飾語
--------+------------------------+--------------
a01 | character(12) | not null
a02 | character(15) |
a03 | character(3) |
a04 | integer |
a05 | character varying(24) |
a06 | character varying(255) |
a07 | integer |
a08 | character varying(384) |
a09 | date |
a10 | date |
a11 | character(7) |
a12 | character(7) |
a13 | character(7) |
a14 | integer |
a15 | integer |
a16 | character varying(384) |
a17 | date |
a18 | character(1) | デフォルト 0
a19 | date |
a20 | character varying(20) |
a21 | character varying(20) |
a22 | date |
a23 | date |
a24 | character(1) | デフォルト 0
インデックス:
"t_test02_pkey" PRIMARY KEY, btree (a01)
"idx_a02" btree (a07)
②SQL文
postgres=# \! date
2011年 8月 19日 金曜日 03:20:59 JST
postgres=# TRUNCATE t_test02;
TRUNCATE TABLE
postgres=# ANALYZE t_test02; ←①実行計画が最新ならこのSQLは実行計画がindex scanになる（0件にする）
ANALYZE
postgres=# \! date
2011年 8月 19日 金曜日 03:21:07 JST
postgres=#
postgres=#
postgres=#
postgres=#
postgres=# \! date
2011年 8月 19日 金曜日 03:21:15 JST
postgres=# INSERT INTO t_test02 (a01,a07) VALUES (1,1);
INSERT 0 1
postgres=# ANALYZE t_test02; ←②実行計画が最新ならこのSQLは実行計画がSeq Scanになる（1件にする）
ANALYZE
postgres=# \! date
2011年 8月 19日 金曜日 03:21:23 JST
postgres=#
postgres=#
postgres=#
postgres=# \! date
2011年 8月 19日 金曜日 03:21:46 JST
postgres=# TRUNCATE t_test02;
TRUNCATE TABLE
postgres=# delete from t_test02 where a01='1';
DELETE 0
postgres=# \COPY t_test02 from /home/test/a.txt
postgres=# ANALYZE t_test02; ←③実行計画が最新ならこのSQLは実行計画がindex scanになる（10万件にする）
ANALYZE
postgres=# \! date
2011年 8月 19日 金曜日 03:22:04 JST
③ログ（pg_log/postgresql.log）⇒auto_explain
2011-08-19 03:21:09 JST 28477 LOG: duration: 0.053 ms plan:
Index Scan using idx_a02 on t_test02 (cost=0.00..8.27 rows=1 width=1963)
Index Cond: (a07 = 1)
2011-08-19 03:21:39 JST 28477 LOG: duration: 0.045 ms plan:
Seq Scan on t_test02 (cost=0.00..1.01 rows=1 width=1963) ←実行計画が最新化されている！！
Filter: (a07 = 1)
2011-08-19 03:22:09 JST 28477 LOG: duration: 0.039 ms plan:
Index Scan using idx_a02 on t_test02 (cost=0.00..8.28 rows=1 width=122)
Index Cond: (a07 = 1)
【参考】
④JAVA側
-bash-3.1$ java PreparedTest
start SQL: 0 2011.08.19 at 03:21:09 JST
end SQL: 0 2011.08.19 at 03:21:09 JST
start SQL: 1 2011.08.19 at 03:21:39 JST
1
end SQL: 1 2011.08.19 at 03:21:39 JST
start SQL: 2 2011.08.19 at 03:22:09 JST
end SQL: 2 2011.08.19 at 03:22:09 JST
⑤JAVAソース
import java.sql.*;
import java.text.*;
import java.util.*;
public class PreparedTest {
public static void main(String[] args) {
PreparedTest preparedtest =new PreparedTest();
try {
preparedtest.selectPostgre();
} catch (Exception e) {
e.printStackTrace();
}
}
public void selectPostgre() throws Exception{
/* ユーザ名 */
String user = "postgres";
/* パスワード */
String pass = "nttdata";
/* サーバ名 */
String servername = "localhost";
/* データベース名 */
String dbname = "postgres";
ResultSet rset = null;
Connection conn = null;
Statement stmt = null;
try {
/* ドライバクラスのロード */
Class.forName ("org.postgresql.Driver");
/* Connectionの作成 */
conn = DriverManager.getConnection
("jdbc:postgresql://" + servername + ":5432/" + dbname,user,pass);
// ステートメントを作成
stmt = conn.createStatement();
// select文の場合
PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM t_test02 WHERE a07 = ?;");
for (int i = 0; i <= 2; i++) {
java.util.Date date1 = new java.util.Date();
SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy.MM.dd 'at' HH:mm:ss z");
System.out.println("\nstart SQL: " + i + " "+ sdf1.format(date1)); //Dateオブジェクトを表示
pstmt.setInt(1, 1);
// Resultsetの作成
rset = pstmt.executeQuery();
// 取得したデータを表示します。
while (rset.next()) {
System.out.println(rset.getString(7));
}
java.util.Date date2 = new java.util.Date();
SimpleDateFormat sdf2 = new SimpleDateFormat("yyyy.MM.dd 'at' HH:mm:ss z");
System.out.println("end SQL: " + i + " " + sdf2.format(date2)); //Dateオブジェクトを表示
Thread.sleep(30000);
}
} catch (SQLException e) {
throw e;
} catch ( Exception e){
throw e;
} finally{
/* クローズ処理 */
if(conn != null){
conn.close();
conn = null;
}
if(stmt != null){
stmt.close();
stmt = null;
}
}
}
}
以上、よろしくお願い致します。
pgsql-jp メーリングリストの案内