PostgreSQL中几种索引的原理和应用场景

李氏之姊

已于 2022-08-11 14:13:22 修改

阅读量1.1k

点赞数

文章标签：哈希算法算法

于 2022-08-10 16:25:12 首次发布

本文链接：https://blog.csdn.net/xinxinshigehaohaizhi/article/details/126261007

版权

本文详细介绍了PostgreSQL中的多种索引类型，包括B-tree、Hash、GIN、GIST、sp-gist、BRIN和Bitmap。B-tree适合常规查询，Hash索引适用于等值查询长字符串，GIN适合多值类型搜索，GIST为通用索引接口，sp-gist支持空间分区，BRIN是块级索引，而Bitmap适用于大量记录和较少唯一值的场景。不同类型的索引有其特定的应用场景和优势。

摘要由CSDN通过智能技术生成

1.B-tree

原理

示例

qianbase=# create table t_btree(id int, info text);
CREATE TABLE
qianbase=# insert into t_btree select generate_series(1,10000), md5(random()::text) ;
INSERT 0 10000
qianbase=# create index idx_t_btree_1 on t_btree using btree (id);
CREATE INDEX
qianbase=# explain (analyze,verbose,timing,costs,buffers) select * from t_btree where id=1
qianbase-# ;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on public.t_btree  (cost=4.67..81.58 rows=50 width=36) (actual time=0.094..0.095 rows=1 loops=1)
   Output: id, info
   Recheck Cond: (t_btree.id = 1)
   Heap Blocks: exact=1
   Buffers: shared hit=1 read=2
   ->  Bitmap Index Scan on idx_t_btree_1  (cost=0.00..4.66 rows=50 width=0) (actual time=0.089..0.090 rows=1 loops=1)
         Index Cond: (t_btree.id = 1)
         Buffers: shared read=2
 Planning Time: 0.215 ms
 Execution Time: 0.120 ms
(10 rows)

2.hash

原理

hash索引存储的是被索引字段VALUE的哈希值，只支持等值查询。

qianbase=# create table t_hash (id int, info text);
CREATE TABLE
qianbase=# insert into t_hash select generate_series(1,100), repeat(md5(random()::text),10000);
INSERT 0 100

--此时使用btree索引会报错，因为长度超过了1/3的索引页大小
qianbase=# create index idx_t_hash_1 on t_hash using btree (info);
2022-08-09 22:27:23.604 EDT [6723] ERROR:  index row size 3720 exceeds btree version 4 maximum 2704 for index "idx_t_hash_1"
2022-08-09 22:27:23.604 EDT [6723] DETAIL:  Index row references tuple (0,50) in relation "t_hash".
2022-08-09 22:27:23.604 EDT [6723] HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
        Consider a function index of an MD5 hash of the value, or use full text indexing.
2022-08-09 22:27:23.604 EDT [6723] STATEMENT:  create index idx_t_hash_1 on t_hash using btree (info);
ERROR:  index row size 3720 exceeds btree version 4 maximum 2704 for index "idx_t_hash_1"
DETAIL:  Index row references tuple (0,50) in relation "t_hash".
HINT:  Values larger than 1/3 of a buffer page cannot be indexed.
Consider a function index of an MD5 hash of the value, or use full text indexing.


--创建hash索引
qianbase=# create index idx_t_hash_1 on t_hash using hash (info);
CREATE INDEX
qianbase=# set enable_hashjoin=off;
SET
qianbase=#  explain (analyze,verbose,timing,costs,buffers) select * from t_hash where info in (select info from t_hash limit 1);
                                                             QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------
------------
 Nested Loop  (cost=0.03..3.29 rows=1 width=22) (actual time=1.356..62.251 rows=1 loops=1)
   Output: t_hash.id, t_hash.info
   Join Filter: (t_hash.info = t_hash_1.info)
   Rows Removed by Join Filter: 99
   Buffers: shared hit=404
   ->  HashAggregate  (cost=0.03..0.04 rows=1 width=18) (actual time=0.667..0.670 rows=1 loops=1)
         Output: t_hash_1.info
         Group Key: t_hash_1.info
         Buffers: shared hit=3
         ->  Limit  (cost=0.00..0.02 rows=1 width=18) (actual time=0.019..0.020 rows=1 loops=1)
               Output: t_hash_1.info
               Buffers: shared hit=1
               ->  Seq Scan on public.t_hash t_hash_1  (cost=0.00..2.00 rows=100 width=18) (actual time=0.017..0.017 rows
=1 loops=1)