PG中的索引

由 · 发布日期 · 已更新  

0    663    1

Tags：

👉 本文共约29794个字，系统预计阅读时间或需113分钟。

本页目录 隐藏
常见索引
1. 简介
2. 索引类型
B-tree
Hash索引
GiST索引
GIN 索引
BRIN 索引
SP-GiST索引
3. 多列索引
4. 索引和ORDER BY
5. 组合多个索引
6. 唯一索引
注意
7. 表达式索引
8. 部分索引
9. 只用索引的扫描和覆盖索引
10. 操作符类和操作符族
提示
11. 索引和排序规则
12. 检查索引使用
创建索引
CREATE INDEX
大纲
描述
参数
索引存储参数
注解
提示
示例
兼容性
另见
全文搜索
相关文章

常见索引

http://postgres.cn/docs/13/indexes.html

索引是提高数据库性能的常用途径。比起没有索引，使用索引可以让数据库服务器更快找到并获取特定行。但是索引同时也会增加数据库系统的日常管理负担，因此我们应该聪明地使用索引。

索引其实就是一种数据结构，将数据库中的数据以一定的数据结构算法进行存储。当表数据量越来越大时查询速度会下降，建立合适的索引能够帮助我们快速的检索数据库中的数据，快速定位到可能满足条件的记录，不需要遍历所有记录。

索引自身也占用存储空间、消耗计算资源，创建过多的索引将对数据库性能造成负面影响（尤其影响数据导入的性能，建议在数据导入后再建索引）。postgresql里的所有索引都是“从属索引”，也就是索引在物理上与它描述的表文件分离。索引是一种数据库对象，每个索引在pg_class里都有记录。不同种类的索引有着不同的访问方法和内部构造。PG里所有的索引访问方法都通过页面来组织索引的内部结构。从本质来讲，索引是一些数据的键值和元组标识符（TID）之间的映射。在查询数据时如果一个page中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。（想了解索引页内容的话，可以通过pageinspect插件）。

1. 简介

假设我们有一个如下的表：

而应用发出很多以下形式的查询：

在没有事前准备的情况下，系统不得不扫描整个test1表，一行一行地去找到所有匹配的项。如果test1中有很多行但是只有一小部分行（可能是0或者1）需要被该查询返回，这显然是一种低效的方式。但是如果系统被指示维护一个在id列上的索引，它就能使用一种更有效的方式来定位匹配行。例如，它可能仅仅需要遍历一棵搜索树的几层而已。

类似的方法也被用于大部分非小说书籍中：经常被读者查找的术语和概念被收集在一个字母序索引中放在书籍的末尾。感兴趣的读者可以相对快地扫描索引并跳到合适的页而不需要阅读整本书来寻找感兴趣的材料。正如作者的任务是准备好读者可能会查找的术语一样，数据库程序员也需要预见哪些索引会有用。

正如前面讨论的，下列命令可以用来在id列上创建一个索引：

索引的名字test1_id_index可以自由选择，但我们最好选择一个能让我们想起该索引用途的名字。

为了移除一个索引，可以使用DROP INDEX命令。索引可以随时被创建或删除。

一旦一个索引被创建，就不再需要进一步的干预：系统会在表更新时更新索引，而且会在它觉得使用索引比顺序扫描表效率更高时使用索引。但我们可能需要定期地运行ANALYZE命令来更新统计信息以便查询规划器能做出正确的决定。通过第 14 章的信息可以了解如何找出一个索引是否被使用以及规划器在何时以及为什么会选择不\使用索引。

索引也会使带有搜索条件的UPDATE和DELETE命令受益。此外索引还可以在连接搜索中使用。因此，一个定义在连接条件列上的索引可以显著地提高连接查询的速度。

在一个大表上创建一个索引会耗费很长的时间。默认情况下，PostgreSQL允许在索引创建时并行地进行读（SELECT命令），但写（INSERT、UPDATE和DELETE）则会被阻塞直到索引创建完成。在生产环境中这通常是不可接受的。在创建索引时允许并行的写是可能的，但是有些警告需要注意，更多信息可以参考Building Indexes Concurrently。

一个索引被创建后，系统必须保持它与表同步。这增加了数据操作的负担。因此哪些很少或从不在查询中使用的索引应该被移除。

PostgreSQL拥有众多开放特性，例如开放的数据类型接口，除了传统数据库支持的类型，还支持GIS，JSON，RANGE，IP，ISBN，图像特征值，化学，DNA等等扩展的类型，用户还可以根据实际业务扩展更多的类型。不同的索引接口针对的数据类型、业务场景是不一样的.

2. 索引类型

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

pg_am存储关于关系访问方法的信息。系统支持的每种访问方法在这个目录中都有一行。

pg_am为每一种索引方法都包含一行（内部被称为访问方法）。PostgreSQL中内建了对表 常规访问的支持，但是所有的索引方法则是在pg_am中描述。可以通过编写必要的代码并且 在pg_am中创建一项来增加一种新的索引访问方法. 一个索引方法的例程并不直接了解它将要操作的数据类型。而是由一个操作符类标识索引方法用来操作一种特定数据类型的一组操作。

B-tree

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

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

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

B-tree索引也可以用于检索排序数据。这并不会总是比简单扫描和排序更快，但是总是有用的。B-tree索引是最常见的索引并且适合处理等值查询和范围查询的索引。

PostgreSQL的B-tree索引与Oracle的B-tree索引区别比较大，主要是以下4点：

  1. PostgreSQL中索引会存储NULL，而Oracle不会；
  2. PostgreSQL中建立索引时，可以使用where来建立部分索引，而Oracle不能；
  3. PostgreSQL中可以对同一列建立两个相同的索引，而Oracle不能；
  4. PostgreSQL中可以使用concurrently关键字达到创建索引时不阻塞表的DML的功能，Oracle也有online参数实现类似的功能。

注意点：

  1. 此选项只能指定一个索引的名称。
  2. 普通CREATE INDEX命令可以在事务内执行，但是CREATE INDEX CONCURRENTLY不可以在事务内执行。
  3. 列存表、分区表和临时表不支持CONCURRENTLY方式创建索引。

Hash索引

适用场景：hash索引存储的是被索引字段VALUE的哈希值，只支持简单的等值查询。hash索引特别适用于字段VALUE非常长（不适合b-tree索引，因为b-tree一个PAGE至少要存储3个ENTRY，所以不支持特别长的VALUE）的场景，例如很长的字符串，并且用户只需要等值搜索，建议使用hash index。

在pg10之前是不提倡使用hash索引的，因为hash索引不会写wal日志。不过从pg10开始解决了这一问题，并且对hash索引进行了一些加强hash索引其主要目的就是对于某些数据类型（索引键）的值，我们的任务是快速找到匹配的行的ctid。

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

GiST索引

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

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

GIST是广义搜索树generalized search tree的缩写。这是一个平衡搜索树。用于解决一些B-tree，GIN难以解决的数据减少问题，例如，范围是否相交，是否包含，地理位置中的点面相交，或者按点搜索附近的点。

本人提供Oracle、MySQL、PG等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！

GiST索引也有能力优化“最近邻”搜索，例如：

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

GIN 索引

GIN(Generalized Inverted Index, 通用倒排索引) 是一个存储对(key, posting list)集合的索引结构。GIN 索引是“倒排索引”或“反转索引”，它适合于包含多个组成值的数据值，例如数组。倒排索引中为每一个组成值都包含一个单独的项，它可以高效地处理测试指定组成值是否存在的查询。倒排索引来源于搜索引擎的技术，正是有了倒排索引技术，搜索引擎才能有效率的进行数据库查找、删除等操作。

GIN索引常用于查询索引字段中的部分元素值，如在text类型和json类型字段中检索某个关键字。相同的键值只存储一次。

在PG中，GIN索引会为每一个键建立一个B-tree索引，这会导致GIN索引的更新速度非常慢，因为插入或更新一条记录，所有相关键值的索引都会被更新。

PG提供gin_pending_list_limit参数来控制GIN索引的更新速度，适当将maintenance_work_mem参数增大，可以加快GIN索引的创建过程。如果查询返回的结果集特别大，则可以用gin_fuzzy_search_limit参数来控制返回的行数，默认为0，不限制，一般建议设置为5000~20000比较合适。

与 GiST 和 SP-GiST相似， GIN 可以支持多种不同的用户定义的索引策略，并且可以与一个 GIN 索引配合使用的特定操作符取决于索引策略。作为一个例子，PostgreSQL的标准贡献包中包含了用于数组的GIN操作符类，它用于支持使用下列操作符的索引化查询：

<@
@>
=
&&

（这些操作符的含义见第 9.19 节）表 66.1中给出了标准发布中所包括的 GIN 操作符类。在contrib集合中还有更多其他GIN操作符类，更多信息参见第 66 章。

BRIN 索引

BRIN 索引是块级索引，有别于B-TREE等索引，BRIN记录并不是以行号为单位记录索引明细，而是记录每个数据块或者每段连续的数据块的统计信息。因此BRIN索引空间占用特别的小，对数据写入、更新、删除的影响也很小。

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

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

BRIN属于LOSSLY索引，当被索引列的值与物理存储相关性很强时，BRIN索引的效果非常的好。例如时序数据，在时间或序列字段创建BRIN索引，进行等值、范围查询时效果很好。与我们已经熟悉的索引不同，BRIN避免查找绝对不合适的行，而不是快速找到匹配的行。BRIN是一个不准确的索引:不包含表行的ctid。

表被分割成ranges(好多个pages的大小)：因此被称作block range index(BRIN)。在每个range中存储数据的摘要信息。作为规则，这里是最小值和最大值，但有时也并非如此。假设执行了一个查询，该查询包含某列的条件;如果所查找的值没有进入区间，则可以跳过整个range;但如果它们确实在，所有块中的所有行都必须被查看以从中选择匹配的行。在元数据页和摘要数据之间，是reverse range map页(revmap)。是一个指向相应索引行的指针(TIDs)数组。

在BRIN索引中，PostgreSQL会为每个8k大小的存储数据页面读取所选列的最大值和最小值，然后将该信息（页码以及列的最小值和最大值）存储到BRIN索引中。一般可以不把BRIN看作索引，而是看作顺序扫描的加速器。如果我们把每个range都看作是一个虚拟分区，那么我们可以把BRIN看作分区的替代方案。BRIN适合单值类型，当被索引列存储相关性越接近1或-1时，数据存储越有序，块的边界越明显，BRIN索引的效果就越好。

SP-GiST索引

SP代表空间分区。这里的空间通常就是我们所说的空间，例如，一个二维平面。但我们会发现，任何搜索空间，实际上都是任意值域。不相交的特性简化了在插入和搜索时的决策。另一方面，作为规则，树是低分枝的。例如，四叉树的一个节点通常有四个子节点(与b树不同，b树的节点有数百个)，而且深度更大。像这样的树很适合在内存中工作，但索引存储在磁盘上，因此，为了减少I/O操作的数量，必须将节点打包到页中。此外，由于分支深度的不同，在索引中找到不同值所需的时间也会不同。

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

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

就像 GiST, SP-GiST 支持 “nearest-neighbor” 搜索. 对于支持距离排序的 SP-GiST 运算符类别，相应的运算符被规定在表 65.1里面的“Ordering Operators”列中。

3. 多列索引

一个索引可以定义在表的多个列上。例如，我们有这样一个表：

（即将我们的/dev目录保存在数据库中）而且我们经常会做如下形式的查询：

那么我们可以在major和minor上定义一个索引：

目前，只有 B-tree、GiST、GIN 和 BRIN 索引类型支持多列索引，最多可以指定32个列（该限制可以在源代码文件pg_config_manual.h中修改，但是修改后需要重新编译PostgreSQL）。

一个B-tree索引可以用于条件中涉及到任意索引列子集的查询，但是当先导列（即最左边的那些列）上有约束条件时索引最为有效。确切的规则是：在先导列上的等值约束，加上第一个无等值约束的列上的不等值约束，将被用于限制索引被扫描的部分。在这些列右边的列上的约束将在索引中被检查，这样它们适当节约了对表的访问，但它们并未减小索引被扫描的部分。例如，在(a, b, c)上有一个索引并且给定一个查询条件WHERE a = 5 AND b >= 42 AND c < 77，对索引的扫描将从第一个具有a = 5和b = 42的项开始向上进行，直到最后一个具有a = 5的项。在扫描过程中，具有c >= 77的索引项将被跳过，但是它们还是会被扫描到。这个索引在原则上可以被用于在b和/或c上有约束而在a上没有约束的查询，但是整个索引都不得不被扫描，因此在大部分情况下规划器宁可使用一个顺序的表扫描来替代索引。

一个多列GiST索引可以用于条件中涉及到任意索引列子集的查询。在其余列上的条件将限制由索引返回的项，但是第一列上的条件是决定索引上扫描量的最重要因素。当第一列中具有很少的可区分值时，一个GiST索引将会相对比较低效，即便在其他列上有很多可区分值。

一个GIN索引可以用于条件中涉及到任意索引列子集的查询。与B-tree和GiST不同，GIN的搜索效率与查询条件中使用哪些索引列无关。

多列 BRIN 索引可以被用于涉及该索引被索引列的任意子集的查询条件。和 GIN 相似且不同于 B-树 或者 GiST，索引搜索效率与查询条件使用哪个索引列无关。在单个表上使用多个 BRIN 索引来取代一个多列 BRIN 索引的唯一原因是为了使用不同的pages_per_range存储参数。

当然，要使索引起作用，查询条件中的列必须要使用适合于索引类型的操作符，使用其他操作符的子句将不会被考虑使用索引。

多列索引应该较少地使用。在绝大多数情况下，单列索引就足够了且能节约时间和空间。具有超过三个列的索引不太有用，除非该表的使用是极端程式化的。第 11.5 节以及第 11.9 节中有对不同索引配置优点的讨论。

4. 索引和ORDER BY

除了简单地查找查询要返回的行外，一个索引可能还需要将它们以指定的顺序传递。这使得查询中的ORDER BY不需要独立的排序步骤。在PostgreSQL当前支持的索引类型中，只有B-tree可以产生排序后的输出，其他索引类型会把行以一种没有指定的且与实现相关的顺序返回。

规划器会考虑以两种方式来满足一个ORDER BY说明：扫描一个符合说明的可用索引，或者先以物理顺序扫描表然后再显式排序。对于一个需要扫描表的大部分的查询，一个显式的排序很可能比使用一个索引更快，因为其顺序访问模式使得它所需要的磁盘I/O更少。只有在少数行需要被取出时，索引才会更有用。一种重要的特殊情况是ORDER BY与LIMIT n联合使用：一个显式的排序将会处理所有的数据来确定最前面的n行，但如果有一个符合ORDER BY的索引，前n行将会被直接获取且根本不需要扫描剩下的数据。

默认情况下，B-tree索引将它的项以升序方式存储，并将空值放在最后(表TID被处理为其它相等条目之间的分线器列)。这意味着对列x上索引的一次前向扫描将产生满足ORDER BY x（或者更长的形式：ORDER BY x ASC NULLS LAST）的结果。索引也可以被后向扫描，产生满足ORDER BY x DESC（ORDER BY x DESC NULLS FIRST， NULLS FIRST是ORDER BY DESC的默认情况）。

我们可以在创建B-tree索引时通过ASC、DESC、NULLS FIRST和NULLS LAST选项来改变索引的排序，例如：

一个以升序存储且将空值前置的索引可以根据扫描方向来支持ORDER BY x ASC NULLS FIRST或 ORDER BY x DESC NULLS LAST。

读者可能会疑惑为什么要麻烦地提供所有四个选项，因为两个选项连同可能的后向扫描可以覆盖所有ORDER BY的变体。在单列索引中这些选项确实有冗余，但是在多列索引中它们却很有用。考虑(x, y)上的一个两列索引：它可以通过前向扫描满足ORDER BY x, y，或者通过后向扫描满足ORDER BY x DESC, y DESC。但是应用可能需要频繁地使用ORDER BY x ASC, y DESC。这样就没有办法从通常的索引中得到这种顺序，但是如果将索引定义为(x ASC, y DESC)或者(x DESC, y ASC)就可以产生这种排序。

显然，具有非默认排序的索引是相当专门的特性，但是有时它们会为特定查询提供巨大的速度提升。是否值得维护这样一个索引取决于我们会多频繁地使用需要特殊排序的查询。

5. 组合多个索引

只有查询子句中在索引列上使用了索引操作符类中的操作符并且通过AND连接时才能使用单一索引。例如，给定一个(a, b) 上的索引，查询条件WHERE a = 5 AND b = 6可以使用该索引，而查询WHERE a = 5 OR b = 6不能直接使用该索引。

幸运的是，PostgreSQL具有组合多个索引（包括多次使用同一个索引）的能力来处理那些不 能用单个索引扫描实现的情况。系统能在多个索引扫描之间安排AND和OR条件。例如， WHERE x = 42 OR x = 47 OR x = 53 OR x = 99这样一个查询可以被分解成为四个独立的在x上索引扫描，每一个扫描使用其中一个条件。这些查询的结果将被“或”起来形成最后的结果。另一个例子是如果我们在x和y上都有独立的索引，WHERE x = 5 AND y = 6这样的查询的一种可能的实现方式就是分别使用两个索引配合相应的条件，然后将结果“与”起来得到最后的结果行。

为了组合多个索引，系统扫描每一个所需的索引并在内存中准备一个位图用于指示表中符合索引条件的行的位置。然后这些位图会被根据查询的需要“与”和“或”起来。最后，实际的表行将被访问并返回。表行将被以物理顺序访问，因为位图就是以这种顺序布局的。这意味着原始索引中的任何排序都会被丢失，并且如果存在一个ORDER BY子句就需要一个单独的排序步骤。由于这个原因以及每一个附加的索引都需要额外的时间，即使有额外的索引可用，规划器有时也会选择使用单一索引扫描。

在所有的应用（除了最简单的应用）中，可能会有多种有用的索引组合，数据库开发人员必须做出权衡以决定提供哪些索引。有时候多列索引最好，但是有时更好的选择是创建单独的索引并依赖于索引组合特性。例如，如果我们的查询中有时只涉及到列x，有时候只涉及到列y，还有时候会同时涉及到两列，我们可以选择在x和y上创建两个独立索引然后依赖索引组合来处理同时涉及到两列的查询。我们当然也可以创建一个(x, y)上的多列索引。当查询同时涉及到两列时，该索引会比组合索引效率更高，但是正如第 11.3 节中讨论的，它在只涉及到y的查询中几乎完全无用，因此它不能是唯一的一个索引。一个多列索引和一个y上的独立索引的组合将会工作得很好。多列索引可以用于那些只涉及到x的查询，尽管它比x上的独立索引更大且更慢。最后一种选择是创建所有三个索引，但是这种选择最适合表经常被执行所有三种查询但是很少被更新的情况。如果其中一种查询要明显少于其他类型的查询，我们可能需要只为常见类型的查询创建两个索引。

6. 唯一索引

索引也可以被用来强制列值的唯一性，或者是多个列组合值的唯一性。

当前，只有B-tree能够被声明为唯一。

当一个索引被声明为唯一时，索引中不允许多个表行具有相同的索引值。空值被视为不相同。一个多列唯一索引将会拒绝在所有索引列上具有相同组合值的表行。

PostgreSQL会自动为定义了一个唯一约束或主键的表创建一个唯一索引。该索引包含组成主键或唯一约束的所有列（可能是一个多列索引），它也是用于强制这些约束的机制。

注意

不需要手工在唯一列上创建索引，如果那样做也只是重复了自动创建的索引而已。

7. 表达式索引

一个索引列并不一定是底层表的一个列，也可以是从表的一列或多列计算而来的一个函数或者标量表达式。这种特性对于根据计算结果快速获取表中内容是有用的。

例如，一种进行大小写不敏感比较的常用方法是使用lower函数：

这种查询可以利用一个建立在lower(col1)函数结果之上的索引：

如果我们将该索引声明为UNIQUE，它将阻止创建在col1值上只有大小写不同的行。

另外一个例子，如果我们经常进行如下的查询：

那么值得创建一个这样的索引：

正如第二个例子所示，CREATE INDEX命令的语法通常要求在被索引的表达式周围书写圆括号。而如第一个例子所示，当表达式只是一个函数调用时可以省略掉圆括号。

索引表达式的维护代价较为昂贵，因为在每一个行被插入或更新时都得为它重新计算相应的表达式。然而，索引表达式在进行索引搜索时却不\需要重新计算，因为它们的结果已经被存储在索引中了。在上面两个例子中，系统将会发现查询的条件是WHERE indexedcolumn = 'constant'，因此查询的速度将等同于其他简单索引查询。因此，表达式索引对于检索速度远比插入和更新速度重要的情况非常有用。

8. 部分索引

一个部分索引是建立在表的一个子集上，而该子集则由一个条件表达式（被称为部分索引的谓词）定义。而索引中只包含那些符合该谓词的表行的项。部分索引是一种专门的特性，但在很多种情况下它们也很有用。

使用部分索引的一个主要原因是避免索引公值。由于搜索一个公值的查询（一个在所有表行中占比超过一定百分比的值）不会使用索引，所以完全没有理由将这些行保留在索引中。这可以减小索引的尺寸，同时也将加速使用索引的查询。它也将加速很多表更新操作，因为这种索引并不需要在所有情况下都被更新。

例 1. 建立一个部分索引来排除公值

假设我们要在一个数据库中保存网页服务器访问日志。大部分访问都来自于我们组织内的IP地址，但是有些来自于其他地方（如使用拨号连接的员工）。如果我们主要通过IP搜索来自于外部的访问，我们就没有必要索引对应于我们组织内网的IP范围。

假设有这样一个表：

用以下命令可以创建适用于我们的部分索引：

一个使用该索引的典型查询是：

此处查询的IP地址由部分索引覆盖。

以下查询无法使用部分索引，因为它使用从索引中排除的 IP 地址：

可以看到部分索引查询要求公值能被预知，因此部分索引最适合于数据分布不会改变的情况。这样的索引也可以偶尔被重建来适应新的数据分布，但是这会增加维护负担。

例 2展示了部分索引的另一个可能的用途：从索引中排除那些查询不感兴趣的值。这导致了上述相同的好处，但它防止了通过索引来访问“不感兴趣的”值，即便在这种情况下一个索引扫描是有益的。显然，为这种场景建立部分索引需要很多考虑和实验。

例 2. 建立一个部分索引来排除不感兴趣的值

如果我们有一个表包含已上账和未上账的订单，其中未上账的订单在整个表中占据一小部分且它们是最经常被访问的行。我们可以通过只在未上账的行上创建一个索引来提高性能。创建索引的命令如下：

使用该索引的一个可能查询是：

然而，索引也可以用于完全不涉及order_nr的查询，例如：

这并不如在amount列上部分索引有效，因为系统必须扫描整个索引。然而，如果有相对较少的未上账订单，使用这个部分索引来查找未上账订单将会更好。

注意这个查询将不会使用该索引：

订单3501可能在已上账订单或未上账订单中。

例 2也显示索引列和谓词中使用的列并不需要匹配。PostgreSQL支持使用任意谓词的部分索引，只要其中涉及的只有被索引表的列。然而，记住谓词必须匹配在将要受益于索引的查询中使用的条件。更准确地，只有当系统能识别查询的WHERE条件从数学上索引的谓词时，一个部分索引才能被用于一个查询。PostgreSQL并不能给出一个精致的定理证明器来识别写成不同形式在数学上等价的表达式（一方面创建这种证明器极端困难，另一方面即便能创建出来对于实用也过慢）。系统可以识别简单的不等蕴含，例如“x < 1”蕴含“x < 2”；否则谓词条件必须准确匹配查询的WHERE条件中的部分，或者索引将不会被识别为可用。匹配发生在查询规划期间而不是运行期间。因此，参数化查询子句无法配合一个部分索引工作。例如，对于参数的所有可能值来说，一个具有参数“x < ?”的预备查询绝不会蕴含“x < 2”。

部分索引的第三种可能的用途并不要求索引被用于查询。其思想是在一个表的子集上创建一个唯一索引，如例 11.3所示。这对那些满足索引谓词的行强制了唯一性，而对那些不满足的行则没有影响。

例 3. 建立一个部分唯一索引

假设我们有一个描述测试结果的表。我们希望保证其中对于一个给定的主题和目标组合只有一个“成功”项，但其中可能会有任意多个“不成功”项。实现它的方式是：

当有少数成功测试和很多不成功测试时这是一种特别有效的方法。通过创建具有IS NULL限制的惟一部分索引，也可以允许列中仅有一个空。

最后，一个部分索引也可以被用来重载系统的查询规划选择。同样，具有特殊分布的数据集可能导致系统在它并不需要索引的时候选择使用索引。在此种情况下可以被建立，这样它将不会被那些无关的查询所用。通常，PostgreSQL会对索引使用做出合理的选择（例如，它会在检索公值时避开索引，这样前面的例子只能节约索引尺寸，它并非是避免索引使用所必需的），非常不正确的规划选择则需要作为故障报告。

记住建立一个部分索引意味着我们知道的至少和查询规划器所知的一样多，尤其是我们知道什么时候一个索引会是有益的。 构建这些知识需要经验和对于PostgreSQL中索引工作方式的理解。 在大部分情况下，一个部分索引相对于一个普通索引的优势很小。在某些情况下，它们会完全相反，例如例 4。

例 4. 不要使用部分索引代替分区

你可能想尝试创建一组巨大的、不重叠的部分索引，例如

这是个个坏主意!几乎可以肯定，使用一个非部分索引会更好一些，声明如

(将类别列放在前面，基于第 11.3 节所述的原因。) 虽然在这个更大的索引中进行搜索可能比在更小的索引中进行搜索要下降两倍以上的树级别， 但这几乎肯定会比选择适当的部分索引中的一个所需的规划器的开销更便宜。 问题的核心是系统不理解部分索引之间的关系，并将费力地测试每个索引，以确定它是否适用于当前查询。

如果你的表足够大，单个索引确实是一个坏主意，你应该考虑使用分区代替(参见第 5.11 节)。 通过这种机制，系统理解表和索引是不重叠的，就此而言可以获得更好的性能。

关于部分索引的更多信息可以在[ston89b]、[olson93]和[seshadri95]中找到。

9. 只用索引的扫描和覆盖索引

PostgreSQL中的所有索引是二级索引,这意味着每个索引都是与表的主数据区（在PostgreSQL术语称为表的堆中）分开存储。这意味着在普通索引扫描中，每行检索都需要从索引和堆中取数据。 此外，虽然匹配给定的可索引WHERE条件的索引条目通常在一起靠近存储，但它们引用的表行可能在堆中的任何地方。 因此索引扫描的堆访问部分涉及到对堆的大量随机访问，这可能很慢，特别是在传统旋转媒介上。如 第 11.5 节 中所述，位图扫描尝试通过按排序的顺序进行堆访问来减少成本，但这远远不够）。

为了解决这种性能问题，PostgreSQL支持只用索引的扫描，这类扫描可以仅用一个索引来回答查询而不产生任何堆访问。其基本思想是直接从每一个索引项中直接返回值，而不是去参考相关的堆项。在使用这种方法时有两个根本的限制：

  1. 索引类型必须支持只用索引的扫描。B-树索引总是支持只用索引的扫描。GiST 和 SP-GiST 索引只对某些操作符类支持只用索引的扫描。其他索引类型不支持这种扫描。底层的要求是索引必须在物理上存储或者可以重构出每一个索引项对应的原始数据值。GIN 索引是一个不支持只用索引的扫描的反例，因为它的每一个索引项通常只包含原始数据值的一部分。

  2. 查询必须只引用存储在该索引中的列。例如，给定的索引建立在表的列x和y上，而该表还有一个列z，这些查询可以使用只用索引的扫描：

    但是这些查询不能使用只用索引的查询：

    （如下面所讨论的，表达式索引和部分索引会让这条规则更加复杂）。

如果符合这两个根本要求，那么该查询所要求的所有数据值都可以从索引得到，因此才可能使用只用索引的扫描。但是对PostgreSQL中的任何表扫描还有一个额外的要求：必须验证每一个检索到的行对该查询的 MVCC 快照是“可见的”，如第 13 章中讨论的那样。可见性信息并不存储在索引项中，只存储在堆项中。因此，乍一看似乎每一次行检索无论如何都会要求一次堆访问。如果表行最近被修改过，确实是这样。但是，对于很少更改的数据有一种方法可以解决这个问题。PostgreSQL为表堆中的每一个页面跟踪是否其中所有的行的年龄都足够大，以至于对所有当前以及未来的事务都可见。这个信息存储在该表的可见性映射的一个位中。在找到一个候选索引项后，只用索引的扫描会检查对应堆页面的可见性映射位。如果该位被设置，那么这一行就是可见的并且该数据库可以直接被返回。如果该位没有被设置，那么就必须访问堆项以确定这一行是否可见，这种情况下相对于标准索引扫描就没有性能优势。即便是在成功的情况下，这种方法也是把对堆的访问换成了对可见性映射的访问。不过由于可见性映射比它所描述的堆要小四个数量级，所以访问可见性映射所需的物理 I/O 要少很多。在大部分情况下，可见性映射总是会被保留在内存中的缓冲中。

总之，虽然当两个根本要求满足时可以使用只用索引的扫描，但是只有该表的堆页面中有很大一部分的“所有都可见”映射位被设置时这种索引才有优势。不过，有很大一部分行不被更改的表是很常见的，这也让这一类扫描在实际中非常有用。

为了有效利用仅索引扫描功能，您可以选择创建一个覆盖索引，它是一个特别设计的索引，包含经常运行的特殊类型查询所需要的列。由于查询通常需要检索的列不仅仅是他们搜索的列，PostgreSQL允许您创建索引,这个索引中有些列只是“负荷”而不是搜索键的一部分。这可以通过添加INCLUDE来完成子句来列出了额外的列。 例如，如果您通常可以运行这样的查询：

加快此类查询的传统方法是仅在x上的索引。但是，一个索引定义为

可以将这些查询作为仅索引扫描处理，因为y可以从索引中获取而不需要访问堆。

因为列y不是搜索键的一部分，它不必是索引可以处理的数据类型；它只存储在索引中，不由索引机解释。另外，如果索引是唯一的索引，则

唯一性条件仅适用于x 列，而不是x和y的组合。（如果使用和在索引中设置的类似语法，一个INCLUDE子句可以写在UNIQUE和PRIMARY KEY约束中。）

保守地将非键负载列添加到索引是明智的，尤其是宽列。 如果索引元组超过索引类型允许的最大大小，数据插入将失败。在任何情况下，非键列都将复制索引表中的数据并放大了索引的大小，从而有可能减慢搜索速度。请记住，除非一个表足够慢以至于仅索引扫描可能不必访问堆，否则没有什么理由在一个索引中包含负载列。无论如何，如果必须访问堆元组，从堆里获取列的值并不会带来更高的开销。其他限制是表达式不被作为包含的来支持。只有B树和GiST索引当前支持包含的列。

在 PostgreSQL有INCLUDE特性之前，人们有时会通过写负载列作为普通索引列来制作覆盖索引。它这样写：

即使他们无意将y用作WHER子句的一部分，只要额外的列是尾列就可以很好的工作。 让它们成为前导字段是不明智的，原因在第 11.3 节中有说明。但是，此方法不支持您希望索引在键列上实施唯一性。

Suffix truncation总是从B-Tree的上层移除非键列。作为有效负载列，它们从不用于指导索引扫描。 当键列的其余前缀恰好足以描述最低B-Tree级别上的元组时，截断过程还会删除一个或多个尾随的键列。 实际中，不使用INCLUDE子句覆盖索引通常会避免存储在上层有效负载的列。 然而，显式地将有效负载列定义为非键列reliably\使上层元组保持较小。

原则上，只用索引的扫描可以被用于表达式索引。例如，给定一个f(x)上的索引（x是一个表列），可以把

作为只用索引的扫描执行，如果f()是一个计算代价昂贵的函数，这会非常有吸引力。不过，PostgreSQL的规划器当前面对这类情况时并不是很聪明。只有在索引中有查询所需要的所有列\时，规划器才会考虑用只用索引的扫描来执行一个查询。在这个例子中，除了在f(x)环境中之外，查询的其他部分不需要x，但是规划器并不能意识到这一点，因此它会得出不能使用只用索引的扫描的结论。如果只用索引的扫描足够有价值，有一种解决方法是把该索引定义在(f(x), x)上，其中第二个列实际上并不会被使用，它只是用来说服规划器可以使用只用索引的扫描而已。如果目标是避免重复计算f(x)，一个额外的警示是规划器不一定会把不在可索引WHERE子句中对f(x)的使用匹配到索引列。通常在上述那种简单查询中一切正常，但是涉及到连接的查询中就不行了。这些不足将在未来的PostgreSQL版本中修正。

部分索引也和只用索引的扫描之间有着有趣的关系。考虑例 11.3中所展示的部分索引：

原则上，我们可以在这个索引上使用只用索引的扫描来满足查询

但是有一个问题：WHERE子句引用的是不能作为索引结果列的success。尽管如此，还是可以使用只用索引的扫描，因为在运行时计划不需要重新检查WHERE子句的那个部分：在该索引中找到的所有项必定具有success = true，因此在计划中检查这个部分的需要并不明显。PostgreSQL 9.6 和以后的版本将会识别这种情况，并且允许生成只用索引的扫描，但是旧版本无法这样做。

10. 操作符类和操作符族

一个索引定义可以为索引中的每一列都指定一个操作符类。

操作符类标识该列上索引要使用的操作符。例如，一个int4类型上的B树索引会使用int4_ops类，这个操作符类包括用于int4类型值的比较函数。实际上列的数据类型的默认操作符类通常就足够了。存在多个操作符类的原因是，对于某些数据类型可能会有多于一种的有意义的索引行为。例如，我们可能想要对一种复数数据类型按照绝对值排序或者按照实数部分排序。我们可以通过为该数据类型定义两个操作符类来实现，并且在创建一个索引时选择合适的类。操作符类会决定基本的排序顺序（可以通过增加排序选项COLLATE、 ASC/DESC和/或 NULLS FIRST/NULLS LAST来修改）。

除了默认的操作符类，还有一些内建的操作符类：

  • 操作符类text_pattern_ops、varchar_pattern_ops和 bpchar_pattern_ops分别支持类型text、varchar和 char上的B树索引。它们与默认操作符类的区别是值的比较是严格按照字符进行而不是根据区域相关的排序规则。这使得这些操作符类适合于当一个数据库没有使用标准“C”区域时被使用在涉及模式匹配表达式（LIKE或POSIX正则表达式）的查询中。一个例子是，你可以这样索引一个varchar列：

    注意如果你希望涉及到<、<=、>或>=比较的查询使用一个索引，你也应该创建一个使用默认操作符类的索引。这些查询不能使用*xxx*_pattern_ops操作符类（但是普通的等值比较可以使用这些操作符类）。可以在同一个列上创建多个使用不同操作符类的索引。如果你正在使用C区域，你并不需要*xxx*_pattern_ops操作符类，因为在C区域中的模式匹配查询可以用带有默认操作符类的索引。

下面的查询展示了所有已定义的操作符类：

一个操作符类实际上只是一个更大的被称为操作符族的结构的一个子集。在多种数据类型具有相似行为的情况下，常常会定义跨数据类型的操作符并且允许索引使用它们。为了实现该目的，这些类型的操作符类必须被分组到同一个操作符族中。跨类型的操作符是该族的成员，但是并不与族内任意一个单独的类相关联。

前一个查询的扩展版本展示了每个操作符类所属的操作符族：

这个查询展示所有已定义的操作符族和每一个族中包含的所有操作符：

提示

psql has commands \dAc, \dAf, and \dAo, which provide slightly more sophisticated versions of these queries.

11. 索引和排序规则

一个索引在每一个索引列上只能支持一种排序规则。如果需要多种排序规则，你可能需要多个索引。

考虑这些语句：

该索引自动使用下层列的排序规则。因此一个如下形式的查询：

可以使用该索引，因为比较会默认使用列的排序规则。但是，这个索引无法加速涉及到某些其他排序规则的查询。因此对于下面形式的查询：

可以创建一个额外的支持"y"排序规则的索引，例如：

12. 检查索引使用

尽管PostgreSQL中的索引并不需要维护或调优，但是检查真实的查询负载实际使用了哪些索引仍然非常重要。检查一个独立查询的索引使用情况可以使用EXPLAIN命令，它应用于这种目的的内容在第 14.1 节中有介绍。也可以在一个运行中的服务器上收集有关索引使用的总体统计情况，如第 27.2 节所述。

很难明确地表达决定创建哪些索引的通用过程。在之前的小节中的例子里有一些典型的情况。通常需要大量的实验才能决定应该创建哪些索引。本小节剩余的部分将给出一些创建索引的提示：

  • 总是先运行ANALYZE。这个命令会收集有关表中值分布情况的统计信息。估计一个查询将要返回的行数需要这些信息，而结果行数则被规划器用来为每一个可能的查询计划分配实际的代价。如果没有任何真实的统计信息，将会假定一些默认值，这几乎肯定是不准确的。在没有运行的情况下检查一个应用的索引使用情况是注定要失败的。详见第 24.1.3 节和第 24.1.6 节。

  • 使用真实数据进行实验。使用测试数据来建立索引将会告诉你测试数据需要什么样的索引，但这并不代表真实数据的需要。

    使用非常小的测试数据集是特别致命的。在从100000行中选出1000行时可能会用到索引，但是从100行里选出1行是很难用到索引的，因为100行完全可能放入到一个磁盘页面中，而没有任何计划能够比得上从一个磁盘页面顺序获取的计划。

    在创建测试数据时也要小心，特别是当应用还没有产生时通常是不可避免的。值非常相似、完全随机或以排好序的方式被插入都将使得统计信息倾斜于真实数据中的值分布。

  • 如果索引没有被用到，强制使用它们将会对测试非常有用。有一些运行时参数可以关闭多种计划类型（参见第 19.7.1 节）。例如，关闭顺序扫描（enable_seqscan）以及嵌套循环连接（enable_nestloop）将强制系统使用一种不同的计划。如果系统仍然选择使用一个顺序扫描或嵌套循环连接，则索引没有被使用的原因可能更加根本，例如查询条件不匹配索引（哪种查询能够使用哪种索引已经在前面的小节中解释过了）。

  • 如果强制索引使用确实使用了索引，则有两种可能性：系统是正确的并且索引确实不合适，或者查询计划的代价估计并没有反映真实情况。因此你应该对用索引的查询和不用索引的查询计时。此时EXPLAIN ANALYZE命令就能发挥作用了。

  • 如果发现代价估计是错误的，也分为两种可能性。总代价是用每个计划节点的每行代价乘以计划节点的选择度估计来计算的。计划节点的代价估计可以通过运行时参数调整（如第 19.7.2 节所述）。不准确的选择度估计可能是由于缺乏统计信息，可以通过调节统计信息收集参数（见ALTER TABLE）来改进。

    如果你不能成功地把代价调整得更合适，那么你可能必须依靠显式地强制索引使用。你也可能希望联系PostgreSQL开发者来检查该问题。

创建索引

CREATE INDEX

CREATE INDEX — 定义一个新索引

大纲

描述

CREATE INDEX在指定关系的指定列上构建 一个索引，该关系可以是一个表或者一个物化视图。索引主要被用来提升 数据库性能（不过不当的使用会导致性能变差）。

索引的键域被指定为列名或者写在圆括号中的表达式。如果索引方法支持 多列索引，可以指定多个域。

一个索引域可以是一个从表行的一列或者更多列值进行计算的表达式。 这种特性可以被用来获得对基于基本数据某种变换的数据的快速访问。 例如，一个在upper(col)上计算的索引可以允许子句 WHERE upper(col) = 'JIM'使用索引。

PostgreSQL提供了索引方法 B-树、哈希、GiST、SP-GiST、GIN 以及 BRIN。用户也可以定义自己的索引 方法，但是相对较复杂。

当WHERE子句存在时，会创建一个 部分索引。部分索引只包含表中一部分行的项， 通常索引这一部分会比表的其他部分更有用。例如，如果有一个表包含了 已付和未付订单，其中未付订单占了整个表的一小部分并且是经常被使用 的部分，可以通过只在这一部分上创建一个索引来改进性能。另一种可能 的应用是使用带有UNIQUE的 WHERE在表的一个子集上强制唯一性。更多的讨论 请见第 11.8 节。

WHERE子句中使用的表达式只能引用底层表的列，但 它可以引用所有列而不仅仅是被索引的列。当前， WHERE中也禁止使用子查询和聚集表达式。同样的 限制也适用于表达式索引中的表达式域。

所有在索引定义中使用的函数和操作符必须是“不可变的”， 就是说它们的结果必须仅依赖于它们的参数而不受外在因素（例如另 一个表的内容和当前的时间）的影响。这种限制确保了索引的行为是 良定的。要在一个索引表达式或者WHERE子句中 使用用户定义的函数，记住在创建函数时把它标记为不可变。

参数

  • UNIQUE

    导致系统在索引被创建时（如果数据已经存在）或者加入数据时 检查重复值。会导致重复项的数据插入或者更新尝试将会产生一 个错误。当唯一索引被应用在分区边上时会有额外的限制，请参考CREATE TABLE。

  • CONCURRENTLY

    当使用了这个选项时，PostgreSQL在构建索引时 不会取得任何会阻止该表上并发插入、更新或者删除的锁。而标准的索引 构建将会把表锁住以阻止对表的写（但不阻塞读），这种锁定会持续到索 引创建完毕。对于临时表，CREATE INDEX始终是非并发的，因为没有其他会话可以访问它们，并且创建非并发索引的成本更低。参考：https://www.xmmup.com/pgbingfachuangjiansuoyinconcurrently.html

  • IF NOT EXISTS

    如果一个同名关系已经存在则不要抛出错误。这种情况下会发出一个提示。 注意着并不保证现有的索引与将要创建的索引有任何相似。当 IF NOT EXISTS被指定时，需要指定索引名。

  • INCLUDE

    可选的INCLUDE子句指定一个列的列表，其中的列将被包括在索引中作为非键列。非键列不能作为索引扫描的条件，并且该索引所强制的任何唯一性或者排除约束都不会考虑它们。不过，只用索引的扫描可以返回非键列的内容而无需访问该索引的基表，因为在索引项中就能直接拿到它们。因此，非键列的增加允许查询使用只用索引的扫描，否则就无法使用。保守地向索引中增加非键列是明智的，特别是很宽的列。如果一个索引元组超过索引类型允许的最大尺寸，数据插入将会失败。在任何情况下，非键列都会重复来自索引基表的数据并且让索引的尺寸膨胀，因此可能会拖慢搜索。此外，B树重复数据删除永远不会与具有非关键列的索引一起使用。INCLUDE子句中列出的列不需要合适的操作符类，甚至数据类型没有为给定的访问方法定义操作符类的列都可以包括在这个子句中。不支持把表达式作为被包括列，因为它们不能被用在只用索引的扫描中。当前，有B-树和GiST索引访问方法支持这一特性。在B-树和GiST索引中，INCLUDE子句中列出的列的值被包括在对应于堆元组的叶子元组中，但是不包括在用于树导航的上层索引项中。

  • name

    要创建的索引名称。这里不能包括模式名，因为索引总是被创建在其基表所在 的模式中。如果索引名称被省略，PostgreSQL将基于基 表名称和被索引列名称选择一个合适的名称。

  • ONLY

    如果该表是分区表，指示不要在分区上递归创建索引。默认会递归创建索引。

  • table_name

    要被索引的表的名称（可以被模式限定）。

  • method

    要使用的索引方法的名称。可以选择 btree、hash、 gist、spgist、 gin以及brin。 默认方法是btree。

  • column_name

    一个表列的名称。

  • expression

    一个基于一个或者更多个表列的表达式。如语法中所示，表达式通常必须 被写在圆括号中。不过，如果该表达式是一个函数调用的形式，圆括号可 以被省略。

  • collation

    要用于该索引的排序规则的名称。默认情况下，该索引使用被索引列 的排序规则或者被索引表达式的结果排序规则。当查询涉及到使用非 默认排序规则的表达式时，使用非默认排序规则的索引就能排上用场。

  • opclass

    一个操作符类的名称。详见下文。

  • opclass_parameter

    运算符类参数的名称。详情请见下文。

  • ASC

    指定上升排序（默认）。

  • DESC

    指定下降排序。

  • NULLS FIRST

    指定把空值排序在非空值前面。在指定DESC时， 这是默认行为。

  • NULLS LAST

    指定把空值排序在非空值后面。在没有指定DESC时， 这是默认行为。

  • storage_parameter

    索引方法相关的存储参数的名称。详见 Index Storage Parameters。

  • tablespace_name

    在其中创建索引的表空间。如果没有指定，将会使用 default_tablespace。或者对临时表上的索引使用 temp_tablespaces。

  • predicate

    部分索引的约束表达式。

索引存储参数

可选的WITH子句为索引指定存储参数。每一种 索引方法都有自己的存储参数集合。B-树、哈希、GiST以及SP-GiST索引方法都接受这个参数：

  • fillfactor (integer)

    索引的填充因子是一个百分数，它决定索引方法将尝试填充索引页面的充满程度。对于B-树，在初始的索引构建过程中，叶子页面会被填充至该百分数，当在索引右端扩展索引（增加新的最大键值）时也会这样处理。如果页面后来被完全填满，它们就会被分裂，导致索引的效率逐渐退化。B-树使用了默认的填充因子 90，但是也可以选择为 10 到 100 的任何整数值。如果表是静态的，那么填充因子 100 是最好的，因为它可以让索引的物理尺寸最小化。但是对于更新负荷很重的表，较小的填充因子有利于最小化对页面分裂的需求。其他索引方法以不同但是大致类似的方式使用填充因子，不同方法的默认填充因子也不相同。

B-树索引还接受这些参数：

  • deduplicate_items (boolean)

    控制第 63.4.2 节中描述的 B 树重复数据删除技术的使用。设置为 ON或OFF以启用或禁用优化（ON 和OFF的其他写法在第 19.1 节中有介绍）。 默认值为ON.注意通过ALTER INDEX关闭deduplicate_items 可以防止将来的插入触发重复数据删除，但本身不会使现有的发布列表元组使用标准的元组表示。

  • vacuum_cleanup_index_scale_factor (floating point)

    vacuum_cleanup_index_scale_factor针对每个索引的值。

GiST还额外接受这个参数：

  • buffering (enum)

    决定是否用第 64.4.1 节中描述的缓冲构建技术来构建索引。OFF会禁用它，ON则启用该特性，如果设置为AUTO则初始会禁用它，但是一旦索引尺寸到达effective_cache_size就会随时打开。默认值是AUTO。

GIN索引接受不同的参数：

  • fastupdate (boolean)

    这个设置控制第 66.4.1 节中描述的快速更新 技术的使用。它是一个布尔参数：ON启用快速更新， OFF禁用之。默认是 ON。注意通过ALTER INDEX关闭fastupdate 会阻止未来的更新进入到待处理索引项列表中，但它不会自己处理之前的 待处理项。可以使用VACUUM或者调用 gin_clean_pending_list确保处理完待处理列表的项。

  • gin_pending_list_limit (integer)

    自定义gin_pending_list_limit参数。这个值 要以千字节来指定。

BRIN索引接受不同的参数：

  • pages_per_range (integer)

    定义用于每一个BRIN索引项的块范围由多少个表块组成（详见 第 67.1 节）。默认是128。

  • autosummarize (boolean)

    定义是否只要在下一个页面上检测到插入就为前面的页面范围运行概要操作。

注解

关于索引何时能被使用、何时不被使用以及什么情况下它们有用的信息请 见第 11 章。

当前，只有 B-树、GiST、GIN 和 BRIN 索引方法支持多列索引。默认最多可以 索引 32 个域（可以在构建 PostgreSQL修改这种限制）。当前 只有 B-树支持唯一索引。

为索引的每一列可以指定一个带可选参数的操作符类。该操作符 类标识要被该索引用于该列的操作符。例如，一个四字节整数上的 B-树索引 会使用int4_ops类。这个操作符类包括了用于四字节整数 的比较函数。实际上，通常列数据类型的默认操作符类就足够了。对某些数据 类型指定操作符类的主要原因是，可能会有多于一种有意义的顺序。例如， 我们可能想用绝对值或者实数部分对复数类型排序。我们可以通过为该数据 类型定义两个操作符类来做到，并且在创建索引时选择其中合适的类。更多 有关操作符类的信息请见第 11.10 节以及第 37.16 节。

当在一个分区表上调用CREATE INDEX时，默认的行为是递归到所有的分区上以确保它们都具有匹配的索引。每一个分区首先会被检查是否有一个等效的索引存在，如果有则该索引将被挂接为被创建索引的一个分区索引，而被创建的索引将成为其父索引。如果不存在匹配的索引，则会创建一个新的索引并且自动进行挂接。如果命令中没有指定索引名称，每个分区中的新索引的名称将被自动决定。如果指定了ONLY选项，则不会进行递归，并且该索引会被标记为无效（一旦所有的分区都得到该索引，ALTER INDEX ... ATTACH PARTITION可以把该索引标记为有效）。不过，要注意不管是否指定这一选项，未来使用CREATE TABLE ... PARTITION OF创建的任何分区将自动有一个匹配的索引，不管有没有指定ONLY。

对于支持有序扫描的索引方法（当前只有 B-树），可以指定可选子句ASC、 DESC、NULLS FIRST以及NULLS LAST 来修改索引的排序顺序。由于一个有序索引能前向或者反向扫描，通常创建一个 单列DESC索引没什么用处 — 一个常规索引已经提供了排序 顺序。这些选项的价值是可以创建多列索引，让它的排序顺序匹配有混合排序要求 的查询，例如SELECT ... ORDER BY x ASC, y DESC。如果你想要在依靠索引避免排序步骤的查询中支持 “空值排序低”这种行为，NULLS选项就能派上用场，默认 的行为是“空值排序高”。

对于大多数索引方法，索引的创建速度取决于 maintenance_work_mem的设置。较大的值将会减少 索引创建所需的时间，当然不要把它设置得超过实际可用的内存量（那会迫使 机器进行交换）。

PostgreSQL可以在构建索引时利用多个CPU以更快地处理表行。这种特性被称为并行索引构建。对于支持并行构建索引的索引方法（当前只有B-树），maintenance_work_mem指定每次索引构建操作整体可用的最大内存量，而不管启动了多少工作者进程。一般来说，一个代价模型（如果有）自动判断应该请求多少工作者进程。

增加maintenance_work_mem可以让并行索引构建受益，而等效的串行索引构建将无法受益或者得到很小的益处。注意maintenance_work_mem可能会影响请求的工作者进程的数量，因为并行工作者必须在总的maintenance_work_mem预算中占有至少32MB的份额。还必须有32MB的份额留给领袖进程。增加max_parallel_maintenance_workers可以允许使用更多的工作者，这将降低索引创建所需的时间，只要索引构建不是I/O密集型的。当然，还需要有足够的CPU计算能力，否则工作者们会闲置。

通过ALTER TABLE为parallel_workers设置一个值直接控制着CREATE INDEX会对表请求多少并行工作者进程。这会完全绕过代价模型，并且防止maintenance_work_mem对请求多少并行工作者产生影响。通过ALTER TABLE将parallel_workers设置为0将禁用所有情况下的并行索引构建。

提示

在把parallel_workers用于调优一次索引构建之后，你可能想要重置parallel_workers。这可以避免对查询计划的无意更改，因为parallel_workers影响所有的\并行表扫描。

虽然带有CONCURRENTLY选项的CREATE INDEX支持并行构建并且没有特殊的限制，但只有第一次表扫描会实际以并行方式执行。

使用DROP INDEX可以移除一个索引。

以前的PostgreSQL发行也有一种 R-树 索引方法。这种方法已经被移除，因为它比起 GiST 方法来说没有什么明显的 优势。如果指定了USING rtree，CREATE INDEX 将会把它解释为USING gist，以便把旧的数据库转换成 GiST。

示例

在表films中的列title上创建一个 B-树索引：

要在表films的列title上创建一个唯一的B-树索引并且包括列director和rating：

要创建禁用重复数据删除的 B 树索引：

在表达式lower(title)上创建一个索引来允许高效的大小写 无关搜索：

（在这个例子中我们选择省略索引名称，这样系统会选择一个名字， 通常是films_lower_idx）。

创建一个具有非默认排序规则的索引：

创建一个具有非默认空值排序顺序的索引：

创建一个具有非默认填充因子的索引：

创建一个禁用快速更新的GIN索引：

在表films中的列code上创建一个而索引并且 把索引放在表空间indexspace中：

在一个点属性上创建一个 GiST 索引，这样我们可以在转换函数的结果 上有效地使用 box 操作符：

创建一个表而不排斥对表的写操作：

兼容性

CREATE INDEX是一种 PostgreSQL的语言扩展。在 SQL 标准中 没有对于索引的规定。

另见

ALTER INDEX, DROP INDEX, REINDEX

全文搜索

http://postgres.cn/docs/13/textsearch.html

目录

标签：

头像

小麦苗

学习或考证，均可联系麦老师，请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿，我是小麦，需要帮助随时找我哦
  • 18509239930

  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群

  • 个人微店

  • 回到顶部
返回顶部