Kane BlueriverKane Blueriver

PostgreSQL 的索引类型

PostgreSQL 提供了多种索引类型:B 树、hash、GiST 和 GIN,每种索引类型都有适合的应用场景,可以根据场景选择合适的索引以提高效率。

B 树

B 树适合相等判断和有序的区间查询,通常来说,经常使用这些查询时推荐使用 B 树索引: <><=>= 以及 =

此外,对于判断字符串开头的 LIKE~ 查询也可以考虑使用 B 树索引。比如: col LIKE 'foo%' 或者 col ~ '^foo'

B 树对内容进行了排序,并不保证总是快于遍历查询,但大多数情况下都很有效率。

hash 索引

hash 索引只适合相等判断,对于 = 以外的查询并没有优化效果。

GiST 索引

GiST 并不是一种索引,更像是多种索引策略的集合。PostgreSQL 默认的 GiST 支持多种二维几何图形,并且支持一下比较查询:

  • <<
  • &<
  • &>
  • >>
  • <<|
  • &<|
  • |&>
  • |>>
  • @>
  • <@
  • ~=
  • &&>

GiST 索引也支持“邻近查询”,比如:

SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;

GIN

GIN 是一种反向索引,可以处理拥有多个 key 的数据,比如数组。和 GiST 和 SP-GiST 一样,GIN 支持多种不同用户定义的索引策略,根据索引策略的不同,能够优化的操作符也不尽相同。

比如,PostgreSQL 的标准实现中包含了支持一维数组 GIN 操作类,支持这些操作符的查询优化:

  • <@
  • @>
  • =
  • &&

(这些操作符的意义见 9.18)标准实现中的 GIN 操作类更多课件文档 Table 61-1,更多 GIN 操作类可以在 contrib 集合中找到,第 61 章会更详细地介绍。

BRIN 索引(Block Range Index)存储了物理区块值概要。和 GiST、SP-GIST 和 GIN 一样,BRIN 也支持多种不同的索引策略,能够使用的操作类也依索引策略而定。对于线性短顺序,索引的数据相当于块区间行内最小和最大值。支持下面这些操作符的优化:

  • <
  • <=
  • =
  • >=
  • >

标准实现的 BRIN 操作类介绍见 Table 62-1,详情见 62 章。

参考:

官方文档