PostgreSQL中的B-tree索引

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

索引简介

在数据库中，一旦一个索引被创建，就不再需要进一步的干预：系统会在表更新时更新索引，而且会在它觉得使用索引比顺序扫描表效率更高时使用索引。但我们可能需要定期地运行ANALYZE命令来更新统计信息以便查询规划器能做出正确的决定。

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

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

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

B-tree索引

postgresql支持多种索引类型，每一种索引类型使用了 一种不同的算法来适应不同类型的查询。默认情况下， CREATE INDEX命令创建适合于大部分情况的B-tree 索引。
B-tree用于在可排序数据上等值及范围查询。特别地，PostgreSQL的查询规划器会在任何一种涉及到以下操作符的已索引列上考虑使用B-tree索引：

<	<=	=	>=	>

将这些操作符组合起来，例如BETWEEN和IN，也可以用B-tree索引搜索实现。同样，在索引列上的IS NULL或IS NOT NULL条件也可以在B-tree索引中使用。B-tree索引也可以用于涉及到模式匹配操作符LIKE和~ 的查询，前提是如果模式是一个常量且被固定在字符串的开头。
补充：主键和唯一键会自动创建B-tree索引，无需另外单独再为主键和唯一键创建索引。
关于B-tree索引的理论，可参考文章：https://www.cnblogs.com/bypp/p/7755307.html中的介绍。

多列索引

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

CREATE TABLE test2 (
  major int,
  minor int,
  name varchar
);

而我们经常会做如下形式的查询：

SELECT name FROM test2 WHERE major = constant AND minor = constant;

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

CREATE INDEX test2_mm_idx ON test2 (major, minor);

当使用多列索引时，有以下规则（也被称为最左匹配特性）：在先导列（即最左边的那些列）上的等值约束，加上第一个无等值约束的列上的不等值约束，将被用于限制索引被扫描的部分。在这些列右边的列上的约束将在索引中被检查，这样它们适当节约了对表的访问，但它们并未减小索引被扫描的部分。
用自己的话说，就是尽量将具有等值约束的索引放在最左边，以减少索引被扫描的范围，从而节约时间。
例如，在(a, b, c)上有一个索引并且给定一个查询条件WHERE a = 5 AND b >= 42 AND c < 77，对索引的扫描将从第一个具有a = 5和b = 42的项开始向上进行，直到最后一个具有a = 5的项。在扫描过程中，具有c >= 77的索引项将被跳过，但是它们还是会被扫描到。这个索引在原则上可以被用于在b和、或c上有约束而在a上没有约束的查询，但是整个索引都不得不被扫描，因此在大部分情况下规划器宁可使用一个顺序的表扫描来替代索引。

多列索引应该较少地使用。在绝大多数情况下，单列索引就足够了且能解决时间和空间。具有超过三个列的索引不太有用，除非该表的使用是极端程式化的。

表达式索引

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

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

SELECT * FROM test1 WHERE lower(col1) = 'value';

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

CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));

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

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

检查使用索引

尽管PostgreSQL中的索引并不需要维护或调优，但是检查真实的查询负载实际使用了哪些索引仍然非常重要。检查一个独立查询的索引使用情况可以使用EXPLAIN命令，也可以在一个运行中的服务器上收集有关索引使用的总体统计情况。
很难明确地表达决定创建哪些索引的通用过程。通常需要大量的实验才能决定应该创建哪些索引。对于创建和使用索引有以下提示：
1.总是先运行ANALYZE。这个命令会收集有关表中值分布情况的统计信息。估计一个查询将要返回的行数需要这些信息，而结果行数则被规划器用来为每一个可能的查询计划分配实际的代价。如果没有任何真实的统计信息，将会假定一些默认值，这几乎肯定是不准确的。在没有运行的情况下检查一个应用的索引使用情况是注定要失败的。
2.使用真实数据进行实验。避免使用很小的数据量来测试索引。
3.如果索引没有被用到，强制使用它们将会对测试非常有用。
4.如果强制索引使用确实使用了索引，则有两种可能性：系统是正确的并且索引确实不合适，或者查询计划的代价估计并没有反映真实情况。因此你应该对用索引的查询和不用索引的查询计时。此时EXPLAIN ANALYZE命令就能发挥作用了。
5.如果发现代价估计是错误的，也分为两种可能性。总代价是用每个计划节点的每行代价乘以计划节点的选择度估计来计算的。计划节点的代价估计可以通过运行时参数调整。不准确的选择度估计可能是由于缺乏统计信息，可以通过调节统计信息收集参数来改进。

并发构建索引

创建索引可能会干扰数据库的常规操作。通常 PostgreSQL会锁住要被索引的表，让它不能被写入， 并且用该表上的一次扫描来执行整个索引的构建。其他事务仍然可以读取表 ， 但是如果它们尝试在该表上进行插入、更新或者删除，它们会被阻塞直到索引 构建完成。如果系统是一个生产数据库，这可能会导致严重的后果。索引非常 大的表可能会需要很多个小时，而且即使是较小的表，在构建索引过程中阻塞 写入者一段时间在生产系统中也是不能接受的。

PostgreSQL支持构建索引时不阻塞写入。这种方法通过指定CREATE INDEX的CONCURRENTLY选项实现。当使用这个选项时，PostgreSQL必须执行该表的两次扫描，此外它必须等待所有现有可能会修改或者使用该索引的事务终止。因此这种方法比起标准索引构建过程来说要做更多工作并且需要更多时间。不过，由于它 允许在构建索引时继续普通操作，这种方式对于在生产环境中增加新索引很有用。 当然，由索引创建带来的额外 CPU 和 I/O 开销可能会拖慢其他操作。

在并发索引构建中，索引实际上在一个事务中被录入到系统目录，然后在两个事务中发生两次表扫描。在每一次表扫描之前，索引构建必须等待已经修改了 表的现有事务终止。在第二次扫描之后，索引构建必须等待任何持有早于第二次扫描的快照的事务终止。然后该索引最终 能被标记为准备好使用，并且CREATE INDEX命令终止。 不过即便那样，该索引也不是立刻可以用于查询：在最坏的情况下，只要早于索引构建开始时存在的事务存在，该索引就无法使用。

如果在扫描表示出现问题，例如死锁或者唯一索引中的唯一性被违背， CREATE INDEX将会失败，但留下一个"不可用" 的索引。这个索引会被查询所忽略，因为它可能不完整。不过它仍将消耗更新开销。psql的\d命令将把这类索引报告为 INVALID：

postgres=# \d tab
       Table "public.tab"
 Column |  Type   | Modifiers 
--------+---------+-----------
 col    | integer | 
Indexes:
    "idx" btree (col) INVALID

这种情况下推荐的恢复方法是删除该索引并且尝试再次执行 CREATE INDEX CONCURRENTLY（另一种可能性是用 REINDEX重建该索引。不过，由于 REINDEX不支持并发构建，这种选择不那么有吸引力）。

并发构建一个唯一索引时需要注意的另一点是，当第二次表扫描开始时，唯一约束已经被强制在其他事务上。这意味着在该索引变得可用之前，其他查询中可能就会报告该约束被违背，或者甚至在索引构建最终失败的情况中也是这样。还有，如果在 第二次扫描时发生失败，"无效的"索引也会继续强制它的唯一性约束。

表达式索引和部分索引的并发构建也被支持。在这些表达式计算过程中发生的错误可能导致和上述唯一约束违背类似的行为。

常规索引构建允许在同一个表上并行构建其他常规索引，但是在一个表上同时只能有一个并发索引构建发生。在两种情况下，同时都不允许在表上有其他类 型的模式修改。另一个不同是，一个常规CREATE INDEX 命令可以在一个事务块中执行，但是 CREATE INDEX CONCURRENTLY不行。

参考：
http://www.postgres.cn/docs/9.6/indexes.html