Database Support Blog

  • EDB
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の試使用 を申し込まれている方は、ぜひこの機会に試してみてください。



■本記事の内容について
 本記事に示した定義及び条件は変更される場合があります。あらかじめご了承ください。

■商標に関して
 ・Oracle®、Java、MySQL及びNetSuiteは、Oracle、その子会社及び関連会社の米国及びその他の国における登録商標です。
 ・Amazon Web Services、AWS、Powered by AWS ロゴ、[およびかかる資料で使用されるその他の AWS 商標] は、Amazon.com, Inc. またはその関連会社の商標です。
  文中の社名、商品名等は各社の商標または登録商標である場合があります。

関連している記事

  • EDB
  • PostgreSQL
2024.01.16

EDBがもたらすデータベースの新たな価値 ~ EDB社Field CTO Ajit Gadge氏来日、セミナー講演レポート ~

EDB社のAjit Gadge氏を招き「PostgreSQLユーザーに捧ぐ、EDBを使ったDB機能向上とコスト削減の両立」セミナーを開催しました。DB市場の現状やトレンド、EDBの最新動向について紹介しております。アシストセッションのアーカイブ配信の視聴申し込みも可能です。ぜひご覧ください。

  • PostgreSQL
  • EDB
2023.12.20

PostgreSQLのSQLチューニングを体験してみよう!

35年以上教育事業を展開しているアシストが新たに取り組み始めた「ポスグレ学園」。連載最終回となる9回目の記事では、「PostgreSQL SQLチューニング実践」のワークショップ主管である 田中 健一朗 にインタビューしました。

  • PostgreSQL
  • EDB
2023.10.30

データベースの健康診断! ~ PostgreSQL DB稼働分析体験 ~

35年以上教育事業を展開しているアシストが新たに取り組み始めた「ポスグレ学園」。連載8回目となる今回の記事では、PostgreSQL DB稼働分析ワークショップの主管である保田 公貴にインタービューしました。

ページの先頭へ戻る