PostgreSQL 10.1 手册_部分 II. SQL 语言_第 11 章 索引_11.2. 索引类型

本文涉及的产品
云原生数据库 PolarDB PostgreSQL 版,标准版 2核4GB 50GB
云原生数据库 PolarDB MySQL 版,通用型 2核8GB 50GB
简介: 11.2. 索引类型 PostgreSQL提供了多种索引类型: B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了 一种不同的算法来适应不同类型的查询。

11.2. 索引类型

PostgreSQL提供了多种索引类型: B-tree、Hash、GiST、SP-GiST 、GIN 和 BRIN。每一种索引类型使用了 一种不同的算法来适应不同类型的查询。默认情况下, CREATE INDEX命令创建适合于大部分情况的B-tree 索引。

B-tree可以在可排序数据上的处理等值和范围查询。特别地,PostgreSQL的查询规划器会在任何一种涉及到以下操作符的已索引列上考虑使用B-tree索引:

<
<=
=
>=
>

将这些操作符组合起来,例如BETWEENIN,也可以用B-tree索引搜索实现。同样,在索引列上的IS NULLIS NOT NULL条件也可以在B-tree索引中使用。

优化器也会将B-tree索引用于涉及到模式匹配操作符LIKE~ 的查询,前提是如果模式是一个常量且被固定在字符串的开头—例如:col LIKE 'foo%'或者col ~ '^foo', 但在col LIKE '%bar'上则不会。但是,如果我们的数据库没有使用C区域设置,我们需要创建一个具有特殊操作符类的索引来支持模式匹配查询,参见下面的第 11.9 节。同样可以将B-tree索引用于ILIKE~*,但仅当模式以非字母字符开始,即不受大小写转换影响的字符。

B-tree索引也可以用于检索排序数据。这并不会总是比简单扫描和排序更快,但是总是有用的。

Hash索引只能处理简单等值比较。不论何时当一个索引列涉及到一个使用了=操作符的比较时,查询规划器将考虑使用一个Hash索引。下面的命令将创建一个Hash索引:

CREATE INDEX name ON table USING HASH (column);

GiST索引并不是一种单独的索引,而是可以用于实现很多不同索引策略的基础设施。相应地,可以使用一个GiST索引的特定操作符根据索引策略(操作符类)而变化。作为一个例子,PostgreSQL的标准捐献包中包括了用于多种二维几何数据类型的GiST操作符类,它用来支持使用下列操作符的索引化查询:

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

(这些操作符的含义见第 9.11 节表 62.1中给出了标准发布中所包括的 GiST 操作符类。contrib集合中还包括了很多其他GiST操作符类,可见第 62 章

GiST索引也有能力优化最近邻搜索,例如:

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

它将找到离给定目标点最近的10个位置。能够支持这种查询的能力同样取决于被使用的特定操作符类。 在表 62.1中,Ordering Operators列中列出了可以在这种方法中使用的操作符。

和GiST相似,SP-GiST索引为支持多种搜索提供了一种基础结构。SP-GiST 允许实现众多不同的非平衡的基于磁盘的数据结构,例如四叉树、k-d树和radix树。作为一个例 子,PostgreSQL的标准捐献包中包含了一个用于二维点的SP-GiST操作符类,它用于支持使用下列操作符的索引化查询:

<<
>>
~=
<@
<^
>^

(其含义见第 9.11 节表 63.1中给出了标准发布中所包括的 SP-GiST 操作符类。更多信息参见第 63 章

GIN 索引是倒排索引,它适合于包含多个组成值的数据值,例如数组。倒排索引中为每一个组成值都包含一个单独的项,它可以高效地处理测试指定组成值是否存在的查询。

与 GiST 和 SP-GiST相似, GIN可以支持多种不同的用户定义的索引策略和特定操作符,通过它一个GIN索引可以被根据索引策略被使用。作为一个例子,PostgreSQL的标准贡献包中包含了一个用于数组的GIN操作符类,它用于支持使用下列操作符的索引化查询:

<@
@>
=
&&

(这些操作符的含义见第 9.18 节表 64.1中给出了标准发布中所包括的 GIN 操作符类。在contrib集合中还有更多其他GIN操作符类,更多信息参见第 64 章

BRIN 索引(块范围索引的缩写)存储有关存放在一个表的连续物理块范围上的值摘要信息。与 GiST、SP-GiST 和 GIN 相似,BRIN可以支持很多种不同的索引策略,并且可以与一个 BRIN 索引配合使用的特定操作符取决于索引策略。对于具有线性排序顺序的数据类型,被索引的数据对应于每个块范围的列中值的最小值和最大值,使用这些操作符来支持用到索引的查询:

<
<=
=
>=
>

包括在标准发布中的 BRIN 操作符类的文档在表 65.1中。更多信息请见第 65 章

本文转自PostgreSQL中文社区,原文链接:11.2. 索引类型

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
3月前
|
SQL 存储 关系型数据库
SQL优化策略与实践:组合索引与最左前缀原则详解
本文介绍了SQL优化的多种方式,包括优化查询语句(避免使用SELECT *、减少数据处理量)、使用索引(创建合适索引类型)、查询缓存、优化表结构、使用存储过程和触发器、批量处理以及分析和监控数据库性能。同时,文章详细讲解了组合索引的概念及其最左前缀原则,即MySQL从索引的最左列开始匹配条件,若跳过最左列,则索引失效。通过示例代码,展示了如何在实际场景中应用这些优化策略,以提高数据库查询效率和系统响应速度。
120 10
|
17天前
|
SQL 关系型数据库 PostgreSQL
CTE vs 子查询:深入拆解PostgreSQL复杂SQL的隐藏性能差异
本文深入探讨了PostgreSQL中CTE(公共表表达式)与子查询的选择对SQL性能的影响。通过分析两者底层机制,揭示CTE的物化特性及子查询的优化融合优势,并结合多场景案例对比执行效率。最终给出决策指南,帮助开发者根据数据量、引用次数和复杂度选择最优方案,同时提供高级优化技巧和版本演进建议,助力SQL性能调优。
|
4月前
|
SQL 索引
【YashanDB知识库】字段加上索引后,SQL查询不到结果
【YashanDB知识库】字段加上索引后,SQL查询不到结果
|
8月前
|
关系型数据库 Go 网络安全
go语言中PostgreSQL驱动安装
【11月更文挑战第2天】
335 5
|
5月前
|
SQL 关系型数据库 OLAP
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
126 2
|
存储 SQL 关系型数据库
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 12 章 全文搜索_12.9. GIN 和 GiST 索引类型
12.9. GIN 和 GiST 索引类型 有两种索引可以被用来加速全文搜索。注意全文搜索并非一定需要索引,但是在一个定期会被搜索的列上,通常需要有一个索引。 CREATE INDEX name ON table USING GIN(column); 创建一个基于 GIN(通用倒排索引)的索引。
1684 0
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 8 章 数据类型_8.18. 对象标识符类型
8.18. 对象标识符类型 对象标识符(OID)被PostgreSQL用来在内部作为多个系统表的主键。OID不会被添加到用户创建的表中,除非在创建表时指定了WITH OIDS或者default_with_oids配置变量被启用。
1387 0
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 8 章 数据类型_8.17. 范围类型
8.17. 范围类型 8.17.1. 内建范围类型 8.17.2. 例子 8.17.3. 包含和排除边界 8.17.4. 无限(无界)范围 8.17.5. 范围输入/输出 8.17.6. 构造范围 8.17.7. 离散范围类型 8.17.8. 定义新的范围类型 8.17.9. 索引 8.17.10. 范围上的约束 范围类型是表达某种元素类型(称为范围的subtype)的一个值的范围的数据类型。
1118 0
|
SQL 关系型数据库 PostgreSQL
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 8 章 数据类型_8.16. 复合类型
8.16. 复合类型 8.16.1. 复合类型的声明 8.16.2. 构造组合值 8.16.3. 访问复合类型 8.16.4. 修改复合类型 8.16.5. 在查询中使用复合类型 8.16.6. 复合类型输入和输出语法 一个复合类型表示一行或一个记录的结构,它本质上就是一个域名和它们数据类型的列表。
1070 0
|
存储 JSON 数据库
PostgreSQL 10.1 手册_部分 II. SQL 语言_第 8 章 数据类型_8.14. JSON 类型
8.14. JSON 类型 8.14.1. JSON 输入和输出语法 8.14.2. 有效地设计 JSON 文档 8.14.3. jsonb 包含和存在 8.14.4. jsonb 索引 根据RFC 7159 中的说明,JSON 数据类型是用来存储 JSON(JavaScript Object Notation) 数据的。
1324 0

热门文章

最新文章

推荐镜像

更多