Postgresql索引

原创于 2025-08-24 10:14:20 发布 · 773 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #数据库

数据库同时被 2 个专栏收录

9 篇文章

订阅专栏

postgresql

2 篇文章

订阅专栏

在Postgresql中，索引类型大致有以下几种：
B-tree，Hash，Gist，SP-Gist，Gin，BRin
Postgresql默认使用B-Tree创建索引，创建索引的语法如下：
create index idx_name on tb_name(col_name);
例如，为num表中的id字段创建索引：
在这里插入图片描述
如果想使用hash类型的索引：
create index idx_name on tb_name using hash(col_name);
例如：

Btree

B -Tree适合按照顺序存储的数据进行比较查询和范围查询。具体而言，在索引字段上使用<，<=，=，>，>=运算符，使用between and，使用is null和is not null，PostgreSQL 会使用
Btree索引。除此之外，模糊查询也可以使用Btree索引，但是开头必须是字符常量。例如’zhangsan%‘，如果使用’%zhangsan’则会导致索引失效。
为num表插入100000条数据：insert into num values(generate_series(1,100000));
开启分析：analyze num;
不使用索引：
在这里插入图片描述

可以看到cost的第二个数字为1693.00，代表返回第一行数据的代价。
使用索引：
在这里插入图片描述
可以看到cost的第二个数字为4.31，代价比不使用索引低的多，因此索引对查询效率的提升非常巨大。
Hash

只能处理简单的等值比较，当索引列涉及到等于操作比较时，优化器会考虑使用Hash索引。Hash索引是通过比较hash值来查找定位，如果hash索引列的数据重复度比较高，容易产生严重的hash冲突，从而降低查询效率，因此这种情况下，不适合hash索引。

不使用hash索引：
在这里插入图片描述
使用hash索引：

hash失效的情况：

可以看到，hash索引只能处理等值比较的情况，不能处理范围查询的情况

Gist
不是独立的索引类型，是一种架构或者索引模板，是一棵平衡二叉树。适用于多维数据类型和集合数据类型，和Btree索引类似，同样适用于其他的数据类型。GiST可以用来做位置搜索，如包含、相交、左边、右边等。和Btree索引相比，GiST多字段索引在查询条件中包含索引字段的任何子集都会使用索引扫描，而Btree索引只有查询条件包含第一个索引字段才会使用索引扫描。GiST索引特定操作符类型高度依赖于索引策略（操作符类）。GiST跟Btree索引相比，索引创建耗时较长，占用空间也比较大。
不使用Gist索引，只创建Btree索引：
在这里插入图片描述

Btree索引失效的情况：

创建Gist索引架构

使用Btree索引：
在这里插入图片描述
Btree索引失效，使用Gist索引：

SP-Gist

和GiST类似，但是是一棵不平衡树，支持多维和海量数据，把空间分割成互不相交的部分。SP-GiST适用于空间可以递归分割成不相交区域的结构，包括四叉树、k-D树和基数树。
GIN

倒排序索引，适合于包含多个组成值的数据，比如数组，全文检索等。用来保存一个键值对的集合，支持用户定义的索引策略，对于不同的索引策略，可以使用不同的操作符。

键值对（Key，postion list）：其中Key是一个键值，而postion list是包含Key的位置值。比如（‘Bob’，‘10:25 14:3 29:5’）就表示关键字’Bob’在这些位置（元组TID）上存在。当我们用关键字’Bob’去查询的时候，一下就定位到包含关键字的元组有这三个。

创建GIN索引：
在这里插入图片描述
使用GIN索引

BRIN索引

块范围索引，它将数据在磁盘上的block按照一定的数目进行分组，分组之后，计算每组的取值范围。在查找数据时，会遍历这些取值范围，排除掉不在范围之内的分组。BRIN索引适用于存储流式数据日志。例如：按照时间插入的数据，由于数据是按照时间插入，因此数据块上记录的范围信息很少会出现交叉情况，索引过滤后需要比较的数据块也会少很多；反之，如果数据交叉严重，通过索引无法过滤掉任何一个数据块时，操作起来会比全表扫描更加耗时。