PostgreSQLの実行計画をコントロールする

2017.09.13

こんにちは、YDCのミッツです。
今回はPostgreSQLで使用する統計情報を管理するツール「pg_dbms_stats」をご紹介します。

オプティマイザの限界

現在、RDBMSのオプティマイザはコストベースが主流です。コストベースオプティマイザ(CBO)は統計情報を適切に取得していれば、統計情報からコストを見積もり、効率が良いと思われる実行計画を選択してくれます。

しかし、統計情報を定期的に取得しているにも関わらず、突然、効率の悪い実行計画が選択され性能が劣化した、という経験がある方もいらっしゃるかと思います。
なぜ、このようなことが起きてしまうのでしょうか。
それは様々な要因でオプティマイザが最適な実行計画を選択できないことがあるためです。
オプティマイザは常に完璧な仕事をこなしてくれるわけではないのです。

悪い実行計画を選ばせないために

では、効率の悪い実行計画を選ばせないためにユーザーはどんな対策を打てばよいでしょうか。
まず、考えられるのがヒント句の利用です。
しかし、ヒント句の利用はSQLが改修可能な場合に限られるため、パッケージソフトが発行したSQLなどが対象である場合は対処することができません。

こんなときは実行計画の固定が有効な手段として考えられます。
実行計画を固定してしまい、オプティマイザが不適切な実行計画を選ぶことを防ぐのです。
Oracleでは11gで実行計画を固定することができる機能、SPM(SQL Plan Management)が提供されました。

その他の多くのDBMSでも実行計画を固定するための機能が提供されています。
冒頭で名前だけご紹介した「pg_dbms_stats」はPostgreSQLで実行計画を固定するためのツールというわけです。

pg_dbms_statsの使い方

pg_dbms_statsは統計情報のバックアップ、リストア、ロックといった機能を持っており、
バックアップしておいた統計情報をプランナ(オプティマイザ)に使用させることで、
バックアップしたときと同じ実行計画をプランナに再現させる、といった方法で間接的に実行計画を制御します。

では、実際に実行計画を固定する方法について説明していきます。

テスト用のデータを準備

まずは、psqlでテスト用のデータを作成します。
次のSQLでシンプルなテーブルを作成します。

postgres=# CREATE TABLE test_tbl (col1 int PRIMARY KEY,col2 varchar(20));
CREATE TABLE

続いてテストデータを挿入するファンクションを作成します。
先ほど作成したテーブルに10000件のデータを挿入するファンクションです。

postgres=# CREATE FUNCTION instest1() returns int as
postgres-# '
postgres'# DECLARE
postgres'# i int;
postgres'# BEGIN
postgres'# FOR i IN 1..10000 loop
postgres'# INSERT INTO test_tbl VALUES(i,''test'');
postgres'# END LOOP;
postgres'# RETURN 0;
postgres'# END;
postgres'# '
postgres-# LANGUAGE 'plpgsql'
postgres-# ;
CREATE FUNCTION

作成したファンクションを使ってデータを挿入してみます。

postgres=# SELECT instest1();
instest1
----------
0
(1 row)

postgres=# SELECT COUNT(*) FROM test_tbl;
count
-------
10000
(1 row)

ちゃんと1万件挿入されたようです。

統計情報をバックアップ

ANALYZEコマンドで統計情報を取得しておきます。

postgres=# ANALYZE;
ANALYZE

EXPLAINコマンドでcol1が1001から2000の範囲でSELECT文が発行された場合の実行計画を確認しておきます。

postgres=# EXPLAIN ANALYZE SELECT * FROM test_tbl WHERE col1 > 1000 AND col1 <=2000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using test_tbl_pkey on test_tbl (cost=0.29..41.28 rows=1000 width=9) (actual time=0.014..0.170 rows=1000 loops=1)
Index Cond: ((col1 > 1000) AND (col1 <= 2000))
Planning time: 0.066 ms
Execution time: 0.217 ms
(4 rows)

プランナがインデックススキャンを選択しているのが分かります。
この統計情報をpg_dbms_statsのバックアップ機能で
バックアップしてしまいます。

postgres=# SELECT dbms_stats.backup_database_stats('Index Scan');
backup_database_stats
-----------------------
9
(1 row)

統計情報をリストア

ここでcol1が2001以上のレコードをバッサリ削除して、もう一度統計情報を取得します。

postgres=# DELETE FROM test_tbl WHERE col1 > 2000;
DELETE 8000
postgres=# ANALYZE;
ANALYZE

このときの実行計画はどうなっているでしょう。

postgres=# EXPLAIN ANALYZE SELECT * FROM test_tbl WHERE col1 > 1000 AND col1 <=2000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on test_tbl (cost=0.00..41.00 rows=1000 width=9) (actual time=0.060..0.171 rows=1000 loops=1)
Filter: ((col1 > 1000) AND (col1 <= 2000))
Rows Removed by Filter: 1000
Planning time: 0.194 ms
Execution time: 0.213 ms
(5 rows)

シーケンシャルスキャンが採用されています。
プランナが「インデックスを使わない方が早い」と判断した結果です。
では、pg_dbms_statsのリストア機能を使用してプランナをだましてしまいましょう。
先ほどバックアップした統計情報をbackup_historyテーブルで確認します。

postgres=# SELECT * FROM dbms_stats.backup_history;
id | time | unit | comment
------+-------------------------------+------+------------
9 | 2017-09-08 18:39:27.401039+09 | d | Index Scan
(1 row)

取得したバックアップにはidが振られます。
先ほどバックアップした統計情報のidは9です。
この9番のバックアップをリストアします。

postgres=# SELECT dbms_stats.restore_stats(9);
restore_stats
-----------------------
pgbench_accounts
pgbench_accounts_pkey
pgbench_branches
pgbench_branches_pkey
pgbench_history
pgbench_tellers
pgbench_tellers_pkey
test_tbl
test_tbl_pkey
(9 rows)

これでバックアップした実行計画をリストアしました。
ここでもう一度実行計画を確認します。

postgres=# EXPLAIN ANALYZE SELECT * FROM test_tbl WHERE col1 > 1000 AND col1 <=2000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using test_tbl_pkey on test_tbl (cost=0.29..45.28 rows=1000 width=9) (actual time=0.009..0.139 rows=1000 loops=1)
Index Cond: ((col1 > 1000) AND (col1 <= 2000))
Planning time: 0.194 ms
Execution time: 0.187 ms
(4 rows)

インデックススキャンが採用されています。

本当はシーケンシャルスキャンを使った方が早いのに
プランナは偽の統計情報に騙されてインデックススキャンを選んでしまったのです。

今回はプランナをだまして本来の実行計画よりも効率の悪い実行計画を選ばせています。
しかし、もし本来の実行計画が効率の悪い実行計画を選んでいる状況であれば、プランナが間違えないように導くことが可能です。

統計情報の固定を解除

固定を解除するにはアンロック機能を使います。

postgres=# SELECT dbms_stats.unlock_database_stats();
unlock_database_stats
-----------------------
pgbench_history
pgbench_tellers
pgbench_accounts
pgbench_branches
pgbench_branches_pkey
pgbench_tellers_pkey
pgbench_accounts_pkey
test_tbl
test_tbl_pkey
(9 rows)

もう一度実行計画を確認します。

postgres=# EXPLAIN ANALYZE SELECT * FROM test_tbl WHERE col1 > 1000 AND col1 <=2000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on test_tbl (cost=0.00..52.00 rows=1000 width=9) (actual time=0.066..0.174 rows=1000 loops=1)
Filter: ((col1 > 1000) AND (col1 <= 2000))
Rows Removed by Filter: 1000
Planning time: 0.358 ms
Execution time: 0.242 ms
(5 rows)

シーケンシャルスキャンに戻ってますね。

現在の統計情報で固定

現在の統計情報で固定したい、という場合にはバックアップ、リストアを使用しなくても
ロックという機能で固定することができます。
シーケンシャルスキャンを採用している今の実行計画で固定してしまいましょう。

postgres=# SELECT dbms_stats.lock_database_stats();
lock_database_stats
-----------------------
pgbench_history
pgbench_tellers
pgbench_accounts
pgbench_branches
pgbench_branches_pkey
pgbench_tellers_pkey
pgbench_accounts_pkey
test_tbl
test_tbl_pkey
(9 rows)

ちゃんと固定されているか確認するため、インデックススキャンが選ばれるはずの状況に戻します。
一度、test_tblの情報を削除してまた1万件入れ直してみます。

postgres=# DELETE FROM test_tbl;
DELETE 2000
postgres=# SELECT COUNT(*) FROM test_tbl;
count
-------
0
(1 row)

postgres=# SELECT instest1();
instest1
----------
0
(1 row)

postgres=# SELECT COUNT(*) FROM test_tbl;
count
-------
10000
(1 row)

統計情報を取得して実行計画を確認します。
データが1万件あるとき、プランナはインデックススキャンを選択していましたが...

postgres=# ANALYZE;
ANALYZE
postgres=# EXPLAIN ANALYZE SELECT * FROM test_tbl WHERE col1 > 1000 AND col1 <=2000;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on test_tbl (cost=0.00..52.00 rows=980 width=9) (actual time=0.065..0.995 rows=1000 loops=1)
Filter: ((col1 > 1000) AND (col1 <= 2000))
Rows Removed by Filter: 9000
Planning time: 0.339 ms
Execution time: 1.039 ms
(5 rows)

統計情報が固定されているため、シーケンシャルスキャンを選択しています。
アンロックすれば本来の統計情報を参照してインデックススキャンに戻ります。

postgres=# SELECT dbms_stats.unlock_database_stats();
unlock_database_stats
-----------------------
pgbench_history
pgbench_tellers
pgbench_accounts
pgbench_branches
pgbench_branches_pkey
pgbench_tellers_pkey
pgbench_accounts_pkey
test_tbl
test_tbl_pkey
(9 rows)

postgres=# EXPLAIN ANALYZE SELECT * FROM test_tbl WHERE col1 > 1000 AND col1 <=2000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Index Scan using test_tbl_pkey on test_tbl (cost=0.29..53.28 rows=1000 width=9) (actual time=0.008..0.144 rows=1000 loops=1)
Index Cond: ((col1 > 1000) AND (col1 <= 2000))
Planning time: 0.187 ms
Execution time: 0.185 ms
(4 rows)

なお、上記ではロック、アンロック共にデータベース単位で行っていますが、
ロック、アンロックの範囲はデータベース・スキーマ・テーブル・列のいずれかの単位で指定することができます。

他にも現在の統計情報を外部ファイルに出力するエクスポートや
エクスポートで作成したファイルから統計情報を読み込むインポートなど
pg_dbms_statsには全部で8つの機能があります。
興味のある方は公式マニュアルをご参照ください。


いかがでしょうか。
意外に簡単な操作で実行計画が制御できると思われたのではないでしょうか。

実行計画の固定はDBの性能を安定させるのに有効な手段の一つです。
しかし、実行計画を固定するとデータのサイズや偏りなどデータの特性が変わった場合でも
実行計画は固定されているので当然、最適化されません。

また、統計情報を取得する頻度や対象に問題があり、
効率の悪い実行計画が選択されている可能性もあります。
こんなとき実行計画を固定して問題を解決すると
根本的な原因を見落としてしまうかもしれません。

リスクを理解して適切に実行計画を制御し、
PostgreSQLを使いこなしていきましょう。

Standby Expressに関するお問合わせ

  • TEL 042-333-6217
  • FAX 042-352-6101
  • LINE
  • Mail