索引
在 PostgreSQL 中，索引是一种特殊的数据库对象，主要用于加速数据访问。它们是辅助结构：每个索引都可以从表中的信息中删除和重新创建。您有时可能会碰巧听到 DBMS 可以在没有索引的情况下工作，尽管速度很慢。然而，情况并非如此，因为索引还用于强制执行某些完整性约束。
尽管索引类型（也称为访问方法 Access Method）之间存在所有差异，但它们中的每一个最终都会将一个键（例如，索引列的值）与包含该键的表行相关联。每行由 TID（元组 ID）标识，它由文件中的块号和该行在块内的位置组成。也就是说，使用已知的键或有关它的一些信息，我们可以快速读取那些可能包含我们感兴趣的信息的行，而无需扫描整个表。
索引以一定的维护成本加速数据访问。对于索引数据的每个操作，无论是插入、删除还是更新表行，该表的索引也需要更新，并且在同一个事务中。请注意，更新尚未建立索引的表字段不会导致索引更新；这种技术称为 HOT（Heap-Only-Tuple）。
为了能够轻松地向系统添加新的访问方法，已经实现了通用索引引擎的接口。它的主要任务是从访问方法中获取 TID 并使用它们：
- 从表行的相应版本中读取数据。
- 通过 TID 或使用预构建位图批量获取行版本 TID。
- 检查当前事务的行版本的可见性，同时考虑其隔离级别。
- 索引引擎参与执行查询。根据在优化阶段创建的计划调用它。优化器整理和评估执行查询的不同方式，应该了解所有可能适用的访问方法的功能。该方法是否能够以所需的顺序返回数据，或者我们应该预期排序吗？我们可以使用这种方法来搜索NULL吗？这些是优化器定期解决的问题。
不仅优化器需要有关访问方法的信息。在建立索引时，系统必须决定索引是否可以建立在几个列上，以及这个索引是否保证唯一性。
因此，每个访问方法都应提供有关其自身的所有必要信息。低于 9.6 的版本为此使用了“pg_am”表，而从版本 9.6 开始，数据移动到更深层次的特殊函数内部。我们将进一步熟悉这个界面。
剩下的就是访问方法(Access Method)的任务了：
实现构建索引的算法，并将数据映射到页面中（用于缓冲区缓存管理器统一处理每个索引）。
通过“索引字段运算符表达式”形式的谓词搜索索引中的信息。
评估索引使用成本。
操纵正确并行处理所需的锁。
生成预写日志 (WAL) 记录。
我们将首先考虑通用索引引擎的功能，然后继续考虑不同的访问方法。
索引引擎
索引引擎使 PostgreSQL 能够统一使用各种访问方法，但要考虑到它们的特性。
主要扫描技术
索引扫描（Index Scan）
通过索引可以找到 TID，可以让 TID 以不同的方式工作。举例：
postgres=# create table t(a integer, b text, c boolean);
postgres=# insert into t(a,b,c) select s.id, chr((32+random()*94)::integer), random() < 0.01 from generate_series(1,100000) as s(id) order by random();
postgres=# create index on t(a);
postgres=# analyze t;
postgres=# explain (costs off) select * from t where a = 1; QUERY PLAN ------------------------------- Index Scan using t_a_idx on t Index Cond: (a = 1)复制
上面这个例子中优化器决定使用索引扫描。使用索引扫描，访问方法会一个一个地返回 TID 值，直到到达最后一个匹配行。 索引引擎依次访问 TID 指示的表行，获取行版本，根据多版本并发规则检查其可见性，并返回获取的数据。
位图扫描（Bitmap scan）
当处理的值不多的情况下，索引扫描可以很好地工作。但是，随着检索行数的增加，很可能多次访问同一个 page，这时候需要用到位图扫描。
postgres=# explain (costs off) select * from t where a <= 100; QUERY PLAN ------------------------------------ Bitmap Heap Scan on t Recheck Cond: (a <= 100) -> Bitmap Index Scan on t_a_idx Index Cond: (a <= 100) (4 rows)复制
使用位图扫描时，访问方法首先返回所有符合条件的 TID（Bitmap Index Scan），根据这些 TID 构建行版本的位图。然后从表中读取行版本（Bitmap Heap Scan），每个页面只读取一次。
在第二步中，可能会重新检查条件（Recheck Cond）。检索的行数可能太大，行版本的位图无法完全放到内存中（受“work_mem”参数限制）。在这种情况下，位图仅针对包含至少一个匹配行版本的页面构建。这个“有损”位图占用更少的空间，但是在读取页面时，我们需要重新检查其中包含的每一行的条件。
如果查询条件涉及表上的多个字段且这些字段上都有索引，位图扫描允许同时使用多个索引（如果优化器认为这样有效）。对于每个索引，构建行版本的位图，然后对其执行按位布尔乘法（如果表达式由 AND 连接）或布尔加法（如果表达式由 OR 连接）。例如：
postgres=# create index on t(b); postgres=# analyze t; postgres=# explain (costs off) select * from t where a <= 100 and b = 'a'; QUERY PLAN -------------------------------------------------- Bitmap Heap Scan on t Recheck Cond: ((a <= 100) AND (b = 'a'::text)) -> BitmapAnd -> Bitmap Index Scan on t_a_idx Index Cond: (a <= 100) -> Bitmap Index Scan on t_b_idx Index Cond: (b = 'a'::text) (7 rows)复制
这里通过按位执行 AND 操作 连接两个位图。
顺序扫描（Sequential Scan）
如果查询的条件不具有选择性，优化器更倾向于对整个表进行顺序扫描而不是使用索引。
postgres=# explain (costs off) select * from t where a <= 40000; QUERY PLAN ------------------------ Seq Scan on t Filter: (a <= 40000) (2 rows)复制
查询条件的选择性越高，索引工作得越好，也就是说，匹配到的行越少。 检索行数的增长增加了读取索引页的开销。
顺序扫描比随机扫描更快，使这个问题更复杂。 尤其适用于硬盘，将磁头带到磁道的机械操作比读取数据本身花费的时间要多得多。 对于 SSD，这种影响不太明显。 有两个参数可以考虑访问成本的差异，“seq_page_cost”和“random_page_cost”，我们不仅可以全局设置，还可以在表空间级别设置，这样可以适应不同磁盘子系统的特性。
覆盖索引(Covering Indexes)
通常，索引的主要任务是返回匹配表行的标识符，以便索引引擎从这些行中读取必要的数据。 但是如果索引已经包含查询所需的所有数据呢？ 例如，查询要返回的列就是索引列，且索引中包含所有数据。这样的索引称为覆盖，在这种情况下，优化器可以应用仅索引扫描（Index Only Scan）：
postgres=# vacuum t; postgres=# explain (costs off) select a from t where a < 100; QUERY PLAN ------------------------------------ Index Only Scan using t_a_idx on t Index Cond: (a < 100) (2 rows)复制
Index Only Scan 听上去让人觉得索引引擎根本不访问表，而是单独从索引中获取所有必要的信息。但事实并非如此，因为 PostgreSQL 中的索引不存储使我们能够判断行可见性的信息。因此，索引返回匹配搜索条件的行版本，而不管它们在当前事务中的可见性。
但是，如果索引引擎每次都需要查看表的可见性，则这种扫描方法与常规索引扫描没有任何不同。
为了解决这个问题，PostgreSQL 为表维护了一个所谓的可见性映射，其中 vacuum 标记数据更改时间不够长的页面，以便所有事务都可以看到这些数据，无论开始时间和隔离级别如何。如果索引返回的行标识符与这样的页面相关，则可以避免可见性检查。
因此，定期 vacuum 可以提高 Index Only Scan 的效率。此外，优化器会考虑死元组的数量，如果预测可见性检查的开销成本很高，则可以决定不使用 Index Only Scan。
可以使用 EXPLAIN ANALYZE 命令了解对表的强制访问次数：
postgres=# explain (analyze, costs off) select a from t where a < 100; QUERY PLAN ------------------------------------------------------------------------------- Index Only Scan using t_a_idx on t (actual time=0.025..0.036 rows=99 loops=1) Index Cond: (a < 100) Heap Fetches: 0 Planning time: 0.092 ms Execution time: 0.059 ms (5 rows)复制
上面的 case 中，不需要访问表 (Heap Fetches: 0)，因为刚刚完成了 vacuum 工作。 一般来说，这个数字越接近零越好。
并非所有索引都将索引值与行标识符一起存储。 如果访问方法无法返回数据，则不能用 Index Only Scan。
NULL 值
NULL 在关系数据库中扮演着重要的角色，作为一种表示不存在或未知值的便捷方式。
但是特殊值需要特殊处理。NULL 不清楚应该比常规值小还是大（这需要特殊的排序结构，NULLS FIRST 和 NULLS LAST）；聚合函数是否应该考虑 NULL 并不清楚；查询计划计划需要一个特殊的统计数据…
从索引支持的角度来看，是否需要索引为 NULL 的值也不清楚。如果 NULL 未编入索引，则索引可能更紧凑。但是，如果对 NULL 进行索引，我们将能够将索引用于像“索引字段是 [NOT] NULL”之类的条件，并且还可以在没有为表指定任何条件时用作覆盖索引（因为在这种情况下，index 必须返回所有表行的数据，包括那些有 NULL 的行）。
对于每个访问方法，开发人员单独决定是否索引 NULL。但作为一项规则，它们确实会被编入索引。
多列索引（Index on several fields）
为了支持多个字段的条件，可以使用多列索引。 例如，我们可能会在表的两个字段上建立索引：
postgres=# create index on t(a,b); postgres=# analyze t;复制
优化器很可能更倾向于使用这个索引而不是连接位图，因为在这里我们很容易获得所需的 TID，而无需任何辅助操作：
多列索引也可用于通过某些字段的条件加快数据检索：
postgres=# explain (costs off) select * from t where a <= 100; QUERY PLAN -------------------------------------- Bitmap Heap Scan on t Recheck Cond: (a <= 100) -> Bitmap Index Scan on t_a_b_idx Index Cond: (a <= 100) (4 rows)复制
一般情况下，使用多列索引时，如果没有对多列索引的第一列的强加条件，则不会使用索引。但有时优化器可能认为使用索引比顺序扫描更有效。
另外需要注意的是，并非所有索引方法都支持多列索引。
表达式索引（Indexes on expressions）
通常使用索引时，索引列的过滤条件必须看起来像 “索引字段运算符表达式”。下面这个例子，将不能使用索引，因为过滤条件是索引列的表达式，而不是列本身：
postgres=# explain (costs off) select * from t where lower(b) = 'a'; QUERY PLAN ------------------------------------------ Seq Scan on t Filter: (lower((b)::text) = 'a'::text) (2 rows)复制
这种情况下，使用表达式索引可能有所帮助：
postgres=# create index on t(lower(b)); postgres=# analyze t; postgres=# explain (costs off) select * from t where lower(b) = 'a'; QUERY PLAN ---------------------------------------------------- Bitmap Heap Scan on t Recheck Cond: (lower((b)::text) = 'a'::text) -> Bitmap Index Scan on t_lower_idx Index Cond: (lower((b)::text) = 'a'::text) (4 rows)复制
该索引不是建立在表字段上，而是建立在任意表达式上。 优化器将在“索引表达式运算符表达式”等条件下考虑此索引。 如果要索引的表达式的计算是一个代价高昂的操作，那么索引的更新也将需要相当多的计算资源。
另外，PG 为索引表达式收集了单独的统计信息。 我们可以通过索引名称在“pg_stats”视图中了解此统计信息。
部分索引（Partial indexes）
有时需要仅对表中的一部分行建立索引。这通常数据分布不均匀有关：通过索引搜索不常见的值是有意义的，但通过对表进行全扫描更容易找到频繁的值。
排序（Sorting）
如果索引以某种特定顺序返回行标识符，则为优化器提供了执行查询的附加选项，例如需要对返回数据进行排序。
如果索引中数据已经是按照字段排好序的，则不需要额外的排序工作。
并发构建（Concurrent building）
索引构建通常要花费较长的时间，如果构建期间不能正常执行 DML 操作，对业务可能会产生一些影响。并发构建，通过降低锁的级别以及对索引构建流程的控制，能够在不影响读写操作的情况下，完成索引构建。
