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

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ミリ秒まで短縮できている

-- 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)
# などを確認できる