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