Database Support Blog

Database Support Blog>【EDB Postgres】Index Advisorを使ってSQLチューニングを行う方法(CUI編)

  • EDB Postgres
2017.05.22

【EDB Postgres】Index Advisorを使ってSQLチューニングを行う方法(CUI編)

【EDB Postgres】Index Advisorを使ってSQLチューニングを行う方法(CUI編)

PostgreSQLでSQLチューニングを検討する際には、 EXPLAIN ANALYZE 文の結果からどの計画ノードで時間を要しているかなどの確認を進めることが一般的です。そのため、EXPLAIN ANALYZE文の結果を読み解くための前提知識が必要です。

しかし、 EDB Postgres に標準で搭載されている Index Advisor を使えば、このような前提知識がなくとも簡単にSQLチューニングを行うことができます。

Index Advisorを使用する方法は大きく3つありますが、本記事では主にCUIベースでSQLチューニングを行う2つの方法をご紹介します。GUIツール「EDB Postgres Enterprise Manager(PEM)」でIndex Advisorを使用する方法については、また別の機会にご紹介します。

(補足)

EDB Postgresの最新マニュアルは英文のため、EDB PostgresとPostgreSQLで機能やパラメーターの内容に差異がないものは、日本語に翻訳されているPostgreSQLのマニュアルをリンクしています。


Index Advisorとは

Index Advisor は、ある特定のSQLのパフォーマンス改善に必要な索引をアドバイス(提案)してくれる機能です。INSERT、UPDATE、DELETE、SELECT文を対象とします。

Index AdvisorによるSQLの解析時には、索引が存在しない場合と索引を実装した場合の実行コストを比較します。索引が存在しない場合に比べて索引を実装した場合の実行コストが小さければ、解析結果として索引のサイズやCREATE INDEX文(定義文)、実行コストの差分(benefit)などの情報を表示します。

提案される可能性のある索引タイプは、B-tree(単一もしくは複合)索引のみです。その他の索引タイプ(GIN、GiST、Hash)には対応していません。


Index AdvisorをCUIベースで使用する2つの方法

Index AdvisorをCUIベースで使用する2つの方法をご紹介します。

Index Advisorの機能を気軽に試したい方やチューニング対象のSQLがすでに特定できている方には、CUIベースの pg_advise_index コマンドや edb-psql(psql) コマンドを使用する方法をおすすめします。「 障害発生に備えて設定すべき3つのログ関連パラメーター 」の記事でチューニング対象のSQLを絞り込む際に役立つログファイルの設定を紹介していますので、併せてご参考ください。

Index Advisorが提案する索引実装前後の実行計画まで確認した上で比較検討したい方には、pg_advice_index以外の方法をおすすめします。

pg_advise_indexコマンドを使用する方法

pg_advise_index コマンドを使用する方法は、Index Advisorを使用する方法の中で事前作業が最も少なく簡単な方法です。Index Advisorで解析したいSQLを任意のテキストファイルに記入してpg_advise_indexコマンドで読み込ませることによって、Index Advisorが提案する索引のサイズやCREATE INDEX文(定義文)、索引実装前後の実行コストの差分(benefit)などを確認できます。

次の例では、pg_advise_indexを使用して100万件のデータを持つtest表から999行分のデータを参照するSQLのチューニングを行っています。

検証環境

OS:Oracle Enterprise Linux 6.4 64bit
DB:EDB Postgres Advanced Server 9.6.2.7

-- 100万行のデータを持つtest表を作成

edb=# CREATE TABLE test AS SELECT g AS col1,md5(g::text) AS col2
edb-# FROM generate_series(1,1000000) g;
SELECT 1000000

-- test表に対する最新の統計情報を取得

edb=# ANALYZE test;
ANALYZE

-- EXPLAIN ANALYZE文を付けてtest表に対する処理を実行

edb=# EXPLAIN ANALYZE SELECT * FROM test WHERE col1 < 1000;
QUERY PLAN
----------------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..20834.00 rows=1005 width=37) (actual time=0.037..218.184 rows=999 loops=1)
# Seq Scan(フルスキャン)が選択されている
Filter: (col1 < 1000)
Rows Removed by Filter: 999001
Planning time: 0.118 ms
Execution time: 218.473 ms # 実行時間は218ミリ秒

-- pg_advise_indexコマンドで読み込むテキストファイルを作成

$ echo "SELECT * FROM test WHERE col1 < 1000" > test.txt
$ cat test.txt
SELECT * FROM test WHERE col1 < 1000

-- pg_advise_indexコマンドを実行

$ pg_advise_index test.txt
load workload from file 'test.txt'
Analyzing queries . done.
size = 26176 KB, benefit = 20788.000000 # 索引を作成することで20788分のコストが削減されることが予想される
/* 1. test("col1"): size=26176 KB, benefit=20788.00 */
create index "idx_test_col1" on "enterprisedb"."test" ("col1"); # Index Advisorが提案する索引のCREATE INDEX文
/* Total size = 26176KB */ # Index Advisorにより提案された索引の推定サイズは26176 KB

-- Index Advisorで提案された索引を作成

edb=# create index "idx_test_col1" on "enterprisedb"."test" ("col1");
CREATE INDEX

-- EXPLAIN ANALYZE文を付けてtest表に対する処理を実行

edb=# EXPLAIN ANALYZE SELECT * FROM test WHERE col1 < 1000;
QUERY PLAN
------------------------------------------------------------------
Index Scan using idx_test_col1 on test (cost=0.42..42.15 rows=1013 width=37) (actual time=0.074..0.977 rows=999 loops=1) 
# Index Scanが選択され、costも減少している
Index Cond: (col1 < 1000)
Planning time: 10.304 ms
Execution time: 2.031 ms # 実行時間は218ミリ秒から2ミリ秒まで短縮できている

ただし、pg_advise_indexコマンドでは、索引実装前後に手動でEXPLAIN文またはEXPLAIN ANALYZE文を実行しなければ、Index Advisorにより提案された索引実装前後の実行計画などを確認できません。以降で紹介するedb-psqlコマンドを使用する方法であれば、索引実装前にEXPLAIN ANALYZE文を1回実行することで索引実装前後の実行計画と実行時間に関する情報を確認できます。


edb-psqlコマンドを使用する方法

edb-psql(psql) コマンドでIndex Advisorを使用するには、事前に shared_preload_libraries パラメーターや LOAD コマンドでindex_advisorのライブラリファイル(Windowsの場合:index_advisor.dll、Linuxの場合:index_advisor.so)を読み込む必要があります。

また、edb-psqlコマンドを使ってIndex Advisorが提案する索引のCREATE INDEX文(定義文)や実行コストの差分(benefit)などの情報を確認するには、事前にIndex Advisorを利用するデータベース上で$EDBHOME/share/contrib/index_advisor.sqlを実行する必要もあります。このスクリプトを実行することでIndex Advisorが提案した内容や実行履歴などを確認できる各種オブジェクトが作成されます。各オブジェクトの詳細については、 マニュアル をご参考ください。

edb-psqlコマンドでデータベースに接続後にEXPLAIN ANALYZE文を1回実行することで、Index Advisorにより提案された索引実装前の実行計画と実行時間に加えて、索引実装後に予想される実行計画と実行時間に関する情報も確認できます。

次の例では、上述のpg_advise_indexコマンドの検証と同一の環境・同一のテーブル・同一のSQLを使用して、edb-psqlコマンドを使用したSQLチューニングを行っています。

-- Index Advisor関連オブジェクトの作成

edb=# \i /opt/edb/as9.6/share/contrib/index_advisor.sql
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE FUNCTION
CREATE FUNCTION
CREATE VIEW

-- Index Advisorのライブラリファイルの読み込み

edb=# LOAD 'index_advisor.so';
LOAD

-- test表に対する最新の統計情報を取得

edb=# ANALYZE test;
ANALYZE

-- EXPLAIN ANALYZE文を付けてtest表に対する処理を実行

edb=# EXPLAIN ANALYZE SELECT * FROM test WHERE col1 < 1000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on test (cost=0.00..20834.00 rows=947 width=37) (actual time=0.034..209.442 rows=999 loops=1)
# Seq Scan(フルスキャン)が選択されている
Filter: (col1 < 1000)
Rows Removed by Filter: 999001
Execution time: 209.751 ms # 実行時間は209ミリ秒
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: '===[ HYPOTHETICAL PLAN ]==='::text
# 以降はIndex Advisorが提案する索引実装後に想定される実行計画
-> Index Scan using ":1" on test (cost=0.42..44.00 rows=947 width=37) # Index Scanが選択されるようになる
Index Cond: (col1 < 1000)
Execution time: 0.011 ms # 実行時間は209ミリ秒から0.01ミリ秒まで短縮されると予想される

-- index_recommendationsビューを確認

edb=# select * from index_recommendations where show_index_recommendations like '%test%';
 backend_pid |                                             show_index_recommendations
-------------+---------------------------------------------------------------------------------------------------------------------
       21132 | create index idx_test_col1 on enterprisedb.test(col1);/* size: 26176 KB, benefit: 20790, gain: 0.794239072170118 */
# Index Advisorが提案する索引のCREATE INDEX文や索引の推定サイズ、索引実装前後の実行コストの差分(benefit)
# などを確認できる

EXPLAIN ANALYZEの結果から索引を作成することで大幅な処理時間の短縮が期待できるため、index_recommendationsビューから確認したCREATE INDEX文を使用して索引を作成することを検討します。


まとめ

今回はCUIベースのIndex Advisor機能を使用してSQLチューニングを行う方法をご紹介しました。CUIベースのIndex Advisor機能は事前作業が少なく簡単に使用することができますので、EDB Postgres Advanced Serverをご利用の方や弊社に EDB Postgresの試使用 を申し込まれている方は、ぜひこの機会に試してみてください。


執筆者情報

家島 拓也

サービス事業部 サポートセンター

2007年にアシストに入社して以来、ORACLE製品やPostgreSQL・EDB Postgres製品のサポートに従事してきました。このブログではサポート対応で得た知識を元に、お客様がお困りになることが多い問題や各製品の新機能に関する検証結果などを紹介します。


アシスト データベース ウェビナー

関連している記事

  • PostgreSQL
  • EDB Postgres
2019.04.16

【PostgreSQL/EPAS11 新機能】autoprewarmでPostgreSQL再起動後の性能劣化を予防しよう

PostgreSQL/EDB Postgres Advanced Server 11にて、PostgreSQL/EPAS停止前の共有バッファの内容を復元する autoprewarm機能が実装されました。

  • PostgreSQL
  • EDB Postgres
2018.12.12

レプリケーションスロットを使用する際の4つの注意点

レプリケーションスロットを使用する際の4つの注意点を紹介します。

  • PostgreSQL
  • EDB Postgres
2018.10.19

【PostgreSQL/EPAS11 新機能】バックアップと同時にデータ破損チェック!

pg_basebackupにチェックサムの検証機能が付きました。

ページの先頭へ戻る