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