这篇文章主要讲解了“怎么使用PostgreSQL中的Bloom索引”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“怎么使用PostgreSQL中的Bloom索引”吧！

简介
Bloom Index源于Bloom filter(布隆过滤器),布隆过滤器用于在使用少量的空间的情况下可以很快速的判定某个值是否在集合中,其缺点是存在假阳性False Positives,因此需要Recheck来判断该值是否在集合中,但布隆过滤器不存在假阴性,也就是说,对于某个值如果过滤器返回不存在,那就是不存在.

结构
其结构如下图所示:

第一个page为metadata,然后每一行都会有一个bit array(signature)和TID与其对应.

示例
创建数据表,插入数据

testdb=#droptableifexistst_bloom;DROPTABLEtestdb=#CREATETABLEt_bloom(idint,deptint,id2int,id3int,id4int,id5int,id6int,id7int,detailstext,zipcodeint);CREATETABLEtestdb=#testdb=#INSERTINTOt_bloomtestdb-#SELECT(random()*1000000)::int,(random()*1000000)::int,testdb-#(random()*1000000)::int,(random()*1000000)::int,(random()*1000000)::int,(random()*1000000)::int,testdb-#(random()*1000000)::int,(random()*1000000)::int,md5(g::text),floor(random()*(20000-9999+1)+9999)testdb-#fromgenerate_series(1,16*1024*1024)g;INSERT016777216testdb=#testdb=#analyzet_bloom;ANALYZEtestdb=#testdb=#selectpg_size_pretty(pg_table_size('t_bloom'));pg_size_pretty----------------1619MB(1row)

创建Btree索引

testdb=#testdb=#createindexidx_t_bloom_btreeont_bloomusingbtree(id,dept,id2,id3,id4,id5,id6,id7,zipcode);CREATEINDEXtestdb=#\di+idx_t_bloom_btreeListofrelationsSchema|Name|Type|Owner|Table|Size|Description--------+-------------------+-------+-------+---------+--------+-------------public|idx_t_bloom_btree|index|pg12|t_bloom|940MB|(1row)

执行查询

testdb=#EXPLAINANALYZEselect*fromt_bloomwhereid4=305294andzipcode=13266;QUERYPLAN---------------------------------------------------------------------------------------------------------IndexScanusingidx_t_bloom_btreeont_bloom(cost=0.56..648832.73rows=1width=69)(actualtime=2648.215..2648.215rows=0loops=1)IndexCond:((id4=305294)AND(zipcode=13266))PlanningTime:3.244msExecutionTime:2659.804ms(4rows)testdb=#EXPLAINANALYZEselect*fromt_bloomwhereid5=241326andid6=354198;QUERYPLAN---------------------------------------------------------------------------------------------------------IndexScanusingidx_t_bloom_btreeont_bloom(cost=0.56..648832.73rows=1width=69)(actualtime=2365.533..2365.533rows=0loops=1)IndexCond:((id5=241326)AND(id6=354198))PlanningTime:1.918msExecutionTime:2365.629ms(4rows)

创建Bloom索引

testdb=#createextensionbloom;CREATEEXTENSIONtestdb=#CREATEINDEXidx_t_bloom_bloomONt_bloomUSINGbloom(id,dept,id2,id3,id4,id5,id6,id7,zipcode)testdb-#WITH(length=64,col1=4,col2=4,col3=4,col4=4,col5=4,col6=4,col7=4,col8=4,col9=4);CREATEINDEXtestdb=#\di+idx_t_bloom_bloomListofrelationsSchema|Name|Type|Owner|Table|Size|Description--------+-------------------+-------+-------+---------+--------+-------------public|idx_t_bloom_bloom|index|pg12|t_bloom|225MB|(1row)

执行查询

testdb=#EXPLAINANALYZEselect*fromt_bloomwhereid4=305294andzipcode=13266;QUERYPLAN-------------------------------------------------------------------------------------------------BitmapHeapScanont_bloom(cost=283084.16..283088.18rows=1width=69)(actualtime=998.727..998.727rows=0loops=1)RecheckCond:((id4=305294)AND(zipcode=13266))RowsRemovedbyIndexRecheck:12597HeapBlocks:exact=12235->BitmapIndexScanonidx_t_bloom_bloom(cost=0.00..283084.16rows=1width=0)(actualtime=234.893..234.893rows=12597loops=1)IndexCond:((id4=305294)AND(zipcode=13266))PlanningTime:31.482msExecutionTime:998.975ms(8rows)testdb=#EXPLAINANALYZEselect*fromt_bloomwhereid5=241326andid6=354198;QUERYPLAN-------------------------------------------------------------------------------------------------BitmapHeapScanont_bloom(cost=283084.16..283088.18rows=1width=69)(actualtime=1019.621..1019.621rows=0loops=1)RecheckCond:((id5=241326)AND(id6=354198))RowsRemovedbyIndexRecheck:13033HeapBlocks:exact=12633->BitmapIndexScanonidx_t_bloom_bloom(cost=0.00..283084.16rows=1width=0)(actualtime=204.873..204.873rows=13033loops=1)IndexCond:((id5=241326)AND(id6=354198))PlanningTime:0.441msExecutionTime:1019.811ms(8rows)

从执行结果来看,在查询条件中没有非前导列(上例中为id1)的情况下多列任意组合查询,bloom index会优于btree index.

感谢各位的阅读，以上就是“怎么使用PostgreSQL中的Bloom索引”的内容了，经过本文的学习后，相信大家对怎么使用PostgreSQL中的Bloom索引这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！