13518219792
本篇内容介绍了“怎么使用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]# 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)
“怎么使用PostgreSQL的Hypothetical Indexes”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联-成都网站建设公司网站，小编将为大家输出更多高质量的实用文章！
