EDB 的 Index 輔助建議功能 - Index Advisor

EDB 在企業版 EDB Postgres Advanced Server 提供協助建立 INDEX 的工具，主要以提供 B-Tree Index 建議為主，目前還不包含 PostgreSQL 提供的其他類型 INDEX（Hash Index、GiST Index、spGiST Index、GIN Index、BRIN Index）。

Index Advisor 會藉由假定暫時的 Index，對於給定的 SQL 指令，產生不同的 Query Plan，並計算個別 Cost Esitmation，找出最佳的 Index 設置組合，然後輸出建議的 CREATE INDEX 指令。

Index Advisor 只會產生建議 Index 的 SQL 指令（輸出到檔案），不會擅自建立這些 Index。

在此練習使用 Index Advisor 的指令工具 pg_advise_index 以及 psql 下的操作方式。此處使用的版本為 EPAS 9.5 版。

執行之前，要先對準備考慮加入 Index 的表格執行 ANALYZE，進行 Query Planner 要參考的採樣資訊更新，才會有比較貼近現況的建議。

要在資料庫內部啟用 Index Advisor，直接執行安裝的 Script 即可

edb=# \i /opt/PostgresPlus/9.5AS/share/contrib/index_advisor.sql
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE FUNCTION
CREATE FUNCTION
CREATE VIEW

弄好 Index Advisor 相關物件後，可以開始使用。

以下將以一個簡單例子作示範：

在此以用以下的表格以及插入資料

CREATE TABLE tstabcd(
id INT,
intime TIMESTAMP WITH TIME ZONE,
mystr TEXT);

INSERT INTO tstab VALUES (
generate_series(1,100000),
now(),
random()::TEXT );

上面的例子，我們可以預期，在 id 欄位上放一個 Primary Key 比較好。

接著便來看看 Index Advisor 如何使用。

Index Advisor 有兩種模式： psql 下的一個 Session 內進行追蹤，以及指令工具 pg_advise_index。

psql 下操作：要啟動 Index Advisor 進行分析、紀錄前，需要先載入 index_advisor 模組，然後於過程中，Index Advisor 便會將建議紀錄到 index_advisor_log 中，便可以用 show_index_recommandations() 或 index_recommendations 進行查看。

edb=# LOAD 'index_advisor';                                                     
LOAD
edb=# select * from tstabcd where id < 400;
id  |        mystr        |              intime            
-----+---------------------+----------------------------------
  1 | 0.887104642577469   | 22-AUG-16 07:15:32.843803 +00:00
  2 | 0.659782143775374   | 22-AUG-16 07:15:32.843803 +00:00
  3 | 0.966384563129395   | 22-AUG-16 07:15:32.843803 +00:00
  4 | 0.953097302932292   | 22-AUG-16 07:15:32.843803 +00:00
  5 | 0.120846566744149   | 22-AUG-16 07:15:32.843803 +00:00
  6 | 0.559676289092749   | 22-AUG-16 07:15:32.843803 +00:00
  7 | 0.541864807717502   | 22-AUG-16 07:15:32.843803 +00:00
  8 | 0.913167451974005   | 22-AUG-16 07:15:32.843803 +00:00
  9 | 0.864201350603253   | 22-AUG-16 07:15:32.843803 +00:00
 10 | 0.361520271748304   | 22-AUG-16 07:15:32.843803 +00:00
 11 | 0.444020337890834   | 22-AUG-16 07:15:32.843803 +00:00
 12 | 0.067021322902292   | 22-AUG-16 07:15:32.843803 +00:00
 13 | 0.340361757669598   | 22-AUG-16 07:15:32.843803 +00:00
 14 | 0.66826061764732    | 22-AUG-16 07:15:32.843803 +00:00
 15 | 0.350825355388224   | 22-AUG-16 07:15:32.843803 +00:00
 16 | 0.391068525612354   | 22-AUG-16 07:15:32.843803 +00:00
 17 | 0.441221610177308   | 22-AUG-16 07:15:32.843803 +00:00
 18 | 0.921129890717566   | 22-AUG-16 07:15:32.843803 +00:00
 19 | 0.225387694779783   | 22-AUG-16 07:15:32.843803 +00:00
 20 | 0.504998795688152   | 22-AUG-16 07:15:32.843803 +00:00
 21 | 0.368924054317176   | 22-AUG-16 07:15:32.843803 +00:00
edb=# SELECT show_index_recommendations(NULL);
                                               show_index_recommendations                                          
------------------------------------------------------------------------------------------------------------------------
 create index idx_tstabcd_id on enterprisedb.tstabcd(id);/* size: 2624 KB, benefit: 1964.68, gain: 0.748734776566668 */
(1 row)

edb=# select * from index_recommendations;
 backend_pid |                                               show_index_recommendations                                            
-------------+------------------------------------------------------------------------------------------------------------------------
        2855 | create index idx_tstabcd_id on enterprisedb.tstabcd(id);/* size: 2624 KB, benefit: 1964.68, gain: 0.748734776566668 */
(1 row)

Command-Line 下：將需要考慮的 SQL 指令放到檔案中，由 pg_advise_index 載入並將建議的 CREATE INDEX 指令寫到檔案中

[enterprisedb@edbvm ~]$ # 把需要分析的 SQL 寫到檔案裡
[enterprisedb@edbvm ~]$ echo 'select * from tstabcd where id < 400;' >> ~/test.sql
[enterprisedb@edbvm ~]$ # 接著進行分析
[enterprisedb@edbvm ~]$ pg_advise_index -p 5432 -s 100M -o ~/advise.sql ~/test.sql
poolsize = 102400 KB
load workload from file '/home/enterprisedb/test.sql'
Password:
Analyzing queries . done.
size = 2624 KB, benefit = 1964.680000
/* 1. tstabcd("id"): size=2624 KB, benefit=1964.68 */
/* Total size = 2624KB */
[enterprisedb@edbvm ~]$ # 最後查看建議內容
[enterprisedb@edbvm ~]$ cat ~/advise.sql
create index "idx_tstabcd_id" on "enterprisedb"."tstabcd" ("id");

如果認為 Index Advisor 所建議的指令可以用，就執行這些指令即可。

edb=# -- 建立所建議的 Index 之前
edb=# explain select * from tstabcd where id < 400;
                        QUERY PLAN                        
-------------------------------------------------------------
 Seq Scan on tstabcd  (cost=0.00..1986.00 rows=423 width=30)
   Filter: (id < 400)
(2 rows)
edb=# -- 建立所建議的 Index
edb=# \i ~/advise.sql
CREATE INDEX
edb=# explain select * from tstabcd where id < 400;
                                   QUERY PLAN                                  
----------------------------------------------------------------------------------
 Index Scan using idx_tstabcd_id on tstabcd  (cost=0.29..21.20 rows=423 width=30)
   Index Cond: (id < 400)
(2 rows)

以上便是 Index Advisor 的操作。

最後，Index Advisor 也有整合到 EDB 的監控/開發工具 - Postgres Enterprise Manager Client 內，詳情請參考使用手冊。以下為對 pgbench 活動分析的截圖

SQL Profiler 追蹤活動：

Index Advisor GUI 對選取的追蹤活動產生建議：

參考：

EDB™ Postgres (Postgres Plus) Enterprise Edition Guide v9.5 / 8.3 Index Advisor

Postgres Enterprise Manager Getting Started Guide / 11.4 Using the Index Advisor
plProfiler: Performance Timings & Counters for PL/pgSQL Code