怎么使用PostgreSQL的Hypothetical Indexes

共计 8209 个字符，预计需要花费 21 分钟才能阅读完成。

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

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;
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”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注丸趣 TV 网站，丸趣 TV 小编将为大家输出更多高质量的实用文章！