這篇文章主要講解了“怎么使用PostgreSQL中的Bloom索引”,文中的講解內(nèi)容簡(jiǎn)單清晰,易于學(xué)習(xí)與理解,下面請(qǐng)大家跟著小編的思路慢慢深入,一起來(lái)研究和學(xué)習(xí)“怎么使用PostgreSQL中的Bloom索引”吧!
成都創(chuàng)新互聯(lián)公司是一家集網(wǎng)站建設(shè),赤壁企業(yè)網(wǎng)站建設(shè),赤壁品牌網(wǎng)站建設(shè),網(wǎng)站定制,赤壁網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷,網(wǎng)絡(luò)優(yōu)化,赤壁網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè),幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力。可充分滿足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿,時(shí)刻以成就客戶成長(zhǎng)自我,堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己,讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。簡(jiǎn)介
Bloom Index源于Bloom filter(布隆過(guò)濾器),布隆過(guò)濾器用于在使用少量的空間的情況下可以很快速的判定某個(gè)值是否在集合中,其缺點(diǎn)是存在假陽(yáng)性False Positives,因此需要Recheck來(lái)判斷該值是否在集合中,但布隆過(guò)濾器不存在假陰性,也就是說(shuō),對(duì)于某個(gè)值如果過(guò)濾器返回不存在,那就是不存在.
結(jié)構(gòu)
其結(jié)構(gòu)如下圖所示:
第一個(gè)page為metadata,然后每一行都會(huì)有一個(gè)bit array(signature)和TID與其對(duì)應(yīng).
示例
創(chuàng)建數(shù)據(jù)表,插入數(shù)據(jù)
testdb=# drop table if exists t_bloom; DROP TABLE testdb=# CREATE TABLE t_bloom (id int, dept int, id2 int, id3 int, id4 int, id5 int,id6 int,id7 int,details text, zipcode int); CREATE TABLE testdb=# testdb=# INSERT INTO t_bloom testdb-# 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-# from generate_series(1,16*1024*1024) g; INSERT 0 16777216 testdb=# testdb=# analyze t_bloom; ANALYZE testdb=# testdb=# select pg_size_pretty(pg_table_size('t_bloom')); pg_size_pretty ---------------- 1619 MB (1 row)
創(chuàng)建Btree索引
testdb=# testdb=# create index idx_t_bloom_btree on t_bloom using btree(id,dept,id2,id3,id4,id5,id6,id7,zipcode); CREATE INDEX testdb=# \di+ idx_t_bloom_btree List of relations Schema | Name | Type | Owner | Table | Size | Description --------+-------------------+-------+-------+---------+--------+------------- public | idx_t_bloom_btree | index | pg12 | t_bloom | 940 MB | (1 row)
執(zhí)行查詢
testdb=# EXPLAIN ANALYZE select * from t_bloom where id4 = 305294 and zipcode = 13266; QUERY PLAN --------------------------------------------------------------------------------------------------------- Index Scan using idx_t_bloom_btree on t_bloom (cost=0.56..648832.73 rows=1 width=69) (actual time=2648.215..2648.215 rows=0 loops=1) Index Cond: ((id4 = 305294) AND (zipcode = 13266)) Planning Time: 3.244 ms Execution Time: 2659.804 ms (4 rows) testdb=# EXPLAIN ANALYZE select * from t_bloom where id5 = 241326 and id6 = 354198; QUERY PLAN --------------------------------------------------------------------------------------------------------- Index Scan using idx_t_bloom_btree on t_bloom (cost=0.56..648832.73 rows=1 width=69) (actual time=2365.533..2365.533 rows=0 loops=1) Index Cond: ((id5 = 241326) AND (id6 = 354198)) Planning Time: 1.918 ms Execution Time: 2365.629 ms (4 rows)
創(chuàng)建Bloom索引
testdb=# create extension bloom; CREATE EXTENSION testdb=# CREATE INDEX idx_t_bloom_bloom ON t_bloom USING bloom(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); CREATE INDEX testdb=# \di+ idx_t_bloom_bloom List of relations Schema | Name | Type | Owner | Table | Size | Description --------+-------------------+-------+-------+---------+--------+------------- public | idx_t_bloom_bloom | index | pg12 | t_bloom | 225 MB | (1 row)
執(zhí)行查詢
testdb=# EXPLAIN ANALYZE select * from t_bloom where id4 = 305294 and zipcode = 13266; QUERY PLAN ------------------------------------------------------------------------------------------------- Bitmap Heap Scan on t_bloom (cost=283084.16..283088.18 rows=1 width=69) (actual time=998.727..998.727 rows=0 loops=1) Recheck Cond: ((id4 = 305294) AND (zipcode = 13266)) Rows Removed by Index Recheck: 12597 Heap Blocks: exact=12235 -> Bitmap Index Scan on idx_t_bloom_bloom (cost=0.00..283084.16 rows=1 width=0) (actual time=234.893..234.893 rows=12597 loops=1) Index Cond: ((id4 = 305294) AND (zipcode = 13266)) Planning Time: 31.482 ms Execution Time: 998.975 ms (8 rows) testdb=# EXPLAIN ANALYZE select * from t_bloom where id5 = 241326 and id6 = 354198; QUERY PLAN ------------------------------------------------------------------------------------------------- Bitmap Heap Scan on t_bloom (cost=283084.16..283088.18 rows=1 width=69) (actual time=1019.621..1019.621 rows=0 loops=1) Recheck Cond: ((id5 = 241326) AND (id6 = 354198)) Rows Removed by Index Recheck: 13033 Heap Blocks: exact=12633 -> Bitmap Index Scan on idx_t_bloom_bloom (cost=0.00..283084.16 rows=1 width=0) (actual time=204.873..204.873 rows=13033 loops=1) Index Cond: ((id5 = 241326) AND (id6 = 354198)) Planning Time: 0.441 ms Execution Time: 1019.811 ms (8 rows)
從執(zhí)行結(jié)果來(lái)看,在查詢條件中沒有非前導(dǎo)列(上例中為id1)的情況下多列任意組合查詢,bloom index會(huì)優(yōu)于btree index.
感謝各位的閱讀,以上就是“怎么使用PostgreSQL中的Bloom索引”的內(nèi)容了,經(jīng)過(guò)本文的學(xué)習(xí)后,相信大家對(duì)怎么使用PostgreSQL中的Bloom索引這一問(wèn)題有了更深刻的體會(huì),具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián),小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章,歡迎關(guān)注!
網(wǎng)站名稱:怎么使用PostgreSQL中的Bloom索引-創(chuàng)新互聯(lián)
本文地址:http://chinadenli.net/article26/cejcjg.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供品牌網(wǎng)站建設(shè)、外貿(mào)建站、網(wǎng)站設(shè)計(jì)、商城網(wǎng)站、網(wǎng)站制作、電子商務(wù)
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請(qǐng)盡快告知,我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如需處理請(qǐng)聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時(shí)需注明來(lái)源: 創(chuàng)新互聯(lián)
猜你還喜歡下面的內(nèi)容