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

逻辑结构
可以把Hash Index理解为一个Hash Table,每个Hash bucket存储根据Hash Function计算得到的对应的索引条目,为了节省空间,Hash索引条目只存储Hash Code(即Hash Value) + TID而不存储Hash Key(即索引键值),扫描索引后还必须读取相应的数据表行,因此Index Only Scan不适用于Hash Index.

testdb=#droptableifexistst_idx1;DROPTABLEtestdb=#createtablet_idx1(idint,c1varchar(20));CREATETABLEtestdb=#createindexidx_t_idx1_idont_idx1usinghash(id);CREATEINDEXtestdb=#insertintot_idx1selectgenerate_series(1,100000);INSERT0100000testdb=#analyzet_idx1;ANALYZEtestdb=#explainverboseselect*fromt_idx1whereid=1;QUERYPLAN------------------------------------------------------------------------------------IndexScanusingidx_t_idx1_idonpublic.t_idx1(cost=0.00..8.02rows=1width=62)Output:id,c1IndexCond:(t_idx1.id=1)(3rows)testdb=#--不能实现IndexOnlyScantestdb=#explainverboseselectidfromt_idx1whereid=100;QUERYPLAN-----------------------------------------------------------------------------------IndexScanusingidx_t_idx1_idonpublic.t_idx1(cost=0.00..8.02rows=1width=4)Output:idIndexCond:(t_idx1.id=100)(3rows)

而普通的B-Tree索引是可以Index Only Scan的:

testdb=#createtablet_idx2(idint,c1varchar(20));CREATETABLEtestdb=#insertintot_idx2selectgenerate_series(1,100000);INSERT0100000testdb=#createindexidx_t_idx2_idont_idx2usingbtree(id);CREATEINDEXtestdb=#analyzet_idx2;ANALYZEtestdb=#explainverboseselectidfromt_idx2whereid=100;QUERYPLAN----------------------------------------------------------------------------------------IndexOnlyScanusingidx_t_idx2_idonpublic.t_idx2(cost=0.29..8.31rows=1width=4)Output:idIndexCond:(t_idx2.id=100)(3rows)

有四种页面,分别是Meta page,Bucket Page,Overflow page和Bitmap page.

页面类型说明Meta pagepage number zero, which contains information on what is inside the index.Bucket pagesmain pages of the index, which store data as «hash code — TID» pairs.Overflow pagesstructured the same way as bucket pages and used when one page is insufficient for a bucketBitmap pageswhich keep track of overflow pages that are currently clear and can be reused for other buckets

使用pageinspect插件可查看index中的相关信息

testdb=#selecthash_page_type(get_raw_page('idx_t_idx1_id',0));hash_page_type----------------metapage(1row)testdb=#selecthash_page_type(get_raw_page('idx_t_idx1_id',1));hash_page_type----------------bucket(1row)testdb=#\xExpandeddisplayison.testdb=#select*fromhash_page_stats(get_raw_page('idx_t_idx1_id',1));-[RECORD1]---+-----------live_items|189dead_items|0page_size|8192free_size|4368hasho_prevblkno|256hasho_nextblkno|4294967295hasho_bucket|0hasho_flag|2hasho_page_id|65408testdb=#select*fromhash_page_stats(get_raw_page('idx_t_idx1_id',2));-[RECORD1]---+-----------live_items|201dead_items|0page_size|8192free_size|4128hasho_prevblkno|257hasho_nextblkno|4294967295hasho_bucket|1hasho_flag|2hasho_page_id|65408

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