1、背景：
索引是提高数据库性能的常用途径。使用索引可以让数据库服务器更快找到并获取特定行。但是索引同时也会增加数据库系统的日常管理负担，因此如何正确的使用索引是每个数据库使用人员都关心的事情。
PostgreSQL中索引类型很多，并且开放了索引接口，这就允许用户可以自定义索引类型，使得PG支持非常丰富的索引方法。例如btree , hash , gin , gist , sp-gist , brin , bloom , rum , zombodb , bitmap (greenplum)，用户可以根据不同的数据类型，以及查询的场景，选择不同的索引。
面对众多的索引类型，我们该如何选择、使用呢？我打算通过这一系列文章，结合自己之前的学习和使用经历，来详细介绍下PostgreSQL中的索引使用，本文先对pg的索引做个总体的介绍。
2、索引简介：
在PostgreSQL中，索引是特殊的数据库对象，主要用于加速数据访问。它们是辅助结构：可以删除每个索引，然后从表中的信息重新创建它们。可能你会觉得没有索引其实也没什么关系，最多查询变慢点，但其实索引除了加速查询之外还有其它的作用，例如可以用来保证数据完整性，如unique索引。
目前，在pg12中内置了btree , hash , gin , gist , sp-gist , brin这6种索引，还有一个内置的扩展索引(bloom索引)。
尽管索引类型（也称为访问方法）之间存在差异，但它们最终都会将一个键（例如，索引列的值）与包含该键的表行相关联。每行都由ctid（元组ID）标识，ctid是PostgreSQL中的一个系统列，用来表示数据记录的物理行信息，指的是一条记录位于哪个数据块的哪个位移上面。 跟oracle中伪列rowid 的意义一样的，只是形式不一样。也就是说，使用已知键或有关它的某些信息，我们可以快速读取可能包含我们感兴趣的信息的那些行，而无需扫描整个表。
重要的是要了解索引以一定的维护成本加快了数据访问速度。对于索引数据的每个操作，无论是插入，删除还是更新表行，也需要在同一事务中更新该表的索引。请注意，未建立索引的表字段的更新不会导致索引更新。这项技术称为HOT（Heap-Only Tuples）。
可扩展性带来一些影响。为了能够轻松地向系统添加新的访问方法，已经实现了通用索引引擎的接口。它的主要任务是从访问方法中获取ctid并使用它们：
- 从表行的相应版本中读取数据。
- 使用预建位图按ctid或批量获取行版本的ctid。
- 考虑到其隔离级别，请检查当前事务的行版本的可见性。
索引引擎参与执行查询。根据在优化阶段创建的计划来调用它。优化器整理并评估执行查询的不同方法，应了解所有可能适用的访问方法的功能。
该方法将能够按所需顺序返回数据，还是应该预期排序？我们可以使用这种方法搜索NULL吗？这些是优化器定期解决的问题。
不仅优化程序需要有关访问方法的信息。建立索引时，系统必须决定是否可以在多个列上建立索引以及该索引是否确保唯一性。
因此，每种访问方法都应提供有关其自身的所有必要信息。低于9.6的版本为此使用pg_am表，而从9.6版开始，则将数据移至更深层次的特殊功能内。我们将进一步熟悉该界面。
剩下的就是访问方法的任务：
- 实现用于构建索引并将数据映射到页面的算法（以使缓冲区高速缓存管理器统一处理每个索引）。
- 通过谓词以“ 索引字段运算符表达式 ” 形式在索引中搜索信息。
- 评估索引使用成本。
- 操作正确的并行处理所需的锁。
- 生成预写日志（WAL）记录。
我们将首先考虑通用索引引擎的功能，然后再考虑不同的访问方法。
3、索引引擎：
索引引擎使PostgreSQL可以统一使用各种访问方法，但要考虑到它们的功能。这里我们介绍下主要的索引扫描技术。
3.1、index scan
这是最简单的索引扫描，通过索引获得数据行的ctid，然后通过ctid去表中得到数据。
bill=# create table t(a integer, b text, c boolean);
CREATE TABLE
bill=# insert into t(a,b,c)
bill-# select s.id, chr((32+random()*94)::integer), random() < 0.01
bill-# from generate_series(1,100000) as s(id)
bill-# order by random();
INSERT 0 100000
bill=# create index on t(a);
CREATE INDEX
bill=# analyze t;
ANALYZE
我们创建了一个三个字段的表。第一个字段包含从1到100000的数字，并且在此字段上创建索引（无论什么类型）。第二个字段包含各种ASCII字符，但不可打印的字符除外。最后，第三个字段包含一个逻辑值，该逻辑值对于大约1％的行为true，对于其余行为false。行以随机顺序插入表中。
然后我们进行索引扫描查询：
bill=# explain select * from t where a = 1;
QUERY PLAN
-----------------------------------------------------------------
Index Scan using t_a_idx on t (cost=0.29..2.91 rows=1 width=7)
Index Cond: (a = 1)
(2 rows)
可以看到这里使用了index scan。使用索引扫描时，我们将根据过滤条件一个接一个的得到ctid，直到到达最后一个匹配行。索引引擎依次访问由ctid指示的表行，获取行版本，针对多版本并发规则检查其可见性，并返回获得的数据。
3.2、Bitmap scan
当我只查询少量数据时会使用index scan，但是当我们查询的数据变多时，很可能会多次返回表的同一个数据页多次，那么这时就可能会用到bitmap scan。
bill=# explain select * from t where a <= 100;
QUERY PLAN
-----------------------------------------------------------------------
Bitmap Heap Scan on t (cost=2.34..106.19 rows=97 width=7)
Recheck Cond: (a <= 100)
-> Bitmap Index Scan on t_a_idx (cost=0.00..2.32 rows=97 width=0)
Index Cond: (a <= 100)
(4 rows)
可以看到这里使用了bitmap scan。这种访问方法首先返回所有与条件匹配的ctid（位图索引扫描节点），然后根据这些ctid构建行版本的位图。然后从表中读取行版本（位图堆扫描），每页仅读取一次。
bitmap scan在对应索引中找到符合条件的堆表PAGE，每个索引构造一个bitmap串。在这个bitmap串中，每一个BIT位对应一个HEAP PAGE，代表这个HEAP PAGE中有符合该条件的行(只要任意一行符合条件则该PAGE的BIT位就会被标位1)。
如果在几个表字段上都加上条件并为这些字段建立索引，则bitmap scan将允许同时使用多个索引。对于每个索引，将构建行版本的bitmap串，然后针对这些位图执行按位bitmap and（如果表达式通过AND进行连接）或bitmap or（如果表达式通过OR进行连接）。例如：
bill=# create index on t(b);
CREATE INDEX
bill=# analyze t;
ANALYZE
bill=# explain select * from t where a <= 100 and b = 'a';
QUERY PLAN
--------------------------------------------------------------------------------
Bitmap Heap Scan on t (cost=14.58..15.90 rows=1 width=7)
Recheck Cond: ((a <= 100) AND (b = 'a'::text))
-> BitmapAnd (cost=14.58..14.58 rows=1 width=0)
-> Bitmap Index Scan on t_a_idx (cost=0.00..2.34 rows=100 width=0)
Index Cond: (a <= 100)
-> Bitmap Index Scan on t_b_idx (cost=0.00..11.99 rows=1023 width=0)
Index Cond: (b = 'a'::text)
(7 rows)
总的来说，bitmap scan和index scan最大的区别就在于：bitmap scan可以使我们能够避免重复访问同一数据页。
3.3、Sequential scan
顺序扫描，即指按照表中数据的顺序去扫描，而不是使用索引扫描。
bill=# explain select * from t where a <= 40000;
QUERY PLAN
------------------------------------------------------------------------
Index Scan using t_a_idx on t (cost=0.29..1420.73 rows=40014 width=7)
Index Cond: (a <= 40000)
(2 rows)
当我们需要从表中获取大量数据时就会用到顺序扫描，为什么呢？
因为使用索引扫描随着需要查找的数据量增大时查询的开销也会越大，因为索引扫描时一种随机扫描，顺序扫描比随机扫描更快。这尤其适用于机械盘，在机械盘中，将磁头带到磁道上的机械操作比读取数据本身要花费更多的时间。而对于SSD，这种影响不太明显。我们可以通过"seq_page_cost”和“ random_page_cost”这两个参数来设置。并且我们不仅可以全局设置，而且可以在表空间级别设置这种方式，以适应不同磁盘子系统的特性。
3.4、index only scan
通常我们通过索引扫描都需要根据ctid返回表中去获取数据，但是如果我们查询的所有数据都包含在索引列中那么还需要回表吗？pg针对这种情况提供了index only scan。
bill=# explain select a from t where a < 100;
QUERY PLAN
-------------------------------------------------------------------------
Index Only Scan using t_a_idx on t (cost=0.29..113.74 rows=94 width=4)
Index Cond: (a < 100)
(2 rows)
那么这是如何实现的呢？要知道在pg中由于索引上没有多版本的信息，那即使select的全部是索引列，不还是应该得回表获取数据吗？
从pg9.2开始，引入了index-only scan的索引扫描方法，顾名思义即只扫描索引列。这种方法并不是在索引上添加多版本信息，而是通过可见性映射文件来实现，可见性映射文件中记录了数据块中的数据行是否对全部事务可见，如果是则不必再返回表获取数据。且只支持btree索引，gist和sp-gist索引的部分操作符，其它索引类型均不支持。
注：pg11新增了一个新功能：include index。因为某些场景下，例如当我们只扫描a，b两列，但是我们并不能把a，b都加入到一个索引中，因为a可能需要满足唯一性约束，这个时候我们使用include index仍能使用index only scan。
3.5、多列索引
为了支持多个字段的条件，可以使用多列索引。例如，我们可以在表的两个字段上建立索引：
bill=# create index on t(a,b);
CREATE INDEX
bill=# analyze t;
ANALYZE
优化器很可能更喜欢此索引而不是bitmap index，因为在这里，我们无需任何辅助操作即可轻松获得所需的ctid：
bill=# explain select * from t where a <= 100 and b = 'a';
QUERY PLAN
-------------------------------------------------------------------
Index Scan using t_a_b_idx on t (cost=0.29..3.91 rows=1 width=7)
Index Cond: ((a <= 100) AND (b = 'a'::text))
(2 rows)
从第一个字段开始，通过某些字段的条件，也可以使用多列索引来加速数据检索：
bill=# explain select * from t where a <= 100;
QUERY PLAN
--------------------------------------------------------------------------
Bitmap Heap Scan on t (cost=2.38..109.56 rows=101 width=7)
Recheck Cond: (a <= 100)
-> Bitmap Index Scan on t_a_b_idx (cost=0.00..2.35 rows=101 width=0)
Index Cond: (a <= 100)
(4 rows)
通常，如果不对第一个字段强加条件，则不会使用索引。但是有时优化器可能认为索引的使用比顺序扫描更有效。
3.6、表达式索引
一个索引列并不一定是底层表的一个列，也可以是从表的一列或多列计算而来的一个函数或 者标量表达式。这种特性对于根据计算结果快速获取表中内容是有用的。这种情况我们可以表达式索引，例如：
bill=# create index on t(lower(b));
CREATE INDEX
bill=# analyze t;
ANALYZE
bill=# explain select * from t where lower(b) = 'a';
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using t_lower_idx on t (cost=0.42..205.60 rows=2137 width=7)
Index Cond: (lower(b) = 'a'::text)
(2 rows)
表达式索引不是建立在表字段上，而是建立在任意表达式上。因此优化器对于表达式将会考虑使用索引扫描，如果索引的表达式计算代价很大，那么更新索引该表达式索引时代价也会很大。
另外需要注意的是：已为索引表达式收集了单个统计信息。我们可以在pg_stats视图中通过索引名称了解此统计信息：
bill=# \d t
Table "public.t"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
a | integer | | |
b | text | | |
c | boolean | | |
Indexes:
"t_a_b_idx" btree (a, b)
"t_a_idx" btree (a)
"t_b_idx" btree (b)
"t_lower_idx" btree (lower(b))
bill=# select * from pg_stats where tablename = 't_lower_idx';
如有必要，我们可以用与常规数据字段相同的方式来控制直方图篮的数量（请注意，列名可能因索引表达式而异)，例如：
bill=# \d t_lower_idx
Index "public.t_lower_idx"
Column | Type | Key? | Definition
--------+------+------+------------
lower | text | yes | lower(b)
btree, for table "public.t"
bill=# alter index t_lower_idx alter column "lower" set statistics 69;
ALTER INDEX
注：PostgreSQL 11可以通过在ALTER INDEX…SET STATISTICS命令中指定列号来修改。
3.6、部分索引
一个部分索引是建立在表的一个子集上，而该子集则由一个条件表达式(被称为部分索引 的谓词)定义。而索引中只包含那些符合该谓词的表行的项。部分索引是一种专门的特性， 但在很多种情况下它们也很有用。
有时需要只索引部分表行。这通常与高度不均匀的分布有关：通过索引搜索不频繁的值很有意义，但是通过对表进行全面扫描更容易找到频繁的值。
例如我们可以在c列上建立一个常规索引，该索引将按照我们期望的方式工作：
bill=# create index on t(c);
CREATE INDEX
bill=# analyze t;
ANALYZE
bill=# explain select * from t where c;
QUERY PLAN
--------------------------------------------------------------------
Index Scan using t_c_idx on t (cost=0.42..88.59 rows=947 width=7)
Index Cond: (c = true)
(2 rows)
bill=# explain select * from t where not c;
QUERY PLAN
--------------------------------------------------------
Seq Scan on t (cost=0.00..1443.00 rows=99053 width=7)
Filter: (NOT c)
(2 rows)
索引大小为278个page：
bill=# select relpages from pg_class where relname='t_c_idx';
relpages
----------
278
(1 row)
但是由于c列仅对1％的行具有true值，因此实际上从未使用过99％的索引。在这种情况下，我们可以建立部分索引：
bill=# create index on t(c) where c;
CREATE INDEX
bill=# analyze t;
ANALYZE
索引页变成了5:
bill=# select relpages from pg_class where relname='t_c_idx1';
relpages
----------
5
(1 row)
有时，大小和性能上的差异可能非常明显。
3.7、排序
如果访问方法以某些特定顺序返回行数据，这将为优化器提供执行查询的其他选项。
我们可以扫描表，然后对数据进行排序：
bill=# set enable_indexscan=off;
SET
bill=# explain select * from t order by a;
QUERY PLAN
---------------------------------------------------------------
Sort (cost=9747.82..9997.82 rows=100000 width=7)
Sort Key: a
-> Seq Scan on t (cost=0.00..1443.00 rows=100000 width=7)
(3 rows)
但是我们可以通过按照期望的顺序使用索引轻松读取数据：
bill=# set enable_indexscan=on;
SET
bill=# explain select * from t order by a;
QUERY PLAN
-------------------------------------------------------------------------
Index Scan using t_a_idx on t (cost=0.29..2434.99 rows=100000 width=7)
(1 row)
所有访问索引中只有btree索引可以进行排序。
另外需要注意：一个索引在每一个索引列上只能支持一种排序规则。如果需要多种排序规则，你可能需要多个索引。
3.8、并发创建索引
通常，建立索引需要为表获取SHARE锁。该锁允许从表中读取数据，但禁止在建立索引时进行任何更改。
我们可以确定这一点，例如，如果在t表上建立索引期间，我们在另一个会话中执行以下查询：
bill=# select mode, granted from pg_locks where relation = 't'::regclass;
mode | granted
-----------+---------
ShareLock | t
(1 row)
如果表足够大并且广泛用于插入，更新或删除，则这似乎是不可接受的，因为修改过程将长时间等待锁释放。
在这种情况下，我们可以使用并发建立索引。
bill=# create index CONCURRENTLY on t(a);
CREATE INDEX
此命令将表锁定为SHARE UPDATE EXCLUSIVE模式，该模式允许读取和更新（仅更改表结构是禁止的，并且不允许同时清理，分析或在该表上构建另一个索引）。
但是，也有另一面。首先，索引的建立将比平时慢，因为完成了两次遍历表而不是一次遍历，并且还必须等待修改数据的并行事务完成。
3.9、NULL
在PostgreSQL中，NULL表示不存在或未知值。pg中创建索引时是可以包含null值的，这一点和oracle不同。因此pg中还支持nulls first/last这种写法。
参考链接：
https://habr.com/en/company/postgrespro/blog/441962/
https://www.postgresql.org/docs/12/indexes.html