怎么使用PostgreSQL的Hypothetical Indexes

本篇内容介绍了"怎么使用PostgreSQL的Hypothetical Indexes"的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！

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]# lsCHANGELOG.md     debian  expected           hypopg.c        hypopg_index.c  include  Makefile   README.md  TODO.mdCONTRIBUTORS.md  docs    hypopg--1.1.3.sql  hypopg.control  import          LICENSE  META.json  test       typedefs.list[root@localhost hypopg-1.1.3]# makegcc -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.Pogcc -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.Pogcc -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.Pogcc -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.Pogcc -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 SCHEMAtestdb=# CREATE EXTENSION hypopg WITH SCHEMA pgextensions;CREATE EXTENSION

hypopg extension
首先创建测试表

testdb=# create table t_hypopg(id int,c1 varchar(20));CREATE TABLEtestdb=# 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 INtestdb-# (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 existLINE 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.amidCONTEXT:  SQL function "hypopg_list_indexes" during startuptestdb=# set search_path = "$user", public, pgextensions;SETtestdb=# 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)

"怎么使用PostgreSQL的Hypothetical Indexes"的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注网站，小编将为大家输出更多高质量的实用文章！