索引是数据库中一种快速查询数据的方法。索引中记录了表中一列或多列的值与其物理位置之间的对应关系，就好比试一本书正文面的目录，通过目录后面的页号能快速定位到需要查询的内容。
索引分类
在PostgreSQL中，支持以下几类索引：
- B-tree：最常用的索引，B-tree索引适合处理等值查询和范围查询
- Hash：智能处理简单的等值查询
- GiST：不是一种单独的索引类型，而是一种架构，可以在这种架构上实现很多不同的索引策略。GiST索引定义的特定操作符可以用于特定索引策略。PostgreSQL的标准发布中包含了用于二维几何数据类型的GiST操作符类。比如，一个图形包含另一个图形的操作符"@>",一个图形在另一个图形的左边且没有重叠的操作符"<<",等等。
- SP-GiST: SP-GiST是"space-partitioned GiST"的缩写，即空间分区GiST索引。它是从PostgreSQL9.2版本开始提高了一种新索引类型，主要是通过一些新的索引算法提高GiST索引在某个情况下的性能。
- GIN：反转索引，它可以处理包含多个键的值，如数组等。与GiST类似，GIN支持用户定义的索引策略，可通过定义GIN索引的特定操作符类型实现不同的功能。PostgreSQL的标准发布中包含了用于一维数组的GIN操作符类。比如，它支持包含操作符"@>",被包含操作符"<@",相等操作符"=",重叠操作符"&&".
创建索引
语法:
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ]
( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( column_name [, ...] ) ]
[ WITH ( storage_parameter [= value] [, ... ] ) ]
[ TABLESPACE tablespace_name ]
[ WHERE predicate ]一般在创建索引的过程中，会把表的数据都读一遍，这个过程所用时间由表的大小决定，对于比较大的表，可能会花很久的时间。
在创建索引的过程中，对表的查询可以正常运行，但对表的增删改等操作需要等索引建完后才能进行，为此PostgreSQL提供了一种并发创建索引的方法。
如何在不同的情况下创建索引？
假设有一个联系人的表
CREATE TABLE contacts(
id int primary key,
name varchar(40),
phone varchar(32)[],
address text
);
在该表中，由于一个人可能有多个电话号码，所以把phone定义为一个数组。
为了实现按姓名快速查询，可以在字段name上建一个简单的B-tree索引
CREATE INDEX idx_contacts_name on contacts(name);
插入数据
INSERT INTO contacts VALUES(1,'塞尔达',ARRAY['13422222221','13422222222'],'海拉鲁城堡');
INSERT INTO contacts VALUES(2,'林克',ARRAY['13411111111','13411111112'],'海拉鲁城堡');
INSERT INTO contacts VALUES(3,'英达',ARRAY['13433333333','13433333334'],'英拉村');
如果想按电话号码(phone)字段做快速查询，比如，想查询一个电话号码是谁的，由于此字段是一个数组，前面所建的B-tree索引不再起作用，因此这时可以建一个GIN索引
CREATE INDEX idx_contacts_phone on contacts using gin(phone);
这时就可进行快速查询了。假设想查询号码"13422222222"是谁的，查询
SELECT * FROM contacts WHERE phone @> array['13422222222'::varchar(32)];
注意 "@>"是数组操作符，表示包含。GIN索引能在"@>"上起作用。
Hash索引的更新不会记录到WAL日志中，索引在实际场景中应用较少。
创建索引时指定存储参数"WITH(storage_parameter=value)" ,常用的存储参数为FILLFACTOR。
CREATE INDEX idx_contacts_name_01 on contacts(name) WITH (FILLFACTOR=50);
也可以按降序建索引
CREATE INDEX idx_contacts_name_02 on contacts(name desc);
如果时字段name 中有空值，可以在建索引时，指定空值排在非空值前面
CREATE INDEX idx_contacts_name_03 on contacts(name DESC NULLS FIRST);
或空值排在非空值后面
CREATE INDEX idx_contacts_name_04 on contacts(name DESC NULLS LAST);
并发创建索引
通常，在创建索引的说话PostgreSQL会锁定表以防止写入，然后对表做全表扫描，从而完成创建索引操作。
在此过程中，其他用仍然可以读取表，但是插入、更新、删除等操作将被一直阻塞，知道索引创建完毕。如果这张表更新比较频繁，且表比较大，那么创建索引可能需要几十分钟，甚至数个小时，这段时间内都不能做任何的插入、删除、更新，这在大多数的在线数据库中都是不可接受的行尾。鉴于此，PostgreSQL支持不长时间阻塞更新的情况下创建索引，这是通过在CREATE INDEX中加CONCURRENTLY(并发创建索引)选项来实现的。当该选项被使用时，PostgreSQL会执行表的两次扫描，因此该方法需要更长一些的时间来建索引。尽管如此，这个选项也是很有用的一个功能。
测试表
CREATE TABLE test01(id int primary key, note text);
插入数据
INSERT INTO test01 select generate_series(1,500000), generate_series(1,500000);
这时开两个psql的窗口，在其中一个创建中建索引：
psql postgres
\timing
CREATE INDEX idx_test01_note on test01(note);
另一个窗口中删除一条数据，可以看到，它一直在等另一个窗口中创建索引的操作完成：
psql postgres
\timing
DELETE FROM test01 where id=1;
如果创建索引时加上"CONCURRENTLY"选项
DROP INDEX idx_test01_note;
CREATE INDEX CONCURRENTLY idx_test01_note on test01(note);
另一个窗口的删除操作不会出现等待
DELETE FROM test01 where id=2;
DELETE FROM test01 where id=3;
一个表经过频繁更新后，如果想重建其上的索引该怎么做？
要知道，在PostgreSQL中重建索引不支持"CONCURRENTLY"选项，但它支持在同个字段中建两个索引，因此可以考虑这样做：使用"CONCURRENTLY"选项建一个新的索引，
然后把旧索引删除掉，这样就相对于重建了这个索引
CREATE INDEX CONCURRENTLY idx_test01_note on test01(note);
CREATE INDEX CONCURRENTLY idx_test01_note_2 on test01(note);
DROP INDEX idx_test01_note;
db01=# \d test01
Table "public.test01"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
note | text | | |
Indexes:
"test01_pkey" PRIMARY KEY, btree (id)
"idx_test01_note_2" btree (note)
并发创建索引的时候需要注意，如果索引在创建过程中被强制取消，可能会留下一个无效索引，这个索引仍然会导致更新变慢。
如果创建的是唯一索引，这个无效的索引还会导致插入重复值失败，测试示例如下：
先在索引创建过程中取消操作
CREATE INDEX CONCURRENTLY idx_test01_note on test01(note);
^CCancel request sent
ERROR: canceling statement due to user request
然后使用\d查看表，可以看到遗留一个INVALID索引:
db01=# \d test01
Table "public.test01"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
id | integer | | not null |
note | text | | |
Indexes:
"test01_pkey" PRIMARY KEY, btree (id)
"idx_test01_note" btree (note) INVALID
"idx_test01_note_2" btree (note)
这时，若插入重复数据，此无效唯一索引的约束仍然有效，如下：
INSERT INTO test01 VALUES(10,'10');
ERROR: duplicate key value violates unique constraint "test01_pkey"
DETAIL: Key (id)=(10) already exists.
对于此，手工删除索引就可以了。
DROP INDEX idx_test01_note;修改索引
语法:
ALTER INDEX name RENAME TO new_name
ALTER INDEX name SET TABLESPACE tablespace_name例子:
给索引改名
ALTER INDEX idx_contacts_name RENAME TO idx_contacts_name_old;
把索引移到表空间tbs_data01下
ALTER INDEX idx_contacts_name_old SET TABLESPACE tbs_data01;
把索引的填充因子(fillfactor)设置为50
ALTER INDEX idx_contacts_name_old SET (fillfactor=75);
把索引的填充因子重置为默认值
ALTER INDEX idx_contacts_name_old RESET (fillfactor);
查看索引的信息
db01=# \d+ idx_contacts_name_old
Index "public.idx_contacts_name_old"
Column | Type | Key? | Definition | Storage | Stats target
--------+-----------------------+------+------------+----------+--------------
name | character varying(40) | yes | name | extended |
btree, for table "public.contacts"
删除所有
语法:
DROP INDEX name [, ...] [ CASCADE | RESTRICT ]例子：
确认索引是否存在，若存在则删除，若不存在，将不报错
DROP INDEX IF EXISTS idx_contacts_name_old;
DROP INDEX IF EXISTS idx_contacts_name_old;
NOTICE: index "idx_contacts_name_old" does not exist, skipping
DROP INDEX
删除索引时，默认会使用选项"RESTRICT",如果有依赖对象依赖这个索引，则删除会失败。而使用"CASCADE"选项，表示当有依赖于这个索引的对象时，一并把这些对象删除掉，例如外键约束。
CREATE TABLE class(
class_no int,
class_name varchar(40)
);
CREATE UNIQUE INDEX index_unique_class_no ON class(class_no);
CREATE TABLE student(
student_no int primary key,
student_name varchar(40),
age int,
class_no int REFERENCES class(class_no)
);
如果表"student"上的外键引用了表"class"上的唯一索引"index_unique_class_no",这时删除此索引将会失败
DROP INDEX index_unique_class_no;
ERROR: cannot drop index index_unique_class_no because other objects depend on it
DETAIL: constraint student_class_no_fkey on table student depends on index index_unique_class_no
HINT: Use DROP ... CASCADE to drop the dependent objects too.
此时，加上CASCADE即可删除成功
DROP INDEX index_unique_class_no CASCADE;