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 ]CREATE INDEX 在一个指定表或者物化视图的指定列上创建一个索引,索引主要用来提高数据库的效率(尽管不合理的使用将导致较慢的效率)
索引的键字是用列名来声明的,或者在括号里面写一个表达式 .如果索引支持多列索引,我们也可以指定多个字段.
一个索引域可以是一个使用表的一个或多个字段进行计算的表达式。 这个特性可以快速访问一些基础数据的变形.例如,一个在upper(col)上计算的索引允许WHERE upper(col) = 'JIM'字句使用这个索引.
PostgreSQL提供的索引类型包括B-tree, hash, GiST, SP-GiST, 和 GIN.用户也可以自己定义索引类型,但这是相当复杂的.
当使用WHERE字句,将会创建一个partial index.一个部分索引是仅包括表的一部分记录的索引,通常是比表中其他部分更有用的记录. 例如,如果你有一个包含了记账和不记账的订单,不记帐的订单只占了表的一小部分,并且是经常使用的部分.你可以在这部分上建立一个索引来提高效率.另一个应用是使用WHERE 和UNIQUE 来强制保证表的一个子集的唯一性.更多的信息见:第 11.8 节
WHERE 字句的表达式可以使用底层表的列,并且可以是用它的所有列,而不仅仅建立索引的列.目前子查询和聚合表达式时禁止出现在WHERE字句的,这个限制同样适用于索引.
在索引定义中使用的所有函数和操作符必须是"immutable",即他们的结果依赖与他们的参数,而不受任何外部的数据的影响(例如另一个表的内容,或当前时间).这个限制保证了索引的定义完整性.在一个索引或WHERE子句中使用用户自定义的函数时,确保在定义这些函数时标记他们是不可变的.
当索引被创建时使系统检查表的重复值(如果已经有数据),并且当有数据插入时检查唯一性.当插入数据或更新数据导致重复记录时将会产生一个错误.
当使用这个选项时, PostgreSQL在生成索引时将不会在表上加任何锁阻止并发的插入,更新,删除.而标准的建立一个索引将产生一个阻止写(不包括读)的锁直到索引建立完毕.当使用这个选项时有一些问题需要注意,详见:并发建立索引.
被创建索引的名字.这里不需要包括模式的名字,索引总是在同一个模式中作为其父表创建的.如果忽略这个选项,PostgreSQL基于父表的名字和建立索引的列选择一个合适的名字.
要建立索引的表名(可能有模式修饰)
建立索引使用的方法名字. 可选的方法有btree, hash, gist, spgist and gin.默认方法是btree.
表中 要建立索引的列名.
基于表中一列或多列的表达式.在上面的语法中,表达式必须在园括号中.如果表达式有函数调用的格式则圆括号可以省略.
索引使用排序方式的名字,默认索引使用创建索引列中声明的排序方式,创建索引的表达式结果的排序方式.对于使用指定排序方式的表达式查询,索引使用指定的排序是高效的.
操作符类的名字,详细内容见下面.
指定升序排序(默认).
指定降序排序.
指定null排在非null值前面,在 DESC 中时默认的.
指定null排在非null值后面, 默认在 DESC没有指定.
index-method-specific 存储参数的名字.详细见:索引存储参数.
在哪个表空间建立索引,如果不指定,将使用default_tablespace ,或者临时表的索引将使用temp_tablespaces .
部分索引的约束表达式.
可选的WITH子句指定了索引的storage parameters.每个索引方式有它自己的存储参数.B-tree, hash, GiST and SP-GiST索引都可以带这个参数.
填充因子是一个索引实际数据的百分比,它决定索引方法占用页的空间的比率.对于B-trees,在索引创建时叶子页填充这个百分比的数据.其余的用来扩展索引(添加一个新的最大的键值).如果接下来页占用100%,它将会分页,这将导致索引效率的逐渐下降.B-tree使用一个90做为默认填充比,但是可以选择10到100的任何值.如果是一个静态表,填充比使用100将会最大的减小索引所占的物理空间.但是对于有大量更新的表一个较小的填充比将会尽可能的减少分页.其他的索引方法使用填充比是不同的但是大致方法是相同; 方法之间的默认填充比不是一致的.
GiST索引额外接受这个参数:
在使用第 55.3.1 节建立索引时决定是否缓存建立的方法.使用OFF 关闭这个功能,ON 打开这个功能. 使用AUTO 它初始化时是关闭的,但是当索引的达到effective_cache_size将会打开.默认使用AUTO.
GIN索引接受一个不同的参数:
这个设置用来控制在 第 57.3.1 节中描述的快速更新技术.它是一个布尔类型参数:ON使能快速更新,OFF关闭这个功能.(在第 18.1 节中描述了允许可选的拼写 ON and OFF.)默认是ON.
注意: 通过ALTER INDEX关闭FASTUPDATE防止将来数据插入到待建立的索引记录中,但是它自己不写之前的记录到磁盘.你可以使用VACUUM 表来确保待建立索引空.
建立一个索引将影响正常的数据库操作.一般的 PostgreSQL锁住建立索引的表防止写，然后通过扫描表来建立整个索引.其他的事物可以读表,但是插入,更新,删除操作将被锁住直到索引建立完成.如果这是一个线上的生成库将会有较严重的影响.非常大的表将使用数个小时来建立索引,即使一个较小的表,对于生产库也会在一个不可接受的时间内锁住该表的写操作.
PostgreSQL支持建立索引时不锁写操作.这个方法通过CREATE INDEX时指定CONCURRENTLY选项,当使用这个选项时, PostgreSQL必须扫描表2次,它必须等待所有的将要使用该索引的事物结束.所以这个方法比标准的建立索引需要更多的工作并且花费更多的时间.然而,在建立索引的时候它允许正常的操作所以对于生产环境它是非常有用的.当然在建立索引时额外的CPU和I/o开销可能会降低其他操作的效率.
在并发创建索引中，索引在一个事物中进入系统表，然后两表扫描发生在另外的事物中．在第二遍扫描时任何活跃的事物（在该表上）将会阻塞并发索引的建立直到事物完成．甚至在第二遍表扫描时事物仅仅涉及到该表．并发建立索引使用第 47.59 节中的方法等待每一个发生的事物完成．
当扫描表时发生了问题，例如一个唯一索引违反了唯一性，CREATE INDEX将会失败并且留下一个 "invalid"索引．在查询时这个索引将会被忽略因为他是不完整的；然而它仍然会增加更新的开销．psql\d命令将列出带 INVALID的索引．
postgres=# \d tab
Table "public.tab"
Column | Type | Modifiers
--------+---------+-----------
col | integer |
Indexes:
"idx" btree (col) INVALID这种情况推荐的恢复方法是删除掉索引并且再次执行CREATE INDEX CONCURRENTLY. （另一种重建索引的方法是使用REINDEX.然而，因为REINDEX不支持并发建立索引，所以这种方式可用性不高．）
另一个缺点是当并发建立唯一索引第二遍扫描表后唯一性将会约束其他事物．这意味着在索引建立完成前在其他查询中违反了该索引将会报违反约束错误，即使最后索引建立失败．如果在第二遍扫描发生了错误，在后续的操作中"invalid"索引也会（在相关列上）保持该唯一性．
支持建立并发索引和部分索引．在这些语句中出现错误将会产生和前面违反唯一性约束相似的错误．
标准建立索引允许并行的执行其他标准的建立索引语句，但是在一个表上一次仅可以有一个并发建立索引语句．在这两种情况下，建立索引的同时不允许有其他模式类型的修改．另一个不同点是 CREATE INDEX可以在一个阻塞的事物里执行，而 CREATE INDEX CONCURRENTLY不可以．
在第 11 章中描述了什么时候会使用索引，什么时候不使用索引，以及在什么情况下索引是有用的．
| 小心 |
哈希索引不记录到WAL日志中，所以当系统崩溃有未写入磁盘的数据时哈希索引需要使用REINDEX重新建立．哈希索引的变化不能在恢复一个基础备份后通过流复制或者文件复制来重写．所以接下来在查询中使用他们将会给出错误的结果．因为这些原因使用哈希索引是有阻碍的． |
现在只有B-tree, GiST 和 GIN支持多列索引．默认支持最多32列．（当编译PostgreSQL时可以改变这个限制）．现在只有B-tree支持唯一索引．
对于索引的每一列可以指定一个operator class．operator class标识了索引那一列的使用的操作符．例如一个B-tree索引在一个四字节整数上可以使用int4_ops；这个操作符类包括四字节整数的比较函数．实际上对于列上的数据类型默认的操作符类时足够用的．操作符类主要用于一些有多种排序的数据．例如，我们想按照绝对值或者实数部分排序一个复数．我们能通过定义两个操作符类然后当建立索引时选择合适的类．详细的操作符类信息请参考：第 11.9 节和第 35.14 节.
索引（现在只有B-tree）中支持有序扫描的字句中可以制定ASC, DESC, NULLS FIRST, 和 NULLS LAST修改索引的排序方式．因为一个有序的索引能被从前向后或者从后向前扫描．建立一个单列的DESC索引是没有意义的．因为在一个标准的索引是已经排序的．在创建多列索引时使用这些选项值来匹配一些复合查询的排序请求，例如SELECT ... ORDER BY x ASC, y DESC.如果你在查询中需要支持 "nulls sort low"特性NULLS选项是很有用的，而不是默认的"nulls sort high",
对于大多数索引，创建索引的速度依赖于maintenance_work_mem的设置．只要你没有使他超过可得到内存而进入swap分区，较大的值将会减少索引创建的时间．对于创建哈希索引的时间与effective_cache_size值相关,PostgreSQL将使用两种不同的哈希索引创建方法，主要取决于索引的大小比effective_cache_size大还是小．为了得到较好的结果，确保这个参数的设置考虑到可用内存，并且注意maintenance_work_mem和effective_cache_size的和小于其他程序所需要的内存空间．
使用 DROP INDEX删除一个索引．
PostgreSQL之前的版本有一个R-tree索引方法．这个方法已经被删除因为它对于GiST索引没有明显的优势．如果指定USING rtree，CREATE INDEX会将他翻译为USING gist，去转换老的数据库版本到GiST.
在表films的title列上建立B-tree索引：
CREATE UNIQUE INDEX title_idx ON films (title);
允许大小写无关查询在 lower(title)表达式上建立索引：
CREATE INDEX ON films ((lower(title)));
(在这个例子中我们可以选在忽略索引名字，系统将给出一个名字，典型的是films_lower_idx.)
创建一个使用非默认排序方式的索引：
CREATE INDEX title_idx_german ON films (title COLLATE "de_DE");
创建一个null值的非默认排序的索引：
CREATE INDEX title_idx_nulls_low ON films (title NULLS FIRST);
创建一个非默认填充因子的索引：
CREATE UNIQUE INDEX title_idx ON films (title) WITH (fillfactor = 70);
创建一个将快速更新关闭的GIN索引：
CREATE INDEX gin_idx ON documents_table USING gin (locations) WITH (fastupdate = off);
创建一个在films的code列的索引，并且使索引建立在indexspace表空间中：
CREATE INDEX code_idx ON films (code) TABLESPACE indexspace;
在一个点属性上创建GiST索引，让我们在转换函数的结果上高效的使用box操作符：
CREATE INDEX pointloc
ON points USING gist (box(location,location));
SELECT * FROM points
WHERE box(location,location) && '(0,0),(1,1)'::box;
创建一个在表上不加写锁的索引：
CREATE INDEX CONCURRENTLY sales_quantity_index ON sales_table (quantity);