本文简单介绍了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