CREATE INDEX

创建索引

定义一个新的索引

概要

CREATE [UNIQUE] INDEX name ON table
       [USING btree|bitmap|gist]
       ( {column | (expression)} [opclass] [, ...] )
       [ WITH ( FILLFACTOR = value ) ]
       [TABLESPACE tablespace]
       [WHERE predicate]

描述

CREATE INDEX 构造指定表上的索引。索引主要用于增强数据库性能(尽管不当使用会导致性能降低)。

索引的关键字段被指定为列名称,或者替换为括号中的表达式。如果索引方法支持多列索引,则可以指定多个字段。

索引字段可以是从表行的一个或者多个列的值计算的表达式。 该功能可以基于基本数据的一些变换来获取对数据的快速访问。 列如,在 upper(col) 上计算的索引将允许子句 WHERE upper(col) = 'JIM' 来使用索引。

Greenplum数据库提供索引方法 B-tree,位图,和 GiST。 用户可以自定义自己的索引方法,但这是相当复杂的。

当存在 WHERE 字句时,将创建部分索引,部分索引是仅包含表的一部分条目的索引, 通常是与表的其余部分相比对索引更有用的地方。例如,如果用户的表格包含已结算订单和未结算订单,其中未列出的订单占总表中的一小部分,但最常选择,则可以通过在该部分上创建索引来提高性能。

WHERE 子句中使用的表达式可能仅指基础表的列,但它可以使用所有列,而不仅仅是索引的列。 在 WHERE 中也禁止子查询和聚合表达式。 相同的限制适用于表达式的索引字段。

索引定义中使用的所有函数和操作符必须是不可改变的。他们的结果必须紧紧取决于他们的参数,没有任何外界的影响(如另一个表的内容或参数值)。此限制可以确保索引的行为被很好的定义。要在索引表达式或 WHERE 子句中使用用户定义的函数, 请记住在创建函数时标记函数 IMMUTABLE

参数

UNIQUE
当创建索引并添加每个时间数据时,检查表中的重复值。重复条目将产生错误。唯一索引仅适用于B树索引。在Greenplum数据库中,只有索引关键字与Greenplum分发密钥(或者它的超集)相同时才允许使用唯一索引。在分区表上,唯一索引仅在单个分区中支持,而不是跨越所有分区。
name
要创建索引的名称。索引始终与父表在统一模式中创建。
table
要索引表的名称(可选方案限定)
btree | bitmap | gist
要使用的索引方法,该有的选择是 b树位图,和 gist。 默认方法是 b树
column
表中要创建索引列的名称,仅B-tree, 位图, and GiST 索引支持多列索引。
expression
基于表的一个或多个列的表达式。 表达式通常用括号括起来,如语法所示。但是如果表达式有函数调用的形式,则可以省略括号。
opclass
操作符类的名称。操作符类指明了该列的索引要使用的操作符。例如,四字节整数的B树索引将使用 int4_ops 类(该操作包含了四字节整数的比较函数)。 实际上,列的数据类型的默认的操作符类 通常是足够的。 操作符类的要点是对于某些数据类型,可能会有多个有意义的排序。例如,复杂数据类型可以按绝对值或实数排序。我们可以通过为数据类型定义两个操作符类,然后在进行索引的时候选择适当的操作符类来实现。
FILLFACTOR
索引的fillfactor是一个百分比,用于确定索引方法将索引页填充什么程度。 对于B树来说,在初始化索引编译期间和扩展右侧索引(最大键值)时将页面填充该百分比程度,如果页面随后变得充满,它将会被分隔,导致索引效率逐渐下降。
B树使用默认的填充因子90,但是可以选择10到100之间的任何值。如果表示静态的,那么100是最好的最小化索引的物理大小的填充因子。但是对于大量更新的表,较小的填充因子是最好最小化页面分隔的需要。其他索引方法使用不同填充因子,但大致是相似的选择方式。默认的填充因子在方法之间有所不同。
tablespace
创建索引的表空间,如果没有指定,将使用默认的表空间。
predicate
部分索引的约束表达式。

注解

当在分区表上建立索引时,该索引将传播到由Greenplum数据库创建的所有子表。在Greenplum数据库创建的表上建立由分区表使用的索引是不支持的。

仅当索引列与Greenplum分布键列相同(或者是其超集)时,才允许UNIQUE 索引。

在追加优化表上不允许使用UNIQUE 索引。

在分区表上可以创建 UNIQUE 索引。但是,唯一性仅在分区内执行;分区之间之间不执行唯一性。例如,对于基于年份的分区和基于季度的自分区的分区表,仅在每个单独的季度分区上执行唯一性。在季度之间不执行唯一性。

默认情况下,索引不用于 IS NULL 字句。这种情况下使用索引的最好方法是使用 IS NULL 谓词创建部分索引。

bitmap 索引对于具有100到100000个不同值的列的效果最好。对于有超过100000个不同值的列, 位图索引的性能和空间效率下降。位图索引的大小与表中行数成比例,是索引列中不同值数量的数倍。

少于100个不同值的列通常不会从任何类型的索引中受益,例如,男性和女性只有两个不同值的性别列不是索引的良好候选。

Greenplum 的先前版本也有一个 R-tree 索引方法。 该方法已经被删除,因为它相对于GIST方法相比没有显著的优点。 如果指定了 USING rtree, 则CREATE INDEX 将会将其解释为使用 USING gist

更多关于 GiST 索引类型的信息,请参考PostgreSQL文档

Greenplum中已经禁止使用 hash 和 GIN 索引。

例子

创建一个B树索引在 films表的 title 列中:

CREATE UNIQUE INDEX title_idx ON films (title);

创建一个位图索引在 employee表的 gender 列中:

CREATE INDEX gender_bmp_idx ON employee USING bitmap 
(gender);

创建一个索引在表达式 lower(title)上,允许有效的区分大小写的搜索:

CREATE INDEX lower_title_idx ON films ((lower(title)));

使用非默认的填充因子创建一个索引:

CREATE UNIQUE INDEX title_idx ON films (title) WITH 
(fillfactor = 70);

创建一个索引在 films 表的 code 列上,并且使索引驻留在表空间的 索引空间中:

CREATE INDEX code_idx ON films(code) TABLESPACE indexspace;

兼容性

CREATE INDEX 是Greenplum数据库的语言扩展。SQL标准中没有索引规定。

Greenplum数据库不支持并发创建索引(CONCURRENTLY 关键字不支持)。