本文簡單介紹了PostgreSQL的外掛：hypopg。該外掛可在PG中實現不建立索引的情況下模擬存在此索引的執行計劃。

**安裝**
編譯安裝
```
[pg13@k8s-master contrib]$ git clone https://github.com.cnpmjs.org/HypoPG/hypopg.git --branch REL1_STABLE
Cloning into 'hypopg'...
remote: Enumerating objects: 1736, done.
remote: Counting objects: 100% (222/222), done.
remote: Compressing objects: 100% (139/139), done.
remote: Total 1736 (delta 136), reused 138 (delta 72), pack-reused 1514
Receiving objects: 100% (1736/1736), 782.91 KiB | 0 bytes/s, done.
Resolving deltas: 100% (1121/1121), done.
[pg13@k8s-master contrib]$ cd hypopg/
[pg13@k8s-master hypopg]$ ls
CHANGELOG.md  CONTRIBUTORS.md  debian  docs  expected  hypopg--1.3.0.sql  hypopg.c  hypopg.control  hypopg_index.c  import  include  LICENSE  Makefile  META.json  README.md  test  TODO.md  typedefs.list
[pg13@k8s-master hypopg]$ 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 -fPIC -I. -I./ -I/opt/pg13/include/postgresql/server -I/opt/pg13/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 -fPIC -I. -I./ -I/opt/pg13/include/postgresql/server -I/opt/pg13/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 -fPIC -I. -I./ -I/opt/pg13/include/postgresql/server -I/opt/pg13/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 -fPIC -I. -I./ -I/opt/pg13/include/postgresql/server -I/opt/pg13/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 -fPIC -shared -o hypopg.so hypopg.o hypopg_index.o import/hypopg_import.o import/hypopg_import_index.o -L/opt/pg13/lib    -Wl,--as-needed -Wl,-rpath,'/opt/pg13/lib',--enable-new-dtags
[pg13@k8s-master hypopg]$ make install
/bin/mkdir -p '/opt/pg13/lib/postgresql'
/bin/mkdir -p '/opt/pg13/share/postgresql/extension'
/bin/mkdir -p '/opt/pg13/share/postgresql/extension'
/bin/install -c -m 755  hypopg.so '/opt/pg13/lib/postgresql/hypopg.so'
/bin/install -c -m 644 .//hypopg.control '/opt/pg13/share/postgresql/extension/'
/bin/install -c -m 644 .//hypopg--1.3.0.sql  '/opt/pg13/share/postgresql/extension/'
[pg13@k8s-master hypopg]$

```

**體驗**
```
testdb=# create extension hypopg;
CREATE EXTENSION
testdb=# \d t
Did not find any relation named "t".
testdb=# create table t as select x as id,'c1'||x as c1 from generate_series(1,100000) as x;
SELECT 100000
testdb=# explain select * from t where id  = 1000;
                     QUERY PLAN
-----------------------------------------------------
 Seq Scan on t  (cost=0.00..1791.00 rows=1 width=11)
   Filter: (id = 1000)
(2 rows)

testdb=# select hypopg_create_index('create index on t(id)');
    hypopg_create_index
---------------------------
 (13564,<13564>btree_t_id)
(1 row)

testdb=# EXPLAIN SELECT * FROM t WHERE id = 1;
                                  QUERY PLAN
------------------------------------------------------------------------------
 Index Scan using "<13564>btree_t_id" on t  (cost=0.04..8.06 rows=1 width=11)
   Index Cond: (id = 1)
(2 rows)

testdb=# EXPLAIN ANALYZE SELECT * FROM t WHERE id = 1;
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..1791.00 rows=1 width=11) (actual time=0.032..12.943 rows=1 loops=1)
   Filter: (id = 1)
   Rows Removed by Filter: 99999
 Planning Time: 0.082 ms
 Execution Time: 12.996 ms
(5 rows)

testdb=# select hypopg_get_indexdef(13564);
            hypopg_get_indexdef
-------------------------------------------
 CREATE INDEX ON public.t USING btree (id)
(1 row)

testdb=#

```

**參考資料**
[hypopg](https://github.com/HypoPG/hypopg)

PostgreSQL DBA(184) - Extension(hypoPG)

相關文章