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

What is Hypothetical Indexes
Hypothetical Indexes直译为”假设索引”,是相对于”物理索引”而言的,可以理解为假设存在但实际上物理不存在的索引,其作用在于对SQL的调整和优化.在测试环境,数据量不太大的情况下,可以通过添加实际的索引来对SQL进行调优,但在生产环境,由于添加索引会影响业务和数据库的正常运行,因此需要使用Hypothetical Indexes这种技术假设索引存在,在添加Hypothetical Indexes后,通过观察验证执行计划的变化,如添加的索引合符期望满足需求,则实际添加物理索引,因此有效的降低了试验的成本.

Install
在Github上下载源码,放在contrib目录下,编译&安装

[root@localhostcontrib]#cdhypopg-1.1.3/[root@localhosthypopg-1.1.3]#lsCHANGELOG.mddebianexpectedhypopg.chypopg_index.cincludeMakefileREADME.mdTODO.mdCONTRIBUTORS.mddocshypopg--1.1.3.sqlhypopg.controlimportLICENSEMETA.jsontesttypedefs.list[root@localhosthypopg-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-ohypopg.ohypopg.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-ohypopg_index.ohypopg_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-oimport/hypopg_import.oimport/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-oimport/hypopg_import_index.oimport/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-ohypopg.sohypopg.ohypopg_index.oimport/hypopg_import.oimport/hypopg_import_index.o-L/appdb/xdb/pg12beta1/lib-Wl,--as-needed-Wl,-rpath,'/appdb/xdb/pg12beta1/lib',--enable-new-dtags[root@localhosthypopg-1.1.3]#makeinstall/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-m755hypopg.so'/appdb/xdb/pg12beta1/lib/postgresql/hypopg.so'/usr/bin/install-c-m644.//hypopg.control'/appdb/xdb/pg12beta1/share/postgresql/extension/'/usr/bin/install-c-m644.//hypopg--1.1.3.sql'/appdb/xdb/pg12beta1/share/postgresql/extension/'

创建extension

testdb=#createschemapgextensions;CREATESCHEMAtestdb=#CREATEEXTENSIONhypopgWITHSCHEMApgextensions;CREATEEXTENSION

hypopg extension
首先创建测试表

testdb=#createtablet_hypopg(idint,c1varchar(20));CREATETABLEtestdb=#insertintot_hypopgselectx,'c1'||xfromgenerate_series(1,100000)asx;INSERT0100000

hypopg extension提供了8个函数:

testdb=#selectpronamefrompg_procwherepronamespaceINtestdb-#(selectoidfrompg_namespacewherenspname='pgextensions');proname----------------------hypopg_reset_indexhypopg_resethypopg_create_indexhypopg_drop_indexhypopghypopg_list_indexeshypopg_relation_sizehypopg_get_indexdef(8rows)

1.hypopg_create_index - 创建索引

testdb=#SELECTpgextensions.hypopg_create_index('CREATEINDEXidx_t_hypopg_idont_hypopgUSINGBTREE(id)');indexrelid|indexname------------+--------------------------99425|<99425>btree_t_hypopg_id(1row)testdb=#SELECTpgextensions.hypopg_create_index('CREATEINDEXidx_t_hypopg_idont_hypopgUSINGBTREE(id)');hypopg_create_index----------------------------------(99426,<99426>btree_t_hypopg_id)(1row)

2.hypopg_drop_index - 删除索引

testdb=#selectpgextensions.hypopg_drop_index(99425);hypopg_drop_index-------------------t(1row)

3.hypopg_list_indexes - 列出索引信息

testdb=#selectpgextensions.hypopg_list_indexes();psql:ERROR:functionhypopg()doesnotexistLINE3:FROMhypopg()h^HINT:Nofunctionmatchesthegivennameandargumenttypes.Youmightneedtoaddexplicittypecasts.QUERY:SELECTh.indexrelid,h.indexname,n.nspname,c.relname,am.amnameFROMhypopg()hJOINpg_classcONc.oid=h.indrelidJOINpg_namespacenONn.oid=c.relnamespaceJOINpg_amamONam.oid=h.amidCONTEXT:SQLfunction"hypopg_list_indexes"duringstartuptestdb=#setsearch_path="$user",public,pgextensions;SETtestdb=#selectpgextensions.hypopg_list_indexes();hypopg_list_indexes--------------------------------------------------------(99426,<99426>btree_t_hypopg_id,public,t_hypopg,btree)(1row)

4.hypopg_get_indexdef — 列出索引定义

testdb=#selecthypopg_get_indexdef(99426);hypopg_get_indexdef--------------------------------------------------CREATEINDEXONpublic.t_hypopgUSINGbtree(id)(1row)

5.hypopg_reset_index — 删除索引

testdb=#selecthypopg_reset_index();hypopg_reset_index--------------------(1row)

6.hypopg_reset - 删除所有的索引

testdb=#selecthypopg_reset();hypopg_reset--------------(1row)testdb=#selectpgextensions.hypopg_list_indexes();hypopg_list_indexes---------------------(0rows)testdb=#SELECThypopg_create_index('CREATEINDEXidx_t_hypopg_idont_hypopgUSINGBTREE(id)');hypopg_create_index----------------------------------(99427,<99427>btree_t_hypopg_id)(1row)

7.hypopg - 列出索引原始信息

testdb=#selecthypopg();hypopg------------------------------------------------------------(<99427>btree_t_hypopg_id,99427,99422,1,f,1,0,1978,,,,403)(1row)

8.hypopg_relation_size - 估算索引大小

testdb=#selecthypopg_relation_size(99427);hypopg_relation_size----------------------2605056(1row)testdb=#selectpg_size_pretty(hypopg_relation_size(99427));pg_size_pretty----------------2544kB(1row)

实际使用
在没有索引的情况下,执行查询

testdb=#selecthypopg_reset();hypopg_reset--------------(1row)testdb=#explainverboseselect*fromt_hypopgwhereid=1000;QUERYPLAN-------------------------------------------------------------------SeqScanonpublic.t_hypopg(cost=0.00..1791.00rows=1width=11)Output:id,c1Filter:(t_hypopg.id=1000)(3rows)

PG使用顺序扫描

创建Hypothetical Index : idx_t_hypopg_id,再次使用explain检查查询语句的执行计划:

testdb=#SELECThypopg_create_index('CREATEINDEXidx_t_hypopg_idont_hypopgUSINGBTREE(id)');hypopg_create_index----------------------------------(99429,<99429>btree_t_hypopg_id)(1row)testdb=#testdb=#explainverboseselect*fromt_hypopgwhereid=1000;QUERYPLAN-------------------------------------------------------------------------------------------------IndexScanusing<99429>btree_t_hypopg_idonpublic.t_hypopg(cost=0.04..8.06rows=1width=11)Output:id,c1IndexCond:(t_hypopg.id=1000)(3rows)

在不需要实际创建索引的情况下可以查看创建索引后的执行计划,这是Hypothetical Indexes的价值所在.
值得注意的是,如果explain使用analyze选项,则Hypothetical Indexes无效.

testdb=#explainanalyzeselect*fromt_hypopgwhereid=1000;QUERYPLAN-------------------------------------------------------------------------------------------------------SeqScanont_hypopg(cost=0.00..1791.00rows=1width=11)(actualtime=2.544..98.130rows=1loops=1)Filter:(id=1000)RowsRemovedbyFilter:99999PlanningTime:1.341msExecutionTime:98.193ms(5rows)

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