本文简单介绍了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=# \df hypopg_* List of functions Schema | Name | Result data type | Argument data types | Type --------+----------------------+------------------+--------------------------------------------------------+------ public | hypopg_create_index | SETOF record | sql_order text, OUT indexrelid oid, OUT indexname text | func public | hypopg_drop_index | boolean | indexid oid | func public | hypopg_get_indexdef | text | indexid oid | func public | hypopg_relation_size | bigint | indexid oid | func public | hypopg_reset | void | | func public | hypopg_reset_index | void | | func (6 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)