亚马逊 Aurora PostgreSQL 和亚马逊 RDS for PostgreSQL 支持的索

• 2026-01-27 12:20:09

在 Amazon Aurora PostgreSQL 和 Amazon RDS for PostgreSQL 中支持的索引类型

重点摘录

PostgreSQL 提供多种索引类型，包括 Btree、GiST、GIN、HASH 和 BRIN，以满足不同的用例和优化查询性能。GIN 索引适合处理 JSONB、数组和全文搜索，GiST 索引适用于区间查询和地理空间数据。HASH 索引主要用于等值查询，而 BRIN 索引则适合时间序列数据，节省存储空间。了解应用程序的更新频率、数据特性及查询需求，以选择合适的索引类型。

在 PostgreSQL 中，索引是高效访问特定数据记录的关键，因为它们基于指定列中的值。为了满足不同用例和优化查询性能，PostgreSQL 提供了多种索引类型，其中一些常见的索引类型包括：Btree、GiST广义搜索树、GIN广义倒排索引、HASH 和 BRIN块范围索引。此外，PostgreSQL 的索引基础设施是可扩展的，允许开发人员创建定制的索引类型以适应特定需求。通过使用运算符类，这些类定义了特定数据类型和操作的索引行为。

在本系列的 第一部分，我们详细讨论了 Btree 索引及其各种用例。在此文中，我们将讨论在 Amazon Aurora PostgreSQL 兼容版 和 Amazon RDS for PostgreSQL 中支持的其他原生索引，包括 GIN、GiST、HASH 和 BRIN，以及它们的用例。

在以下章节中，我们将讨论在创建索引时应依据数据检索需求和用例所采用的策略。

前提条件

要继续阅读，您需要拥有一个 RDS for PostgreSQL 或 Aurora PostgreSQL 数据库，以及安装了 psql 的客户端机器，并连接到该数据库。有关更多信息，请参阅 连接到运行 PostgreSQL 数据库引擎的数据库实例。

请查看以下脚本以生成样本数据集。此脚本为一个员工表生成样本数据，其中包含不同数据类型的多个列，例如字符字符串、整数、日期、数组、JSONB 等。通过使用 PostgreSQL 函数 RANDOM()、REPEAT() 和 GENERATESERIES() 来生成随机样本数据，这些数据将在本篇文章中使用。

sqlCREATE TABLE employee ( empname CHARACTER VARYING COLLATE C empid BIGINT empsalary BIGINT empdept CHARACTER VARYING empdoj DATE emplwd DATE empage INTEGER empcities TEXT empaddresshist JSONB empcitylist TEXT[] emptenure TSRANGE )

INSERT INTO employeeSELECTsubstr(REPEAT(abexoptkhngsadl3)((random()(301)1)integer)5) AS empnameI AS empidTRUNC(RANDOM() 10000) AS empsalaryUPPER(substr(REPEAT(REVERSE(abexoptkhngsadl)3)((random()(301)1)integer)4)) AS empdept 1/1/2016DATE (1 YEARINTERVAL FLOOR(RANDOM()5)) AS empdoj 1/1/2020DATE (2 YEARINTERVAL FLOOR(RANDOM()6)) AS emplwdfloor(random() (39301) 30)int AS empageCITYICITYXCITYI2 AS empcitiesTOJSONB(ROW(ITEXT CITYICITYXCITYI2)) AS empaddresshistARRAY[CONCATWS(CITYI)CITYXCONCATWS(CITYI2)] AS empcitylist([2015I12I30 2017I12I30))TSRANGE AS emptenureFROM GENERATESERIES(1 5000000) AS IWHERE i12 BETWEEN 1 AND 13 AND i30 BETWEEN 1 AND 28

GIN 索引

广义倒排GIN索引通常用于索引的项为复合值且需要搜索位于复合项中的元素的情况。它最适合用于处理 JSONB、数组和 全文搜索 数据类型及其特性。

GIN 索引还使用多种运算符类，基于内容以及你所使用的数据类型和运算符类。例如，jsonbops、gintrgmops 等。运算符类定义了索引在该列上使用的运算符。存在运算符类的主要原因是对于某些数据类型，可能会有多种有意义的索引行为。

此外，GIN 索引仅支持位图索引扫描，这是因为它们仅存储每个索引页中的行值部分。位图索引扫描意味着数据库正在执行树遍历，并跟随叶节点链以找到所有匹配的条目。一旦数据库找到匹配条目，它将根据表中行的物理存储位置对行进行排序并从表中获取该行。这最后一步称为位图堆扫描。

让我们查看一些 GIN 索引的使用案例。

全文搜索

“全文搜索”用于对文档如文章、网站或其他书面格式执行自然语言搜索。PostgreSQL 具有特定的数据类型和内置函数用于处理全文搜索。您可以利用 GIN 索引加速全文搜索。全文搜索使用内置的 totsvector 和 totsquery PostgreSQL 函数。tsvector 类型用于表示可全文搜索的文档，而 tsquery 类型用于以文本查询格式表示文本查询。

以下查询搜索 empcities 中含有“city4”和“city0”的记录：

sqlEXPLAIN ANALYZE SELECT FROM employee WHERE totsvector(englishempcities) @@ totsquery(englishcity4 amp city0)

JSONB

GIN 索引也对加快 JSON 数据查询非常有用。在 PostgreSQL 中，有两个可用的运算符类：jsonbops 和 jsonbpathops。在创建 GIN 索引时，默认使用 jsonbops，当未指定运算符类时便会如此。

这里我们利用先前的 employee 表的示例，该表中的城市列表以 JSONB 格式存储：

sqlEXPLAIN ANALYZE SELECT jsonbpretty(empaddresshist) FROM employee WHERE empaddresshist @gt {f2 city4cityXcity0}

现在，我们在 empaddresshist 列上创建一个 GIN 索引并重新执行相同的查询：

sqlCREATE INDEX idxempginjsonb ON employee USING gin(empaddresshist)EXPLAIN ANALYZE SELECT jsonbpretty(empaddresshist) FROM employee WHERE empaddresshist @gt {f2 city4cityXcity0}

数组

PostgreSQL 允许在表中将列定义为可变长度的多维数组。您可以创建任何内置或用户定义的基本类型、枚举类型、复合类型、范围类型或域的数组。您可以使用 GIN 索引加速在数组中查找元素。

让我们考虑同一个 employee 表，其中 empcitylist 存储了数组形式的数据。我们查询该数组以获取在“city5”中的员工详情：

sqlEXPLAIN ANALYZE SELECT FROM employee WHERE empcitylist @gt ARRAY[city5]text[]

现在我们在 empcitylist 列上创建一个 GIN 索引，并重新运行上面的查询：

sqlCREATE INDEX idxempginarray ON employee USING gin(empcitylist)EXPLAIN ANALYZE SELECT FROM employee WHERE empcitylist @gt ARRAY[city5]text[]

在所有这些案例中，我们都看到使用 GIN 索引时查询执行时间得到了改善。尽管 GIN 索引可以加速各种数据选择操作，但在 GIN 索引中的数据更新相对较慢。了解更新频率对于判断使用 GIN 索引是否为最佳方案至关重要。

GIN 索引提供了一些参数，比如在索引创建时或后续更新时可以指定的 fastupdate。该参数要求 PostgreSQL 引擎在一个单独的无序列表中累积更新，并仅在累积更新的数量足够大时如在 ginpendinglistlimit 配置参数中定义更新索引。

在以下情况下，使用 fastupdate 的 GIN 索引可能会带来优势：

频繁更新：如果您的应用程序频繁更新已索引数据，启用 fastupdate 则可显著减少索引维护的开销。大数据集：对于进行频繁更新时整个索引重建不实际或过于缓慢的大数据集，fastupdate 可提供性能收益。高写入吞吐量：在高写入吞吐量的情形中，例如实时数据摄取或日志系统，fastupdate 可帮助保持可接受的性能水平。混合负载：如果您的应用程序同时存在读取和写入操作，并且写入操作显著多于读取操作，fastupdate 可以确保索引维护不会对写入性能产生过度影响。

GiST 索引

您可以在 PostgreSQL 中使用广义搜索树GiST索引来处理多种类型的查询和运算符，例如相等运算符、范围查询和部分匹配。GiST 索引主要用于与涉及某种边界的范围查询、全文搜索和地理空间数据相关的情况。

与 GIN 索引一样，GiST 也支持多种运算符类来扩展其用法，例如 gisttrgmops、gistintops 等。

GiST 支持的另一个重要特性是排除约束。排除约束确保两个表行的给定字段在某些运算符下不彼此对应。排除约束比较表中的两行，用户可以给出条件，例如“没有两行重叠”或“没有两行可以是不同的”。

全文搜索

与 GIN 索引一样，我们也可以使用 GiST 索引进行全文搜索。在接下来的代码中，我们使用 employee 表并创建 GiST 索引，而不是 GIN 索引，并重新执行相同的查询：

sqlEXPLAIN ANALYZE SELECT FROM employee WHERE totsvector(englishempcities) @@ totsquery(englishcity4 amp city0)CREATE INDEX idxempgistfts ON employee USING GIST (totsvector(english empcities))EXPLAIN ANALYZE SELECT FROM employee WHERE totsvector(englishempcities) @@ totsquery(englishcity4 amp city0)

正如我们所见，在进行全文搜索时，GiST 索引的性能与 GIN 索引相当，但由于 GIN 索引提供了更快的响应速度，因此处理文本搜索时更为优先，GiST 索引可能会出现损失性，但您可以重排序结果，可以使用 @@ 运算符配合排名函数。如前所述，如果您的表频繁更新则使用 GIN 索引的成本相对较高。因此，在选择进行全文搜索的索引时，您应根据应用场景的需求进行考虑。

范围类型

GiST 索引对于处理时间区间数据类型如 tsrange的查询亦非常有用。为了更好地理解它，让我们考虑员工表并搜索 员工 任职期间的详细信息：

sqlEXPLAIN ANALYZE SELECT FROM employee WHERE emptenure ampamp [20190101 20190401)CREATE index idxempgisttenurerange on employee USING GIST(emptenure)EXPLAIN ANALYZE SELECT FROM employee WHERE emptenure ampamp [20190101 20190401)

HASH 索引

HASH 索引存储从被索引列的值派生的 32 位哈希代码，因此它可以有 21 个唯一哈希代码。您应仅在索引列涉及相等运算符=时考虑使用 HASH 索引。它是对仅使用 = 运算符的查找条件的 Btree 索引的常见替代方案。

HASH 索引仅存储 4 字节的哈希值，而不是实际的列值，因此，HASH 索引的大小比 Btree 索引小得多。

HASH 索引的许多用例包括：相等查找您需要在索引列中查找特定值的行、唯一约束执行、联结操作在优化某些类型的联结操作，特别是哈希联结时可能十分有用等。

为了用示例理解 HASH 索引，这里我们对数组中的元素执行查找。在 PostgreSQL 中，您可以直接在数组上创建索引，而如果主要的应用程序运算符仅涉及 = 运算符，则 HASH 索引的性能优于 GIN 或 Btree 索引。

为此我们创建一个复合类型数组，并创建 Btree、GIN 和 HASH 索引。然后我们检查对复合类型使用 = 运算符的各索引类型的成本：

银河加速器官网

sqlCREATE TYPE comptype AS (id1 int id2 int)CREATE TABLE idxdemotable (sn INT ids comptype[])INSERT INTO idxdemotable SELECT i ARRAY[(ii)comptype]FROM generateseries(11000000)i

EXPLAIN ANALYZE SELECT FROM idxdemotable WHERE ids = ARRAY[(1515)comptype]

从中我们可以观察到，HASH 索引的规划成本为 802，优于 Btree857和 GIN2402索引。

HASH 索引在某些用例中性能较好，尤其是数组作为记录进行比较时，不过当数据表快速增加行数时可能不适用。此外，表格中的重复数据会降低 HASH 索引的性能。

BRIN最小最大索引

块范围索引BRIN在数据块页范围上工作，块 是在磁盘上物理相邻的一组页面。索引存储每个块范围的摘要信息。此信息保存相邻表页面范围的最小值和最大值。

BRIN 通常用于时间序列数据、顺序数据集或当数据类型如整数、日期的排序为线性时，可以作为范围的最小和最大值存储。BRIN 在高效搜索大型时间序列数据时极为有用，并且其在磁盘上的占用空间远小于标准 Btree 索引。然而，扫描一个 BRIN 索引将返回比您请求的更多行，因此在查询计划中，您还必须排除这些额外的行。

例如，如果我们考虑 empid，它是利用 generateseries() 函数创建的顺序号，并构建 BRIN 索引，我们可以使用 BRIN 索引扫描。然而，在上面的示例中，Btree 索引的运行时间为 0512 毫秒，而 BRIN 索引则为 3852 毫秒，尽管 BRIN 索引的大小384 KB要比 Btree 索引928 MB小。

sqlCREATE INDEX idxempidbrin ON employee USING brin(empid)EXPLAIN ANALYZE select from employee WHERE empid gt 444 and empid lt 555

清理

完成以下步骤以清理在此文中创建的资源和数据：

删除您的数据库集群或数据库实例。使用以下代码删除样本数据：

sqlDROP TABLE employeeDROP TABLE pointsinboxDROP TABLE idxdemotableDROP TYPE comptype

结论

在本文中，我们讨论了在 Amazon Aurora PostgreSQL 和 Amazon RDS for PostgreSQL 中支持的 GIN、GiST、HASH 和 BRIN 索引的各种用例。

如果您的应用程序或工作负载有类似用例，可以参考本文并实施类似方案。

在本系列的即将到来的第三部分中，我们将讨论通过数据库扩展支持的索引，如 btreegin、btreegist、bloom 和 SPGiST。

如有任何想法或问题，请在评论区留言。

关于作者

![Sachin Khanna](https//d2908q01vomqb2cloudfrontnet/887309d048beef83ad3eabf2a79a64a389ab1c