PgSQL 索引

索引是数据库中的一种快速查询数据的方法。索引中记录了表中的一列或多列值与其物理位置之间的对应关系，就好比是一本书前面的目录，通过目录中页码就能快速定位到我们需要查询的内容。

建立索引的好处是加快对表中记录的查找或排序。但建索引要付出以下代价：

增加了数据库的存储空间。
在插入和修改数据时要花费较多的时间，因为索引也要随之更新。

除有加快查询的作用外，索引还有一些其他的用途，如唯一索引还可以起到唯一约束的作用。

索引的分类

PostgreSQL中支持以下几类索引。

BTree：最常用的索引，BTree索引适合用于处理等值查询和范围查询。
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 ] [ name ] ON table_name [ USING method ]
  ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
  [ 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
);

为了实现按姓名“name”快速查询，可以在字段“name”上建一个简单的BTree索引，命令如下：

CREATE INDEX idx_contacts_name on contacts(name);

想查询某个电话号码是谁的，由于此字段是一个数组，前面所建的BTree索引将不再起作用，这时可以建一个GIN索引，命令如下：

CREATE INDEX idx_contacts_phone on contacts using gin(phone);

如果想查询号码“13422334455”是谁的，则可以使用下面的查询语句：

SELECT * FROM contacts WHERE phone @> array['13422334455'::varchar(32)];

注意，“@>”是数组操作符，表示“包含”的意思，GIN索引能在“@>”上起作用。

创建索引的时候可以指定存储参数“WITH (storage_parameter=value)”，常用的存储参数为FILLFACTOR，比如，可以这样创建索引：

CREATE INDEX idx_contacts_name on contacts(name) WITH (FILLFACTOR=50);

也可以按降序创建索引：

CREATE INDEX idx_contacts_name on contacts(name desc);

并发创建索引

通常情况下，在创建索引的时候PostgreSQL会锁定表以防止写入，然后对表做全表扫描，从而完成创建索引的操作。在此过程中，其他用户仍然可以读取表，但是插入、更新、删除等操作将一直被阻塞，直到索引创建完毕。

PostgreSQL支持在不长时间阻塞更新的情况下建立创建索引，这是通过在CREATE INDEX中加CONCURRENTLY选项来实现的。当该选项被启用时，PostgreSQL会执行表的两次扫描，因此该方法需要更长的时间来建索引。尽管如此，该选项也是很有用的。

CREATE INDEX CONCURRENTLY idx_testtab01_note on testtab01(note);

修改索引

ALTER INDEX name RENAME TO new_name
ALTER INDEX name SET TABLESPACE tablespace_name
ALTER INDEX name SET ( storage_parameter = value [, ... ] )
ALTER INDEX name RESET ( storage_parameter [, ... ] )

删除索引

DROP INDEX [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

删除索引时，默认使用选项“RESTRICT”

如果有对象依赖该索引，则会删除失败，而使用CASCADE选项表示当有依赖这个索引的对象时，一并把这些对象删除掉，如外键约束。