GreenPlum中的索引
简介
在Greenplum数据库中通常不需要索引。大部分分析型查询会在大体量数据上操作，而索引是用于从多行数据中定位 某一行或某几行。在Greenplum数据库中，顺序扫描是一种读取数据的有效方法，因为每个segment都含有数据同等 大小的一部分并且所有的segment都并行工作以读取数据。
如果增加索引不能获得性能提升，马上删掉它。验证您创建的每个索引都被优化器使用到。
对于具有高选择性的查询，索引可能会提升查询性能。对于选择性查询所要求的高基数表，在一个列式表的单列上 创建用于钻透目的的索引。
不要在频繁更新的列上创建索引。在频繁被更新的列上创建索引会增加更新时所需的写次数。
只有当表达式被频繁地使用在查询中时，才应该在表达式上建立索引。
带有谓词的索引会创建一个部分索引，它可以被用来从大型表中选择少量行。
避免重叠的索引。具有相同前导列的索引是冗余的。
对于返回一个定向行集合的查询来说，索引能够提高在压缩追加优化表上的性能。对于压缩数据，采用索引访问 方法意味着只有必要的页面会被解压缩。
创建有选择性的B-树索引。索引选择度是一列中的唯一值数量除以表中的行数。例如，如果一个表有1000行并且 有一列中有800个唯一值，那么该索引的选择度就是0.8，这被认为是中不错的索引使用情形。
总是在向表中装载数据前删除索引。这样装载的运行速度将会比在带有索引的表中装载数据快一个数量级。 在装载之后，重新创建索引。
位图索引适合于查询但不适合于更新。当列具有较低的基数（100到100,000个唯一值）时位图索引表现得最好。 不要为唯一列、基数非常高或者非常低的数据使用位图索引。不要为事务性负载使用位图索引。
通常，不要索引分区表。如果需要索引，索引列必须不同于分区列。索引分区表的一个好处是因为当B-树尺寸增长时 其性能呈指数下降，在分区表上创建索引可以得到很多较小的B-树，其性能比未分区表上的B-树更好。
在Greenplum数据库中使用索引
在大部分传统数据库中，索引能够极大地改善数据访问时间。 不过，在一个Greenplum之类的分布式数据库中，索引应该被更保守地使用。 Greenplum数据库会执行非常快的顺序扫描，索引则使用一种随机搜索的模式在磁盘上定位记录。 Greenplum的数据分布在Segment上，因此每个Segment会扫描全体数据的一小部分来得到结果。 通过表分区，要扫描的数据量可能会更少。 因为商业智能（BI）查询负载通常会返回非常大的数据集，使用索引并不是很有效。
首先在不加索引时尝试用户的查询负载。 索引更有可能为OLTP负载改进性能，在那种场景中查询会返回一个单一记录或者数据的一个小的子集。 在被压缩过的追加优化表上，索引也可以提高返回一个目标行集合的查询的性能，因为优化器在适当的时候可以使用一种索引访问方法而不是全表扫描。 对于压缩过的数据，使用一种索引访问方法意味着只有必要的行会被解压。
Greenplum数据库会自动为带有主键的表创建PRIMARY KEY约束。 要在一个被分区的表上创建索引，就在用户创建的分区表上创建一个索引。 该索引会被传播到Greenplum数据库所创建的所有子表上。不支持在Greenplum数据库为分区表创建的子表上创建索引。
注意一个UNIQUE CONSTRAINT（例如PRIMARY KEY CONSTRAINT）会隐式地创建一个UNIQUE INDEX，它必须包括分布键中所有的列以及任何分区键。 UNIQUE CONSTRAINT会在整个表上被强制要求，包括所有的表分区（如果有）。
索引会增加一些数据库负担，它们使用存储空间并且在表被更新时需要被维护。 要确保查询负载会用到用户创建的索引，并且检查用户增加的索引是否改进了查询性能（与表的顺序扫描相比）。 要确定是否使用了索引，检查查询的EXPLAIN计划。参见查询分析。
在创建索引时请考虑以下几点。
- 用户的查询负载。 索引能改进查询返回单一记录或者非常小的数据集的性能，例如OLTP负载。
- 压缩表。在被压缩过的追加优化表上，索引也可以提高返回一个目标行集合的查询的性能。对于压缩过的数据，一种索引访问方法意味着只有必要的行会被解压。
- 避免在频繁更新的列上建立索引。在一个被频繁更新的列上建立索引会增加该列被更新时所要求的写操作数据量。
- 创建选择性的B-树索引。 索引选择度是一个列中具有的可区分值的数量除以表中行数得到的比例。 例如，如果一个表有1000行并且一个列中有800个可区分的值，则该索引的选择度为0.8，这还不错。 唯一索引的选择度总是1.0，这是最好的选择度。Greenplum数据库只允许在分布键列上的唯一索引。
- 为低选择度的列使用位图索引。 Greenplum数据库的位图索引类型在常规的PostgreSQL中不可用。参见关于位图索引。
- 索引在连接中用到的列。 在被用于频繁连接的一个列（例如一个外键列）上的索引能够提升连接性能，因为这让查询优化器有更多的连接方法可以使用。
- 索引在谓词中频繁使用的列。 频繁地在WHERE子句中被引用的列是索引的首选。
- 避免重叠的索引。具有相同前导列的索引是冗余的。
- 批量载入前删掉索引。 对于载入大量数据到一个表中，请考虑先删掉索引并且在数据装载完成后重建它们。这常常比更新索引更快。
- 考虑一个聚簇索引。 聚簇一个索引意味着记录会根据索引被物理排序后存储在磁盘上。 如果用户需要的数据被随机分布在磁盘上，数据库必须在磁盘上来回寻找以取得所需的记录。 如果这些记录被存储得彼此临近，那么取得它们的操作就会更高效。 例如，一个在日期列上的聚簇索引中数据会按照日期顺序存放。 针对一个指定日期范围的查询将会导致对磁盘的一次有序地读取，这会利用快速的顺序访问。
在Greenplum数据库中聚簇一个索引
使用CLUSTER命令根据一个索引从物理上重新排序一个非常大的表可能会花费很长的时间。 为了更快达到同样的结果，用户可以通过创建一个中间表并且按照想要的顺序重载数据来手工在磁盘上重排数据。例如：
1 2 3 4 5 6 | CREATE TABLE new_table (LIKE old_table) AS SELECT * FROM old_table ORDER BY myixcolumn; DROP old_table; ALTER TABLE new_table RENAME TO old_table; CREATE INDEX myixcolumn_ix ON old_table; VACUUM ANALYZE old_table; |
索引类型
Greenplum数据库数据库支持Postgres索引类型B-树、GiST和GIN，不支持Hash索引。 每一种索引类型都使用一种不同的算法，它们最适合的查询类型也不同。 B-树索引适合于最常见的情况并且是默认的索引类型。 对于这些类型的描述请见PostgreSQL文档中的索引类型。
Note: 只有索引键的列与Greenplum分布键相同（或者是其超集）时，Greenplum数据库才允许唯一索引。 在追加优化表上不支持唯一索引。在分区表上，唯一索引无法在一个分区表的所有子表分区之间被实施。唯一索引只能在一个分区内实施。
关于位图索引
Greenplum数据库提供位图索引类型。 位图索引最适合于拥有大量数据、很多临时查询以及少量数据修改（DML）事务的数据仓库应用和决策支持系统。
一个索引提供了指向表中包含一个给定键值的行的指针。 常规索引存储了每个键存储了一个元组ID的列表，列表中的元组ID对应于具有那个键值的行。 位图索引为每一个键值都存储一个位图。 常规索引可能会比表中的数据大几倍，但位图索引提供了和常规索引相同的功能并且只需要被索引数据尺寸的一小部分。
位图中的每一个位对应于一个可能的元组ID。 如果该位被设置，则具有相应元组ID的行包含该键值。 一个映射函数负责将这个位的位置转换成一个元组ID。 位图被压缩存储。如果可区分键值的数量很小，位图索引会小很多同时也会被压缩得更好，并且比常规索引节省可观的空间。 一个位图索引的大小与该表中行数乘以被索引列中不同值数量的结果成比例。
位图索引对于在WHERE子句中包含多个条件的查询最有效。 满足某些但不是全部条件的行在访问表之前就会被过滤掉。这通常会极大地改善响应时间。
何时使用位图索引
位图索引最适合用户只查询数据而不更新数据的数据仓库应用。 对于拥有100至100,000个可区分值的列并且当被索引列常常与其他被索引列联合查询时，位图索引表现最好。 低于100个可区分值的列通常无法从任何类型的索引受益，例如有两个可区分值的性别列（男和女）。 而在具有超过100,000个可区分值的列上，位图索引的性能和空间效率会下降。
位图索引能够提升临时查询的查询性能。 在将结果位图转换成元组ID之前，一个查询的WHERE子句中的AND以及OR条件可以通过在位图上直接执行相应的布尔操作快速地解决。 如果结果行数很小，查询能够在不做全表扫描的情况下很快地被回答。
何时不用位图索引
不要为唯一列或者具有高基数数据的列使用位图索引，例如顾客姓名或者电话号码。 位图索引的性能增益和磁盘空间优势在具有100,000或者更多唯一值的列上开始减小，这与表中的行数无关。
位图索引不适合有大量并发事务修改数据的OLTP应用。
请保守地使用位图索引。测试并且比较使用索引和不使用索引的查询性能。只有被索引列的查询性能有提升时才增加索引。
创建一个索引
CREATE INDEX命令在一个表上定义一个索引。 例如，要在表employee的gender列上创建一个B-树索引：
1 | CREATE INDEX gender_idx ON employee (gender); |
要在表title中的列title上创建一个位图索引：
1 | CREATE INDEX title_bmp_idx ON films USING bitmap (title); |
表达式索引
索引列不必只是表的一列，而是可以是从表的一列或多列计算的函数或标量表达式。 此功能对于根据计算结果快速访问表非常有用。
索引表达式的维护成本相对较高，因为必须在插入和每次更新时为每一行计算派生表达式。 但是，索引表达式在索引搜索期间不会重新计算，因为它们已存储在索引中。 在下面两个例子中，系统把查询视为WHERE indexedcolumn = ‘constant’，所以查询的速度与其他普通索引相同。 因此，当检索速度比插入和更新速度更重要时，表达式上的索引很有用。
第一个示例是使用lower函数进行不区分大小写的比较的常用方法：
1 | SELECT * FROM test1 WHERE lower(col1) = 'value'; |
如果一个索引已经在lower(col1)函数的结果上被定义，该查询可以使用索引：
1 | CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1)); |
此示例假定经常执行以下类型的查询。
1 | SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith'; |
查询可能会受益于以下索引。
1 | CREATE INDEX people_names ON people ((first_name || ' ' || last_name)); |
CREATE INDEX命令的语法通常需要在索引表达式周围编写括号，如第二个示例所示。 当表达式只是函数调用时，可以省略括号，如第一个示例中所示。
检查索引使用
Greenplum数据库的索引并不要求维护和调优。 用户可以检查实际的查询负载使用了哪些索引。 使用EXPLAIN命令可以检查一个查询的索引使用。
查询计划展示了数据库将用来回答一个查询的步骤或者计划节点以及每一个计划节点的时间估计。 要检查索引的使用，请在用户的EXPLAIN输出中寻找以下查询计划节点类型：
- 索引扫描 - 一次索引的扫描。
- 位图堆扫描 - 检索所有由BitmapAnd、BitmapOr或者BitmapIndexScan生成的位图并且访问堆以检索相关的行。
- 位图索引扫描 - 计算一个由所有来自底层索引的满足查询谓词的位图通过OR操作形成的位图。
- BitmapAnd或BitmapOr - 取得从多个BitmapIndexScan节点生成的位图，把它们AND或者OR在一起，并且生成一个新的位图作为其输出。
用户必须做实验来确定要创建哪些索引。请考虑以下几点。
- 在创建或者更新一个索引后运行ANALYZE。 ANALYZE会收集表统计信息。 查询优化器使用表统计信息来估算一个查询所返回的行数并且为每一种可能的查询计划赋予实际开销。
- 实验中使用真实数据。使用测试数据建立索引会告诉用户该测试数据需要什么样的索引，但也仅此而已。
- 不要使用非常小的测试数据集，因为它们的结果很可能是不真实的或者倾斜的。
- 在开发测试数据时要小心。相似的、完全随机的或者排序后插入的值都将使统计信息偏离真实数据的分布。
- 通过使用运行时参数来关闭特定的计划类型，用户可以强制使用索引来进行测试。 例如，关闭顺序扫描（enable_seqscan）以及嵌套循环连接（enable_nestloop）两种最基本的计划来强制系统使用一种不同的计划。 对用户的查询使用索引和不用索引的执行进行计时，并且使用EXPLAIN ANALYZE命令来比较结果。
管理索引
使用REINDEX命令可以重建一个表现不好的索引。 REINDEX使用存储在一个索引的基表中的数据重建该索引来替换该索引。
重建一个表上的所有索引
1 2 | REINDEX my_table; REINDEX my_index; |
删除一个索引
DROP INDEX命令移除一个索引。例如：
1 | DROP INDEX title_idx; |
在载入数据时，删除所有索引、载入数据然后重建索引会更快。
在数据库表中搜索文本
本主题说明如何使用文本搜索运算符搜索数据库表以及如何创建索引以加快文本搜索。
上一节中的示例说明了使用简单常量字符串进行全文匹配。 本节介绍如何搜索表数据，可选择使用索引。
搜索表
可以在没有索引的情况下进行全文搜索。 在其body字段中打印包含单词friend的每一行的title的简单查询是：
1 | SELECT titleFROM pgwebWHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend'); |
这也将找到诸如friends和friendly之类的相关单词，因为所有这些都被简化为相同的标准化词汇。
上面的查询指定english配置用于解析和规范化字符串。 或者，我们可以省略配置参数：
1 | SELECT titleFROM pgwebWHERE to_tsvector(body) @@ to_tsquery('friend'); |
此查询将使用default_text_search_config设置的配置。
更复杂的示例是在title或body中选择包含create和table的十个最新文档：
1 | SELECT titleFROM pgwebWHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create & table')ORDER BY last_mod_date DESCLIMIT 10; |
为清楚起见，我们省略了在两个字段之一中查找包含NULL的行所需的coalesce函数调用。
虽然这些查询在没有索引的情况下可以正常工作，但大多数应用程序会发现这种方法太慢，除非是偶尔的临时搜索。 文本搜索的实际使用通常需要创建索引。
创建索引
我们可以创建一个GIN索引（文本搜索的GiST和GIN索引）来加速文本搜索：
1 | CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', body)); |
请注意，使用了to_tsvector的双参数版本。 只能在表达式索引中使用指定配置名称的文本搜索功能。 这是因为索引内容必须不受default_text_search_config的影响。 如果它们受到影响，索引内容可能会不一致，因为不同的条目可能包含使用不同文本搜索配置创建的tsvector，并且无法猜测哪个是哪个。 正确地导出和恢复这样的索引是不可能的。
因为在上面的索引中使用了to_tsvector的双参数版本，所以只有使用具有相同配置名称的to_tsvector的双参数版本的查询引用才会使用该索引。 也就是说，WHERE to_tsvector(‘english’, body) @@ ‘a & b’可以使用索引，但WHERE to_tsvector(body) @@ ‘a & b’不能。 这可确保索引仅与用于创建索引条目的相同配置一起使用。
可以设置更复杂的表达式索引，其中配置名称由另一列指定，例如：
1 | CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector(config_name, body)); |
其中config_name是pgweb表中的一列。 这允许在同一索引中进行混合配置，同时记录每个索引条目使用的配置。 例如，如果文档集合包含不同语言的文档，这将是有用的。 同样，要使用索引的查询必须用来匹配，例如，WHERE to_tsvector(config_name, body) @@ ‘a & b’。
索引甚至可以连接列：
1 | CREATE INDEX pgweb_idx ON pgweb USING gin(to_tsvector('english', title || ' ' || body)); |
另一种方法是创建一个单独的tsvector列来保存to_tsvector的输出。 此示例是title和body的连接，使用coalesce确保当另一个为NULL时仍将索引一个字段：
1 | ALTER TABLE pgweb ADD COLUMN textsearchable_index_col tsvector;UPDATE pgweb SET textsearchable_index_col = to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,'')); |
然后我们创建一个GIN索引来加速搜索：
1 | CREATE INDEX textsearch_idx ON pgweb USING gin(textsearchable_index_col); |
现在我们准备执行快速全文搜索：
1 | SELECT title FROM pgweb WHERE textsearchable_index_col @@ to_tsquery('create & table') ORDER BY last_mod_date DESC LIMIT 10; |
与表达式索引相比，单列方法的一个优点是，不必在查询中显式指定文本搜索配置以便使用索引。 如上例所示，查询可以依赖于default_text_search_config。 另一个优点是搜索速度更快，因为没有必要重做to_tsvector调用来验证索引匹配。 （当使用GiST索引而不是GIN索引时，这一点更为重要;请参阅文本搜索的GiST和GIN索引。） 然而，表达式索引方法设置起来更简单，并且由于tsvector不是显式存储，因此需要更少的磁盘空间。
总结
- 通常在Greenplum数据库中无需使用索引。
- 对高基数的表在列式表的单列上创建索引用于钻透目的要求查询具有较高的选择度。
- 不要索引被频繁更新的列。
- 总是在装载数据到表之前删除索引。在装载后，重新为该表创建索引。
- 创建具有选择性的B-树索引。
- 不要在被更新的列上创建位图索引。
- 不要为唯一列、基数非常高或者非常低的数据使用位图索引。位图索引在列值唯一性位于100-100,000之间时性能最好。
- 不要为事务性负载使用位图索引。
- 通常不要索引分区表。如果需要索引，索引列必须与分区列不同。
参考
https://www.bookstack.cn/read/greenplum-admin_guide-6.0-zh/b82bf6891a21734f.md#frys4x
https://www.bookstack.cn/read/greenplum-admin_guide-6.0-zh/c3403fabdc572b34.md
https://www.bookstack.cn/read/greenplum-admin_guide-6.0-zh/835eb4f10e1deceb.md
