本文簡單介紹了PostgreSQL的Hypothetical Indexes,原文詳見[Hypothetical Indexes in PostgreSQL](https://www.percona.com/blog/2019/06/21/hypothetical-indexes-in-postgresql/),有所刪減和調整.

**What is Hypothetical Indexes**
Hypothetical Indexes直譯為"假設索引",是相對於"物理索引"而言的,可以理解為假設存在但實際上物理不存在的索引,其作用在於對SQL的調整和最佳化.在測試環境,資料量不太大的情況下,可以透過新增實際的索引來對SQL進行調優,但在生產環境,由於新增索引會影響業務和資料庫的正常執行,因此需要使用Hypothetical Indexes這種技術假設索引存在,在新增Hypothetical Indexes後,透過觀察驗證執行計劃的變化,如新增的索引合符期望滿足需求,則實際新增物理索引,因此有效的降低了試驗的成本.

**Install**
在Github上下載原始碼,放在contrib目錄下,編譯&安裝
```
[root@localhost contrib]# cd hypopg-1.1.3/
[root@localhost hypopg-1.1.3]# ls
CHANGELOG.md     debian  expected           hypopg.c        hypopg_index.c  include  Makefile   README.md  TODO.md
CONTRIBUTORS.md  docs    hypopg--1.1.3.sql  hypopg.control  import          LICENSE  META.json  test       typedefs.list
[root@localhost hypopg-1.1.3]# make
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -I. -I./ -I/appdb/xdb/pg12beta1/include/postgresql/server -I/appdb/xdb/pg12beta1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o hypopg.o hypopg.c -MMD -MP -MF .deps/hypopg.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -I. -I./ -I/appdb/xdb/pg12beta1/include/postgresql/server -I/appdb/xdb/pg12beta1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o hypopg_index.o hypopg_index.c -MMD -MP -MF .deps/hypopg_index.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -I. -I./ -I/appdb/xdb/pg12beta1/include/postgresql/server -I/appdb/xdb/pg12beta1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o import/hypopg_import.o import/hypopg_import.c -MMD -MP -MF .deps/hypopg_import.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -I. -I./ -I/appdb/xdb/pg12beta1/include/postgresql/server -I/appdb/xdb/pg12beta1/include/postgresql/internal  -D_GNU_SOURCE -I/usr/include/libxml2   -c -o import/hypopg_import_index.o import/hypopg_import_index.c -MMD -MP -MF .deps/hypopg_import_index.Po
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -O0 -DOPTIMIZER_DEBUG -g3 -gdwarf-2 -fPIC -shared -o hypopg.so hypopg.o hypopg_index.o import/hypopg_import.o import/hypopg_import_index.o -L/appdb/xdb/pg12beta1/lib    -Wl,--as-needed -Wl,-rpath,'/appdb/xdb/pg12beta1/lib',--enable-new-dtags  
[root@localhost hypopg-1.1.3]# make install
/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/lib/postgresql'
/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/share/postgresql/extension'
/usr/bin/mkdir -p '/appdb/xdb/pg12beta1/share/postgresql/extension'
/usr/bin/install -c -m 755  hypopg.so '/appdb/xdb/pg12beta1/lib/postgresql/hypopg.so'
/usr/bin/install -c -m 644 .//hypopg.control '/appdb/xdb/pg12beta1/share/postgresql/extension/'
/usr/bin/install -c -m 644 .//hypopg--1.1.3.sql  '/appdb/xdb/pg12beta1/share/postgresql/extension/'

```

建立extension
```
testdb=# create schema pgextensions;
CREATE SCHEMA
testdb=# CREATE EXTENSION hypopg WITH SCHEMA pgextensions;
CREATE EXTENSION
```

**hypopg extension**
首先建立測試表
```
testdb=# create table t_hypopg(id int,c1 varchar(20));
CREATE TABLE
testdb=# insert into t_hypopg select x,'c1'||x from generate_series(1,100000) as x;
INSERT 0 100000
```
hypopg extension提供了8個函式:
```
testdb=# select proname from pg_proc where pronamespace IN
testdb-# (select oid from pg_namespace where nspname = 'pgextensions');
       proname        
----------------------
 hypopg_reset_index
 hypopg_reset
 hypopg_create_index
 hypopg_drop_index
 hypopg
 hypopg_list_indexes
 hypopg_relation_size
 hypopg_get_indexdef
(8 rows)
```

1.hypopg_create_index - 建立索引
```
testdb=# SELECT pgextensions.hypopg_create_index('CREATE INDEX idx_t_hypopg_id on t_hypopg USING BTREE(id)');
 indexrelid |        indexname         
------------+--------------------------
      99425 | <99425>btree_t_hypopg_id
(1 row)
testdb=# SELECT pgextensions.hypopg_create_index('CREATE INDEX idx_t_hypopg_id on t_hypopg USING BTREE(id)');
       hypopg_create_index        
----------------------------------
 (99426,<99426>btree_t_hypopg_id)
(1 row)
```
2.hypopg_drop_index - 刪除索引
```
testdb=# select pgextensions.hypopg_drop_index(99425);
 hypopg_drop_index 
-------------------
 t
(1 row)
```

3.hypopg_list_indexes - 列出索引資訊
```
testdb=# select pgextensions.hypopg_list_indexes();
psql: ERROR:  function hypopg() does not exist
LINE 3:     FROM hypopg() h
                 ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  
    SELECT h.indexrelid, h.indexname, n.nspname, c.relname, am.amname
    FROM hypopg() h
    JOIN pg_class c ON c.oid = h.indrelid
    JOIN pg_namespace n ON n.oid = c.relnamespace
    JOIN pg_am am ON am.oid = h.amid

CONTEXT:  SQL function "hypopg_list_indexes" during startup
testdb=# set search_path = "$user", public, pgextensions;
SET
testdb=# select pgextensions.hypopg_list_indexes();
                  hypopg_list_indexes                   
--------------------------------------------------------
 (99426,<99426>btree_t_hypopg_id,public,t_hypopg,btree)
(1 row)

```

4.hypopg_get_indexdef -- 列出索引定義
```
testdb=# select hypopg_get_indexdef(99426);
               hypopg_get_indexdef                
--------------------------------------------------
 CREATE INDEX ON public.t_hypopg USING btree (id)
(1 row)
```

5.hypopg_reset_index -- 刪除索引
```
testdb=# select hypopg_reset_index();
 hypopg_reset_index 
--------------------
 
(1 row)
```

6.hypopg_reset - 刪除所有的索引
```
testdb=# select hypopg_reset();
 hypopg_reset 
--------------
 
(1 row)
testdb=# select pgextensions.hypopg_list_indexes();
 hypopg_list_indexes 
---------------------
(0 rows)
testdb=# SELECT hypopg_create_index('CREATE INDEX idx_t_hypopg_id on t_hypopg USING BTREE(id)');
       hypopg_create_index        
----------------------------------
 (99427,<99427>btree_t_hypopg_id)
(1 row)
```

7.hypopg - 列出索引原始資訊
```
testdb=# select hypopg();
                           hypopg                           
------------------------------------------------------------
 (<99427>btree_t_hypopg_id,99427,99422,1,f,1,0,1978,,,,403)
(1 row)
```

8.hypopg_relation_size - 估算索引大小
```
testdb=# select hypopg_relation_size(99427);
 hypopg_relation_size 
----------------------
              2605056
(1 row)

testdb=# select pg_size_pretty(hypopg_relation_size(99427));
 pg_size_pretty 
----------------
 2544 kB
(1 row)
```

**實際使用**
在沒有索引的情況下,執行查詢
```
testdb=# select hypopg_reset();
 hypopg_reset 
--------------
 
(1 row)

testdb=# explain verbose select * from t_hypopg where id = 1000;
                            QUERY PLAN                             
-------------------------------------------------------------------
 Seq Scan on public.t_hypopg  (cost=0.00..1791.00 rows=1 width=11)
   Output: id, c1
   Filter: (t_hypopg.id = 1000)
(3 rows)
```
PG使用順序掃描

建立Hypothetical Index : idx_t_hypopg_id,再次使用explain檢查查詢語句的執行計劃:
```
testdb=# SELECT hypopg_create_index('CREATE INDEX idx_t_hypopg_id on t_hypopg USING BTREE(id)');
       hypopg_create_index        
----------------------------------
 (99429,<99429>btree_t_hypopg_id)
(1 row)

testdb=# 
testdb=# explain verbose select * from t_hypopg where id = 1000;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Index Scan using <99429>btree_t_hypopg_id on public.t_hypopg  (cost=0.04..8.06 rows=1 width=11)
   Output: id, c1
   Index Cond: (t_hypopg.id = 1000)
(3 rows)

```
在不需要實際建立索引的情況下可以檢視建立索引後的執行計劃,這是Hypothetical Indexes的價值所在.
值得注意的是,如果explain使用analyze選項,則Hypothetical Indexes無效.
```
testdb=# explain analyze select * from t_hypopg where id = 1000;
                                              QUERY PLAN                                               
-------------------------------------------------------------------------------------------------------
 Seq Scan on t_hypopg  (cost=0.00..1791.00 rows=1 width=11) (actual time=2.544..98.130 rows=1 loops=1)
   Filter: (id = 1000)
   Rows Removed by Filter: 99999
 Planning Time: 1.341 ms
 Execution Time: 98.193 ms
(5 rows)
```

**參考資料**
[Hypothetical Indexes in PostgreSQL](https://www.percona.com/blog/2019/06/21/hypothetical-indexes-in-postgresql/)

PostgreSQL DBA(45) - Hypothetical Indexes in PostgreSQL

相關文章