性能调优
1.索引
索引是提高数据库性能的常见方式。索引允许数据库服务器比没有索引时更快地找到和检索特定的行。但是，索引也会给整个数据库系统增加开销，因此应该明智地使用。
1.1.介绍
假设我们有一个类似于这样的表：
CREATE TABLE test1 (
id integer,
content varchar
);
应用程序会发出许多以下形式的查询：
SELECT content FROM test1 WHERE id = constant;
如果没有预先准备，系统将不得不逐行扫描整个 test1 表以查找所有匹配的条目。如果 test1 中有许多行，但只有很少的行（可能是零或一个）会被这样的查询返回，这显然是一种低效的方法。但是，如果系统已经被指示在 id列上维护一个索引，它可以使用一种更有效的方法来定位匹配的行。例如，它可能只需要在搜索树中向下走几层。
大多数非小说类书籍都采用类似的方法：将读者经常查找的术语和概念收集在书的字母索引中。感兴趣的读者可以相对快速地扫描索引并翻到适当的页面，而不必读整本书以找到感兴趣的材料。就像作者的任务是预测读者可能查找的项目一样，数据库程序员的任务是预见哪些索引将是有用的。
可以使用以下命令在 id 列上创建索引，如下所述：
CREATE INDEX test1_id_index ON test1 (id);
名称 test1_id_index 可以自由选择，但应选择一些可以让您在以后记住索引用途的内容。
要删除索引，请使用 DROP INDEX 命令。可以随时向表添加和删除索引。
创建索引后，不需要进一步干预：当修改表时，系统将更新索引，并在查询中使用索引，当它认为这样做比顺序表扫描更有效时。但是，您可能需要定期运行ANALYZE 命令以更新统计信息，以便查询规划器能够做出有根据的决策。有关信息，请参见性能提示。如何查找索引是否被使用，以及规划器何时以及为什么可能选择不使用索引。
索引还可以受益于带有搜索条件的UPDATE和DELETE命令。索引还可以用于连接搜索。因此，在连接条件中定义了一个列的索引也可以显着加快带有连接的查询。
在大型表上创建索引可能需要很长时间。默认情况下，UXDB允许在索引创建过程中对表进行读取（SELECT语句），但是写入（INSERT，UPDATE，DELETE）将被阻塞，直到索引构建完成。在生产环境中，这通常是不可接受的。可以允许写入与索引创建并行进行，但是需要注意几个注意事项。
创建索引后，系统必须使其与表保持同步。这增加了数据操作操作的开销。索引还可以防止创建仅堆元组。因此，很少或从不在查询中使用的索引应该被删除。
1.2.索引类型
UXDB 提供了几种索引类型：B-tree、Hash、GiST、SP-GiST、GIN、BRIN 和扩展模块bloom。每种索引类型都使用不同的算法，最适合不同类型的查询。默认情况下，CREATE INDEX命令创建 B-tree 索引，适用于大多数情况。其他索引类型可以通过写入关键字 USING后跟索引类型名称来选择。例如，要创建 Hash 索引：
CREATE INDEX name ON table USING HASH (column);
1.2.1.B-Tree
B-tree 可以处理可以排序的数据的等值和范围查询。特别地，当索引列参与使用以下运算符之一的比较时，UXDB 查询规划器将考虑使用B-tree 索引：
< <= = >= >
等价于这些运算符的组合的结构，例如 BETWEEN 和 IN，也可以使用 B-tree 索引搜索实现。此外，对于索引列的 ISNULL 或 IS NOT NULL 条件，可以使用 B-tree 索引。
优化器还可以使用 B-tree 索引处理涉及到模式匹配的查询，例如 LIKE 和 ~ 运算符。模式匹配运算符LIKE和~，如果模式是一个常量并且锚定在字符串的开头，例如col LIKE 'foo%'或col ~'^foo'，但不包括col LIKE'%bar'。然而，如果您的数据库不使用C语言环境，您需要使用特殊的操作符类来支持模式匹配查询的索引。也可以使用B-tree索引进行ILIKE和~*，但只有当模式以非字母字符开头时才可以，即不受大小写转换影响的字符。
B-tree索引也可以用于按排序顺序检索数据。这并不总是比简单的扫描和排序更快，但通常是有帮助的。
1.2.2.哈希
哈希索引存储从索引列的值派生的32位哈希码。因此，这种索引只能处理简单的等式比较。每当涉及到使用等式运算符进行比较时，查询规划器将考虑使用哈希索引：
=
1.2.3.GiST
GiST索引不是单一类型的索引，而是一个基础架构，可以在其中实现许多不同的索引策略。因此，可以使用GiST索引的特定运算符取决于索引策略（操作符类）。例如，标准的UXDB发行版包括用于几种二维几何数据类型的GiST操作符类，支持使用这些运算符进行索引查询：
<< &< &> >> <<| &<| |&> |>> @> <@ ~= &&
许多其他GiST操作符类可在contrib集合或单独的项目中使用。
GiST索引还能够优化“最近邻”搜索，例如：
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
这将找到距离给定目标点最近的十个地点。再次强调，这取决于所使用的特定操作符类。
1.2.4.SP-GiST
SP-GiST索引与GiST索引一样，提供了支持各种搜索的基础设施。SP-GiST允许实现各种不平衡的基于磁盘的数据结构，例如四叉树、k-d树和基数树（tries）。例如，标准的UXDB发行版包括用于二维点的SP-GiST操作符类，支持使用以下操作符进行索引查询：
<< >> ~= <@ <<| |>>
与GiST一样，SP-GiST支持“最近邻”搜索。
1.2.5.GIN
GIN索引是适用于包含多个组件值的数据值（例如数组）的“反向索引”。反向索引对于每个组件值都包含一个单独的条目，并且可以有效地处理测试特定组件值存在的查询。
与GiST和SP-GiST一样，GIN可以支持许多不同的用户定义的索引策略，并且可以使用的特定操作符取决于索引策略。例如，标准的UXDB发行版包括用于数组的GIN操作符类，支持使用以下操作符进行索引查询：
<@ @> = &&
许多其他GIN操作符类可在contrib集合或作为单独的项目中使用。
1.2.6.BRIN
BRIN索引（块范围索引的简称）存储关于表的连续物理块范围中存储的值的摘要。因此，它们对于其值与表行的物理顺序密切相关的列最为有效。与GiST、SP-GiST和GIN一样，BRIN可以支持许多不同的索引策略，并且可以使用的特定操作符取决于索引策略。对于具有线性排序顺序的数据类型，索引数据对应于每个块范围中列中值的最小值和最大值。这支持使用以下运算符的索引查询：
< <= = >= >
1.3.多列索引
可以在表的多列上定义索引。例如，如果您有这样一个表：
CREATE TABLE test2 (
major int,
minor int,
name varchar
);
（比如，您将您的/dev目录存储在数据库中...）并且您经常发出这样的查询：
SELECT name FROM test2 WHERE major = constant AND minor = constant;
那么在major和minor列上定义一个索引可能是合适的，例如：
CREATE INDEX test2_mm_idx ON test2 (ux15docs/major, minor);
目前，只有B树、GiST、GIN和BRIN索引类型支持多键列索引。是否可以有多个键列与是否可以向索引中添加INCLUDE列无关。索引最多可以有32列，包括INCLUDE列。（在构建UXDB时可以更改此限制；请参见文件ux_config_manual.h。）
多列B树索引可用于涉及索引的任何子集的查询条件，但是当左侧列有约束条件时，索引最有效。确切的规则是，前导列上的等式约束条件以及第一个没有等式约束条件的列上的任何不等式约束条件将用于限制扫描的索引部分。对于这些列右侧的列的约束条件将在索引中进行检查，因此它们可以节省对表的访问，但它们不会减少必须扫描的索引部分。例如，给定一个索引(ux15docs/a, b, c)和一个查询条件WHERE a = 5 AND b >= 42 AND c < 77，索引将从第一个具有a = 5和b =42的条目开始扫描，直到最后一个具有a = 5的条目。具有c >=77的索引条目将被跳过，但仍需要扫描它们。原则上，此索引可以用于具有约束条件的查询。可以在b 和/或 c 上使用多列 B-tree 索引，对 a没有约束。但是，整个索引都必须被扫描，因此在大多数情况下，规划器会优先选择顺序表扫描而不是使用索引。
多列GiST索引可用于涉及索引任何子集的查询条件。对其他列的条件会限制索引返回的条目，但是对第一列的条件是确定需要扫描多少索引的最重要因素。如果GiST索引的第一列只有少数不同的值，则其效果相对较低，即使其他列有许多不同的值。
多列GIN索引可用于涉及索引任何子集的查询条件。与B-tree或GiST不同，无论查询条件使用哪个索引列，索引搜索效果都是相同的。
多列BRIN索引可用于涉及索引任何子集的查询条件。与B-tree或GiST不同，与查询条件使用哪个索引列无关，索引搜索效果是相同的。在单个表上具有多个BRIN索引而不是一个多列BRIN索引的唯一原因是具有不同的pages_per_range存储参数。
当然，每个列必须与适合索引类型的运算符一起使用；涉及其他运算符的子句将不予考虑。
应谨慎使用多列索引。在大多数情况下，单列索引就足够了，可以节省空间和时间。除非表的使用极为特殊，否则具有超过三个列的索引不太可能有帮助。
1.4.索引和 ORDER BY
除了简单地查找查询返回的行之外，索引还可以按特定的排序顺序提供它们。这允许查询的ORDER BY规范得到遵守，而无需单独进行排序步骤。在当前由UXDB 支持的索引类型中，只有 B 树可以产生排序输出，其他索引类型以未指定的实现相关顺序返回匹配的行。
规划器将考虑通过扫描与规范匹配的可用索引来满足ORDER BY规范，或者通过按物理顺序扫描表并进行显式排序来满足规范。对于需要扫描表的大部分数据的查询，显式排序可能比使用索引更快，因为它需要更少的磁盘I/O，因为它遵循顺序访问模式。当只需要获取少量行时，索引更有用。一个重要的特殊情况是 ORDER BY 与 LIMIT n 的组合：显式排序将不得不处理所有数据以识别前 n 行，但如果有一个与 ORDER BY 匹配的索引，则可以直接检索前n行，而无需扫描其余部分。
默认情况下，B 树索引以升序存储其条目，空值最后（表 TID 被视为在其他相等条目之间的 tiebreaker 列）。这意味着对列 x的索引的正向扫描会产生满足 ORDER BY x（或更详细地说，ORDER BY x ASC NULLS LAST）的输出。索引也可以向后扫描，产生满足 ORDER BY x DESC（或更详细地说，ORDER BY x DESC NULLS FIRST，因为 NULLS FIRST 是 ORDER BY DESC 的默认值）的输出。
您可以通过包括ORDER BY规范中的表达式的逆序或 NULLS FIRST/LAST 子句来调整 B 树索引的排序。例如，CREATE INDEX test1_idx ON test1 (ux15docs/x DESC NULLS FIRST); 将创建一个按列 x的逆序排序的索引，其中空值排在前面。
在创建索引时，可以使用选项ASC、DESC、NULLS FIRST和/或NULLS LAST。例如：
CREATE INDEX test2_info_nulls_low ON test2 (ux15docs/info NULLS FIRST);
CREATE INDEX test3_desc_index ON test3 (ux15docs/id DESC NULLS LAST);
按升序存储的索引，如果空值排在前面，可以满足ORDER BY x ASC NULLS FIRST或ORDER BY x DESC NULLS LAST，具体取决于扫描的方向。
你可能会想为什么要提供所有四个选项，因为两个选项加上向后扫描的可能性就可以涵盖所有ORDERBY的变体。在单列索引中，这些选项确实是多余的，但在多列索引中它们可能很有用。考虑一个(ux15docs/x, y)的两列索引：如果我们向前扫描，它可以满足ORDER BY x, y，或者如果我们向后扫描，它可以满足ORDER BY x DESC, y DESC。但是，应用程序可能经常需要使用ORDER BY x ASC, yDESC。没有办法从普通索引中获得这种排序，但如果索引定义为(ux15docs/x ASC, y DESC)或(ux15docs/x DESC, y ASC)，则可能是可能的。
显然，具有非默认排序顺序的索引是一个相当专业的功能，但有时它们可以为某些查询产生巨大的加速。是否值得维护这样的索引取决于您使用需要特殊排序顺序的查询的频率。
1.5.组合多个索引
单个索引扫描只能使用使用该索引的操作符类别的查询子句，并使用AND连接。例如，给定一个索引(ux15docs/a, b)，查询条件WHERE a = 5 AND b = 6可以使用该索引，但是查询WHERE a = 5 OR b = 6不能直接使用该索引。
幸运的是，UXDB可以组合多个索引（包括多次使用同一索引）来处理无法通过单个索引扫描实现的情况。系统可以在多个索引扫描之间形成AND和OR条件。例如，像WHERE x = 42 OR x = 47 OR x = 53 OR x = 99这样的查询可以分解为对x索引的四个单独扫描，每个扫描使用一个查询子句。然后将这些扫描的结果进行OR运算以产生结果。另一个例子是，如果我们在x和y上有单独的索引，那么像WHERE x = 5 AND y = 6这样的查询的一个可能的实现是使用每个索引与相应的查询子句，然后AND在一起来识别结果行的索引结果。
为了组合多个索引，系统扫描每个需要的索引，并在内存中准备一个位图，给出报告为匹配该索引条件的表行的位置。根据需要，位图进行AND和OR运算。最后，访问实际的表行并返回。表行按物理顺序访问，因为这是位图的布局方式；这意味着原始索引的任何排序都会丢失，因此如果查询具有ORDER BY子句，则需要单独进行排序步骤。由于每个额外的索引扫描都会增加额外的时间，因此规划器有时会选择使用简单的索引扫描，即使有其他可用的索引也可以使用。
在除了最简单的应用程序之外，有各种组合索引的方法。可能有用的索引，数据库开发人员必须做出权衡来决定提供哪些索引。有时，多列索引是最好的选择，但有时最好创建单独的索引并依靠索引组合功能。例如，如果您的工作负载包括有时仅涉及列x，有时仅涉及列y，有时涉及两个列的查询，您可以选择在x和y上创建两个单独的索引，依靠索引组合来处理使用两个列的查询。您还可以在(x，y)上创建多列索引。对于涉及两个列的查询，此索引通常比索引组合更有效，对于仅涉及y的查询几乎没有用处，因此它不应该是唯一的索引。多列索引和y上的单独索引的组合将合理地服务。对于仅涉及x的查询，可以使用多列索引，尽管它比仅在x上创建的索引更大，因此更慢。最后一种选择是创建所有三个索引，但这可能只有在表被搜索的频率远高于更新频率且所有三种类型的查询都很常见时才合理。如果其中一种类型的查询比其他类型少得多，您可能会选择仅创建最符合常见类型的两个索引。
1.6.唯一索引
索引也可以用于强制列值的唯一性，或多个列的组合值的唯一性。
CREATE UNIQUE INDEX name ON table (column [, ...]);
目前，只有B树索引可以声明为唯一。
当索引被声明为唯一时，不允许存在具有相等索引值的多个表行。空值不被视为相等。多列唯一索引仅会拒绝多行中所有索引列都相等的情况。
UXDB在为表定义唯一约束或主键时会自动创建唯一索引。该索引覆盖组成主键或唯一约束的列（如果适用，则为多列索引），并且是强制约束的机制。
注意
不需要手动为唯一列创建索引；这样做只会重复自动创建的索引。
1.7.表达式索引
索引列不一定只是底层表的一列，还可以是从表的一个或多个列计算出的函数或标量表达式。这个特性对于基于计算结果快速访问表格非常有用。
例如，进行不区分大小写的比较的常见方法是使用 lower 函数：
SELECT * FROM test1 WHERE lower(ux15docs/col1) = 'value';
如果已经在 lower(col1) 函数的结果上定义了索引，则此查询可以使用索引：
CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
如果我们将此索引声明为UNIQUE，它将防止创建仅在大小写方面不同的 col1 值的行，以及实际上相同的 col1值的行。因此，表达式索引可用于强制执行不可定义为简单唯一约束的约束。
另一个例子，如果经常执行以下查询：
SELECT * FROM people WHERE (first_name || ' ' || last_name) = 'John Smith';
那么可能值得创建这样的索引：
CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
CREATE INDEX命令的语法通常要求在索引表达式周围写括号，如第二个示例所示。当表达式只是函数调用时，可以省略括号，如第一个示例所示。
索引表达式的维护成本相对较高，因为必须为每个行插入和非-HOT更新计算派生表达式。但是，索引表达式在索引搜索期间不会重新计算，因为它们已经存储在索引中。在上面的两个示例中，系统将查询视为WHERE indexedcolumn ='constant'，因此搜索速度等同于任何其他简单索引查询。因此，当检索速度比插入和更新速度更重要时，表达式索引非常有用。
1.8.部分索引
部分索引是建立在表的子集上的索引；子集由条件表达式（称为部分索引的谓词）定义。索引仅包含满足谓词的表行的条目。部分索引是一种专门的功能，但有几种情况下它们是有用的。
使用部分索引的一个主要原因是避免索引常见值。由于查询常见值（占所有表行的几个百分点以上的值）的查询不会使用索引，因此根本没有必要将这些行全部保留在索引中。这减小了索引的大小，将加速那些使用索引的查询。它还将加速许多表更新操作，因为索引不需要在所有情况下都进行更新。示例 设置部分索引以排除常见值 显示了这个想法的一个可能应用。
示例 设置部分索引以排除常见值
假设您正在将 Web 服务器访问日志存储在数据库中。大多数访问来自您组织的 IP 地址范围，但有些来自其他地方（例如，拨号连接的员工）。如果您按IP 进行搜索主要是为了外部访问，那么您可能不需要索引对应于您组织子网的 IP 范围。
假设有这样一个表：
CREATE TABLE access_log (
url varchar,
client_ip inet,
...
);
要创建适合我们示例的部分索引，请使用以下命令：
CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
WHERE NOT (client_ip > inet '192.168.100.0' AND
client_ip < inet '192.168.100.255');
可以使用此索引的典型查询是：
SELECT *
FROM access_log
WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';
这里查询的 IP 地址被部分索引覆盖。以下查询无法使用部分索引，因为它使用了一个 IP 地址。被排除在索引之外的内容如下：
SELECT *
FROM access_log
WHERE url = '/index.html' AND client_ip = inet '192.168.100.23';
请注意，这种类型的部分索引需要预先确定共同的值，因此这种部分索引最适用于不会改变的数据分布。这样的索引可以定期重新创建以调整新的数据分布，但这会增加维护工作量。
部分索引的另一个可能用途是排除查询工作负载不感兴趣的值。这会带来与上述相同的优点，但即使在该情况下索引扫描可能是有利的情况下，它也会防止通过该索引访问“无趣”的值。显然，为这种情况设置部分索引需要大量的小心和实验。
示例 设置部分索引以排除无趣的值
如果您有一个包含已计费和未计费订单的表，其中未计费订单占总表的一小部分，但这些订单是最常访问的行，则可以通过仅在未计费的行上创建索引来提高性能。创建索引的命令如下所示：
CREATE INDEX orders_unbilled_index ON orders (order_nr)
WHERE billed is not true;
使用此索引的可能查询如下所示：
SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;
但是，该索引也可以用于不涉及order_nr的查询，例如：
SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;
这不如对amount列进行部分索引高效，因为系统必须扫描整个索引。但是，如果未计费的订单相对较少，则仅使用此部分索引查找未计费的订单可能是一种胜利。
请注意，此查询无法使用此索引：
SELECT * FROM orders WHERE order_nr = 3501;
订单3501可能是已计费或未计费的订单之一。
示例 设置部分索引以排除无趣的值还说明了索引列和谓词中使用的列不需要匹配。UXDB支持带有任意谓词的部分索引，只要涉及索引的表的列即可。但是，请记住，谓词必须与查询中使用的条件匹配，以从索引中受益。要精确，只有在系统能够识别查询的WHERE条件在数学上意味着索引的谓词时，才能在查询中使用部分索引。UXDB没有复杂的定理证明器，可以识别以不同形式编写的数学上等价的表达式。（创建这样一个通用的定理证明器极其困难，而且可能太慢而无法真正使用。）系统可以识别简单的不等式蕴含，例如“x < 1”蕴含“x <2”；否则，谓词条件必须完全匹配查询的条件。WHERE条件必须匹配，否则索引将无法被识别为可用的。匹配发生在查询计划时而不是运行时。因此，参数化查询子句与部分索引不兼容。例如，带有参数的预处理查询可能会指定“x < ?”，这永远不会意味着对于所有可能的参数值，“x < 2”都成立。
部分索引的第三种可能用途根本不需要在查询中使用索引。这里的想法是在表的子集上创建唯一索引，例如示例 设置部分唯一索引。这强制在满足索引谓词的行之间实现唯一性，而不限制不满足谓词的行。
示例 设置部分唯一索引
假设我们有一个描述测试结果的表。我们希望确保给定主题和目标组合只有一个“成功”的条目，但可能有任意数量的“失败”条目。以下是一种方法：
CREATE TABLE tests (
subject text,
target text,
success boolean,
...
);
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
WHERE success;
当成功测试很少而失败测试很多时，这是一种特别有效的方法。还可以通过创建带有IS NULL限制的唯一部分索引来仅允许一列中的一个空值。
最后，部分索引也可以用于覆盖系统的查询计划选择。此外，具有奇怪分布的数据集可能会导致系统在实际上不应该使用索引时使用索引。在这种情况下，可以设置索引，使其对有问题的查询不可用。通常，UXDB在索引使用方面做出合理的选择（例如，在检索常见值时避免使用它们，因此前面的示例仅保存索引大小，而不需要避免使用索引），而且明显错误的计划选择是一个错误报告的原因。
请记住，设置部分索引表示您知道查询计划程序所知道的至少一样多，特别是您知道何时索引可能是有利的。形成这种知识需要经验和对UXDB中索引工作方式的理解。在大多数情况下，部分索引相对于常规索引的优势将是最小的。有些情况下，它们是相当低效的，例如示例 不要使用部分索引作为分区的替代品。
示例 不要使用部分索引作为分区的替代品
您可能会尝试创建一组大型的非重叠部分索引，例如
CREATE INDEX mytable_cat_1 ON mytable (data) WHERE category = 1;
CREATE INDEX mytable_cat_2 ON mytable (data) WHERE category = 2;
CREATE INDEX mytable_cat_3 ON mytable (data) WHERE category = 3;
...
CREATE INDEX mytable_cat_N ON mytable (data) WHERE category = N;
这不是一个好方法，您最好使用一个单独的非部分索引，声明如下：
CREATE INDEX mytable_cat_data ON mytable (category, data);
虽然在这个更大的索引中进行搜索可能需要下降几个树层，但几乎肯定比选择适当的部分索引需要的规划器工作要便宜得多。问题的核心在于系统不理解部分索引之间的关系，并将费力地测试每个索引以查看它是否适用于当前查询。
如果您的表足够大，以至于单个索引确实是一个坏主意，那么您应该考虑使用分区。通过该机制，系统确实理解表和索引是非重叠的，因此可以实现更好的性能。
1.9.索引仅扫描和覆盖索引
在UXDB中，所有索引都是二级索引，这意味着每个索引都单独存储在表的主数据区域之外（在 UXDB术语中称为表的堆）。这意味着在普通索引扫描中，每个行检索都需要从索引和堆中获取数据。此外，虽然与给定可索引的WHERE条件匹配的索引条目通常在索引中紧密相邻，但它们引用的表行可能在堆中的任何位置。因此，索引扫描的堆访问部分涉及大量随机访问堆，这可能很慢，特别是在传统的旋转介质上。（位图扫描试图通过按排序顺序执行堆访问来缓解这个成本，但这只能解决部分问题。）
为了解决这个性能问题，UXDB 支持索引仅扫描，它可以仅使用索引回答查询而不需要任何堆访问。基本思想是直接从每个索引条目返回值，而不是查询关联的堆条目。这种方法可以使用的两个基本限制是：
-
索引类型必须支持索引仅扫描。B-tree 索引始终支持。GiST 和 SP-GiST索引支持某些操作符类的索引仅扫描，但不支持其他操作符类。其他索引类型没有支持。根本要求是索引必须物理存储或能够重构每个索引条目的原始数据值。作为反例，GIN索引无法支持索引仅扫描，因为每个索引条目通常仅包含原始数据值的一部分。
-
查询只能引用索引中存储的列。例如，给定一个表的列 x 和 y 的索引，同时也有一个列 z，这些查询可以使用仅索引扫描:
SELECT x, y FROM tab WHERE x = 'key'; SELECT x FROM tab WHERE x = 'key' AND y < 42;但是这些查询不能：
SELECT x, z FROM tab WHERE x = 'key'; SELECT x FROM tab WHERE x = 'key' AND z < 42;
（表达式索引和部分索引会使这个规则变得复杂，下面会讨论。）
如果这两个基本要求都满足，那么查询所需的所有数据值都可以从索引中获取，因此索引只扫描在物理上是可行的。但是，在UXDB中进行任何表扫描还有一个额外的要求：它必须验证每个检索到的行对查询的MVCC快照是“可见”的，这在并发控制中讨论过。可见性信息不存储在索引条目中，只存储在堆条目中；因此乍一看，每个行检索都需要一个堆访问。如果表行最近被修改过，这确实是这种情况。然而，对于很少更改的数据，有一种解决方法。对于表堆中的每个页面，UXDB跟踪所有存储在该页面中的行是否足够旧，以便对所有当前和未来的事务都可见。这些信息存储在表的可见性映射中的一个位中。在找到候选索引条目后，索引只扫描会检查相应堆页面的可见性映射位。如果设置了该位，则已知该行可见，因此可以返回数据而无需进一步工作。如果没有设置，则必须访问堆条目以查找其是否可见，因此与标准索引扫描相比没有获得性能优势。即使在成功的情况下，这种方法也会将可见性映射访问与堆访问交换；但由于可见性映射比它描述的堆小四个数量级，因此需要访问的物理I/O要少得多。在大多数情况下，可见性映射始终保持在内存中缓存。
简而言之，如果满足两个基本要求，则索引只扫描是可能的，但只有在表堆页面的相当大比例具有其所有可见映射位设置时，它才会获胜。但是，大部分行不变的表在实践中很常见，使得这种类型的扫描非常有用。
为了有效地使用索引只扫描功能，您可能会选择创建一个覆盖索引，这是一个专门设计用于包括您经常运行的特定类型的查询所需列的索引。由于查询通常需要检索不仅仅是它们搜索的列，UXDB允许您创建一个索引，其中一些列只是“有效负载”，而不是搜索键的一部分。这是通过添加一个INCLUDE子句来完成的，列出了额外的列。例如，如果您经常运行像这样的查询：
SELECT y FROM tab WHERE x = 'key';
传统的加速这些查询的方法是只在x上创建一个索引。然而，定义为以下索引可以将这些查询处理为索引只扫描，因为可以从索引中获取y而无需访问堆。
CREATE UNIQUE INDEX tab_x_y ON tab(x) INCLUDE (y);
即使他们从未打算将y用作WHERE子句的一部分。只要额外的列是尾随列，这种方法就可以正常工作；将它们作为前导列是不明智的。但是，此方法不支持您希望索引强制唯一性的情况下。
后缀截断总是从上层B-Tree级别中删除非键列。作为有效载荷列，它们永远不会用于指导索引扫描。当剩余的键列前缀恰好足以描述最低B-Tree级别上的元组时，截断过程还会删除一个或多个尾随键列。实际上，没有INCLUDE子句的覆盖索引通常避免存储在上层中实际上是有效载荷的列。但是，将有效载荷列明确定义为非键列可靠地使元组保持在上层小。
原则上，可以使用表达式索引进行仅索引扫描。例如，给定一个在f（x）上的索引，其中x是表列，应该可以执行:
SELECT f(x) FROM tab WHERE f(x) < 1;
作为仅索引扫描；如果f()是昂贵的计算函数，则这非常有吸引力。但是，UXDB的规划器目前对这种情况不太聪明。它仅在查询所需的所有列都可以从索引中获取时才考虑通过仅索引扫描执行查询。在此示例中，除了上下文f(x)之外，不需要x，但是规划器没有注意到这一点，并得出结论无法进行仅索引扫描。如果仅索引扫描似乎足够有价值，则可以通过将x作为包含列添加来解决此问题，例如：
CREATE INDEX tab_f_x ON tab (f(x)) INCLUDE (x);
另外需要注意的一点是，如果目标是避免重新计算f(x)，则规划器不一定会将未在可索引的WHERE子句中使用的f(x)匹配到索引列。在简单查询（如上例所示）中，它通常会正确处理，但在涉及连接的查询中则不会。这些不足可能在未来版本的UXDB 中得到解决。
部分索引还与仅索引扫描有有趣的交互。考虑示例 设置部分唯一索引中所示的部分索引：
CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
WHERE success;
原则上，我们可以在此索引上执行仅索引扫描，以满足像下面这样的查询：
SELECT target FROM tests WHERE subject = 'some-subject' AND success;
但是有一个问题：WHERE 子句引用了索引结果列中不可用的success。尽管如此，仅索引扫描仍然是可能的，因为计划不需要在运行时重新检查WHERE子句的那部分内容：在索引中找到的所有条目都必须具有success = true，因此在计划中不需要显式检查这一点。UXDB 2112及更高版本将识别此类情况并允许生成仅索引扫描，但旧版本不会。
1.10.操作符类和操作符族
索引定义可以为索引的每个列指定一个操作符类。
CREATE INDEX name ON table (column opclass [ ( opclass_options ) ] [sort options] [, ...]);
操作符类标识索引在该列上使用的操作符。例如，对于类型int4的B-tree索引将使用int4_ops类；该操作符类包括用于int4值的比较函数。实际上，列数据类型的默认操作符类通常足够使用。拥有操作符类的主要原因是对于某些数据类型，可能存在多种有意义的索引行为。例如，我们可能希望按绝对值或按实部对复数数据类型进行排序。我们可以通过为数据类型定义两个操作符类，然后在创建索引时选择适当的类来实现这一点。操作符类确定基本的排序顺序（然后可以通过添加排序选项COLLATE、ASC/DESC和/或NULLSFIRST/NULLSLAST来修改）。
除了默认操作符类之外，还有一些内置的操作符类：
-
操作符类
text_pattern_ops、varchar_pattern_ops和bpchar_pattern_ops支持在类型text、varchar和char上进行B-tree索引。与默认操作符类的区别在于，值是严格按字符比较而不是按字典顺序比较的。根据特定语言环境的排序规则，创建这些操作符类适用于涉及模式匹配表达式（LIKE或POSIX正则表达式）的查询，当数据库不使用标准的“C”语言环境时。例如，您可以像这样对varchar列进行索引：CREATE INDEX test_index ON test_table (col varchar_pattern_ops);请注意，如果您希望涉及普通的
<，<=，>或>=比较的查询使用索引，则还应创建具有默认操作符类的索引。这些查询不能使用xxx_pattern_ops操作符类。（然而，普通的等式比较可以使用这些操作符类。）可以在同一列上使用不同的操作符类创建多个索引。如果您使用C语言环境，则不需要xxx_pattern_ops操作符类，因为具有默认操作符类的索引可用于C语言环境中的模式匹配查询。
以下查询显示所有定义的操作符类：
SELECT am.amname AS index_method,
opc.opcname AS opclass_name,
opc.opcintype::regtype AS indexed_type,
opc.opcdefault AS is_default
FROM ux_am am, ux_opclass opc
WHERE opc.opcmethod = am.oid
ORDER BY index_method, opclass_name;
操作符类实际上只是一个更大结构的子集，称为操作符族。在多个数据类型具有类似行为的情况下，定义跨数据类型操作符并允许这些操作符与索引一起使用通常是有用的。为此，每种类型的操作符类必须分组到同一操作符族中。跨类型操作符是该族的成员，但不与族中的任何单个类相关联。
下面是上一个查询的扩展版本，显示每个操作符类所属的操作符族：
SELECT am.amname AS index_method,
opc.opcname AS opclass_name,
opf.opfname AS opfamily_name,
opc.opcintype::regtype AS indexed_type,
opc.opcdefault AS is_default
FROM ux_am am, ux_opclass opc, ux_opfamily opf
WHERE opc.opcmethod = am.oid AND
opc.opcfamily = opf.oid
ORDER BY index_method, opclass_name;
此查询显示所有定义的操作符族以及每个族中包含的所有操作符：
SELECT am.amname AS index_method,
opf.opfname AS opfamily_name,
amop.amopopr::regoperator AS opfamily_operator
FROM ux_am am, ux_opfamily opf, ux_amop amop
WHERE opf.opfmethod = am.oid AND
amop.amopfamily = opf.oid
ORDER BY index_method, opfamily_name, opfamily_operator;
提示
uxsql具有\dAc、\dAf和\dAo命令，提供了稍微复杂一些的查询版本。
1.11.索引和排序规则
每个索引列只能支持一个排序规则。 如果有多个排序规则，则可能需要多个索引。
考虑以下语句：
CREATE TABLE test1c (
id integer,
content varchar COLLATE "x"
);
CREATE INDEX test1c_content_index ON test1c (content);
索引自动使用底层列的排序规则。 因此，查询形式为
SELECT * FROM test1c WHERE content > constant;
可以使用索引，因为比较默认使用列的排序规则。 但是，此索引无法加速涉及其他排序规则的查询。 因此，如果还需要查询形式如下：
SELECT * FROM test1c WHERE content > constant COLLATE "y";
可以创建支持"y"排序规则的附加索引，如下所示：
CREATE INDEX test1c_content_y_index ON test1c (content COLLATE "y");
1.12.检查索引使用情况
虽然在UXDB中的索引不需要维护或调整，但检查实际查询工作负载使用哪些索引仍然很重要。使用EXPLAIN命令可以检查单个查询的索引使用情况。还可以收集有关运行服务器中索引使用情况的总体统计信息。
很难制定确定要创建哪些索引的一般程序。在前面的各个部分的示例中已经展示了一些典型情况。通常需要进行大量的实验。本节的其余部分提供了一些提示：
-
始终先运行
ANALYZE命令。此命令收集有关表中值分布的统计信息。这些信息是估计查询返回的行数所需的，规划器需要为每个可能的查询计划分配逼真的成本。在没有任何真实统计信息的情况下，将假定一些默认值，这几乎肯定是不准确的。因此，在没有运行ANALYZE的情况下检查应用程序的索引使用情况是徒劳的。 -
使用真实数据进行实验。使用测试数据设置索引将告诉您测试数据需要哪些索引，但仅此而已。
特别是使用非常小的测试数据集是致命的。虽然从100000行中选择1000行可能是索引的候选项，但从100行中选择1行几乎不可能，因为这将导致索引不起作用。100行可能适合于单个磁盘页面，并且没有计划可以打败顺序获取1个磁盘页面。
在制作测试数据时要小心，当应用程序尚未投入生产时，这是不可避免的。非常相似，完全随机或按排序顺序插入的值会使统计数据偏离真实数据的分布。
-
当不使用索引时，强制使用它们可能对测试有用。有一些运行时参数可以关闭各种计划类型。例如，关闭顺序扫描（
enable_seqscan）和嵌套循环连接（enable_nestloop），这是最基本的计划，将强制系统使用不同的计划。如果系统仍然选择顺序扫描或嵌套循环连接，则可能存在更根本的原因，即查询条件与索引不匹配。（可以在前面的章节中解释什么样的查询可以使用什么样的索引。） -
如果强制使用索引，则有两种可能性：系统是正确的，使用索引确实不合适，或者查询计划的成本估计不反映现实。因此，您应该使用和不使用索引来计时查询。这里可以使用
EXPLAIN ANALYZE命令。 -
如果发现成本估计错误，则有两种可能性。总成本是从每个计划节点的每行成本乘以计划节点的选择性估计得出的。可以通过运行时参数调整计划节点的估计成本。不准确的选择性估计是由于统计数据不足。通过调整收集统计数据的参数可能会改善这种情况。
-
如果您无法调整成本以使其更合适，则可能必须明确地强制使用索引。您还可能希望联系UXDB开发人员以检查该问题。
2.全文搜索
2.1.介绍
全文搜索（或仅称为文本搜索）提供了识别满足查询的自然语言文档的能力，并可选择按与查询相关性排序。最常见的搜索类型是查找包含给定查询词的所有文档，并按其与查询的相似度排序。查询和相似度的概念非常灵活，取决于具体应用。最简单的搜索将查询视为一组单词，将相似度视为文档中查询单词的频率。
文本搜索运算符在数据库中存在多年。UXDB具有用于文本数据类型的~、~*、LIKE和ILIKE运算符，但它们缺乏现代信息系统所需的许多基本属性：
-
没有语言支持，即使是英语。正则表达式不足以处理派生词，例如
satisfies和satisfy。您可能会错过包含satisfies的文档，尽管在搜索satisfy时可能希望找到它们。可以使用OR搜索多个派生形式，但这很繁琐且容易出错（有些单词可能有几千个派生词）。 -
它们不提供搜索结果的排序（排名），这使它们无法满足现代信息系统的要求。例如，搜索引擎需要根据查询的相关性对搜索结果进行排序。
-
它们不提供词干分析（stemming）或同义词扩展（synonym expansion），这使得搜索结果可能不完整。例如，如果您搜索
car，您可能会错过包含cars或automobiles的文档。
全文索引允许对文档进行预处理，并保存索引以供以后快速搜索。预处理包括：
-
将文档解析为标记。有用的是识别各种标记类别，例如数字、单词、复杂单词、电子邮件地址，以便可以以不同方式处理它们。原则上，标记类别取决于具体应用，但对于大多数目的，使用预定义的类别是足够的。UXDB使用解析器执行此步骤。提供了标准解析器，可以为特定需求创建自定义解析器。
-
将标记转换为词元。词元是一个字符串，就像标记一样，但它已经被规范化，以使同一单词的不同形式相似。例如，规范化几乎总是包括将大写字母折叠为小写字母，并且通常涉及删除后缀（例如英语中的s或es）。这允许搜索找到同一单词的变体形式，而不必繁琐地输入所有可能的变体。此外，此步骤通常消除停用词，这些词是如此常见，以至于对于搜索而言是无用的。（简而言之，标记是文档文本的原始片段，而词元是认为对索引和搜索有用的单词。）UXDB使用字典执行此步骤。提供了各种标准字典，可以为特定需求创建自定义字典。
-
存储经过优化的预处理文档以供搜索。例如，每个文档可以表示为规范化词元的排序数组。除了词元之外，通常还希望存储位置信息以用于接近排名，以便包含更密集的查询词区域的文档比分散的查询词的文档分配更高的排名。
字典允许对标记进行细粒度控制。使用适当的字典，您可以：
-
定义不应索引的停用词。
-
使用Ispell将同义词映射到单个单词。
-
使用词库将短语映射到单个单词。
-
使用Ispell字典将单词的不同变体映射到规范形式。
-
使用Snowball词干提取器规则将单词的不同变体映射到规范形式。
提供了数据类型tsvector用于存储预处理文档，以及一种类型tsquery用于表示处理过的查询。这些数据类型有许多可用的函数和运算符，其中最重要的是匹配运算符@@。可以使用索引（全文搜索首选索引类型 ）加速全文搜索。
2.1.1.什么是文档？
文档是全文搜索系统中的搜索单位，例如杂志文章或电子邮件。文本搜索引擎必须能够解析文档并将词元（关键词）与其父文档的关联存储起来。稍后，这些关联将用于搜索包含查询词的文档。
对于在UXDB中的搜索，文档通常是数据库表中的文本字段，或者可能是这些字段的组合（连接），可能存储在多个表中或动态获取。换句话说，可以从不同的部分构建文档以进行索引，并且可能不会完整地存储在任何地方。例如：
SELECT title || ' ' || author || ' ' || abstract || ' ' || body AS document
FROM messages
WHERE mid = 12;
SELECT m.title || ' ' || m.author || ' ' || m.abstract || ' ' || d.body AS document
FROM messages m, docs d
WHERE m.mid = d.did AND m.mid = 12;
注意
实际上，在这些示例查询中，应该使用coalesce来防止单个NULL属性导致整个文档的NULL结果。
另一种可能性是将文档存储为文件系统中的简单文本文件。在这种情况下，可以使用数据库存储完整文本索引并执行搜索，并且可以使用某些唯一标识符从文件系统检索文档。但是，从数据库外部检索文件需要超级用户权限或特殊函数支持，因此通常比将所有数据保留在UXDB内部不方便。此外，将所有内容保留在数据库中允许轻松访问文档元数据以帮助索引和显示。
对于文本搜索目的，每个文档必须缩减为预处理的tsvector格式。搜索和排名完全在文档的tsvector表示上执行，只有在选择要向用户显示的文档时才需要检索原始文本。因此，我们经常将tsvector称为文档，但它当然只是完整文档的紧凑表示。
2.1.2.基本文本匹配
UXDB中的全文搜索基于匹配运算符@@，如果tsvector（文档）与tsquery（查询）匹配，则返回true。不管哪种数据类型先写：
SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector @@ 'cat & rat'::tsquery;
?column?
----------
t
SELECT 'fat & cow'::tsquery @@ 'a fat cat sat on a mat and ate a fat rat'::tsvector;
?column?
----------
f
如上例所示，tsquery 不仅仅是原始文本，tsvector 也不是。 tsquery包含搜索术语，这些术语必须是已经标准化的词元，并且可以使用AND、OR、NOT和FOLLOWED BY运算符组合多个术语。有一些函数to_tsquery、plainto_tsquery 和phraseto_tsquery，它们有助于将用户编写的文本转换为正确的tsquery，主要是通过标准化出现在文本中的单词。同样，to_tsvector用于解析和标准化文档字符串。因此，在实践中，文本搜索匹配看起来更像这样：
SELECT to_tsvector('fat cats ate fat rats') @@ to_tsquery('fat & rat');
?column?
----------
t
请注意，如果写成以下形式，则此匹配将不会成功
SELECT 'fat cats ate fat rats'::tsvector @@ to_tsquery('fat & rat');
?column?
----------
f
因为这里不会对单词 rats 进行标准化。 tsvector 的元素是词元，假定已经标准化，因此 rats 不匹配 rat。
@@ 运算符还支持 text 输入，允许在简单情况下跳过将文本字符串显式转换为 tsvector 或tsquery。可用的变体是：
tsvector @@ tsquery
tsquery @@ tsvector
text @@ tsquery
text @@ text
前两个我们已经看到了。形式 text @@ tsquery 等效于 to_tsvector(x) @@ y。形式 text``@@ text 等效于 to_tsvector(x) @@ plainto_tsquery(y)。
在 tsquery中，&（AND）运算符指定文档中必须同时出现其两个参数才能匹配。类似地，|（OR）运算符指定必须至少出现其一个参数，而!（NOT）运算符指定其参数必须不出现才能匹配。例如，查询 fat & ! rat 匹配包含 fat 但不包含 rat 的文档。
通过使用 <->（FOLLOWED BY）tsquery运算符，可以搜索短语，该运算符仅在其参数具有相邻且按给定顺序匹配的匹配项时才匹配。例如：
SELECT to_tsvector('fatal error') @@ to_tsquery('fatal <-> error');
?column?
----------
t
SELECT to_tsvector('error is not fatal') @@ to_tsquery('fatal <-> error');
?column?
----------
f
有一个更通用的 FOLLOWED BY 运算符版本，其形式为 <N>，其中 N 是一个整数，表示匹配词元之间位置的差异。<1> 与 <-> 相同，而 <2>允许在匹配项之间出现一个词元，以此类推。 phraseto_tsquery函数利用此运算符构建一个 tsquery，可以在一些单词是停用词的情况下匹配多个单词的短语。例如：
SELECT phraseto_tsquery('cats ate rats');
phraseto_tsquery
-------------------------------
'cat' <-> 'ate' <-> 'rat'
SELECT phraseto_tsquery('the cats ate the rats');
phraseto_tsquery
-------------------------------
'cat' <-> 'ate' <2> 'rat'
有时候有一个特殊情况是很有用的，即可以使用 <0> 来要求两个模式匹配相同的单词。
括号可用于控制 tsquery 运算符的嵌套。没有括号时，| 的优先级最低，然后是 &，然后是 <->，最后是 !。
值得注意的是，当 AND/OR/NOT 运算符在 FOLLOWED BY 运算符的参数中时，它们的含义与不在其中时略有不同，因为在FOLLOWED BY 中匹配的确切位置很重要。例如，通常情况下，!x 只匹配不包含 x 的文档。但是，!x <-> y 匹配y，如果它不是紧接在 x 之后；文档中其他地方出现的 x 不会阻止匹配。另一个例子是，x & y 通常只要求 x 和 y在文档中的任何地方都出现，但是 (x & y) <-> z 要求 x 和 y 在同一位置匹配，紧接在 z 之前。因此，此查询与x <-> z & y <-> z 的行为不同，后者将匹配包含两个单独序列 x z 和 y z的文档。（这个特定的查询写法是无用的，因为 x 和 y不能在同一位置匹配；但是对于更复杂的情况，例如前缀匹配模式，这种形式的查询可能是有用的。）
2.1.3.配置
上面都是简单的文本搜索示例。如前所述，全文搜索功能包括许多其他功能：跳过索引某些单词（停用词）、处理同义词并使用复杂的解析，例如基于不仅仅是空格的解析。此功能由文本搜索配置控制。 UXDB 预定义了许多语言的配置，您可以轻松创建自己的配置。（uxsql 的 \dF命令显示所有可用配置。）
在安装期间选择适当的配置，并相应地在 uxsinodb.conf 中设置default_text_search_config。如果您在整个集群中使用相同的文本搜索配置，则可以使用uxsinodb.conf 中的值。要在整个集群中使用不同的配置但在任何一个数据库中使用相同的配置，请使用 ALTER DATABASE ...SET。否则，您可以设置每个数据库中的默认配置。每个会话中都有一个default_text_search_config。
每个依赖于配置的文本搜索函数都有一个可选的regconfig参数，以便可以明确指定要使用的配置。只有在省略此参数时才使用default_text_search_config。
为了更容易地构建自定义文本搜索配置，配置是从更简单的数据库对象构建的。UXDB的文本搜索功能提供了四种与配置相关的数据库对象：
-
文本搜索解析器将文档分解为标记，并对每个标记进行分类（例如，作为单词或数字）。
-
文本搜索词典将标记转换为规范化形式并拒绝停用词。
-
文本搜索模板提供了字典的底层函数。（字典只是为模板指定了一组参数和模板。）
-
文本搜索配置选择一个解析器和一组词典，以用于规范化解析器产生的标记。
文本搜索解析器和模板是从低级C函数构建的；因此，开发新的解析器和模板需要C编程能力，并且需要超级用户特权将其安装到数据库中。（在UXDB分发的contrib/区域中有一些附加解析器和模板的示例。）由于词典和配置只是对一些底层解析器和模板进行参数化和连接，因此创建新的词典或配置不需要特殊权限。本章后面将介绍创建自定义词典和配置的示例。
2.2.表格和索引
前一节的示例演示了使用简单常量字符串进行全文匹配。本节将展示如何搜索表格数据，可选择使用索引。
2.2.1.搜索表格
可以在没有索引的情况下进行全文搜索。一个简单的查询，打印包含单词friend在其body字段中的每一行的title是：
SELECT title
FROM uxweb
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');
这也会找到相关的单词，例如friends和friendly，因为所有这些都被归一化为相同的词元。
上面的查询指定使用english配置来解析和规范化字符串。或者我们可以省略配置参数：
SELECT title
FROM uxweb
WHERE to_tsvector(body) @@ to_tsquery('friend');
此查询将使用由default_text_search_config设置的配置。
一个更复杂的例子是选择包含create和table在title或body中的最近的十个文档：
SELECT title
FROM uxweb
WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC
LIMIT 10;
为了清晰起见，我们省略了coalesce函数调用，这将需要找到在两个字段中包含NULL的行。
虽然这些查询可以在没有索引的情况下工作，但大多数应用程序会发现这种方法太慢，除非偶尔进行临时查询。因此，通常会创建全文搜索索引。搜索。文本搜索的实际应用通常需要创建索引。
2.2.2.创建索引
我们可以创建一个GIN索引（全文搜索首选索引类型）来加速文本搜索：
CREATE INDEX uxweb_idx ON uxweb USING GIN (to_tsvector('english', body));
请注意，使用了2个参数的to_tsvector版本。只有指定配置名称的文本搜索函数才能在表达式索引中使用。这是因为索引内容必须不受default_text_search_config的影响。如果受到影响，索引内容可能会不一致，因为不同的条目可能包含使用不同文本搜索配置创建的tsvector，而且没有办法猜测哪个是哪个。这将无法正确地转储和还原此类索引。
由于上面的索引使用了带有2个参数的 to_tsvector 版本，因此只有使用相同配置名称的 2个参数版本的查询引用才会使用该索引。也就是说，WHERE to_tsvector('english', body) @@ 'a & b' 可以使用该索引，但 WHERE to_tsvector(body) @@ 'a & b'不能。这确保索引仅与用于创建索引条目的相同配置一起使用。
可以设置更复杂的表达式索引，其中配置名称由另一列指定，例如：
CREATE INDEX uxweb_idx ON uxweb USING GIN (to_tsvector(config_name, body));
其中 config_name 是 uxweb表中的一列。这允许在同一索引中使用混合配置，同时记录用于每个索引条目的配置。例如，如果文档集合包含不同语言的文档，则这将非常有用。同样，查询旨在使用索引的查询必须被表述为匹配，例如，WHERE to_tsvector(config_name, body) @@ 'a &b'。
索引甚至可以连接列：
CREATE INDEX uxweb_idx ON uxweb USING GIN (to_tsvector('english', title || ' ' || body));
另一种方法是创建一个单独的 tsvector 列来保存 to_tsvector的输出。为了使此列自动保持与其源数据同步，请使用存储生成的列。此示例是title 和 body 的连接，使用 coalesce 确保在另一个字段为 NULL 时仍将一个字段索引：
ALTER TABLE uxweb
ADD COLUMN textsearchable_index_col tsvector
GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;
然后我们创建一个 GIN索引来加速搜索：
CREATE INDEX textsearch_idx ON uxweb USING GIN (textsearchable_index_col);
现在我们准备执行快速全文搜索：
SELECT title
FROM uxweb
WHERE textsearchable_index_col @@ to_tsquery('create & table')
ORDER BY last_mod_date DESC
LIMIT 10;
单独列的方法比表达式索引的优点是这意味着在查询中不需要显式指定文本搜索配置以利用索引。如上例所示，查询可以依赖于default_text_search_config。另一个优点是搜索速度更快，因为不需要重新执行to_tsvector调用以验证索引匹配（当使用GiST索引而不是GIN索引时，这更为重要；请参见全文搜索首选索引类型。然而，表达式索引方法更简单易行，并且需要更少的磁盘空间，因为tsvector表示不是显式存储的。
2.3.控制文本搜索
要实现全文搜索，必须有一个函数来从文档创建tsvector，并从用户查询创建tsquery。此外，我们需要以有用的顺序返回结果，因此我们需要一个比较文档与查询相关性的函数。还要能够漂亮地显示结果。UXDB提供了所有这些函数的支持。
2.3.1.解析文档
UXDB提供了函数to_tsvector，用于将文档转换为tsvector数据类型。
to_tsvector([ config regconfig, ] document text) returns tsvector
to_tsvector将文本文档解析为标记，将标记缩减为词元，并返回一个tsvector，其中列出了词元及其在文档中的位置。文档根据指定或默认的文本搜索配置进行处理。以下是一个简单的示例：
SELECT to_tsvector('english', 'a fat cat sat on a mat - it ate a fat rats');
to_tsvector
-----------------------------------------------------
'ate':9 'cat':3 'fat':2,11 'mat':7 'rat':12 'sat':4
在上面的示例中，我们可以看到生成的tsvector并没有包含单词 a、on 或 it，单词 rats 变成了rat，标点符号 - 被忽略。
to_tsvector 函数内部调用解析器，将文档文本分解为标记并为每个标记分配类型。对于每个标记，都会查询一组字典（字典），其中列表可能因标记类型而异。第一个识别标记的字典会发出一个或多个规范化的词元来表示该标记。例如，单词rats 变成了 rat，因为其中一个字典识别到单词 rats 是 rat 的复数形式。有些单词被识别为停用词（停用词 ），因为它们出现得太频繁，无法用于搜索。在我们的示例中，这些单词是a、on 和 it。如果列表中没有任何字典识别标记，则该标记也会被忽略。在此示例中，标点符号 -就是这种情况，因为实际上没有为其标记类型（空格符号）分配任何字典，这意味着空格标记永远不会被索引。解析器、字典和要索引的标记类型的选择由所选的文本搜索配置（配置示例）确定。在同一数据库中可能有许多不同的配置，并且针对各种语言提供了预定义的配置。在我们的示例中，我们使用了英语语言的默认配置english。
setweight 函数可用于使用给定的权重标记 tsvector 条目，其中权重是字母 A、B、C 或 D中的一个。这通常用于标记来自文档不同部分的条目，例如标题与正文。稍后，可以使用此信息对搜索结果进行排名。
因为 to_tsvector(NULL) 将返回 NULL，因此建议在字段可能为空时使用coalesce。以下是从结构化文档创建 tsvector 的推荐方法：
UPDATE tt SET ti =
setweight(to_tsvector(coalesce(title,'')), 'A') ||
setweight(to_tsvector(coalesce(keyword,'')), 'B') ||
setweight(to_tsvector(coalesce(abstract,'')), 'C') ||
setweight(to_tsvector(coalesce(body,'')), 'D');
在此示例中，我们使用 setweight 标记了完成的 tsvector 中每个词元的来源，然后使用 tsvector 连接运算符|| 合并了标记的 tsvector 值。
2.3.2.解析查询
UXDB 提供了函数 to_tsquery、plainto_tsquery、phraseto_tsquery 和websearch_to_tsquery，用于将查询转换为 tsquery 数据类型。 to_tsquery 提供了比plainto_tsquery 或 phraseto_tsquery 更多的功能，但对其输入要求更严格。websearch_to_tsquery 是 to_tsquery 的简化版本，具有类似于 Web 搜索引擎使用的替代语法。
to_tsquery([ config regconfig, ] querytext text) returns tsquery
to_tsquery从querytext创建一个tsquery值，它必须由单个令牌组成，这些令牌由tsquery运算符 &（AND）、|（OR）、!（NOT）和 <->（FOLLOWED BY）分隔，可能使用括号分组。换句话说，输入到to_tsquery 必须已经遵循tsquery输入的一般规则。不同之处在于，虽然基本的 tsquery 输入按面值接受令牌，但 to_tsquery使用指定或默认配置将每个令牌规范化为词元，并且丢弃根据配置是停用词的任何令牌。例如：
SELECT to_tsquery('english', 'The & Fat & Rats');
to_tsquery
---------------
'fat' & 'rat'
与基本的 tsquery 输入一样，可以将权重附加到每个词元，以将其限制为仅匹配那些权重的 tsvector 词元。例如：
SELECT to_tsquery('english', 'Fat | Rats:AB');
to_tsquery
------------------
'fat' | 'rat':AB
此外，可以将 * 附加到词元以指定前缀匹配：
SELECT to_tsquery('supern:*A & star:A*B');
to_tsquery
--------------------------
'supern':*A & 'star':*AB
这样的词元将匹配以给定字符串开头的任何单词。to_tsquery还可以接受单引号括起来的短语。当配置包括可能触发这些短语的同义词词典时，这是主要有用的。在下面的示例中，同义词词典包含规则supernovae stars : sn：
SELECT to_tsquery('''supernovae stars'' & !crab');
to_tsquery
---------------
'sn' & !'crab'
如果没有引号，to_tsquery将为未由 AND、OR 或 FOLLOWED BY 运算符分隔的令牌生成语法错误。
plainto_tsquery([ config regconfig, ] querytext text) returns tsquery
plainto_tsquery 将未格式化的文本 querytext 转换为 tsquery值。该文本被解析和规范化，就像to_tsvector一样，然后在幸存单词之间插入&（AND）tsquery运算符。
例如：
SELECT plainto_tsquery('english', 'The Fat Rats');
plainto_tsquery
-----------------
'fat' & 'rat
请注意，plainto_tsquery不会识别其输入中的tsquery运算符、权重标签或前缀匹配标签：
SELECT plainto_tsquery('english', 'The Fat & Rats:C');
plainto_tsquery
---------------------
'fat' & 'rat' & 'c'
在这里，所有输入标点符号都被丢弃了。
phraseto_tsquery([ config regconfig, ] querytext text) returns tsquery
phraseto_tsquery的行为类似于plainto_tsquery，但它在幸存单词之间插入<->（FOLLOWED BY）运算符，而不是&（AND）运算符。 此外，停用词不仅仅被丢弃，而是通过插入<N>运算符而不是<->运算符来考虑。当搜索精确的词元序列时，此函数非常有用，因为FOLLOWED BY运算符不仅检查所有词元的存在，还检查词元的顺序。
例如：
SELECT phraseto_tsquery('english', 'The Fat Rats');
phraseto_tsquery
------------------
'fat' <-> 'rat'
与plainto_tsquery一样，phraseto_tsquery函数不会识别其输入中的tsquery运算符、权重标签或前缀匹配标签：
SELECT phraseto_tsquery('english', 'The Fat & Rats:C');
phraseto_tsquery
-----------------------------
'fat' <-> 'rat' <-> 'c'
websearch_to_tsquery([ config regconfig, ] querytext text) returns tsquery
websearch_to_tsquery使用另一种语法从querytext创建一个tsquery值，其中简单的未格式化文本是有效的查询。与plainto_tsquery和phraseto_tsquery不同，它还识别某些运算符。此外，此函数永远不会引发语法错误，这使得可以使用原始用户提供的输入进行搜索。支持以下语法：
-
unquoted text：未在引号中的文本将转换为由&运算符分隔的术语，就像由plainto_tsquery处理的那样。 -
“quoted text”：引号中的文本将转换为由<->运算符分隔的术语，就像由phraseto_tsquery处理的那样。 -
OR：单词“or”将转换为|运算符。 -
-：破折号将被转换为!运算符。
其他标点符号将被忽略。因此，与plainto_tsquery和phraseto_tsquery一样，websearch_to_tsquery函数将不会在其输入中识别tsquery运算符、权重标签或前缀匹配标签。
示例：
SELECT websearch_to_tsquery('english', 'The fat rats');
websearch_to_tsquery
----------------------
'fat' & 'rat'
(1 row)
SELECT websearch_to_tsquery('english', '"supernovae stars" -crab');
websearch_to_tsquery
----------------------------------
'supernova' <-> 'star' & !'crab'
(1 row)
SELECT websearch_to_tsquery('english', '"sad cat" or "fat rat"');
websearch_to_tsquery
-----------------------------------
'sad' <-> 'cat' | 'fat' <-> 'rat'
(1 row)
SELECT websearch_to_tsquery('english', 'signal -"segmentation fault"');
websearch_to_tsquery
---------------------------------------
'signal' & !( 'segment' <-> 'fault' )
(1 row)
SELECT websearch_to_tsquery('english', '""" )( dummy \\ query <->');
websearch_to_tsquery
----------------------
'dummi' & 'queri'
(1 row)
2.3.3.搜索结果排名
排名尝试衡量文档与特定查询的相关性，以便在有许多匹配项时，可以首先显示最相关的匹配项。UXDB提供了两个预定义的排名函数，它们考虑了词汇、接近度和结构信息；也就是说，它们考虑查询词项在文档中出现的频率、词项在文档中的距离以及它们出现的文档部分的重要性。然而，相关性的概念是模糊的，非常应用程序特定。不同的应用程序可能需要额外的信息来进行排名，例如文档修改时间。内置的排名函数只是示例。您可以编写自己的排名函数和/或将它们的结果与其他因素结合起来以适应您的特定需求。
目前可用的两个排名函数是：
ts_rank([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4
基于匹配词元的频率对向量进行排名。
ts_rank_cd([ weights float4[], ] vector tsvector, query tsquery [, normalization integer ]) returns float4
此函数计算给定文档向量和查询的覆盖密度排名，如Clarke、Cormack和Tudhope在期刊《信息处理和管理》中的“一到三个术语查询的相关性排名”中所述。1999年，Cover密度类似于ts_rank排名，但考虑到匹配词元之间的接近程度。
此函数需要词元位置信息来执行其计算。因此，它忽略tsvector中的任何“剥离”词元。如果输入中没有未剥离的词元，则结果将为零。（有关strip函数和tsvector中的位置信息的更多信息，请参见附加功能 。）
对于这两个函数，可选的weights参数提供了根据标签对单词实例进行更重或更轻加权的能力。权重数组按顺序指定了如何加权每个单词类别：
{D-weight，C-weight，B-weight，A-weight}
如果没有提供weights，则使用以下默认值：
{0.1, 0.2, 0.4, 1.0}
通常，权重用于标记文档中的特殊区域的单词，例如标题或初始摘要，以便可以将它们视为比文档正文中的单词更重要或不重要。
由于较长的文档更有可能包含查询词，因此考虑文档大小是合理的，例如，具有五个搜索词实例的一百字文档可能比具有五个搜索词实例的一千字文档更相关。两个排名函数都采用整数normalization选项，该选项指定文档长度是否以及如何影响其排名。整数选项控制多个行为，因此它是位掩码：您可以使用|指定一个或多个行为（例如，2|4）。
-
0(默认值)忽略文档长度
-
1将排名除以1 + 文档长度的对数
-
2用文档长度除以等级
-
4平均调和距离除以秩(仅由 ts_ rank_cd 实现)
-
8用文档中唯一单词的数量来划分等级
-
16用1 + 文档中唯一单词数的对数除以排名
-
32除以等级本身 + 1
如果指定了多个标志位，则按列出的顺序应用变换。
重要的是要注意，排名函数不使用任何全局信息，因此不可能产生所需的公平归一化为1％或100％。归一化选项32（rank/(rank+1)）可应用于将所有排名缩放到零到一的范围内，但是这只是一种外观上的变化；它不会影响搜索结果的排序。
以下是一个示例，仅选择排名最高的十个匹配项：
SELECT title, ts_rank_cd(textsearch, query) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE query @@ textsearch
ORDER BY rank DESC
LIMIT 10;
title | rank
-----------------------------------------------+----------
Neutrinos in the Sun | 3.1
The Sudbury Neutrino Detector | 2.4
A MACHO View of Galactic Dark Matter | 2.01317
Hot Gas and Dark Matter | 1.91171
The Virgo Cluster: Hot Plasma and Dark Matter | 1.90953
Rafting for Solar Neutrinos | 1.9
NGC 4650A: Strange Galaxy and Dark Matter | 1.85774
Hot Gas and Dark Matter | 1.6123
Ice Fishing for Cosmic Neutrinos | 1.6
Weak Lensing Distorts the Universe | 0.818218
这是使用规范化排名的同一个例子:
SELECT title, ts_rank_cd(textsearch, query, 32 /* rank/(rank+1) */ ) AS rank
FROM apod, to_tsquery('neutrino|(dark & matter)') query
WHERE query @@ textsearch
ORDER BY rank DESC
LIMIT 10;
title | rank
-----------------------------------------------+-------------------
Neutrinos in the Sun | 0.756097569485493
The Sudbury Neutrino Detector | 0.705882361190954
A MACHO View of Galactic Dark Matter | 0.668123210574724
Hot Gas and Dark Matter | 0.65655958650282
The Virgo Cluster: Hot Plasma and Dark Matter | 0.656301290640973
Rafting for Solar Neutrinos | 0.655172410958162
NGC 4650A: Strange Galaxy and Dark Matter | 0.650072921219637
Hot Gas and Dark Matter | 0.617195790024749
Ice Fishing for Cosmic Neutrinos | 0.615384618911517
Weak Lensing Distorts the Universe | 0.450010798361481
排名可能是昂贵的，因为它需要查询每个匹配文档的tsVector，这可能是 I/O 绑定的，因此速度很慢。不幸的是，这几乎是不可避免的，因为实际查询通常会导致大量匹配。
2.3.4.加亮结果
为了显示搜索结果，理想的做法是显示每个文档的一部分以及它与查询的关系。通常，搜索引擎会显示带有标记搜索词的文档片段。UXDB提供了一个实现此功能的函数ts_header。
ts_headline([ config regconfig, ] document text, query tsquery [, options text ]) returns text
ts_header 接受一个文档和一个查询，并返回文档的摘录，其中突出显示了来自查询的术语。用于解析文档的配置可以由config指定；如果省略了config，则使用default_text_search_config配置。
如果指定了选项字符串，它必须由一个逗号分隔的列表组成，列表中有一个或多个option=value对。现有的选择包括:
-
MaxWords，MinWords(整数)：这些数字决定输出的最长和最短标题。默认值分别为35和15。 -
ShortWord(整数)：这个长度或更短的单词将在标题的开头和结尾被删除，除非它们是查询词。缺省值 three 排除了常见的英语文章。 -
HighlightAll（布尔值）：如果为true，则整个文档将用作标题，忽略前面的三个参数。默认值为false。 -
MaxFragments（整数）：要显示的文本片段的最大数量。默认值为零，选择非基于片段的标题生成方法。大于零的值选择基于片段的标题生成（见下文）。 -
StartSel，StopSel（字符串）：用于定界出现在文档中的查询词的字符串，以将它们与其他摘录的单词区分开来。默认值为“<b>”和“</b>”，适用于HTML输出。 -
FragmentDelimiter（字符串）：当显示多个片段时，将使用此字符串分隔片段。默认值为“...”。
这些选项名称不区分大小写。如果字符串值包含空格或逗号，则必须将其用双引号括起来。
在非基于片段的标题生成中，ts_headline定位给定query的匹配项，并选择一个要显示的匹配项，优先选择在允许的标题长度内具有更多查询词的匹配项。在基于片段的标题生成中，ts_headline定位查询匹配项，并将每个匹配项分成不超过MaxWords个单词的“片段”，优先选择具有更多查询词的片段，并在可能的情况下“拉伸”片段以包括周围的单词。因此，在查询匹配跨越文档的大部分内容或希望显示多个匹配项时，基于片段的模式更有用。在任一模式下，如果无法识别查询匹配项，则将显示文档中前MinWords个单词的单个片段。
例如：
SELECT ts_headline('english',
'The most common type of search
is to find all documents containing given query terms
and return them in order of their similarity to the
query.',
to_tsquery('english', 'query & similarity'));
ts_headline
------------------------------------------------------------
containing given <b>query</b> terms +
and return them in order of their <b>similarity</b> to the+
<b>query</b>.
SELECT ts_headline('english',
'Search terms may occur
many times in a document,
requiring ranking of the search matches to decide which
occurrences to display in the result.',
to_tsquery('english', 'search & term'),
'MaxFragments=10, MaxWords=7, MinWords=3, StartSel=<<, StopSel=>>');
ts_headline
------------------------------------------------------------
<<Search>> <<terms>> may occur +
many times ... ranking of the <<search>> matches to decide
ts_headline使用原始文档，而不是tsvector摘要，因此它可能会很慢，应谨慎使用。
2.4.附加功能
本节介绍与文本搜索相关的附加函数和运算符。
2.4.1.操作文档
解析文档介绍了如何将原始文本文档转换为tsvector值。UXDB还提供了可用于操作已经以tsvector形式存在的文档的函数和运算符。
-
tsvector||tsvectortsvector连接运算符返回一个向量，该向量结合了作为参数给出的两个向量的词元和位置信息。 位置和权重标签在连接期间保留。出现在右侧向量中的位置会偏移左侧向量中提到的最大位置，因此结果几乎等同于对两个原始文档字符串进行连接后执行to_tsvector的结果。（等价性不是精确的，因为从左侧参数末尾删除的任何停用词不会影响结果，而如果使用文本连接，则它们会影响右侧参数中词元的位置。）使用向量形式的连接而不是在应用
to_tsvector之前连接文本的一个优点是，可以在不重新解析文档的情况下添加或删除词元。 -
setweight(vector tsvector, weight "char") returns tsvectorsetweight返回输入向量的一个拷贝，其中每一个位置都被标注为给定的权重：A、B、C或D（D是新向量的默认值并且并不会被显示在输出上）。向量被连接时会保留这些标签，允许来自文档的不同部分的词被排名函数给予不同的权重。
注意权重标签是应用到位置而不是词位。如果输入向量已经被剥离了位置，则setweight什么也不会做。
-
length(vector tsvector) returns integer返回存储在向量中的词位数。
-
strip(vector tsvector) returns tsvector返回一个向量，其中列出了和给定向量相同的词位，不过没有任何位置或者权重信息。其结果通常比未被剥离的向量小很多，但是用处也小很多。和未被剥离的向量一样，相关度排名在已剥离的向量上也不起作用。此外，<->（FOLLOWED BY）tsquery操作符不会匹配已剥离的输入，因为它无法确定词位之间的距离。
2.4.2.操作查询
tsquery是一种用于搜索tsvector的查询语言。解析查询介绍了如何编写查询。本节介绍了一些可用于操作查询的函数和运算符。
-
tsquery&&tsquery返回两个给定查询的AND组合。
-
tsquery||tsquery返回两个给定查询的OR组合。
-
!! tsquery返回给定查询的否定（NOT）。
-
tsquery<->tsquery返回一个查询，使用
<->（FOLLOWED BY）tsquery运算符，搜索与第一个给定查询匹配的内容，紧接着是与第二个给定查询匹配的内容。例如：SELECT to_tsquery('fat') <-> to_tsquery('cat | rat'); ?column? ---------------------------- 'fat' <-> ( 'cat' | 'rat' ) -
tsquery_phrase(query1 tsquery, query2 tsquery [, distance integer ]) returns tsquery返回一个查询，使用
<N>tsquery运算符，在恰好distance个词元的距离上，搜索与第一个给定查询匹配的内容，紧接着是与第二个给定查询匹配的内容。例如：SELECT tsquery_phrase(to_tsquery('fat'), to_tsquery('cat'), 10); tsquery_phrase ------------------ 'fat' <10> 'cat' -
numnode(query tsquery) returnsinteger返回
tsquery中节点（词元加运算符）的数量。此函数有助于确定query是否有意义（返回>0），或者是否仅包含停用词（返回0）。例如：SELECT numnode(plainto_tsquery('the any')); NOTICE: query contains only stopword(s) or doesn't contain lexeme(s), ignored numnode --------- 0 SELECT numnode('foo & bar'::tsquery); numnode --------- 3 -
querytree(query tsquery) returnstext返回可以用于搜索索引的
tsquery部分。此函数有助于检测无法索引的查询，例如仅包含停用词或仅包含否定词的查询。例如：SELECT querytree(to_tsquery('defined')); querytree ----------- 'defin' SELECT querytree(to_tsquery('!defined')); querytree ----------- T
2.4.2.1.查询重写
ts_rewrite函数族搜索给定的tsquery，查找目标子查询的出现次数，并用替代子查询替换每个出现次数。本质上，此操作是一个特定于tsquery的子字符串替换版本。目标和替代组合可以被视为一个查询重写规则。这样的重写规则集合可以成为一个强大的搜索辅助工具。例如，您可以使用同义词扩展搜索（例如，new york，bigapple，nyc，gotham），或缩小搜索范围以引导用户到一些热门话题。此功能的功能有些重叠。和同义词词典不同的是，您可以在不重新索引的情况下即时修改一组重写规则，而更新同义词需要重新索引才能生效。
-
ts_rewrite (query tsquery, target tsquery, substitute tsquery) returns tsquery这种形式的
ts_rewrite只是应用单个重写规则：target在query中的所有出现都将被替换为substitute。例如：SELECT ts_rewrite('a & b'::tsquery, 'a'::tsquery, 'c'::tsquery); ts_rewrite ------------ 'b' & 'c' -
ts_rewrite(query tsquery,selecttext) returnstsquery这种形式的
ts_rewrite接受一个起始query和一个SQLselect命令，该命令以文本字符串的形式给出。select必须产生两个tsquery类型的列。对于select结果的每一行，在当前query值中将替换第一列值（目标）的出现位置为第二列值（替代）。例如：CREATE TABLE aliases (t tsquery PRIMARY KEY, s tsquery); INSERT INTO aliases VALUES('a', 'c'); SELECT ts_rewrite('a & b'::tsquery, 'SELECT t,s FROM aliases'); ts_rewrite ------------ 'b' & 'c'请注意，当以这种方式应用多个重写规则时，应用顺序可能很重要； 因此，在实践中，您将希望源查询按某个排序键
ORDER BY。
让我们考虑一个现实生活中的天文学例子。 我们将使用表驱动的重写规则扩展查询supernovae：
CREATE TABLE aliases (t tsquery primary key, s tsquery);
INSERT INTO aliases VALUES(to_tsquery('supernovae'), to_tsquery('supernovae|sn'));
SELECT ts_rewrite(to_tsquery('supernovae & crab'), 'SELECT * FROM aliases');
ts_rewrite
---------------------------------
'crab' & ( 'supernova' | 'sn' )
我们可以通过更新表来更改重写规则：
UPDATE aliases
SET s = to_tsquery('supernovae|sn & !nebulae')
WHERE t = to_tsquery('supernovae');
SELECT ts_rewrite(to_tsquery('supernovae & crab'), 'SELECT * FROM aliases');
ts_rewrite
---------------------------------------------
'crab' & ( 'supernova' | 'sn' & !'nebula' )
当有许多重写规则时，重写可能会变慢，因为它会检查每个规则是否可能匹配。为了过滤掉明显的非候选规则，我们可以使用tsquery类型的包含运算符。在下面的示例中，我们仅选择可能与原始查询匹配的规则：
SELECT ts_rewrite('a & b'::tsquery,
'SELECT t,s FROM aliases WHERE ''a & b''::tsquery @> t');
ts_rewrite
------------
'b' & 'c'
2.4.3.自动更新的触发器
注意
本节中描述的方法已经被存储生成列的使用所取代，如表格和索引所述。
当使用单独的列来存储文档的tsvector表示时，需要创建一个触发器来在文档内容列更改时更新tsvector列。可以使用两个内置的触发器函数，也可以编写自己的函数。
tsvector_update_trigger(tsvector_column_name,config_name,text_column_name [, ... ])
tsvector_update_trigger_column(tsvector_column_name,config_column_name,text_column_name [, ... ])
这些触发器函数会根据CREATE TRIGGER命令中指定的参数，自动从一个或多个文本列计算出tsvector列。以下是它们的使用示例：
CREATE TABLE messages (
title text,
body text,
tsv tsvector
);
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON messages FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(tsv, 'ux_catalog.english', title, body);
INSERT INTO messages VALUES('title here', 'the body text is here');
SELECT * FROM messages;
title | body | tsv
------------+-----------------------+----------------------------
title here | the body text is here | 'bodi':4 'text':5 'titl':1
SELECT title, body FROM messages WHERE tsv @@ to_tsquery('title & body');
title | body
------------+-----------------------
title here | the body text is here
创建了这个触发器后，任何对title或body的更改都会自动反映到tsv中，应用程序不必担心它。
第一个触发器参数必须是要更新的tsvector列的名称。第二个参数指定要用于执行转换的文本搜索配置。对于tsvector_update_trigger，配置名称只需作为第二个触发器参数给出即可。必须像上面所示对其进行模式限定，以便触发器行为不会随着search_path的更改而改变。对于tsvector_update_trigger_column，第二个触发器参数是另一个表列的名称，该列必须是regconfig类型。这允许对每行选择配置。其余的参数是文本列的名称（类型为text、varchar或char）。它们将按给定的顺序包含在文档中。空值将被跳过（但其他列仍将被索引）。
这些内置触发器的一个限制是它们将所有输入列视为相同。要以不同的方式处理列，例如将标题与正文区别对待，需要编写自定义触发器。以下是使用PL/uxSQL作为触发器语言的示例：
CREATE FUNCTION messages_trigger() RETURNS trigger AS $$
begin
new.tsv :=
setweight(to_tsvector('ux_catalog.english', coalesce(new.title,'')), 'A') ||
setweight(to_tsvector('ux_catalog.english', coalesce(new.body,'')), 'D');
return new;
end
$$ LANGUAGE pluxsql;
CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
ON messages FOR EACH ROW EXECUTE FUNCTION messages_trigger();
请注意，在触发器中创建 tsvector 值时，明确指定配置名称非常重要，以便列的内容不会受到 default_text_search_config 更改的影响。未这样做可能会导致问题，例如转储和还原后搜索结果发生变化。
2.4.4.收集文档统计信息
函数 ts_stat 对于检查配置并查找停用词候选项非常有用。
ts_stat(sqlquery text, [ weights text, ]
OUT word text, OUT ndoc integer,
OUT nentry integer) returns setof record
sqlquery 是一个包含必须返回单个 tsvector 列的 SQL 查询的文本值。 ts_stat 执行查询并返回有关tsvector 数据中包含的每个不同词元（单词）的统计信息。返回的列为：
-
wordtext— 词元的值 -
ndocinteger— 词元出现在的文档（tsvector）数量 -
nentryinteger— 词元的总出现次数
如果提供了weights，则仅计算具有其中之一权重的出现次数。例如，要查找文档集中出现频率最高的十个单词。
例如，要查找文档集合中最常见的十个单词:
SELECT * FROM ts_stat('SELECT vector FROM apod')
ORDER BY nentry DESC, ndoc DESC, word
LIMIT 10;
相同，但只计算重量为 A 或 B 的单词出现次数:
SELECT * FROM ts_stat('SELECT vector FROM apod', 'ab')
ORDER BY nentry DESC, ndoc DESC, word
LIMIT 10;
2.5.解析器
文本搜索解析器负责将原始文档文本分割成标记，并识别每个标记的类型，其中可能的类型集由解析器本身定义。请注意，解析器不会修改文本，它只是识别可能的单词边界。由于这种有限的范围，与自定义字典相比，应用程序特定的自定义解析器的需求较少。目前，UXDB仅提供一个内置解析器，已被证明对各种应用程序都很有用。
内置解析器的名称为ux_catalog.default。它识别23种标记类型，如表 默认解析器的标记类型 所示。
表 默认解析器的标记类型
| 别名 | 描述 | 示例 |
|---|---|---|
asciiword |
单词，所有ASCII字母 | elephant |
word |
单词，所有字母 | mañana |
numword |
单词，字母和数字 | beta1 |
asciihword |
连字符单词，所有ASCII | up-to-date |
hword |
连字符单词，所有字母 | lógico-matemática |
numhword |
连字符单词，字母和数字 | uxsinodb-beta1 |
hword_asciipart |
连字符单词部分，所有ASCII | uxsinodb在上下文uxsinodb-beta1中 |
hword_part |
连字符单词部分，所有字母 | lógico或matemática在上下文中，lógico-matemática |
hword_numpart |
连字符词部分，字母和数字 | uxsinodb-beta1中的beta1 |
email |
电子邮件地址 | foo@example.com |
protocol |
协议头 | http:// |
url |
URL | example.com/stuff/index.md |
host |
主机 | example.com |
url_path |
URL路径 | /stuff/index.md，在URL的上下文中 |
file |
文件或路径名 | /usr/local/foo.txt，如果不在URL中 |
sfloat |
科学计数法 | -1.234e56 |
float |
十进制表示法 | -1.234 |
int |
有符号整数 | -1234 |
uint |
无符号整数 | 1234 |
version |
版本号 | 8.3.0 |
tag |
XML标记 | <a href="dictionaries.md"> |
entity |
XML实体 | & |
blank |
空格符号 | (任何未被识别的空格或标点符号) |
注意
解析器对“字母”的概念是由数据库的区域设置确定的，特别是lc_ctype。仅包含基本ASCII字母的单词被报告为单独的标记类型，因为有时区分它们是有用的。在大多数欧洲语言中，标记类型word和asciiword应该被视为相同。
email不支持RFC 5322定义的所有有效电子邮件字符。具体来说，电子邮件用户名仅支持句点、破折号和下划线的非字母数字字符。
解析器可以从同一段文本生成重叠的标记。例如，一个带连字符的单词将被报告为整个单词和每个组成部分:
SELECT alias, description, token FROM ts_debug('foo-bar-beta1');
alias | description | token
-----------------+------------------------------------------+---------------
numhword | Hyphenated word, letters and digits | foo-bar-beta1
hword_asciipart | Hyphenated word part, all ASCII | foo
blank | Space symbols | -
hword_asciipart | Hyphenated word part, all ASCII | bar
blank | Space symbols | -
hword_numpart | Hyphenated word part, letters and digits | beta1
这种行为是可取的，因为它允许搜索同时适用于整个复合词和组件。以下是另一个有启发性的例子：
SELECT alias, description, token FROM ts_debug('http://example.com/stuff/index.html');
alias | description | token
----------+---------------+------------------------------
protocol | Protocol head | http://
url | URL | example.com/stuff/index.html
host | Host | example.com
url_path | URL path | /stuff/index.html
2.6.字典
字典用于消除在搜索中不应考虑的单词（停用词），并对单词进行规范化，以使同一单词的不同派生形式匹配。成功规范化的单词称为词元。除了提高搜索质量外，规范化和去除停用词还可以减小文档的tsvector表示大小，从而提高性能。规范化并不总是具有语言意义，通常取决于应用程序语义。
一些规范化的例子：
-
语言学——Ispell 字典试图将输入单词缩减为规范化形式；词干字典删除单词结尾
-
URL位置可以进行规范化，使等效的URL匹配。
-
颜色名称可以用它们的十六进制值替换，例如：
red, green, blue,magenta -> FF0000,00FF00,0000FF,FF00FF。 -
如果索引数字，我们可以删除一些小数位以减少可能的数字范围，因此例如3.14159265359，3.1415926，3.14将在规范化后保留小数点后两位时相同。
字典是一个接受令牌作为输入并返回以下内容的程序：
-
如果字典知道输入令牌，则返回词元数组（请注意，一个令牌可能会产生多个词元）。
-
设置了
TSL_FILTER标志的单个词元，以替换原始令牌并传递给后续字典（执行此操作的字典称为过滤字典）。 -
如果字典知道令牌但它是停用词，则返回空数组。
-
如果字典无法识别输入令牌，则返回
NULL。
UXDB为许多语言提供预定义的字典。还有几个预定义的模板可用于创建具有自定义参数的新字典。下面描述了每个预定义字典模板。如果没有现有的模板适合，则可以创建新模板；请参阅UXDB分发中的contrib/区域以获取示例。
文本搜索配置将解析器与一组字典绑定在一起，以处理解析器的输出令牌。对于解析器可以返回的每个令牌类型，配置指定一个单独的字典列表。当解析器找到该类型的令牌时，依次查询列表中的每个字典，直到某个字典将其识别为已知单词。如果将其标识为停用词，或者如果没有字典识别令牌，则将丢弃它，不进行索引或搜索。通常，返回非NULL输出的第一个字典确定结果，并且不会查询任何剩余的字典；但是，过滤字典可以将给定单词替换为修改后的单词，然后将其传递给后续字典。
配置字典列表的一般规则是首先放置最窄、最具体的字典，然后是更一般的字典，最后是非常通用的字典，例如Snowball词干分析器或simple，它可以识别所有内容。例如，对于天文学特定搜索（astro_en配置），可以将令牌类型asciiword（ASCII单词）绑定到天文学术语的同义词字典、通用英语字典和英语Snowball词干分析器：
ALTER TEXT SEARCH CONFIGURATION astro_en
ADD MAPPING FOR asciiword WITH astrosyn, english_ispell, english_stem;
过滤字典可以放在列表中的任何位置，除非放在无用的末尾。过滤字典有助于部分规范化单词，以简化后续字典的工作。例如，可以使用筛选字典来从重音字母中删除重音，就像unweight模块所做的那样。
2.6.1.停用词
停用词是指在几乎每个文档中都出现且没有区分价值的常见词语。因此，在全文搜索的上下文中可以忽略它们。例如，每个英语文本都包含像“a”和“the”这样的单词，因此将它们存储在索引中是无用的。但是，停用词确实会影响到tsvector中的位置，进而影响排名。
SELECT to_tsvector('english', 'in the list of stop words');
to_tsvector
----------------------------
'list':3 'stop':5 'word':6
缺失的位置1、2、4是由于停用词。带有和不带有停用词的文档计算出的排名是非常不同的。
SELECT ts_rank_cd (to_tsvector('english', 'in the list of stop words'), to_tsquery('list & stop'));
ts_rank_cd
------------
0.05
SELECT ts_rank_cd (to_tsvector('english', 'list stop words'), to_tsquery('list & stop'));
ts_rank_cd
------------
0.1
具体的字典如何处理停用词取决于它。例如，ispell字典首先规范化单词，然后查看停用词列表，而Snowball词干分析器首先检查停用词列表。不同行为的原因是为了减少噪音。
2.6.2.简单词典
simple字典模板通过将输入标记转换为小写并将其与停用词文件进行比较来运作。如果在文件中找到它，则返回一个空数组，导致标记被丢弃。如果没有找到，则返回单词的小写形式作为规范化的词元。或者，可以将字典配置为将非停用词报告为未识别，从而允许它们传递到列表中的下一个字典。
下面是使用simple模板的字典定义示例：
CREATE TEXT SEARCH DICTIONARY public.simple_dict (
TEMPLATE = ux_catalog.simple,
STOPWORDS = english
);
这里，english是停用词文件的基本名称。文件的完整名称将是$SHAREDIR/tsearch_data/english.stop，其中$SHAREDIR表示UXDB安装的共享数据目录，通常为/usr/local/share/uxsinodb（如果不确定，请使用ux_config--sharedir来确定）。文件格式只是单词列表，每行一个。空行和尾随空格将被忽略，并且大写字母会折叠为小写字母，但不会对文件内容进行其他处理。
现在我们可以测试我们的字典：
SELECT ts_lexize('public.simple_dict', 'YeS');
ts_lexize
-----------
{yes}
SELECT ts_lexize('public.simple_dict', 'The');
ts_lexize
-----------
{}
我们还可以选择返回NULL，而不是小写的单词，如果它没有在停用词文件中找到。通过将字典的Accept参数设置为false来选择此行为。继续上面的示例：
ALTER TEXT SEARCH DICTIONARY public.simple_dict ( Accept = false );
SELECT ts_lexize('public.simple_dict', 'YeS');
ts_lexize
-----------
SELECT ts_lexize('public.simple_dict', 'The');
ts_lexize
-----------
{}
只有在字典列表的末尾放置一个 simple字典才有用，因为它永远不会将任何标记传递给后续的字典。相反，只有在至少有一个后续字典时，Accept= false 才有用。
注意
大多数类型的字典都依赖于配置文件，例如停用词文件。这些文件必须以UTF-8编码存储。当它们被读入服务器时，如果实际数据库编码不同，它们将被转换为实际的数据库编码。
通常，数据库会话只会在第一次在会话中使用时读取字典配置文件。如果您修改了配置文件并希望强制现有会话获取新内容，请对字典发出 ALTER TEXT SEARCH DICTIONARY命令。这可以是一个“虚拟”的更新，不会实际更改任何参数值。
2.6.3.同义词字典
此字典模板用于创建将单词替换为同义词的字典。不支持短语（使用同义词词典模板）。同义词字典可用于解决语言问题，例如防止英语词干字典将单词“Paris”缩减为“pari”。只需在同义词字典中放置一个Paris paris 行，并将其放在 english_stem 字典之前。例如：
SELECT * FROM ts_debug('english', 'Paris');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-----------------+-------+----------------+--------------+---------
asciiword | Word, all ASCII | Paris | {english_stem} | english_stem | {pari}
CREATE TEXT SEARCH DICTIONARY my_synonym (
TEMPLATE = synonym,
SYNONYMS = my_synonyms
);
ALTER TEXT SEARCH CONFIGURATION english
ALTER MAPPING FOR asciiword
WITH my_synonym, english_stem;
SELECT * FROM ts_debug('english', 'Paris');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-----------------+-------+---------------------------+------------+---------
asciiword | Word, all ASCII | Paris | {my_synonym,english_stem} | my_synonym | {paris}
synonym 模板所需的唯一参数是 SYNONYMS，它是其配置文件的基本名称，例如上面的示例中的my_synonyms。文件的完整名称将是$SHAREDIR/tsearch_data/my_synonyms.syn（其中 $SHAREDIR 表示UXDB安装的共享数据目录）。文件格式只是每个要替换的单词一行，单词后跟其同义词，用空格分隔。空行和尾随空格将被忽略。
synonym 模板还有一个可选参数 CaseSensitive，默认为 false。当 CaseSensitive 为false 时，同义词文件中的单词将折叠为小写字母，输入标记也是如此。当它为true时，单词和标记不会折叠为小写字母，而是按原样进行比较。
在配置文件中，可以在同义词的末尾放置一个星号（*），表示该同义词是一个前缀。当使用to_tsvector()时，星号将被忽略，但当使用to_tsquery()时，结果将是一个带有前缀匹配标记的查询项（参见解析查询）。例如，假设我们在$SHAREDIR/tsearch_data/synonym_sample.syn中有以下条目：
uxdb uxsql
UXDB uxsql
uxdb uxsql
gogle googl
indices index*
那么我们将得到以下结果：
mydb=# CREATE TEXT SEARCH DICTIONARY syn (template=synonym, synonyms='synonym_sample');
mydb=# SELECT ts_lexize('syn', 'indices');
ts_lexize
-----------
{index}
(1 row)
mydb=# CREATE TEXT SEARCH CONFIGURATION tst (copy=simple);
mydb=# ALTER TEXT SEARCH CONFIGURATION tst ALTER MAPPING FOR asciiword WITH syn;
mydb=# SELECT to_tsvector('tst', 'indices');
to_tsvector
-------------
'index':1
(1 row)
mydb=# SELECT to_tsquery('tst', 'indices');
to_tsquery
------------
'index':*
(1 row)
mydb=# SELECT 'indexes are very useful'::tsvector;
tsvector
---------------------------------
'are' 'indexes' 'useful' 'very'
(1 row)
mydb=# SELECT 'indexes are very useful'::tsvector @@ to_tsquery('tst', 'indices');
?column?
----------
t
(1 row)
2.6.4.同义词词典
一个词库字典（有时缩写为TZ）是一个包含有关单词和短语关系的信息的单词集合，即更广泛的术语（BT），更窄的术语（NT），首选术语，非首选术语，相关术语等。
基本上，词库字典通过一个首选术语替换所有非首选术语，并可选择保留原始术语进行索引。UXDB当前实现的词库字典是带有短语支持的同义词字典扩展。词库字典需要以下格式的配置文件：
# this is a comment
sample word(s) : indexed word(s)
more sample word(s) : more indexed word(s)
...
其中冒号（:）符号充当短语和其替换之间的分隔符。
词库字典使用一个子字典（在字典的配置中指定）来规范化输入文本，然后检查短语匹配。只能选择一个子字典。如果子字典无法识别单词，则会报告错误。在这种情况下，您应该删除该单词的使用或教子字典了解该单词。您可以在索引单词的开头放置一个星号（*），以跳过将子字典应用于它，但所有示例单词必须为子字典所知。如果有多个短语与输入匹配，则词库字典选择最长的匹配，并使用最后一个定义来解决平局。
子字典识别的特定停用词不能指定；而是使用?标记任何停用词可能出现的位置。例如，假设根据子字典，a和the是停用词：
? one ? two : swsw
匹配a one the two和the one a two；两者都将被替换为swsw。
由于同义词词典具有识别短语的能力，因此它必须记住其状态并与解析器交互。同义词词典使用这些分配来检查它是否应处理下一个单词或停止累积。同义词词典必须仔细配置。例如，如果同义词词典被分配为仅处理asciiword标记，则类似one 7的同义词词典定义将不起作用，因为标记类型uint未分配给同义词词典。
注意
同义词在索引期间使用，因此同义词词典参数的任何更改都需要重新索引。对于大多数其他字典类型，添加或删除停用词等小更改不会强制重新索引。
2.6.4.1.同义词表结构
要定义新的同义词词典，请使用thesaurus模板。例如：
CREATE TEXT SEARCH DICTIONARY thesaurus_simple (
TEMPLATE = thesaurus,
DictFile = mythesaurus,
Dictionary = ux_catalog.english_stem
);
这里：
-
thesaurus_simple是新字典的名称 -
mythesaurus是同义词配置文件的基本名称。（其完整名称将是$SHAREDIR/tsearch_data/mythesaurus.ths，其中$SHAREDIR表示安装的共享数据目录。） -
ux_catalog.english_stem是要用于同义词规范化的子词典（在这里，是Snowball英语词干提取器）。请注意，子词典将具有自己的配置（例如，停用词），这里没有显示。
现在可以将同义词词典thesaurus_simple绑定到配置中所需的标记类型，例如：
ALTER TEXT SEARCH CONFIGURATION russian
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart
WITH thesaurus_simple;
2.6.4.2.同义词表示例
考虑一个简单的天文同义词词典thesaurus_astro，其中包含一些天文词组：
supernovae stars : sn
crab nebulae : crab
下面我们创建一个字典，并将一些标记类型绑定到天文同义词词典和英语词干提取器：
CREATE TEXT SEARCH DICTIONARY thesaurus_astro (
TEMPLATE = thesaurus,
DictFile = thesaurus_astro,
Dictionary = english_stem
);
ALTER TEXT SEARCH CONFIGURATION russian
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart
WITH thesaurus_astro, english_stem;
现在我们可以看看它是如何工作的。ts_lexize对于测试同义词不是非常有用，因为它将其输入视为单个标记。相反，我们可以使用plainto_tsquery和to_tsvector，它们将将其输入字符串分成多个标记：
SELECT plainto_tsquery('supernova star');
plainto_tsquery
-----------------
'sn'
SELECT to_tsvector('supernova star');
to_tsvector
-------------
'sn':1
原则上，如果引用参数，则可以使用to_tsquery：
SELECT to_tsquery('''supernova star''');
to_tsquery
------------
'sn'
请注意，supernova star在thesaurus_astro中匹配supernovae stars，因为我们在词库定义中指定了english_stem词干提取器。词干提取器删除了e和s。要将原始短语以及替代短语索引，请将其包含在定义的右侧：
supernovae stars : sn supernovae stars
SELECT plainto_tsquery('supernova star');
plainto_tsquery
-----------------------------
'sn' & 'supernova' & 'star'
2.6.5.Ispell 字典
Ispell字典模板支持形态学字典，可以将一个单词的许多不同语言形式规范化为相同的词汇。例如，英语Ispell字典可以匹配搜索词bank的所有变格和变位形式，例如banking、banked、banks、banks'和bank's。
标准的UXDB分发版不包括任何Ispell配置文件。许多语言的字典可从Ispell获得。此外，还支持一些更现代的字典文件格式——MySpell（OO < 2.0.1）和Hunspell（OO >=2.0.2）。在OpenOffice Wiki上提供了大量的字典列表。
要创建一个Ispell字典，请执行以下步骤：
-
下载字典配置文件。OpenOffice扩展文件的扩展名为
.oxt。需要提取.aff和.dic文件，将扩展名更改为.affix和.dict。对于某些字典文件，还需要使用命令将字符转换为UTF-8编码（例如，对于挪威语字典）：iconv -f ISO_8859-1 -t UTF-8 -o nn_no.affix nn_NO.aff iconv -f ISO_8859-1 -t UTF-8 -o nn_no.dict nn_NO.dic -
将文件复制到
$SHAREDIR/tsearch_data目录中 -
使用以下命令将文件加载到UXDB中：
CREATE TEXT SEARCH DICTIONARY english_hunspell ( TEMPLATE = ispell, DictFile = en_us, AffFile = en_us, Stopwords = english);
这里，DictFile、AffFile和StopWords指定字典、词缀和停用词文件的基本名称。停用词文件具有与上面解释的相同格式。simple字典类型。其他文件的格式在此未指定，但可以从上述网站获取。
Ispell字典通常只能识别有限的单词集，因此应跟随另一个更广泛的字典；例如，一个识别所有单词的Snowball字典。
Ispell的.affix 文件具有以下结构：
prefixes
flag *A:
. > RE # As in enter > reenter
suffixes
flag T:
E > ST # As in late > latest
[^AEIOU]Y > -Y,IEST # As in dirty > dirtiest
[AEIOU]Y > EST # As in gray > grayest
[^EY] > EST # As in small > smallest
而 .dict 文件具有以下结构：
lapse/ADGRS
lard/DGRS
large/PRTY
lark/MRS
.dict 文件的格式如下：
basic_form/affix_class_name
在 .affix 文件中，每个词缀标志的描述格式如下：
condition > [-stripping_letters,] adding_affix
这里，条件的格式类似于正则表达式的格式。它可以使用分组 [...] 和 [^...]。例如，[AEIOU]Y表示单词的最后一个字母是 "y"，倒数第二个字母是"a"、"e"、"i"、"o" 或 "u"。 [^EY] 表示最后一个字母既不是 "e" 也不是"y"。
Ispell 字典支持拆分复合词；这是一个有用的功能。请注意，词缀文件应使用 compoundwords controlled语句指定一个特殊标志，该标志标记可以参与复合形成的字典单词：
compoundwords controlled z
以下是挪威语的一些示例：
SELECT ts_lexize('norwegian_ispell', 'overbuljongterningpakkmesterassistent');
{over,buljong,terning,pakk,mester,assistent}
SELECT ts_lexize('norwegian_ispell', 'sjokoladefabrikk');
{sjokoladefabrikk,sjokolade,fabrikk}
MySpell格式是Hunspell的子集。Hunspell的.affix文件具有以下结构：
PFX A Y 1
PFX A 0 re .
SFX T N 4
SFX T 0 st e
SFX T y iest [^aeiou]y
SFX T 0 est [aeiou]y
SFX T 0 est [^ey]
词缀类的第一行是标题。在标题之后列出词缀规则的字段：
-
参数名称（PFX 或 SFX）
-
标志（词缀类的名称）
-
从单词的开头（在前缀处）或结尾（在后缀处）剥离字符
-
添加词缀
-
条件的格式类似于正则表达式的格式。
.dict 文件看起来像 Ispell 的 .dict 文件：
larder/M
lardy/RT
large/RSPMYT
largehearted
注意
MySpell不支持复合词。亨斯佩尔对复合词有着复杂的支持。目前，UXDB只实现 Hunspell 的基本复合词操作。
2.6.6.Snowball词典
Snowball词典模板基于Martin Porter的一个项目，他是流行的英语Porter词干分析算法的发明者。Snowball现在对许多语言提供词干分析算法（参见Snowball站点）。每一个算法懂得按照其语言中的拼写，如何缩减词的常见变体形式为一个基础或词干。一个Snowball词典要求一个language参数来标识要用哪种词干分析器，并且可以选择地指定一个stopword文件名来给出一个要被消除的词列表（UXDB的标准停用词列表也是由Snowball项目提供的）。例如，有一个内建的定义等效于
CREATE TEXT SEARCH DICTIONARY english_stem (
TEMPLATE = snowball,
Language = english,
StopWords = english
);
停止词文件格式与前面解释的相同。
Snowball字典可以识别任何东西，无论它是否能够简化单词，所以它应该放在字典列表的末尾。在任何其他字典之前使用它是没有用的，因为标记永远不会通过它传递到下一个字典。
2.7.配置示例
文本搜索配置指定将文档转换为 tsvector 所需的所有选项：用于将文本分解为标记的解析器，以及用于将每个标记转换为词元的词典。每次调用to_tsvector 或 to_tsquery 都需要一个文本搜索配置来执行其处理。配置参数default_text_search_config指定默认配置的名称，如果省略显式配置参数，则使用该配置。它可以在 uxsinodb.conf 中设置，也可以使用 SET命令为单个会话设置。
有几个预定义的文本搜索配置可用，您可以轻松创建自定义配置。为了便于管理文本搜索对象，提供了一组 SQL 命令，并且有几个 uxsql命令显示有关文本搜索对象的信息（请参见uxsql支持 ）。
例如，我们将创建一个名为 ux 的配置，首先复制内置的 english配置：
CREATE TEXT SEARCH CONFIGURATION public.ux ( COPY = ux_catalog.english );
我们将使用一个UXDB特定的同义词列表，并将其存储在$SHAREDIR/tsearch_data/ux_dict.syn中。文件内容如下：
uxdb ux
uxsql ux
UXDB ux
我们像这样定义同义词词典：
CREATE TEXT SEARCH DICTIONARY ux_dict (
TEMPLATE = synonym,
SYNONYMS = ux_dict
);
接下来，我们注册 Ispell 字典 english_ispell，它有自己的配置文件：
CREATE TEXT SEARCH DICTIONARY english_ispell (
TEMPLATE = ispell,
DictFile = english,
AffFile = english,
StopWords = english
);
现在我们可以为配置中的单词设置映射了ux：
ALTER TEXT SEARCH CONFIGURATION ux
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart,
word, hword, hword_part
WITH ux_dict, english_ispell, english_stem;
我们选择不索引或搜索内置配置处理的某些标记类型：
ALTER TEXT SEARCH CONFIGURATION ux
DROP MAPPING FOR email, url, url_path, sfloat, float;
现在我们可以测试我们的配置：
SELECT * FROM ts_debug('public.ux', '
UXDB, the highly scalable, SQL compliant, open source object-relational
database management system, is now undergoing beta testing of the next
version of our software.
');
下一步是将会话设置为使用在 public 模式下创建的新配置：
=> \dF
List of text search configurations
Schema | Name | Description
---------+------+-------------
public | ux |
SET default_text_search_config = 'public.ux';
SET
SHOW default_text_search_config;
default_text_search_config
----------------------------
public.ux
2.8.测试和调试文本搜索
自定义文本搜索配置的行为很容易变得混乱。本节中描述的函数对于测试文本搜索对象非常有用。您可以测试完整的配置，也可以分别测试解析器和词典。
2.8.1.配置测试
函数ts_debug允许轻松测试文本搜索配置。
ts_debug([ config regconfig, ] document text,
OUT alias text,
OUT description text,
OUT token text,
OUT dictionaries regdictionary[],
OUT dictionary regdictionary,
OUT lexemes text[])
returns setof record
ts_debug显示由解析器生成并由配置的词典处理的document的每个标记的信息。它使用由config指定的配置，如果省略该参数，则使用default_text_search_config。
ts_debug为解析器在文本中识别的每个标记返回一行。返回的列为：
-
aliastext— 令牌类型的简称 -
descriptiontext— 令牌类型的描述 -
tokentext— 令牌的文本 -
dictionariesregdictionary[]— 配置为此令牌类型选择的字典 -
dictionaryregdictionary— 识别令牌的字典，如果没有则为NULL -
lexemestext[]—由识别令牌的字典产生的词元，如果没有则为NULL；空数组（{}）表示它被识别为停用词
这是一个简单的例子：
SELECT * FROM ts_debug('english', 'a fat cat sat on a mat - it ate a fat rats');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-----------------+-------+----------------+--------------+---------
asciiword | Word, all ASCII | a | {english_stem} | english_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | fat | {english_stem} | english_stem | {fat}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | cat | {english_stem} | english_stem | {cat}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | sat | {english_stem} | english_stem | {sat}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | on | {english_stem} | english_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | a | {english_stem} | english_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | mat | {english_stem} | english_stem | {mat}
blank | Space symbols | | {} | |
blank | Space symbols | - | {} | |
asciiword | Word, all ASCII | it | {english_stem} | english_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | ate | {english_stem} | english_stem | {ate}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | a | {english_stem} | english_stem | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | fat | {english_stem} | english_stem | {fat}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | rats | {english_stem} | english_stem | {rat}
为了进行更广泛的演示，我们首先创建一个英语语言的public.english配置和Ispell字典：
CREATE TEXT SEARCH CONFIGURATION public.english ( COPY = ux_catalog.english );
CREATE TEXT SEARCH DICTIONARY english_ispell (
TEMPLATE = ispell,
DictFile = english,
AffFile = english,
StopWords = english
);
ALTER TEXT SEARCH CONFIGURATION public.english
ALTER MAPPING FOR asciiword WITH english_ispell, english_stem;
SELECT * FROM ts_debug('public.english', 'The Brightest supernovaes');
alias | description | token | dictionaries | dictionary | lexemes
-----------+-----------------+-------------+-------------------------------+----------------+-------------
asciiword | Word, all ASCII | The | {english_ispell,english_stem} | english_ispell | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | Brightest | {english_ispell,english_stem} | english_ispell | {bright}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | supernovaes | {english_ispell,english_stem} | english_stem | {supernova}
在这个例子中，单词 Brightest 被解析器识别为一个 ASCII 单词（别名为 asciiword）。对于这种令牌类型，字典列表是english_ispell 和 english_stem。这个单词被 english_ispell 识别，将其缩减为名词bright。单词 supernovaes 在 english_ispell字典中是未知的，因此它被传递到下一个字典中，并且幸运的是，它被识别了（实际上，english_stem是一个 Snowball 字典，它可以识别任何东西；这就是为什么它被放在字典列表的末尾）。
单词 The 被 english_ispell 字典识别为停用词，因此不会被索引。空格也被丢弃了，因为配置没有为它们提供任何字典。
您可以通过明确指定要查看的列来缩小输出的宽度：
SELECT alias, token, dictionary, lexemes
FROM ts_debug('public.english', 'The Brightest supernovaes');
alias | token | dictionary | lexemes
-----------+-------------+----------------+-------------
asciiword | The | english_ispell | {}
blank | | |
asciiword | Brightest | english_ispell | {bright}
blank | | |
asciiword | supernovaes | english_stem | {supernova}
2.8.2.解析器测试
以下函数允许直接测试文本搜索解析器。
ts_parse(parser_name text, document text,
OUT tokid integer, OUT token text) returns setof record
ts_parse(parser_oid oid, document text,
OUT tokid integer, OUT token text) returns setof record
ts_parse 解析给定的 document 并返回一系列记录，每个记录都包括一个 tokid，显示分配的令牌类型，以及一个token，它是令牌的文本。例如：
SELECT * FROM ts_parse('default', '123 - a number');
tokid | token
-------+--------
22 | 123
12 |
12 | -
1 | a
12 |
1 | number
ts_token_type(parser_name text, OUT tokid integer,
OUT alias text, OUT description text) returns setof record
ts_token_type(parser_oid oid, OUT tokid integer,
OUT alias text, OUT description text) returns setof record
ts_token_type返回一个表格，描述了指定解析器可以识别的每种类型的标记。对于每种标记类型，表格给出解析器用于标记该类型标记的整数tokid，在配置命令中命名标记类型的alias，以及一个简短的description。例如：
SELECT * FROM ts_token_type('default');
tokid | alias | description
-------+-----------------+------------------------------------------
1 | asciiword | Word, all ASCII
2 | word | Word, all letters
3 | numword | Word, letters and digits
4 | email | Email address
5 | url | URL
6 | host | Host
7 | sfloat | Scientific notation
8 | version | Version number
9 | hword_numpart | Hyphenated word part, letters and digits
10 | hword_part | Hyphenated word part, all letters
11 | hword_asciipart | Hyphenated word part, all ASCII
12 | blank | Space symbols
13 | tag | XML tag
14 | protocol | Protocol head
15 | numhword | Hyphenated word, letters and digits
16 | asciihword | Hyphenated word, all ASCII
17 | hword | Hyphenated word, all letters
18 | url_path | URL path
19 | file | File or path name
20 | float | Decimal notation
21 | int | Signed integer
22 | uint | Unsigned integer
23 | entity | XML entity
2.8.3.字典测试
ts_lexize函数方便了字典测试。
ts_lexize(dict regdictionary, token text) returns text[]
如果输入的token在字典中已知，则ts_lexize返回一个词元数组，如果该词元是停用词，则返回一个空数组，如果它是未知词，则返回NULL。
例如：
SELECT ts_lexize('english_stem', 'stars');
ts_lexize
-----------
{star}
SELECT ts_lexize('english_stem', 'a');
ts_lexize
-----------
{}
注意
ts_lexize 函数期望一个单一的token，而不是文本。这里有一个可能会让人困惑的例子：
SELECT ts_lexize('thesaurus_astro', 'supernovae stars') is null;
?column?
----------
t
词典thesaurus_astro知道短语supernovae stars，但ts_lexize失败了，因为它没有解析输入文本，而是将其视为单个标记。使用plainto_tsquery或to_tsvector来测试词典，例如：
SELECT plainto_tsquery('supernovae stars');
plainto_tsquery
-----------------
'sn'
2.9.全文搜索首选索引类型
有两种索引可以用于加速全文搜索：GIN和GiST。请注意，索引对于全文搜索并非强制要求，但在定期搜索某个列的情况下，索引通常是必要的。
要创建这样的索引，请执行以下操作之一：
-
CREATE INDEX nameONtableUSING GIN (column);创建基于 GIN（广义反向索引）的索引。
column必须是tsvector类型。 -
CREATE INDEX nameONtableUSING GIST (column[ {DEFAULT | tsvector_ops } (siglen =number) ] );创建基于 GiST（广义搜索树）的索引。
column可以是tsvector或tsquery类型。可选的整数参数siglen确定签名长度（以字节为单位）（有关详细信息，请参见下文）。
GIN索引是首选的全文搜索索引类型。作为反向索引，它们包含每个单词（词元）的索引条目，以及匹配位置的压缩列表。多词搜索可以找到第一个匹配项，然后使用索引删除缺少其他单词的行。GIN索引仅存储 tsvector 值的单词（词元），而不存储它们的权重标签。因此，在使用涉及权重的查询时，需要重新检查表行。
GiST 索引是有损的，这意味着索引可能会丢失一些信息，但它们可以支持更多的操作符和查询类型。GiST 索引可以存储 tsvector 或tsquery 值，但是它们不会存储权重标签。GiST 索引可以使用不同的操作符类，以支持更多的查询类型。GiST索引还支持一些其他操作符，例如 &&（相交）和 %（相似度）。
在大多数情况下，GIN索引是更好的选择，因为它们更快，更节省空间，而且通常可以满足大多数需求。但是，如果您需要更多的操作符或查询类型，或者需要更好的查询性能，则可以考虑使用GiST 索引。
可能会产生错误匹配，因此需要检查实际的表行以消除这些错误匹配。（UXDB 在需要时会自动执行此操作。）GiST索引是有损的，因为每个文档都由固定长度的签名表示。签名长度（以字节为单位）由可选的整数参数siglen 的值确定。默认签名长度（当未指定 siglen 时）为 124 字节，最大签名长度为 2024字节。签名是通过将每个单词哈希成 n 位字符串中的单个位，然后将所有这些位 OR起来生成 n位文档签名。当两个单词哈希到相同的位位置时，会出现错误匹配。如果查询中的所有单词都有匹配（真实或错误），则必须检索表行以查看匹配是否正确。较长的签名会导更精确的搜索（扫描更小的索引分数和更少的堆页面），但代价是更大的索引。
GiST 索引可以是覆盖索引，即使用 INCLUDE 子句。包含的列可以具有没有任何 GiST操作符类的数据类型。包含的属性将以未压缩的形式存储。
由于不必要地获取表记录会导致性能下降，因此限制了 GiST索引的实用性。错误匹配的可能性取决于几个因素，特别是唯一单词的数量，因此建议使用字典来减少此数量。
请注意，通过增加maintenance_work_mem可以经常提高 GIN 索引的构建时间，而 GiST 索引的构建时间不受该参数的影响。
大型集合的分区和正确使用 GIN 和 GiST索引允许实现非常快速的搜索和在线更新。可以使用表继承在数据库级别进行分区，也可以通过将文档分布在服务器上并收集外部搜索结果来进行分区。后者是可能的，因为排名函数仅使用本地信息。
2.10.uxsql支持
可以使用一组命令在 uxsql 中获取有关文本搜索配置对象的信息：
\dF{d,p,t}[+] [PATTERN]
可选的 + 会产生更多细节。
可选参数 PATTERN 可以是文本搜索对象的名称，可选地带有模式。如果省略了PATTERN，则会显示所有可见对象的信息。PATTERN可以是正则表达式，并且可以为模式和对象名称提供单独的模式。以下示例说明了这一点：
=> \dF *fulltext*
List of text search configurations
Schema | Name | Description
--------+--------------+-------------
public | fulltext_cfg |
=> \dF *.fulltext*
List of text search configurations
Schema | Name | Description
----------+----------------------------
fulltext | fulltext_cfg |
public | fulltext_cfg |
可用的命令包括：
-
\dF[+] [PATTERN]列出文本搜索配置（添加
+以获取更多详细信息）。=> \dF russian List of text search configurations Schema | Name | Description ------------+---------+------------------------------------ ux_catalog | russian | configuration for russian language => \dF+ russian Text search configuration "ux_catalog.russian" Parser: "ux_catalog.default" Token | Dictionaries -----------------+-------------- asciihword | english_stem asciiword | english_stem email | simple file | simple float | simple host | simple hword | russian_stem hword_asciipart | english_stem hword_numpart | simple hword_part | russian_stem int | simple numhword | simple numword | simple sfloat | simple uint | simple url | simple url_path | simple version | simple word | russian_stem -
\dFd[+] [PATTERN]列出文本搜索字典（添加
+以获取更多详细信息）。=> \dFd List of text search dictionaries Schema | Name | Description ------------+-----------------+----------------------------------------------------------- ux_catalog | arabic_stem | snowball stemmer for arabic language ux_catalog | armenian_stem | snowball stemmer for armenian language ux_catalog | basque_stem | snowball stemmer for basque language ux_catalog | catalan_stem | snowball stemmer for catalan language ux_catalog | danish_stem | snowball stemmer for danish language ux_catalog | dutch_stem | snowball stemmer for dutch language ux_catalog | english_stem | snowball stemmer for english language ux_catalog | finnish_stem | snowball stemmer for finnish language ux_catalog | french_stem | snowball stemmer for french language ux_catalog | german_stem | snowball stemmer for german language ux_catalog | greek_stem | snowball stemmer for greek language ux_catalog | hindi_stem | snowball stemmer for hindi language ux_catalog | hungarian_stem | snowball stemmer for hungarian language ux_catalog | indonesian_stem | snowball stemmer for indonesian language ux_catalog | irish_stem | snowball stemmer for irish language ux_catalog | italian_stem | snowball stemmer for italian language ux_catalog | lithuanian_stem | snowball stemmer for lithuanian language ux_catalog | nepali_stem | snowball stemmer for nepali language ux_catalog | norwegian_stem | snowball stemmer for norwegian language ux_catalog | portuguese_stem | snowball stemmer for portuguese language ux_catalog | romanian_stem | snowball stemmer for romanian language ux_catalog | russian_stem | snowball stemmer for russian language ux_catalog | serbian_stem | snowball stemmer for serbian language ux_catalog | simple | simple dictionary: just lower case and check for stopword ux_catalog | spanish_stem | snowball stemmer for spanish language ux_catalog | swedish_stem | snowball stemmer for swedish language ux_catalog | tamil_stem | snowball stemmer for tamil language ux_catalog | turkish_stem | snowball stemmer for turkish language ux_catalog | yiddish_stem | snowball stemmer for yiddish language -
\dFp[+] [PATTERN]列出文本搜索解析器（添加
+以获取更多详细信息）。=> \dFp List of text search parsers Schema | Name | Description ------------+---------+--------------------- ux_catalog | default | default word parser => \dFp+ Text search parser "ux_catalog.default" Method | Function | Description -----------------+----------------+------------- Start parse | prsd_start | Get next token | prsd_nexttoken | End parse | prsd_end | Get headline | prsd_headline | Get token types | prsd_lextype | Token types for parser "ux_catalog.default" Token name | Description -----------------+------------------------------------------ asciihword | Hyphenated word, all ASCII asciiword | Word, all ASCII blank | Space symbols email | Email address entity | XML entity file | File or path name float | Decimal notation host | Host hword | Hyphenated word, all letters hword_asciipart | Hyphenated word part, all ASCII hword_numpart | Hyphenated word part, letters and digits hword_part | Hyphenated word part, all letters int | Signed integer numhword | Hyphenated word, letters and digits numword | Word, letters and digits protocol | Protocol head sfloat | Scientific notation tag | XML tag uint | Unsigned integer url | URL url_path | URL path version | Version number word | Word, all letters (23 rows) -
\dFt [ + ] [ PATTERN ]列出文本搜索模板（添加
+以获取更多详细信息）。=> \dFt List of text search templates Schema | Name | Description ------------+-----------+----------------------------------------------------------- ux_catalog | ispell | ispell dictionary ux_catalog | simple | simple dictionary: just lower case and check for stopword ux_catalog | snowball | snowball stemmer ux_catalog | synonym | synonym dictionary: replace word by its synonym ux_catalog | thesaurus | thesaurus dictionary: phrase by phrase substitution
2.11.限制
UXDB 的全文搜索功能目前存在以下限制：
-
每个词元的长度必须小于 2 千字节
-
tsvector（词元+位置）的长度必须小于 1 兆字节 -
词元数量必须小于 264
-
tsvector中的位置值必须大于 0 且不超过 16,383 -
<N>（后跟）tsquery运算符中的匹配距离不能超过 16,384 -
每个词元最多有 256 个位置
-
tsquery中的节点（词元+运算符）数量必须小于 32,768
作为比较，UXDB 8.1 文档中包含了 10,441 个唯一单词，共 335,420个单词，最常见的单词“UXDB”在 655 个文档中被提及了 6,127 次。
另一个例子是，UXDB 邮件列表档案中包含了 910,989 个唯一单词，共有 57,491,343 个词元，分布在 461,020条消息中。
3.并发控制
本章描述了当两个或多个会话同时尝试访问相同数据时，UXDB数据库系统的行为。在这种情况下，目标是允许所有会话进行高效访问，同时保持严格的数据完整性。每个数据库应用程序的开发人员都应熟悉本章涵盖的主题。在这一章节中。
3.1.介绍
UXDB 提供了一套丰富的工具，用于管理并发访问数据。在内部，使用多版本模型（Multiversion Concurrency Control，MVCC）来维护数据的一致性。这意味着每个 SQL语句都会看到数据的一个快照（一个数据库版本），而不管底层数据的当前状态如何。这可以防止语句查看由并发事务对相同数据行执行更新产生的不一致数据，为每个数据库会话提供事务隔离。MVCC通过避免传统数据库系统的锁定方法，最小化锁争用，以便在多用户环境中实现合理的性能。
使用 MVCC 并发控制模型而不是锁定的主要优点是，在 MVCC中，用于查询（读取）数据的锁定不会与用于写入数据的锁定发生冲突，因此读取永远不会阻塞写入，写入永远不会阻塞读取。即使通过使用创新的Serializable Snapshot Isolation（SSI）级别提供最严格的事务隔离，UXDB 也会保持这个保证。
在 UXDB 中还提供了表级和行级锁定功能，供那些通常不需要完全事务隔离并且更喜欢显式管理特定冲突点的应用程序使用。然而，正确使用 MVCC 通常会比锁定提供更好的性能。此外，应用程序定义的咨询锁提供了一种获取不与单个事务绑定的锁定的机制。
3.2.事务隔离级别
SQL标准定义了四个事务隔离级别。最严格的是可串行化隔离级别，标准在一段话中定义了这个级别，该级别保证了一组可串行化事务的并发执行将产生与按某种顺序逐个运行它们相同的效果。其他三个级别是根据现象定义的，这些现象是由并发事务之间的交互引起的，每个级别都不能发生这些现象。标准指出，由于可串行化的定义，这些现象在该级别下都是不可能发生的。（这并不奇怪——如果事务的效果必须与逐个运行它们的效果一致，那么你怎么能看到由交互引起的现象呢？）
在各个级别中禁止的现象包括：
-
脏读
一个事务读取了一个并发未提交事务写入的数据。
-
不可重复读
一个事务重新读取它先前读取的数据，并发现数据已被另一个已提交的事务修改（自初始读取以来）。
-
幻读
一个事务重新执行一个返回满足搜索条件的一组行的查询，并发现满足条件的行集由于另一个最近提交的事务而发生了更改。
-
串行化异常
成功提交一组事务的结果与逐个运行这些事务的所有可能顺序不一致。
SQL标准和UXDB实现的事务隔离级别在表 事务隔离级别 中描述。
表 事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 序列化异常 |
|---|---|---|---|---|
| 读未提交 | 允许，但不在ux中 | 可能 | 可能 | 可能 |
| 读已提交 | 不可能 | 可能 | 可能 | 可能 |
| 可重复读 | 不可能 | 不可能 | 允许，但不在ux中 | 可能 |
| 可序列化 | 不可能 | 不可能 | 不可能 | 不可能 |
在UXDB中，您可以请求任何四个标准事务隔离级别，但内部只实现了三个不同的隔离级别，即UXDB的读未提交模式的行为类似于读提交。这是因为将标准隔离级别映射到UXDB的多版本并发控制架构的唯一明智方法。
该表还显示UXDB的可重复读实现不允许幻读。这在SQL标准下是可以接受的，因为标准指定在某些隔离级别下不得发生哪些异常；更高的保证是可以接受的。可用隔离级别的行为在以下子部分中详细说明。
要设置事务的事务隔离级别，请使用SET TRANSACTION命令。
重要
一些UXDB数据类型和函数具有特殊的事务行为规则。特别是，对序列的更改（因此是使用serial声明的列的计数器）立即对所有其他事务可见，并且如果进行更改的事务中止，则不会回滚。
3.2.1.读提交隔离级别
读提交是UXDB的默认隔离级别。当事务使用此隔离级别时，SELECT查询（不带FOR UPDATE /SHARE子句）仅查看查询开始之前提交的数据；它永远不会看到未提交的数据或由并发事务在查询执行期间提交的更改。实际上，SELECT查询看到的是数据库的快照，就像查询开始运行时一样。但是，SELECT确实看到其自己事务中执行的先前更新的影响，即使它们尚未提交。还要注意，连续的两个SELECT命令可能会看到不同的数据，即使它们是在同一个事务中执行的。尽管它们在单个事务中，但如果其他事务在第一个SELECT 开始之后并在第二个 SELECT 开始之前提交更改，则会出现问题。
UPDATE、DELETE、SELECT FOR UPDATE 和 SELECT FOR SHARE 命令在搜索目标行方面与 SELECT行为相同：它们只会找到作为命令开始时间提交的目标行。然而，这样的目标行可能已经被另一个并发事务更新（或删除或锁定）。在这种情况下，想要更新的事务将等待第一个更新事务提交或回滚（如果它仍在进行中）。如果第一个更新者回滚，则其效果将被取消，第二个更新者可以继续更新最初找到的行。如果第一个更新者提交，则第二个更新者将忽略该行，如果第一个更新者删除了该行，则第二个更新者将尝试将其操作应用于该行的更新版本。
命令的搜索条件（WHERE子句）将被重新评估，以查看更新版本的行是否仍符合搜索条件。如果是，则第二个更新者将使用更新版本的行继续进行其操作。对于 SELECT FOR UPDATE 和 SELECT FOR SHARE，这意味着被锁定并返回给客户端的是更新版本的行。
带有 ON CONFLICT DO UPDATE 子句的 INSERT行为类似。在读取提交模式下，每个要插入的行都将插入或更新。除非存在无关的错误，否则两种结果之一是有保证的。如果冲突源自另一个事务，其效果对于INSERT 快照尚不可见，则 UPDATE 子句将影响该行，即使该行的任何版本都不是常规命令可见的。 带有 ON CONFLICT DO NOTHING 子句的 INSERT 可能由于另一个事务的结果而不进行行插入，而该结果对于 INSERT 快照不可见。同样，这只在读取提交模式下才会发生。 MERGE 允许用户指定各种INSERT、UPDATE 和 DELETE 子命令的组合。
带有 INSERT 和 UPDATE 子命令的 MERGE命令类似于带有 ON CONFLICT DO UPDATE 子句的 INSERT，但不能保证会发生 INSERT 或UPDATE。
如果 MERGE 尝试进行 UPDATE 或DELETE，并且该行同时被更新，但联接条件仍然通过当前目标和当前源元组，则MERGE 将像 UPDATE 或 DELETE 命令一样，在更新版本的行上执行其操作。然而，由于 MERGE可以指定多个操作并且它们可以是有条件的，因此每个操作的条件都将在更新版本的行上重新评估，从第一个操作开始，即使最初匹配的操作出现在操作列表的后面。另一方面，如果该行同时被更新或删除，则如果连接条件失败，
那么MERGE将会接下来评估条件的NOT MATCHED操作，并执行第一个成功的操作。如果MERGE尝试进行INSERT，并且存在唯一索引并且同时插入了重复的行，则会引发唯一性冲突错误；MERGE不会尝试通过重新启动MATCHED条件的评估来避免这些错误。由于上述规则，更新命令可能会看到不一致的快照：它可以看到对同一行正在尝试更新的并发更新命令的影响，但它不会看到这些命令对数据库中其他行的影响。这种行为使得读取提交模式不适用于涉及复杂搜索条件的命令；但对于简单情况来说，它是恰当的。例如，考虑使用类似以下的交易更新银行余额：
BEGIN;
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 12345;
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 7534;
COMMIT;
如果两个这样的交易同时尝试更改账户12345的余额，我们显然希望第二个交易从更新后的账户行开始。因为每个命令只影响一个预定的行，让它看到行的更新版本不会创建任何麻烦的不一致性。
更复杂的用法可能会在读取提交模式下产生不良结果。例如，考虑一个DELETE命令，它对正在被另一个命令添加和删除其限制条件的数据进行操作，例如，假设website是一个具有website.hits等于9和10的两行表：
BEGIN;
UPDATE website SET hits = hits + 1;
-- run from another session: DELETE FROM website WHERE hits = 10;
COMMIT;
DELETE将没有任何效果，即使在UPDATE之前和之后都存在website.hits= 10行。这是因为跳过了更新前的行值9，当UPDATE完成并且DELETE获得锁时，新的行值不再是10，而是11，它不再匹配条件。
由于读取提交模式在每个命令开始时都使用包括所有已提交事务的新快照，因此同一事务中的后续命令无论如何都会看到已提交并发事务的影响。上述问题的关键在于单个命令是否看到数据库的绝对一致视图。
读取提交模式提供的部分事务隔离对许多应用程序来说是足够的，而且这种模式使用起来快速简单；但它并不足以满足所有情况。进行复杂查询和更新的应用程序可能需要比读取提交模式提供的更严格一致的数据库视图。
3.2.2.可重复读隔离级别
可重复读隔离级别只看到事务开始前提交的数据；它既不会看到未提交的数据，也不会看到并发事务在事务执行期间提交的更改。（但是，查询会看到在其自己的事务中执行的先前更新的影响，如果连接条件失败，那么MERGE将会接下来评估条件的NOT MATCHED操作，并执行第一个成功的操作。这比SQL标准对于此隔离级别所需的保证更强，可以防止表 事务隔离级别 中描述的所有现象，除了序列化异常。如上所述，这是标准明确允许的，标准仅描述每个隔离级别必须提供的最小保护措施。
这个级别与读取提交不同，因为在可重复读事务中的查询看到的是快照，即从事务中的第一个非事务控制语句开始，而不是从事务中当前语句的开始。因此，在单个事务中的连续SELECT命令看到的是相同的数据，即它们不会看到在它们自己的事务开始后提交的其他事务所做的更改。
使用此级别的应用程序必须准备好由于序列化故障而重试事务。
UPDATE、DELETE、MERGE、SELECT FOR UPDATE和SELECT FOR SHARE命令在搜索目标行方面的行为与SELECT相同：它们只会找到作为事务开始时间提交的目标行。但是，这样的目标行可能已经被另一个并发事务更新（或删除或锁定）在找到它时。在这种情况下，可重复读事务将等待第一个更新事务提交或回滚（如果它仍在进行中）。如果第一个更新者回滚，则其效果将被抵消，可重复读事务可以继续更新最初找到的行。但是，如果第一个更新者提交了（并实际上更新或删除了该行，而不仅仅是锁定了它），则可重复读事务将回滚，并显示以下消息：
ERROR: could not serialize access due to concurrent update
因为可重复读事务不能修改或锁定在可重复读事务开始后由其他事务更改的行。
当应用程序收到此错误消息时，应中止当前事务并从头重试整个事务。第二次通过，事务将看到先前提交的更改作为其对数据库的初始视图的一部分，因此在使用新版本的行作为新事务更新的起点时，不存在逻辑冲突。
请注意，只有更新事务可能需要重试；只读事务永远不会有序列化冲突。
可重复读模式提供了一个严格的保证，即每个事务都看到数据库的完全稳定的视图。但是，这个视图不一定总是与同一级别的并发事务的某些串行（一次一个）执行一致。例如，即使在此级别的只读事务中，也可能会看到控制记录更新以显示批处理已完成，但不会看到逻辑上属于批处理的详细记录之一，因为它读取了控制记录的早期版本。在此隔离级别运行的事务尝试通过使用显式锁定阻止冲突事务来强制执行业务规则可能不会正确工作。
传统的锁定技术会降低并发性能。其他一些系统甚至将可重复读和快照隔离作为不同的隔离级别，具有不同的行为。区分这两种技术所允许的现象直到 SQL标准开发之后才被数据库研究人员正式规范化，并且超出了本手册的范围。
注意
请求可串行化事务隔离级别提供了与此处描述的完全相同的行为。为保留传统的可串行化行为，现在应请求可重复读。
3.2.3.可串行化隔离级别
可串行化隔离级别提供了最严格的事务隔离。该级别模拟了所有已提交事务的串行事务执行；就像事务已经一个接一个地串行执行，而不是并发执行一样。然而，与可重复读级别一样，使用此级别的应用程序必须准备好由于序列化失败而重试事务。实际上，此隔离级别与可重复读完全相同，只是它还监视可能使一组并发的可串行化事务的执行行为与这些事务的所有可能的串行（一次一个）执行不一致的条件。此监视不会引入除可重复读之外的任何阻塞，但是监视会带来一些开销，并且检测可能导致序列化异常的条件将触发序列化失败。
例如，考虑一个初始包含以下内容的表 mytab：
class | value
-------+-------
1 | 10
1 | 20
2 | 100
2 | 200
假设可串行化事务 A 计算：
SELECT SUM(value) FROM mytab WHERE class = 1;
然后将结果（30）作为新行的 value 插入到 class = 2 中。同时，可串行化事务 B 计算：
SELECT SUM(value)FROM mytab WHERE class = 2;
并获得结果 300，将其插入到 class = 1的新行中。然后两个事务都尝试提交。如果任一事务运行在可重复读隔离级别下，则两个事务都将被允许提交；但由于没有与结果一致的串行执行顺序，使用可串行化事务将允许一个事务提交，并将另一个事务回滚，并显示以下消息：
ERROR: could not serialize access due to read/write dependencies amongtransactions
这是因为如果 A 在 B 之前执行，B 将计算出 330 而不是 300，反之亦然。
当依靠可串行化事务来防止异常时，重要的是任何从永久用户表中读取的数据在读取它的事务成功提交之前都不被认为是有效的。即使是只读事务也是如此，除了在可延迟的只读事务中读取的数据被认为是在读取时有效，因为这样的事务会等待直到它可以获取一个快照，保证是免费的。在读取任何数据之前，应用程序必须先解决这些问题。在所有其他情况下，应用程序不应依赖于在稍后中止的事务期间读取的结果；相反，它们应该重试事务，直到成功为止。
为了保证真正的串行化，UXDB使用谓词锁定，这意味着它保留锁定，允许它确定写入是否会对并发事务中先前读取的结果产生影响。在UXDB中，这些锁定不会导致任何阻塞，因此不能在导致死锁方面起任何作用。它们用于识别和标识并发可串行化事务之间的依赖关系，这些依赖关系在某些组合中可能会导致串行化异常。相比之下，想要确保数据一致性的Read Committed或Repeatable Read事务可能需要锁定整个表，这可能会阻止其他尝试使用该表的用户，或者它可能使用SELECT FORUPDATE或SELECT FOR SHARE，这不仅可以阻止其他事务，还会导致磁盘访问。
UXDB中的谓词锁，像大多数其他数据库系统一样，基于事务实际访问的数据。这些将显示在ux_locks系统视图中，其模式为SIReadLock。在执行查询期间获取的特定锁将取决于查询使用的计划，并且在事务过程中可以将多个更细粒度的锁（例如元组锁）合并为较少的更粗粒度的锁（例如页面锁），以防止耗尽用于跟踪锁的内存。如果检测到不再可能发生可能导致串行化异常的冲突，READ ONLY事务可能能够在完成之前释放其SIRead锁。实际上，READ ONLY事务通常能够在启动时建立该事实并避免获取任何谓词锁。如果您明确请求SERIALIZABLE READ ONLY DEFERRABLE事务，则它将阻塞，直到它能够建立这个事实。（这是Serializable事务阻塞而Repeatable Read事务不阻塞的唯一情况。）另一方面，SIRead锁通常需要保留过事务提交，直到重叠的读写事务完成。
一致使用Serializable事务可以简化开发。成功提交的任何一组并发Serializable事务将具有与逐个运行它们相同的效果的保证，这意味着如果您可以证明单个事务在单独运行时会做正确的事情，那么您可以确信它将在任何Serializable事务混合中做正确的事情，即使没有关于那些其他事务可能做什么的任何信息，或者它将不会成功提交。由于很难预测哪些事务可能会导致读/写依赖关系并需要回滚以防止串行化异常，因此使用此技术的环境必须具有处理串行化失败的广义方法（始终返回SQLSTATE值为“40001”）。读/写依赖关系的监视具有成本，以及由于使用显式锁定和SELECT FOR UPDATE或SELECT FOR SHARE而涉及的成本和阻塞，Serializable事务是某些环境的最佳性能选择。
UXDB的Serializable事务隔离级别只有在能够证明存在一种串行执行顺序会产生相同效果时才允许并发事务提交，但它并不总是能够防止在真正的串行执行中不会出现的错误被引发。特别是，即使在明确检查键不存在之后再尝试插入，也可能会因与重叠的Serializable事务冲突而导致唯一约束违规。可以通过确保所有插入可能冲突的键的Serializable事务首先明确检查是否可以这样做来避免这种情况。例如，想象一个应用程序要求用户提供一个新键，然后通过首先尝试选择它来检查它是否已经存在，或者通过选择最大现有键并添加一个来生成一个新键。如果一些Serializable事务直接插入新键而不遵循此协议，则可能会报告唯一约束违规，即使在并发事务的串行执行中也不可能发生这种情况。
为了依赖Serializable事务进行并发控制时获得最佳性能，应考虑以下问题：
-
将可能的事务声明为
READ ONLY。 -
控制活动连接的数量，如有必要，使用连接池。这始终是一个重要的性能考虑因素，但在使用Serializable事务的繁忙系统中尤为重要。
-
不要将更多内容放入单个事务中，除了完整性目的外。不要让连接“空闲在事务中”比必要的时间更长。可以使用配置参数idle_in_transaction_session_timeout自动断开闲置会话。
-
消除显式锁定、
SELECT FOR UPDATE和SELECT FOR SHARE，因为Serializable事务自动提供了保护。 -
当系统被迫将多个页面级谓词锁合并为单个关系级谓词锁时，可能会导致序列化故障率增加。可以通过增加max_pred_locks_per_transaction、max_pred_locks_per_relation和/或max_pred_locks_per_page来避免这种情况。
-
顺序扫描总是需要关系级谓词锁。这可能会导致序列化故障率增加。通过减少random_page_cost和/或增加cpu_tuple_cost来鼓励使用索引扫描可能会有所帮助。一定要权衡事务回滚和重启的减少与查询执行时间的整体变化。
Serializable隔离级别使用一种称为Serializable Snapshot Isolation的技术实现，该技术在Snapshot Isolation的基础上添加了序列化异常检查。与比较时可能会观察到一些行为和性能上的差异。
3.3.显式锁定
UXDB 提供了各种锁定模式，以控制对表中数据的并发访问。这些模式可用于应用程序控制的锁定，在 MVCC不能提供所需行为的情况下使用。此外，大多数 UXDB命令会自动获取适当模式的锁定，以确保在命令执行时不会删除或以不兼容的方式修改引用的表。例如，TRUNCATE不能与同一表上的其他操作并发执行，因此它会获取表的 ACCESS EXCLUSIVE 锁定来强制执行。
要查看数据库服务器中当前未完成的锁定列表，请使用ux_locks系统视图。
3.3.1.表级锁
下面列出了可用的锁定模式及其自动由 UXDB 使用的上下文。您还可以使用命令LOCK显式获取其中任何一个锁定。请记住，所有这些锁定模式都是表级锁定，即使名称中包含单词“row”；锁模式的名称是历史遗留问题。在某种程度上，这些名称反映了每种锁模式的典型用法，但是语义都是相同的。一个锁模式和另一个锁模式之间唯一的真正区别在于它们各自与哪些锁模式冲突（参见表 冲突的锁定模式）。两个事务不能同时在同一张表上持有冲突的锁模式。（但是，一个事务永远不会与自己冲突。例如，它可能在同一张表上先获取ACCESS EXCLUSIVE锁，然后再获取ACCESS SHARE锁。）非冲突的锁模式可以被许多事务同时持有。特别要注意的是，某些锁模式是自我冲突的（例如，ACCESS EXCLUSIVE锁不能同时由多个事务持有），而其他锁模式则不是自我冲突的（例如，ACCESS SHARE锁可以由多个事务持有）。
表级锁模式
-
ACCESS SHARE（AccessShareLock）仅与
ACCESS EXCLUSIVE锁模式冲突。SELECT命令在引用的表上获取此模式的锁。通常，任何仅读取表而不修改它的查询都将获取此锁模式。
-
ROW SHARE（RowShareLock）与
EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。SELECT命令在指定FOR UPDATE、FOR NO KEY UPDATE、FOR SHARE或FOR KEY SHARE选项的表上获取此模式的锁（除了在没有任何显式FOR...锁定选项的情况下引用的任何其他表上的ACCESS SHARE锁）。 -
ROW EXCLUSIVE（RowExclusiveLock）与
SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。UPDATE、DELETE、INSERT和MERGE命令在目标表上获取此锁模式（除了在任何其他引用表上的ACCESS SHARE锁）。 -
SHARE UPDATE EXCLUSIVE（ShareUpdateExclusiveLock）与
SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。此模式保护表免受并发模式更改和VACUUM运行的影响。由
VACUUM（无FULL选项）、ANALYZE、CREATE INDEX CONCURRENTLY、CREATE STATISTICS、COMMENT ON、REINDEX CONCURRENTLY，以及某些ALTER INDEX和ALTER TABLE变体。 -
SHARE(ShareLock)与
ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。此模式保护表免受并发数据更改的影响。由
CREATE INDEX（不带CONCURRENTLY）获取。 -
SHARE ROW EXCLUSIVE(ShareRowExclusiveLock)与
ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。此模式保护表免受并发数据更改的影响，并且是自我排斥的，因此一次只能有一个会话持有它。由
CREATE TRIGGER和某些形式的ALTER TABLE获取。 -
EXCLUSIVE(ExclusiveLock)与
ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE锁模式冲突。此模式仅允许并发的ACCESS SHARE锁，即只有在持有此锁模式的事务中，才能并行读取表。由
REFRESH MATERIALIZED VIEW CONCURRENTLY获取。 -
ACCESS EXCLUSIVE(AccessExclusiveLock)与所有模式的锁（
ACCESS SHARE、ROW SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、EXCLUSIVE和ACCESS EXCLUSIVE）冲突。此模式保证持有者是唯一访问表的事务。由
DROP TABLE、TRUNCATE、REINDEX、CLUSTER、VACUUM FULL和REFRESH MATERIALIZED VIEW（不带CONCURRENTLY）命令获取。许多形式的ALTER INDEX和ALTER TABLE也在此级别获取锁。这也是未显式指定模式的LOCK TABLE语句的默认锁模式。提示
只有
ACCESS EXCLUSIVE锁会阻止SELECT（不带FOR UPDATE/SHARE）语句。
一旦获取，锁通常会持有到事务结束。但是，如果一个在建立保存点后获取锁，如果回滚到保存点，则立即释放锁。这与“ROLLBACK”取消自保存点以来所有命令的影响的原则一致。在PL/uxSQL异常块中获取的锁也是如此：从块中错误跳出会释放其中获取的锁。
表 冲突的锁定模式
| 请求的锁定模式 | 现有锁定模式 | |||||||
|---|---|---|---|---|---|---|---|---|
ACCESS SHARE | ROW SHARE | ROW EXCL. | SHARE UPDATE EXCL. | SHARE | SHARE ROW EXCL. | EXCL. | ACCESS EXCL. | |
ACCESS SHARE | X | |||||||
ROW SHARE | X | X | ||||||
ROW EXCL. | X | X | X | X | ||||
SHARE UPDATE EXCL. | X | X | X | X | X | |||
SHARE | X | X | X | X | X | |||
SHARE ROW EXCL. | X | X | X | X | X | X | ||
EXCL. | X | X | X | X | X | X | X | |
ACCESS EXCL. | X | X | X | X | X | X | X | X |
3.3.2.行级锁
除了表级锁之外，还有行级锁，下面列出了它们的上下文，这些上下文由UXDB自动使用。请参见表 冲突的行级锁，了解完整的行级锁冲突表。请注意，事务可以在同一行上持有冲突锁，即使在不同的子事务中也是如此；但是，除此之外，两个事务永远不会持有冲突的锁。在同一行上。行级锁不影响数据查询；它们只阻止对同一行的写入和锁定。行级锁在事务结束或保存点回滚时释放，就像表级锁一样。
行级锁定模式
-
FOR UPDATEFOR UPDATE会导致由SELECT语句检索到的行被锁定，就像进行更新一样。这可以防止其他事务在当前事务结束之前锁定、修改或删除它们。也就是说，试图对这些行运行UPDATE、DELETE、SELECT FOR UPDATE、SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或SELECT FOR KEY SHARE的其他事务将被阻塞，直到当前事务结束；反之，SELECT FOR UPDATE将等待在同一行上运行任何这些命令的并发事务，然后锁定并返回更新的行（或者如果行已被删除，则不返回行）。然而，在REPEATABLE READ或SERIALIZABLE事务中，如果要锁定的行自事务开始以来已更改，则会抛出错误。有关进一步讨论，参见应用程序级别的数据一致性检查。FOR UPDATE锁模式也会被任何对行的DELETE和修改某些列值的UPDATE获取。目前，考虑用于UPDATE情况的列集是那些在其上具有可用于外键的唯一索引的列集（因此不考虑部分索引和表达式索引），但这可能会在将来发生变化。 -
FOR NO KEY UPDATE与
FOR UPDATE类似，但获取的锁更弱：此锁不会阻止尝试在相同行上获取锁的SELECT FOR KEY SHARE命令。此锁模式也会被不获取FOR UPDATE锁的任何UPDATE获取。 -
FOR SHARE与
FOR NO KEY UPDATE类似，但它获取的是共享锁而不是排他锁。共享锁阻止其他事务在这些行上执行UPDATE、DELETE、SELECT FOR UPDATE或SELECT FOR NO KEY UPDATE，但它不会阻止它们执行SELECT FOR SHARE或SELECT FOR KEY SHARE。 -
FOR KEY SHARE与
FOR SHARE类似，但锁更弱：会阻止SELECT FOR UPDATE，但不会阻止SELECT FOR NO KEY UPDATE。键共享锁阻止其他事务执行更改键值的DELETE或任何UPDATE，但不会阻止其他UPDATE，也不会阻止SELECT FOR NO KEY UPDATE、SELECT FOR SHARE或SELECT FOR KEY SHARE。
UXDB不会在内存中记住任何修改行的信息，因此一次锁定的行数没有限制。但是，锁定一行可能会导致磁盘写入，例如，SELECT FOR UPDATE会修改选定的行以标记它们已锁定，因此会导致磁盘写入。
表 冲突的行级锁
| 请求的锁定模式 | 当前锁定模式 | |||
|---|---|---|---|---|
| 对于关键份额 | 分享 | 无密钥更新 | 更新 | |
| 对于关键份额 | X | |||
| 分享 | X | X | ||
| 无密钥更新 | X | X | X | |
| 更新 | X | X | X | X |
3.3.3.页级锁
除了表和行锁之外，还使用页面级共享/排他锁来控制对共享缓冲池中的表页面的读/写访问。这些锁在获取或更新行后立即释放。应用程序开发人员通常不需要关注页面级锁，但是为了完整起见，在此提及它们。
3.3.4.死锁
显式锁定的使用可能会增加死锁的可能性，其中两个（或多个）事务都持有彼此想要的锁。例如，如果事务1获取了表A的排他锁，然后尝试获取表B的排他锁，而事务2已经对表B进行了排他锁定，现在想要对表A进行排他锁定，则两者都无法继续执行。UXDB会自动检测死锁情况，并通过中止其中一个事务来解决它们，从而允许其他事务完成。（很难预测将中止哪个事务，因此不应依赖它。）
请注意，行级锁也可能导致死锁（因此，即使不使用显式锁定，它们也可能发生）。考虑两个并发事务修改表的情况。第一个事务执行：
UPDATE accounts SET balance = balance + 100.00 WHERE acctnum = 11111;
这会在具有指定帐户号的行上获取行级锁。然后，第二个事务执行：
UPDATE accounts SET balance =balance + 100.00 WHERE acctnum = 22222;
UPDATE accounts SET balance =balance - 100.00 WHERE acctnum = 11111;
第一个UPDATE语句成功获取了指定行的行级锁，因此成功更新了该行。但是，第二个UPDATE语句发现它要更新的行已经被锁定，因此等待获取锁的事务完成。现在，事务二正在等待事务一完成才能继续执行。现在，事务一执行：
UPDATE accounts SET balance = balance - 100.00 WHERE acctnum = 22222;
事务一试图在指定行上获取行级锁，但无法获取，因为事务二已经持有这样的锁。因此，事务一等待事务二完成。因此，事务一被事务二阻塞，事务二被事务一阻塞：死锁情况。UXDB将检测到这种情况并中止其中一个事务。
通常避免死锁的最佳方法是确保使用数据库的所有应用程序以一致的顺序获取多个对象上的锁。在上面的示例中，如果两个事务按相同的顺序更新行，则不会发生死锁。还应确保在事务中获取对象上的第一个锁是将需要该对象的最严格模式。如果事先无法验证这一点，则可以通过重试由于死锁而中止的事务来处理死锁。
只要没有检测到死锁情况，寻求表级或行级锁的事务将无限期地等待冲突锁被释放。这意味着应用程序长时间保持事务处于打开状态是一个不好的想法（例如，等待用户输入时）。
3.3.5.咨询锁
UXDB提供了一种创建具有应用程序定义含义的锁的方法。这些称为“咨询锁”，因为系统不强制执行它们的使用-应用程序使用它们是正确的。咨询锁可用于MVCC模型不适合的锁定策略。例如，咨询锁的常见用途是模拟所谓的“平面文件”数据管理系统典型的悲观锁定策略。虽然可以使用存储在表中的标志来实现相同的目的，但咨询锁更快，避免了表膨胀，并且在会话结束时由服务器自动清理。
在UXDB中获取咨询锁有两种方法：会话级别和事务级别。在会话级别获取咨询锁后，咨询锁将保持到显式释放或会话结束为止。与标准锁请求不同，会话级别的咨询锁请求不遵循事务语义：在稍后回滚的事务期间获取的锁仍将在回滚后保持，同样，即使调用事务失败，解锁也是有效的。拥有进程可以多次获取锁；在锁定实际释放之前，每个完成的锁请求必须有一个相应的解锁请求。另一方面，事务级别的锁请求更像常规锁请求：它们在事务结束时自动释放，没有显式的解锁操作。对于短期使用咨询锁，此行为通常比会话级别的行为更方便。会话级别和事务级别的相同咨询锁标识符的锁请求将以预期的方式相互阻塞。如果会话已经持有给定的咨询锁，则它的其他请求将始终成功，即使其他会话正在等待锁；无论现有锁定和新请求是会话级别还是事务级别，此语句都是正确的。
与UXDB中的所有锁一样，咨询锁的完整列表当前会话持有的所有锁可以在ux_locks系统视图中找到。
咨询锁和常规锁都存储在一个共享内存池中，其大小由配置变量max_locks_per_transaction和max_connections定义。 必须注意不要耗尽此内存，否则服务器将无法授予任何锁。这对服务器可授予的咨询锁数量施加了上限，通常在数万到数十万之间，具体取决于服务器的配置方式。
在某些使用咨询锁方法的情况下，特别是涉及显式排序和 LIMIT 子句的查询中，必须注意控制由于 SQL 表达式求值顺序而获取的锁。 例如：
SELECT ux_advisory_lock(id) FROM foo WHERE id = 12345; -- ok
SELECT ux_advisory_lock(id) FROM foo WHERE id > 12345 LIMIT 100; -- danger!
SELECT ux_advisory_lock(q.id) FROM
(
SELECT id FROM foo WHERE id > 12345 LIMIT 100
) q; -- ok
在上述查询中，第二种形式是危险的，因为不能保证 LIMIT 在锁定函数执行之前被应用。这可能导致获取一些应用程序未预期的锁，因此无法释放（直到结束会话）。从应用程序的角度来看，这些锁将是悬挂的，尽管仍然可以在 ux_locks 中查看。
3.4.应用程序级别的数据一致性检查
使用读取提交事务来强制执行数据完整性的业务规则非常困难，因为每个语句的数据视图都在变化，即使单个语句发生写冲突，它也可能不仅限于语句的快照。
虽然可重复读事务在其执行过程中具有数据的稳定视图，但使用MVCC快照进行数据一致性检查存在一个微妙的问题，涉及到所谓的读/写冲突。如果一个事务写入数据，并发事务尝试读取相同的数据（无论是在写入之前还是之后），它都无法看到另一个事务的工作。然后，无论哪个事务先开始或先提交，读者似乎都已经执行完毕。如果仅到此为止，就没有问题，但是如果读者还写入了被并发事务读取的数据，则现在存在一个事务，它似乎在之前已经运行，而前面提到的任何事务都没有运行。如果最后一个似乎已经执行的事务实际上首先提交，那么在事务执行顺序图中很容易出现一个循环。当出现这样的循环时，如果没有帮助，完整性检查将无法正常工作。
如可串行化隔离级别所述，串行化事务只是可重复读事务，它添加了对危险的读/写冲突模式的非阻塞监视。当检测到可能导致执行顺序中出现循环的模式时，将回滚涉及的事务以打破循环。
3.4.1.通过串行事务实现一致性
如果对于所有写操作和需要数据一致视图的所有读操作都使用Serializable事务隔离级别，则无需进行其他努力来确保一致性。在其他环境中编写使用串行事务来确保一致性的软件应在UXDB 中“正常工作”。
使用此技术时，如果应用程序软件通过自动重试由于序列化失败而回滚的事务的框架，则可以避免为应用程序员创建不必要的负担。将default_transaction_isolation设置为serializable可能是一个好主意。还应该通过触发器中的事务隔离级别检查来采取一些措施，以确保不使用任何其他事务隔离级别，无论是无意中还是为了破坏完整性检查。
有关性能建议，请参见可串行化隔离级别。
警告
使用 Serializable 事务保护完整性的这种级别尚未扩展到热备模式。因此，使用热备的人可能需要在主服务器上使用可重复读和显式锁定。
3.4.2.通过显式阻塞锁实现一致性
当可能存在非串行化写入时，为了确保行的当前有效性并保护其免受并发更新，必须使用 SELECT FOR UPDATE、SELECT FOR SHARE 或适当的 LOCK TABLE 语句。（SELECT FOR UPDATE和SELECT FOR SHARE仅锁定返回的行以防止并发更新，而LOCK TABLE 锁定整个表。）在将应用程序移植到UXDB时应考虑到这一点。
对于那些从其他环境转换的人来说，还需要注意的是，SELECT FOR UPDATE 不能确保并发事务不会更新或删除所选行。在UXDB中，您必须实际更新行，即使不需要更改任何值，也必须这样做才能实现这一点。 SELECT FOR UPDATE会暂时阻止其他事务获取相同的锁或执行会影响锁定行的 UPDATE 或DELETE，但是一旦持有此锁的事务提交或回滚，被阻塞的事务将继续执行冲突操作，除非在持有锁时实际更新了行。
在非串行化 MVCC下，全局有效性检查需要额外的思考。例如，银行应用程序可能希望在两个正在积极更新的表中检查一个表中所有信用额之和是否等于另一个表中所有借方之和。在读取提交模式下，比较两个连续的SELECT sum(...) 命令的结果不会可靠地工作，因为第二个查询很可能包括第一个查询未计算的事务的结果。在一个事务中执行两个sum(...)命令也不会可靠，因为在两个命令之间可能会发生其他事务。因此，必须使用显式锁定或串行化事务来执行此类全局有效性检查。
单个可重复读事务只会准确地反映在可重复读事务开始之前提交的事务的影响，但人们可能会合理地怀疑在交付答案时它是否仍然相关。如果可重复读事务在尝试进行一致性检查之前应用了一些更改，则检查的有用性变得更加值得商榷，因为现在它包括了一些但不是所有的事务开始后的更改。在这种情况下，一个小心的人可能希望锁定所有需要进行检查的表，以获得当前现实的无可争议的图片。SHARE模式（或更高）锁定保证在锁定的表中没有未提交的更改，除了当前事务的更改之外。
还要注意，如果一个人依靠显式锁定来防止并发更改，那么他应该使用读取已提交模式，或者在可重复读模式下在执行查询之前小心地获取锁定。可重复读事务获取的锁定保证没有修改表的其他事务仍在运行，但如果事务看到的快照早于获取锁定，则它可能早于表中现在已提交的一些更改。可重复读事务的快照实际上在其第一个查询或数据修改命令（SELECT、INSERT、UPDATE或DELETE）开始时被冻结，因此可以在快照被冻结之前显式获取锁定。
3.5.序列化失败处理
可重复读和可串行化隔离级别都可能产生旨在防止序列化异常的错误。如前所述，使用这些级别的应用程序必须准备好重试由于序列化错误而失败的事务。这样的错误消息文本将根据具体情况而异，但它将始终具有SQLSTATE 代码 40001 (serialization_failure)。
还可能建议重试死锁失败。这些具有 SQLSTATE 代码 40P01 (deadlock_detected)。
在某些情况下，重试唯一键失败也是适当的，这些失败具有 SQLSTATE 代码 23505(unique_violation)，以及排除约束失败，这些失败具有 SQLSTATE 代码 23P01(exclusion_violation)。例如，如果应用程序在检查当前存储的键后选择主键列的新值，它可能会因为另一个应用程序实例同时选择了相同的新键而获得唯一键失败。这实际上是一个序列化失败，但服务器不会将其检测为序列化失败，因为它无法“看到”插入值和先前读取之间的连接。还有一些边缘情况，即使原则上服务器有足够的信息来确定序列化问题是根本原因，它也会发出唯一键或排除约束错误。虽然建议无条件重试serialization_failure 错误，但在重试这些其他错误代码时需要更加小心，因为它们可能代表持久性错误条件而不是瞬态故障。
重要的是要重试完整的事务，包括决定发出哪些 SQL 和/或使用哪些值的所有逻辑。因此，UXDB不提供自动重试功能，因为它无法以任何正确性保证进行重试。
事务重试并不能保证重试的事务一定会完成；可能需要多次重试。在高争用情况下，事务的完成可能需要多次尝试。在涉及冲突的准备事务的情况下，可能无法取得进展，直到准备事务提交或回滚。
3.6.注意事项
某些 DDL 命令，目前仅包括TRUNCATE和ALTER TABLE的表重写形式，不是 MVCC安全的。这意味着，在截断或重写提交后，如果并发事务使用了在 DDL命令提交之前获取的快照，则该表将对并发事务为空。这只会影响到在 DDL命令开始之前没有访问过相关表的事务，任何已经访问过相关表的事务都将持有至少一个ACCESS SHARE 表锁，这将阻塞 DDL命令直到该事务完成。因此，这些命令不会对目标表的后续查询中的表内容造成任何明显的不一致，但它们可能会导致目标表的内容与数据库中的其他表之间出现可见的不一致。
目前热备复制目标尚未支持 Serializable事务隔离级别。在热备模式下当前支持的最严格隔离级别是 Repeatable Read。虽然在主服务器上在Serializable 事务中执行所有永久数据库写操作将确保所有备机最终达到一致状态，但在备机上运行的 Repeatable Read事务有时可能会看到与主服务器上事务的任何串行执行不一致的瞬态状态。
系统目录的内部访问不是使用当前事务的隔离级别进行的。这意味着，新创建的数据库对象对并发的 Repeatable Read 和Serializable事务是可见的，即使它们包含的行不是可见的。相比之下，显式检查系统目录的查询不会看到代表并发创建的数据库对象的行，在更高的隔离级别下也是如此。
3.7.锁定和索引
尽管UXDB提供了对表数据的非阻塞读写访问，但并非所有在UXDB中实现的索引访问方法都提供了非阻塞读写访问。各种索引类型的处理方式如下：
-
B-tree、GiST和SP-GiST索引
使用短期共享/排他页面级锁进行读/写访问。在获取或插入每个索引行后立即释放锁。这些索引类型提供了最高的并发性，没有死锁条件。
-
哈希索引
使用共享/排他哈希桶级锁进行读/写访问。在整个桶被处理后释放锁。桶级锁提供比索引级锁更好的并发性，但死锁是可能的，因为锁的持有时间比一个索引操作长。
-
GIN索引
使用短期共享/排他页面级锁进行读/写访问。在获取或插入每个索引行后立即释放锁。但请注意，插入GIN索引值通常会为每行产生多个索引键插入，因此GIN可能会为单个值的插入执行大量工作。
目前，B-tree索引为并发应用程序提供了最佳性能；由于它们比哈希索引具有更多的功能，因此它们是并发应用程序中需要对标量数据进行索引的推荐索引类型。处理非标量数据时，B-tree无用，应改用GiST、SP-GiST或GIN索引。
4.性能提示
4.1.使用 EXPLAIN
每个查询都会在 UXDB中生成一个查询计划。选择与查询结构和数据属性匹配的正确计划对于良好的性能至关重要，因此系统包括一个复杂的规划器，试图选择好的计划。您可以使用EXPLAIN命令查看规划器为任何查询创建的查询计划。阅读计划是一门需要一些经验才能掌握的艺术，但本节试图涵盖基础知识。
本节中的示例来自于执行VACUUM ANALYZE 后使用9.3开发源的回归测试数据库。如果您自己尝试这些示例，则应该能够获得类似的结果，但是您的估计成本和行数可能会略有不同，因为ANALYZE 的统计信息是随机样本而不是精确的，并且成本在本质上有些依赖于平台。
本节示例使用 EXPLAIN 的默认输出格式 “text”，这是紧凑且方便人类阅读的格式。如果您想将 EXPLAIN的输出提供给程序进行进一步分析，则应使用其机器可读的输出格式（XML、JSON 或YAML）。
4.1.1.EXPLAIN 基础知识
查询计划的结构是一棵计划节点树。树的底层节点是扫描节点：它们返回原始行。从表中获取数据。不同类型的扫描节点用于不同的表访问方法：顺序扫描、索引扫描和位图索引扫描。还有非表行源，例如FROM中的VALUES子句和返回集函数，它们有自己的扫描节点类型。如果查询需要连接、聚合、排序或其他操作原始行，则会有额外的节点在扫描节点上方执行这些操作。同样，通常有多种可能的方法来执行这些操作，因此不同的节点类型也会出现在这里。EXPLAIN的输出对于计划树中的每个节点都有一行，显示基本节点类型以及规划程序为执行该计划节点所做的成本估计。可能会出现其他行，缩进自节点的摘要行，以显示节点的其他属性。
第一行（最顶层节点的摘要行）具有计划的估计总执行成本；规划程序试图将此数字最小化。
以下是一个微不足道的示例，只是为了展示输出的样子：
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
由于此查询没有WHERE子句，因此必须扫描表的所有行，因此规划程序选择使用简单的顺序扫描计划。括号中引用的数字是（从左到右）：
-
估计的启动成本。这是在输出阶段开始之前花费的时间，例如，在排序节点中进行排序的时间。
-
估计的总成本。这是在假设计划节点运行到完成，即检索所有可用行的情况下给出的。实际上，节点的父节点可能会在读取所有可用行之前停止（请参阅下面的
LIMIT示例）。 -
此计划节点输出的预计行数。同样，假定节点运行到完成。
-
此计划节点输出的预计行的平均宽度（以字节为单位）。
成本是由规划程序的成本参数确定的任意单位测量。传统做法是以磁盘页面获取为单位来衡量成本；即seq_page_cost通常设置为1.0，并且其他成本参数相对于其设置。本节中的示例使用默认成本参数运行。
重要的是要理解上层节点的成本包括所有子节点的成本。还要意识到成本仅反映规划程序关心的事情。特别是，成本不考虑将结果行传输到客户端所花费的时间，这可能是实际经过时间的重要因素；但规划程序忽略它，因为它无法通过更改计划来改变它。（我们相信每个正确的计划都将输出相同的行集。）
rows值有点棘手，因为它不是计划节点处理或扫描的行数，而是节点发出的行数。由于在节点上应用任何WHERE子句条件而进行的过滤，这通常少于扫描的数量。理想情况下，顶级行估计将近似于查询实际返回、更新或删除的行数。
回到我们的例子：
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
这些数字是非常直接地推导出来的。如果你执行：
SELECT relpages, reltuples FROM ux_class WHERE relname = 'tenk1';
你会发现 tenk1 有 358 个磁盘页面和 10000 行。估算成本的方法是（磁盘页面读取 * seq_page_cost）+（扫描的行数 * cpu_tuple_cost）。默认情况下，seq_page_cost是 1.0，cpu_tuple_cost 是 0.01，所以估算成本是（358* 1.0）+（10000 * 0.01）= 458。
现在让我们修改查询以添加一个 WHERE 条件：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7001 width=244)
Filter: (unique1 < 7000)
注意，EXPLAIN 输出显示 WHERE 子句作为附加到 Seq 扫描计划节点的一个 “filter”条件应用。这意味着计划节点为它扫描的每一行检查条件，并仅输出通过条件的行。由于WHERE子句，输出行的估计值已经减少。然而，扫描仍然必须访问所有 10000 行，所以成本没有减少；实际上它增加了一点（精确地说是10000 * cpu_operator_cost），以反映额外的CPU时间用于检查 WHERE 条件。
这个查询实际选择的行数是 7000，但是 rows估计值只是近似值。如果你尝试复制这个实验，你可能会得到稍微不同的估计值；此外，它可以在每个ANALYZE 命令之后改变，因为ANALYZE生成的统计信息来自表的随机样本。
现在，让我们使条件更加严格：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=5.07..229.20 rows=101 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
Index Cond: (unique1 < 100)
这里规划器决定使用两步计划：子计划节点访问索引以查找与索引条件匹配的行的位置，然后上层计划节点实际从表本身获取这些行。单独获取行比顺序读取行要昂贵得多，但是因为不必访问表的所有页面，所以这仍然比顺序扫描更便宜。（使用两个计划级别的原因是上层计划节点在读取它们之前将索引识别的行位置按物理顺序排序，以最小化单独获取的成本。节点名称中提到的“位图” 是执行排序的机制。）
现在让我们在 WHERE 子句中添加另一个条件：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx';
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=5.04..229.43 rows=1 width=244)
Recheck Cond: (unique1 < 100)
Filter: (stringu1 = 'xxx'::name)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
Index Cond: (unique1 < 100)
添加条件 stringu1 = 'xxx'可以减少输出行数的估计，但不会减少成本，因为我们仍然需要访问相同的行集。请注意，stringu1子句不能作为索引条件应用，因为此索引仅针对 unique1列。相反，它被应用为对检索到的行的过滤器。因此，成本实际上略微增加以反映这个额外的检查。在某些情况下，规划器会更喜欢一个“简单”的索引扫描计划：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 = 42;
QUERY PLAN
-----------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.29..8.30 rows=1 width=244)
Index Cond: (unique1 = 42)
在这种类型的计划中，表行按索引顺序获取，这使得它们更昂贵，但是由于数量很少，因此排序行位置的额外成本不值得。您最常见的是看到这种计划类型的查询只获取单个行。它也经常用于具有与索引顺序匹配的ORDER BY 条件的查询，因为此时不需要额外的排序步骤来满足 ORDER BY。在这个例子中，添加 ORDER BY unique1 将使用相同的计划，因为索引已经隐式提供了所请求的排序。
规划器可以以几种方式实现 ORDER BY子句。上面的示例显示了这样的排序子句可以隐式实现。规划器还可以添加显式的 sort 步骤：
EXPLAIN SELECT * FROM tenk1 ORDER BY unique1;
QUERY PLAN
-------------------------------------------------------------------
Sort (cost=1109.39..1134.39 rows=10000 width=244)
Sort Key: unique1
-> Seq Scan on tenk1 (cost=0.00..445.00 rows=10000 width=244)
如果计划的一部分保证了所需排序键的前缀的排序，则规划器可能决定使用增量排序步骤：
EXPLAIN SELECT * FROM tenk1 ORDER BY four, ten LIMIT 100;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Limit (cost=521.06..538.05 rows=100 width=244)
-> Incremental Sort (cost=521.06..2220.95 rows=10000 width=244)
Sort Key: four, ten
Presorted Key: four
-> Index Scan using index_tenk1_on_four on tenk1 (cost=0.29..1510.08 rows=10000 width=244)
与常规排序相比，增量排序允许在整个结果集被排序之前返回元组，这特别适用于LIMIT查询的优化。它还可以减少内存使用和将排序溢出到磁盘的可能性，但代价是将结果集拆分成多个排序批次的增加开销。
如果 WHERE 中引用了多个列的单独索引，则规划器可能选择使用索引的 AND 或 OR 组合：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=25.08..60.21 rows=10 width=244)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
-> BitmapAnd (cost=25.08..25.08 rows=10 width=0)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
Index Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0)
Index Cond: (unique2 > 9000)
但这需要访问两个索引，所以与仅使用一个索引并将另一个条件视为过滤器相比，并不一定是一个胜利。如果您改变涉及的范围，您将看到计划相应地改变。
以下是一个示例，显示了LIMIT的效果：
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;
QUERY PLAN
-------------------------------------------------------------------------------------
Limit (cost=0.29..14.48 rows=2 width=244)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..71.27 rows=10 width=244)
Index Cond: (unique2 > 9000)
Filter: (unique1 < 100)
这与上面的查询相同，但我们添加了LIMIT，因此不需要检索所有行，计划程序改变了它的想法。请注意，索引扫描节点的总成本和行数显示为如果它运行到完成。但是，期望Limit节点在检索了其中的五分之一后停止，因此它的总成本只有五分之一，这是查询的实际预估成本。这个计划优于在先前的计划中添加Limit节点，因为Limit无法避免支付位图扫描的启动成本，因此使用该方法的总成本将超过25个单位。
让我们尝试连接两个表，使用我们一直在讨论的列：
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=4.65..118.62 rows=10 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244)
Recheck Cond: (unique1 < 10)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)
Index Cond: (unique1 < 10)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.91 rows=1 width=244)
Index Cond: (unique2 = t1.unique2)
在这个计划中，我们有一个嵌套循环连接节点，它有两个表扫描作为输入或子节点。节点摘要行的缩进反映了计划树结构。连接的第一个或“外部”子节点是类似于我们之前看到的位图扫描。它的成本和行数与我们从SELECT... WHERE unique1 < 10中得到的相同，因为我们正在该节点应用WHERE子句unique1< 10。目前，t1.unique2 =t2.unique2子句还不相关，因此它不会影响外部扫描的行数。嵌套循环连接节点将为从外部子节点获得的每一行运行其第二个或“内部”子节点一次。当前外部行的列值可以插入内部扫描中；在这里，外部行的t1.unique2值可用，因此我们得到了一个与我们上面看到的简单SELECT... WHERE t2.unique2 =constant情况类似的计划和成本。（预估成本实际上比上面看到的略低，因为预计在对t2的重复索引扫描期间会发生缓存。）然后，循环节点的成本设置基于外部扫描的成本，加上每个外部行的内部扫描的一次重复（这里是10*7.91），再加上一些用于连接处理的CPU时间。
在这个示例中，连接的输出行数与两个扫描的行数的乘积相同，但这并不总是正确的，因为可能有其他同时提到两个表的WHERE子句，因此只能应用于连接点，而不能应用于任何输入扫描。以下是一个示例：
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t2.unique2 < 10 AND t1.hundred < t2.hundred;
QUERY PLAN
---------------------------------------------------------------------------------------------
Nested Loop (cost=4.65..49.46 rows=33 width=488)
Join Filter: (t1.hundred < t2.hundred)
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244)
Recheck Cond: (unique1 < 10)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0)
Index Cond: (unique1 < 10)
-> Materialize (cost=0.29..8.51 rows=10 width=244)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..8.46 rows=10 width=244)
Index Cond: (unique2 < 10)
条件 t1.hundred < t2.hundred 无法在 tenk2_unique2 索引中测试，因此应用于连接节点。这减少了连接节点的预计输出行数，但不会更改任何输入扫描。
请注意，此处规划程序选择通过在其上放置 Materialize 计划节点来“材料化”连接的内部关系。这意味着尽管嵌套循环连接节点需要针对外部关系的每一行读取该数据一次，但只会执行一次 t2 索引扫描。Materialize 节点在读取数据时将数据保存在内存中，然后在每个后续传递中从内存返回数据。
在处理外部连接时，您可能会看到具有附加的“Join Filter”和普通“Filter”条件的连接计划节点。Join Filter 条件来自外部连接的 ON 子句，因此未通过 Join Filter 条件的行仍可能作为空扩展行发出。但是，普通过滤器条件是在外部连接规则之后应用的，因此无条件地起作用以删除行。在内部连接中，这些类型的过滤器之间没有语义差异。
如果我们稍微改变查询的选择性，我们可能会得到非常不同的连接计划：
EXPLAIN SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=230.47..713.98 rows=101 width=488)
Hash Cond: (t2.unique2 = t1.unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244)
-> Hash (cost=229.20..229.20 rows=101 width=244)
-> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0)
Index Cond: (unique1 < 100)
在这里，规划程序选择使用哈希连接，其中一个表的行被输入到内存中的哈希表中，之后扫描另一个表，并为每一行探测哈希表以查找匹配项。再次注意缩进如何反映计划结构：对 tenk1 的位图扫描是 Hash 节点的输入，该节点构造哈希表。然后将其返回到 Hash Join 节点，该节点从其外部子计划读取行，并搜索哈希表以查找每个行。
另一种可能的连接类型是合并连接，如下所示：
EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Merge Join (cost=198.11..268.19 rows=10 width=488)
Merge Cond: (t1.unique2 = t2.unique2)
-> Index Scan using tenk1_unique2 on tenk1 t1 (cost=0.29..656.28 rows=101 width=244)
Filter: (unique1 < 100)
-> Sort (cost=197.83..200.33 rows=1000 width=244)
Sort Key: t2.unique2
-> Seq Scan on onek t2 (cost=0.00..148.00 rows=1000 width=244)
Merge join 要求其输入数据在连接键上排序。在此计划中，使用索引扫描按正确顺序访问行来对 tenk1 数据进行排序，但对于onek，首选顺序扫描和排序，因为在该表中要访问的行数更多。（由于索引扫描需要非顺序磁盘访问，因此顺序扫描和排序经常比索引扫描更适合排序许多行。）
查看变体计划的一种方法是使用的启用/禁用标志，强制规划器忽略它认为最便宜的策略。（这是一个粗糙的工具，但很有用。另请参见使用显式JOIN子句控制规划器。）例如，如果我们不确定顺序扫描和排序是处理前面示例中的 onek 表的最佳方法，我们可以尝试：
SET enable_sort = off;
EXPLAIN SELECT *
FROM tenk1 t1, onek t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Merge Join (cost=0.56..292.65 rows=10 width=488)
Merge Cond: (t1.unique2 = t2.unique2)
-> Index Scan using tenk1_unique2 on tenk1 t1 (cost=0.29..656.28 rows=101 width=244)
Filter: (unique1 < 100)
-> Index Scan using onek_unique2 on onek t2 (cost=0.28..224.79 rows=1000 width=244)
这表明，规划器认为通过索引扫描对 onek 进行排序比顺序扫描和排序要贵约12%。当然，下一个问题是它是否正确。我们可以使用EXPLAIN ANALYZE 进行调查，如下所述。
4.1.2.EXPLAIN ANALYZE
可以使用EXPLAIN的ANALYZE选项来检查规划器估计的准确性。使用此选项，EXPLAIN实际执行查询，然后显示每个计划节点内累积的真实行数和真实运行时间，以及与普通EXPLAIN显示的相同估计值。例如，我们可能会得到如下结果：
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 10 AND t1.unique2 = t2.unique2;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=4.65..118.62 rows=10 width=488) (actual time=0.128..0.377 rows=10 loops=1)
-> Bitmap Heap Scan on tenk1 t1 (cost=4.36..39.47 rows=10 width=244) (actual time=0.057..0.121 rows=10 loops=1)
Recheck Cond: (unique1 < 10)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..4.36 rows=10 width=0) (actual time=0.024..0.024 rows=10 loops=1)
Index Cond: (unique1 < 10)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.29..7.91 rows=1 width=244) (actual time=0.021..0.022 rows=1 loops=10)
Index Cond: (unique2 = t1.unique2)
Planning time: 0.181 ms
Execution time: 0.501 ms
请注意，“实际时间”值以毫秒为单位，而成本估计以任意单位表示；因此它们不太可能匹配。通常最重要的是查找估计的行数是否与实际情况相当接近。在这个例子中，所有估计值都是准确的，但在实践中这是相当不寻常的。
在某些查询计划中，子计划节点可能会被执行多次。例如，在上述嵌套循环计划中，内部索引扫描将针对每个外部行执行一次。在这种情况下，loops值报告节点的执行总次数，所显示的实际时间和行数值是每次执行的平均值。这样做是为了使数字与成本估算的显示方式可比较。将其乘以 loops值即可得到节点实际花费的总时间。在上面的示例中，我们花费了总共 0.220 毫秒来执行对 tenk2 的索引扫描。
在某些情况下，EXPLAIN ANALYZE显示除计划节点执行时间和行数计数之外的其他执行统计信息。例如，排序和哈希节点提供额外的信息：
EXPLAIN ANALYZE SELECT *
FROM tenk1 t1, tenk2 t2
WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2 ORDER BY t1.fivethous;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=717.34..717.59 rows=101 width=488) (actual time=7.761..7.774 rows=100 loops=1)
Sort Key: t1.fivethous
Sort Method: quicksort Memory: 77kB
-> Hash Join (cost=230.47..713.98 rows=101 width=488) (actual time=0.711..7.427 rows=100 loops=1)
Hash Cond: (t2.unique2 = t1.unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..445.00 rows=10000 width=244) (actual time=0.007..2.583 rows=10000 loops=1)
-> Hash (cost=229.20..229.20 rows=101 width=244) (actual time=0.659..0.659 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 28kB
-> Bitmap Heap Scan on tenk1 t1 (cost=5.07..229.20 rows=101 width=244) (actual time=0.080..0.526 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.049..0.049 rows=100 loops=1)
Index Cond: (unique1 < 100)
Planning time: 0.194 ms
Execution time: 8.008 ms
Sort节点显示所使用的排序方法（特别是排序是在内存中还是在磁盘上），以及所需的内存或磁盘空间量。哈希节点显示哈希桶和批次的数量，以及用于哈希表的峰值内存使用量。（如果批次数超过一个，则还涉及磁盘空间使用，但不显示。）
另一种额外信息的类型是过滤条件删除的行数：
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;
QUERY PLAN
---------------------------------------------------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7000 width=244) (actual time=0.016..5.107 rows=7000 loops=1)
Filter: (ten < 7)
Rows Removed by Filter: 3000
Planning time: 0.083 ms
Execution time: 5.905 ms
这些计数对于应用于连接节点的过滤条件尤其有价值。当至少有一个扫描行或潜在的连接对被过滤条件拒绝时，“Rows Removed” 行才会出现。 与过滤条件类似的情况出现在“有损”索引扫描中。
例如，考虑搜索包含特定点的多边形：
EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on polygon_tbl (cost=0.00..1.05 rows=1 width=32) (actual time=0.044..0.044 rows=0 loops=1)
Filter: (f1 @> '((0.5,2))'::polygon)
Rows Removed by Filter: 4
Planning time: 0.040 ms
Execution time: 0.083 ms
计划程序认为（非常正确），这个示例表太小了。为了避免索引扫描的麻烦，我们使用了一个普通的顺序扫描，其中所有行都被过滤条件拒绝了。但是如果我们强制使用索引扫描，我们会看到：
SET enable_seqscan TO off;
EXPLAIN ANALYZE SELECT * FROM polygon_tbl WHERE f1 @> polygon '(0.5,2.0)';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using gpolygonind on polygon_tbl (cost=0.13..8.15 rows=1 width=32) (actual time=0.062..0.062 rows=0 loops=1)
Index Cond: (f1 @> '((0.5,2))'::polygon)
Rows Removed by Index Recheck: 1
Planning time: 0.034 ms
Execution time: 0.144 ms
在这里，我们可以看到索引返回了一个候选行，然后通过索引条件的重新检查被拒绝了。这是因为GiST索引对于多边形包含测试是“有损失的”：它实际上返回与目标重叠的多边形行，然后我们必须对这些行进行精确的包含测试。
EXPLAIN有一个BUFFERS选项，可以与ANALYZE一起使用，以获得更多的运行时统计信息：
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=25.08..60.21 rows=10 width=244) (actual time=0.323..0.342 rows=10 loops=1)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
Buffers: shared hit=15
-> BitmapAnd (cost=25.08..25.08 rows=10 width=0) (actual time=0.309..0.309 rows=0 loops=1)
Buffers: shared hit=7
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) (actual time=0.043..0.043 rows=100 loops=1)
Index Cond: (unique1 < 100)
Buffers: shared hit=2
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..19.78 rows=999 width=0) (actual time=0.227..0.227 rows=999 loops=1)
Index Cond: (unique2 > 9000)
Buffers: shared hit=5
Planning time: 0.088 ms
Execution time: 0.423 ms
BUFFERS提供的数字有助于确定查询中哪些部分是I/O密集型的。
请记住，EXPLAIN ANALYZE实际上运行查询，任何副作用都会像往常一样发生，尽管查询可能输出的任何结果都会被丢弃，以打印EXPLAIN数据。如果要分析修改数据的查询而不更改表格，可以在之后回滚命令。
BEGIN;
EXPLAIN ANALYZE UPDATE tenk1 SET hundred = hundred + 1 WHERE unique1 < 100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Update on tenk1 (cost=5.08..230.08 rows=0 width=0) (actual time=3.791..3.792 rows=0 loops=1)
-> Bitmap Heap Scan on tenk1 (cost=5.08..230.08 rows=102 width=10) (actual time=0.069..0.513 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
Heap Blocks: exact=90
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.05 rows=102 width=0) (actual time=0.036..0.037 rows=300 loops=1)
Index Cond: (unique1 < 100)
Planning Time: 0.113 ms
Execution Time: 3.850 ms
ROLLBACK;
如上例所示，当查询是INSERT，UPDATE或DELETE命令时，应用表更改的实际工作由顶层的Insert，Update或Delete计划节点完成。这个节点下面的计划节点执行定位旧行和/或计算新数据的工作。因此，在上面的例子中，我们看到了与我们已经看到的相同类型的位图表扫描，它的输出被馈送到一个Update节点，该节点存储更新的行。值得注意的是，尽管数据修改节点可能需要相当长的运行时间（在这里，它消耗了大部分时间），但规划器目前没有将任何工作量添加到成本估算中以考虑这项工作。这是因为要执行的工作对于每个正确的查询计划都是相同的，因此不会影响规划决策。
当UPDATE或DELETE命令影响继承层次结构时，输出可能如下所示：
EXPLAIN UPDATE parent SET f2 = f2 + 1 WHERE f1 = 101;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Update on parent (cost=0.00..24.59 rows=0 width=0)
Update on parent parent_1
Update on child1 parent_2
Update on child2 parent_3
Update on child3 parent_4
-> Result (cost=0.00..24.59 rows=4 width=14)
-> Append (cost=0.00..24.54 rows=4 width=14)
-> Seq Scan on parent parent_1 (cost=0.00..0.00 rows=1 width=14)
Filter: (f1 = 101)
-> Index Scan using child1_pkey on child1 parent_2 (cost=0.15..8.17 rows=1 width=14)
Index Cond: (f1 = 101)
-> Index Scan using child2_pkey on child2 parent_3 (cost=0.15..8.17 rows=1 width=14)
Index Cond: (f1 = 101)
-> Index Scan using child3_pkey on child3 parent_4 (cost=0.15..8.17 rows=1 width=14)
Index Cond: (f1 = 101)
在这个例子中，Update节点需要考虑三个子表以及最初提到的父表。因此，有四个输入扫描子计划，每个表一个。为了清晰起见，Update节点被注释以显示将要更新的特定目标表，与相应的子计划的顺序相同。
EXPLAIN ANALYZE显示的Planning time是从解析查询并优化它生成查询计划所需的时间。它不包括解析或重写。
EXPLAIN ANALYZE显示的Execution time包括执行器启动和关闭时间，以及运行任何触发器所需的时间，但不包括解析、重写或规划时间。执行BEFORE触发器所需的时间（如果有）包括在相关的Insert、Update或Delete节点的时间中；但执行AFTER触发器所需的时间不计入其中，因为AFTER触发器在整个计划完成后触发。每个触发器（BEFORE或AFTER）的总时间也分别显示。请注意，延迟约束触发器直到事务结束才会执行，因此在EXPLAIN ANALYZE中根本不考虑它们。
4.1.3.注意事项
有两种情况下，EXPLAIN ANALYZE测量的运行时间可能与同一查询的正常执行有所偏差。首先，由于没有向客户端传递输出行，因此不包括网络传输成本和I/O转换成本。其次，EXPLAIN ANALYZE添加的测量开销可能很大，特别是在具有缓慢的gettimeofday()操作系统调用的机器上。您可以使用ux_test_timing工具来测量系统上的计时开销。
不应将EXPLAIN结果推广到与实际测试情况非常不同的情况；例如，在玩具大小的表上的结果不能假定适用于大表。规划器的成本估算不是线性的，因此它可能会选择不同的计划，具体取决于表的大小和其他因素。一个更大或更小的表可能会有不同的计划。一个极端的例子是，对于只占用一个磁盘页的表，无论是否有索引，你几乎总是会得到一个顺序扫描计划。规划器意识到，在任何情况下，处理表都需要读取一个磁盘页，因此没有必要花费额外的页读取来查看索引。（我们在上面的polygon_tbl示例中看到了这种情况。）
有些情况下，实际值和估计值可能不会很好地匹配，但并没有什么问题。一个这样的例子是，当计划节点的执行被LIMIT或类似的效果提前停止时。例如，在之前使用的LIMIT查询中：
EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000 LIMIT 2;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.29..14.71 rows=2 width=244) (actual time=0.177..0.249 rows=2 loops=1)
-> Index Scan using tenk1_unique2 on tenk1 (cost=0.29..72.42 rows=10 width=244) (actual time=0.174..0.244 rows=2 loops=1)
Index Cond: (unique2 > 9000)
Filter: (unique1 < 100)
Rows Removed by Filter: 287
Planning time: 0.096 ms
Execution time: 0.336 ms
显示 Index Scan 节点的估计成本和行数，就好像它已经运行到完成一样。但是在实际情况中，Limit节点在得到两行之后就停止了请求行，因此实际的行计数只有2行，而且运行时间比成本估计所显示的要少。这不是估计错误，只是估计值和真实值显示方式的差异。
合并连接也有可能会混淆不谨慎的人的测量偏差。如果一个合并连接已经读取完一个输入并且另一个输入的下一个键值大于另一个输入的最后一个键值，那么它将停止读取一个输入，因为没有更多的匹配，因此不需要扫描第一个输入的其余部分。这导致没有读取完整个子节点，结果类似于LIMIT中提到的结果。此外，如果外部（第一个）子节点包含具有重复键值的行，则内部（第二个）子节点将被备份并重新扫描其与该键值匹配的行的部分。EXPLAIN ANALYZE将这些重复发射的相同内部行计算为实际的额外行。当外部有许多重复项时，内部子节点计划节点的报告实际行数可能会显著大于实际内部关系中的行数。
BitmapAnd和BitmapOr节点始终将其实际行数报告为零，由于实现限制。
通常，EXPLAIN将显示规划器创建的每个计划节点。然而，有些情况下，执行器可以确定某些节点不需要执行，因为它们无法根据在规划时不可用的参数值产生任何行。（目前，这只能发生在扫描分区表的Append或MergeAppend节点的子节点中。）当发生这种情况时，这些计划节点将从EXPLAIN输出中省略，并出现“Subplans Removed: N”注释。
4.2.查询规划器使用的统计信息
4.2.1.单列统计信息
正如我们在前一节中所看到的，查询规划器需要估计查询检索的行数，以便做出良好的查询计划选择。本节提供了系统用于这些估计的统计信息的快速查看。
统计信息的一个组成部分是每个表和索引中的条目总数，以及每个表和索引占用的磁盘块数。这些信息存储在表ux_class中的列reltuples和relpages中。我们可以使用类似于以下查询的查询来查看它：
SELECT relname, relkind, reltuples, relpages
FROM ux_class
WHERE relname LIKE 'tenk1%';
relname | relkind | reltuples | relpages
----------------------+---------+-----------+----------
tenk1 | r | 10000 | 358
tenk1_hundred | i | 10000 | 30
tenk1_thous_tenthous | i | 10000 | 30
tenk1_unique1 | i | 10000 | 30
tenk1_unique2 | i | 10000 | 30
(5 rows)
在这里，我们可以看到tenk1包含10000行，它的索引也是10000行，但索引比表（不出所料）小得多。
出于效率原因，reltuples和relpages不会实时更新，因此它们通常包含略微过时的值。它们由VACUUM、ANALYZE和一些DDL命令（如CREATE INDEX）更新。不扫描整个表的VACUUM或ANALYZE操作可能不会更新它们。（通常情况下）将根据扫描的表部分逐步更新reltuples计数，从而得到近似值。无论如何，规划器都会将在ux_class中找到的值按比例缩放，以匹配当前的物理表大小，从而得到更接近的近似值。
由于WHERE子句限制要检查的行的数量，大多数查询仅检索表中的一小部分行。因此，规划器需要估计WHERE子句的选择性，即每个条件与WHERE子句中匹配的行的比例。用于此任务的信息存储在ux_statistic系统目录中。在ANALYZE和VACUUM ANALYZE命令更新ux_statistic条目时，即使在最新更新时，它们也始终是近似值。
在手动检查统计信息时，最好不要直接查看ux_statistic，而是查看其视图ux_stats。ux_stats的设计更易于阅读。此外，ux_stats可被所有人阅读，而ux_statistic仅可被超级用户阅读。（这可以防止非特权用户从统计信息中了解其他人的表内容。ux_stats视图仅限于显示当前用户可以读取的表的行。）例如，我们可以执行以下操作：
SELECT attname, inherited, n_distinct,
array_to_string(most_common_vals, E'\n') as most_common_vals
FROM ux_stats
WHERE tablename = 'road';
attname | inherited | n_distinct | most_common_vals
---------+-----------+------------+------------------------------------
name | f | -0.363388 | I- 580 Ramp+
| | | I- 880 Ramp+
| | | Sp Railroad +
| | | I- 580 +
| | | I- 680 Ramp
name | t | -0.284859 | I- 880 Ramp+
| | | I- 580 Ramp+
| | | I- 680 Ramp+
| | | I- 580 +
| | | State Hwy 13 Ramp
(2 rows)
请注意，对于同一列，显示了两行，一行对应于从road表开始的完整继承层次结构（inherited=t），另一行仅包括road表本身（inherited=f）。
使用ALTER TABLE SET STATISTICS命令按列设置每个列中most_common_vals和histogram_bounds数组中的最大条目数，或通过设置default_statistics_target配置变量来全局设置。默认限制目前为100个条目。可能允许更准确的计划估计，特别是对于具有不规则数据分布的列，但代价是在ux_statistic中消耗更多的空间，并稍微增加计算估计所需的时间。相反，对于具有简单数据分布的列，较低的限制可能已足够。
4.2.2.扩展统计信息
通常会看到慢查询运行错误的执行计划，因为在查询子句中使用的多个列是相关的。计划程序通常假定多个条件是彼此独立的，当列值相关时，这种假设是不成立的。由于常规统计信息是基于单个列的性质，因此无法捕获有关交叉列相关性的任何知识。但是，UXDB具有计算多元统计信息的能力，可以捕获此类信息。
由于可能的列组合数量非常大，因此自动计算多元统计信息是不切实际的。相反，可以创建扩展统计信息对象，更常见的称为统计信息对象，以指示服务器在有趣的列集上获取统计信息。
使用CREATE STATISTICS命令创建统计信息对象。创建此类对象仅创建表示对统计信息的兴趣的目录条目。实际数据收集由ANALYZE执行（可以是手动命令或后台自动分析）。收集的值可以ux_statistic_ext_data目录中检查。
ANALYZE基于用于计算常规单列统计信息的表行样本计算扩展统计信息。由于通过增加表或其任何列的统计目标（如前一节所述）来增加样本大小，因此更大的统计目标通常会导致更准确的扩展统计信息，以及更多的时间用于计算它们。
以下子部分描述了当前支持的扩展统计信息类型。
4.2.2.1.函数依赖关系
最简单的扩展统计信息跟踪函数依赖关系，这是在数据库规范形式的定义中使用的概念。如果知道a的值足以确定b的值，则我们说列b对列a具有函数依赖性，即没有两行具有相同的a值但不同的b值。在完全规范化的数据库中，函数依赖关系应仅存在于主键和超键上。但是，在实践中，由于各种原因，许多数据集不是完全规范化的；为了性能而有意地去规范化是一个常见的例子。即使在完全规范化的数据库中，可能存在部分相关性。在某些列之间存在部分函数依赖关系。
函数依赖的存在直接影响某些查询的准确性。如果查询包含独立列和依赖列的条件，那么对于依赖列的条件不会进一步减少结果集的大小；但是如果没有函数依赖的知识，查询规划器将假定这些条件是独立的，导致结果集大小被低估。
为了通知规划器有关函数依赖关系，ANALYZE可以收集跨列依赖关系的测量数据。评估所有列集之间的依赖关系的程度将是极其昂贵的，因此数据收集仅限于在使用dependencies选项定义的统计对象中一起出现的列组。建议仅为强相关的列组创建dependencies统计信息，以避免ANALYZE和后续查询规划中的不必要开销。
这里是收集函数依赖关系统计信息的示例：
CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes;
ANALYZE zipcodes;
SELECT stxname, stxkeys, stxddependencies
FROM ux_statistic_ext join ux_statistic_ext_data on (oid = stxoid)
WHERE stxname = 'stts';
stxname | stxkeys | stxddependencies
---------+---------+------------------------------------------
stts | 1 5 | {"1 => 5": 1.000000, "5 => 1": 0.423130}
(1 row)
在这里可以看到，第1列（邮政编码）完全确定第5列（城市），因此系数为1.0，而城市仅约42%的时间确定邮政编码，这意味着有许多城市（58%）由多个邮政编码表示。
在涉及函数依赖列的查询的选择性计算时，规划器使用依赖系数调整每个条件的选择性估计，以避免低估。
4.2.2.1.1.函数依赖的限制
函数依赖关系目前仅适用于考虑将列与常量值进行简单相等性条件比较和具有常量值的IN子句时。它们不用于改进比较两个列或将列与表达式进行比较的相等性条件的估计，也不用于范围子句、LIKE或任何其他类型的条件。
当使用函数依赖性进行估计时，规划者假设所涉及的列上的条件是兼容的，因此是冗余的。如果它们不兼容，那么正确的估计应该是零行，但是不考虑这种可能性。例如，给定如下查询：
SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';
规划器将会忽视city子句，因为它不改变选择度，这是正确的。不过，即便真地只有零行满足下面的查询，规划器也会做出同样的假设。
SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';
不过，函数依赖统计信息无法提供足够的信息来排除这种情况。
在很多实际情况中，这种假设通常是能满足的。例如，在应用程序中可能有一个GUI仅允许选择兼容的城市和邮编值用在查询中。但是如果不是这样，函数依赖可能就不是一个可行的选项。
4.2.2.2.多元可区分值计数
单列统计数据存储每列中不同值的数量。当组合多个列（例如，对于GROUP BY a, b）时，如果规划器只有单列统计数据，则估计不同值的数量经常是错误的，导致选择错误的计划。
为了改善这种估计，ANALYZE可以收集一组列的n-distinct统计数据。与以前一样，对于每个可能的列组合进行此操作是不切实际的，因此仅为使用ndistinct选项定义的统计对象中一起出现的这些列组合收集数据。将为来自列集的两个或多个列的每个可能组合收集数据。
继续上一个示例，ZIP代码表中的n-distinct计数可能如下所示：
CREATE STATISTICS stts2 (ndistinct) ON city, state, zip FROM zipcodes;
ANALYZE zipcodes;
SELECT stxkeys AS k, stxdndistinct AS nd
FROM ux_statistic_ext join ux_statistic_ext_data on (oid = stxoid)
WHERE stxname = 'stts2';
-[ RECORD 1 ]--------------------------------------------------------
k | 1 2 5
nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178}
(1 row)
这表明有三个列组合具有33178个不同值：邮政编码和州；邮政编码和城市；以及邮政编码、城市和州（它们都相等是因为仅在此表中仅有邮政编码是唯一的）。另一方面，城市和州的组合仅有27435个不同值。
建议仅在实际用于分组的列组合上创建ndistinct统计对象，并且估计组数错误导致计划不良。否则，ANALYZE循环只是浪费。
4.2.2.3.多元MCV列表
每个列存储的另一种统计数据类型是最常见值列表。这允许对单个列进行非常准确的估计，但可能会导致多个列上具有条件的查询的显著估计误差。
为了改善这种估计，ANALYZE可以在列组合上收集MCV列表。与函数依赖关系和n-distinct系数类似，对于每个可能的列组合进行此操作是不切实际的。在这种情况下更是如此，因为MCV列表（与函数依赖关系和n-distinct系数不同）确实存储常见的列值。因此，仅为使用mcv选项定义的统计对象中一起出现的这些列组合收集数据。
继续上一个示例，ZIP代码表的MCV列表可能如下所示（与简单类型的统计信息不同，需要函数来检查MCV内容）：
CREATE STATISTICS stts3 (mcv) ON city, state FROM zipcodes;
ANALYZE zipcodes;
SELECT m.* FROM ux_statistic_ext join ux_statistic_ext_data on (oid = stxoid),
ux_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';
index | values | nulls | frequency | base_frequency
-------+------------------------+-------+-----------+----------------
0 | {Washington, DC} | {f,f} | 0.003467 | 2.7e-05
1 | {Apo, AE} | {f,f} | 0.003067 | 1.9e-05
2 | {Houston, TX} | {f,f} | 0.002167 | 0.000133
3 | {El Paso, TX} | {f,f} | 0.002 | 0.000113
4 | {New York, NY} | {f,f} | 0.001967 | 0.000114
5 | {Atlanta, GA} | {f,f} | 0.001633 | 3.3e-05
6 | {Sacramento, CA} | {f,f} | 0.001433 | 7.8e-05
7 | {Miami, FL} | {f,f} | 0.0014 | 6e-05
8 | {Dallas, TX} | {f,f} | 0.001367 | 8.8e-05
9 | {Chicago, IL} | {f,f} | 0.001333 | 5.1e-05
...
(99 rows)
这表明，城市和州的最常见组合是华盛顿特区，实际频率（在样本中）约为0.35％。该组合的基础频率（从简单的每列频率计算）仅为0.0027％，导致低估两个数量级。
建议仅在实际上一起在条件中使用的列组合上创建MCV统计对象，并且由于组数的错误估计导致了错误的计划。否则，ANALYZE和计划周期只是浪费。
4.3.使用显式JOIN子句控制规划器
通过使用显式的JOIN语法，可以在一定程度上控制查询规划器。为了理解为什么这很重要，我们首先需要一些背景知识。
在一个简单的联接查询中，例如：
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
规划器可以自由地以任何顺序连接给定的表。例如，它可以生成一个查询计划，将A连接到B，使用WHERE条件a.id =b.id，然后将C连接到这个连接的表，使用另一个WHERE条件。或者它可以将B连接到C，然后将A连接到该结果。或者它可以将A连接到C，然后将它们与B连接起来，但这将是低效的，因为必须形成A和C的完整笛卡尔积，因为在WHERE子句中没有适用的条件来允许连接的优化。（在UXDB执行器中的所有连接都发生在两个输入表之间，因此必须以其中一种方式构建结果。）重要的是，这些不同的连接可能会产生语义上等价的结果，但可能具有极大的执行成本。因此，规划器将探索所有可能性，以尝试找到最有效的查询计划。
当一个查询只涉及两个或三个表时，没有太多的连接顺序需要担心。但是，随着表的数量增加，可能的连接顺序数量呈指数增长。当输入表太多时，不再实用对所有可能性进行详尽搜索，即使对于六七个表的情况，规划可能也需要很长时间。当输入表太多时，UXDB规划器将从详尽搜索切换到通过有限数量的可能性进行遗传概率搜索。（切换阈值由geqo_threshold运行时参数设置。）遗传搜索需要更少的时间，但不一定会找到最佳的计划。
当查询涉及外连接时，规划器的自由度比普通（内）连接要少。例如，考虑以下查询：
SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
尽管这个查询的限制表面上与前面的例子相似，但语义是不同的，因为必须为 A 的每一行发出一行，而这些行在 B 和 C的连接中没有匹配行。因此，规划器在这里没有连接顺序的选择：它必须将B连接到C，然后将A连接到该结果。因此，这个查询的规划时间比前一个查询要短。在其他情况下，规划器可能能够确定多个连接顺序是安全的。例如，给定：
SELECT * FROM a LEFT JOIN b ON (a.bid = b.id) LEFT JOIN c ON (a.cid = c.id);
将 A 连接到 B 或 C 都是有效的。目前，只有 FULL JOIN 完全约束了连接顺序。大多数涉及 LEFT JOIN 或RIGHT JOIN 的实际情况都可以在某种程度上重新排列。
显式的内连接语法（INNER JOIN、CROSS JOIN 或未加修饰的 JOIN）在语义上与在 FROM中列出输入关系相同，因此它不会约束连接顺序。
即使大多数种类的连接不能完全约束连接顺序，也可以指示 UXDB 查询规划器将所有 JOIN子句视为约束连接顺序的方式。例如，这三个查询在逻辑上是等价的：
SELECT * FROM a, b, c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a CROSS JOIN b CROSS JOIN c WHERE a.id = b.id AND b.ref = c.id;
SELECT * FROM a JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);
但是，如果我们告诉规划器遵循连接顺序，第二个和第三个查询的规划时间比第一个要短。这种效果对于只有三个表来说不值得担心，但对于许多表来说可能是一个救命稻草。
为了强制规划器遵循显式 JOIN 所规定的连接顺序，将运行时参数join_collapse_limit设置为 1。（下面讨论了其他可能的值。）
为了缩短搜索时间，您不需要完全约束连接顺序，因为可以在普通的 FROM 列表项中使用 JOIN 运算符。例如，考虑以下查询：
SELECT * FROM a CROSS JOIN b, c, d, e WHERE ...;
当 join_collapse_limit = 1 时，这将强制规划器在将它们连接到其他表之前将 A 连接到B，但不会在其他方面限制它的选择。在这个例子中，可能的连接顺序数量减少了5倍。
以这种方式限制规划器的搜索是一种有用的技术，既可以缩短规划时间，也可以指导规划器选择一个好的查询计划。如果规划器默认选择了一个不好的连接顺序，您可以通过JOIN 语法强制它选择更好的顺序——前提是您知道更好的顺序。建议进行实验。
一个密切相关的影响规划时间的问题是将子查询折叠到其父查询中。例如，考虑以下查询：
SELECT *
FROM x, y,
(SELECT * FROM a, b, c WHERE something) AS ss
WHERE somethingelse;
这种情况可能来自包含连接的视图的使用；视图的 SELECT规则将插入到视图引用的位置，产生一个类似上面的查询。通常，规划器将尝试将子查询折叠到父查询中，以便在单个查询中处理所有关系。这通常是有益的，但在某些情况下，可能会导致规划时间显著增加。可以通过设置subplan_numbering运行时参数来禁用子查询折叠。
将子查询合并到父查询中，得到以下查询语句：
SELECT * FROM x, y, a, b, c WHERE something AND somethingelse;
这通常比单独规划子查询要好。（例如，外部的WHERE条件可能会使得先将X和A连接起来就可以消除许多A的行，从而避免了形成完整的逻辑输出子查询的需要。）但同时，我们增加了规划时间；在这里，我们将两个独立的三元连接问题替换为一个五元连接问题。由于可能性数量的指数增长，这会产生很大的差异。规划器通过不将子查询合并到父查询中来避免陷入巨大的连接搜索问题，如果超过from_collapse_limit个FROM项将导致父查询，则不会合并子查询。您可以通过调整此运行时参数来权衡规划时间和计划质量。
from_collapse_limit和join_collapse_limit的名称相似，因为它们几乎执行相同的操作：一个控制规划器何时“展开”子查询，另一个控制何时展开显式连接。通常，您要么将join_collapse_limit设置为from_collapse_limit（以使显式连接和子查询行为类似），要么将join_collapse_limit设置为1（如果您想使用显式连接控制连接顺序）。但是，如果您想微调规划时间和运行时间之间的权衡，则可以将它们设置为不同的值。
4.4.填充数据库
在首次填充数据库时，可能需要插入大量数据。本节提供了一些建议，以使此过程尽可能高效。
4.4.1.禁用自动提交
当使用多个 INSERT 时，关闭自动提交，只在最后提交一次。（在普通 SQL 中，这意味着在开始时发出 BEGIN，在结束时发出COMMIT。某些客户端库可能会在后台执行此操作，在这种情况下，您需要确保库在您想要执行操作时执行它。）如果允许每个插入单独提交，则UXDB会为添加的每一行执行大量工作。将所有插入操作都放在一个事务中的另一个好处是，如果一行的插入失败，则插入到该点为止插入的所有行都将回滚，因此您不会被卡在部分加载的数据中。
4.4.2.使用 COPY
使用COPY命令一次性加载所有行，而不是使用一系列INSERT命令。COPY命令针对大量行的加载进行了优化；它比INSERT不太灵活，但对于大数据加载，它的开销显著较小。由于COPY是单个命令，因此如果使用此方法填充表，则无需禁用自动提交。
如果无法使用COPY，可以使用PREPARE创建一个预处理的INSERT语句，然后根据需要使用EXECUTE多次。这避免了重复解析和规划INSERT的一些开销。不同的接口以不同的方式提供此功能；在接口文档中查找“准备语句”。
请注意，使用COPY加载大量行几乎总是比使用INSERT更快，即使使用PREPARE并将多个插入批处理到单个事务中也是如此。
在与早期的CREATE TABLE或TRUNCATE命令相同的事务中使用COPY最快。在这种情况下，不需要写WAL，因为在出现错误的情况下，包含新加载数据的文件将被删除。但是，只有在wal_level为minimal时，所有命令都必须写WAL，才适用此考虑因素。
4.4.3.删除索引
如果要加载新创建的表，则最快的方法是创建表，使用COPY批量加载表的数据，然后创建表所需的任何索引。在现有数据上创建索引比逐行增量更新更快。
如果要向现有表中添加大量数据，则删除索引、加载表，然后重新创建索引可能会更快。当然，在索引缺失期间，其他用户的数据库性能可能会受到影响。在删除唯一索引之前，应三思而后行，因为唯一约束提供的错误检查将在索引缺失时丢失。
4.4.4.删除外键约束
与索引一样，可以更有效地检查外键约束“批量”，因此删除外键约束、加载数据，然后重新创建约束可能会有用。同样，在数据加载到具有现有外键约束的表中时，每个新行都需要在服务器的待处理触发器事件列表中进行记录（因为触发器的触发检查行的外键约束）。
更重要的是，当您将数据加载到具有现有外键约束的表中时，每个新行都需要服务器挂起的触发器事件列表中的一个条目(因为触发器是检查行的外键约束的触发器)。加载数百万行可能导致触发器事件队列溢出可用内存，从而导致无法忍受的交换甚至命令彻底失败。因此，在加载大量数据时，删除并重新应用外键可能是必要的，而不仅仅是可取的。如果暂时移除约束是不可接受的，那么唯一的其他办法可能是将加载操作拆分为更小的事务。
4.4.5.增加maintenance_work_mem
临时增加maintenance_work_mem配置变量可以提高性能，特别是在加载大量数据时。这将有助于加快CREATE INDEX命令和ALTER TABLE ADD FOREIGN KEY命令的速度。对于COPY本身来说，这并没有太大作用，因此这个建议只在使用上述技术之一或两者时有用。
4.4.6.增加max_wal_size
临时增加max_wal_size配置变量也可以使大数据加载更快。这是因为将大量数据加载到UXDB中会导致检查点比正常检查点频率（由checkpoint_timeout配置变量指定）更频繁地发生。每当发生检查点时，所有脏页都必须刷新到磁盘。通过在批量数据加载期间临时增加max_wal_size，可以减少所需的检查点数量。
4.4.7.禁用WAL归档和流复制
在使用WAL归档或流复制的安装中加载大量数据时，可能比处理大量增量WAL数据更快地完成加载后进行新的基本备份。为了在加载时防止增量WAL日志记录，请将wal_level设置为minimal，将archive_mode设置为off，并将max_wal_senders设置为零。但请注意，更改这些设置需要重新启动服务器，并使任何以前获取的基本备份不可用于归档恢复和备用服务器，这可能会导致数据丢失。
除了避免归档程序或WAL发送器处理WAL数据的时间外，这样做实际上会使某些命令更快，因为如果wal_level为minimal并且当前子事务（或顶级事务）创建或截断了它们更改的表或索引，则它们根本不需要编写WAL。（它们可以通过在结束时进行fsync来更便宜地保证崩溃安全性，而不是写WAL。）
4.4.8.事后运行ANALYZE
每当您显着更改表中的数据分布时，强烈建议运行ANALYZE。这包括将大量数据批量加载到表中。运行ANALYZE（或VACUUM ANALYZE）可以确保计划程序具有关于表的最新统计信息。如果没有统计信息或过时的统计信息，查询规划器可能会在查询规划期间做出错误决策，导致在具有不准确或不存在统计信息的任何表上性能不佳。请注意，如果启用了自动清理守护程序，则可能会自动运行ANALYZE。
4.4.9.关于ux_dump的一些说明
由ux_dump生成的转储脚本自动应用了上述几个指南中的一些，但并非全部。为了尽快恢复ux_dump转储，您需要手动执行一些额外的操作。（请注意，这些点适用于在恢复转储时，而不是在创建转储时。无论是使用uxsql 加载文本转储还是使用ux_restore从ux_dump存档文件加载，都适用相同的点。）
默认情况下，ux_dump使用COPY，并且在生成完整的模式和数据转储时，它会在创建索引和外键之前小心地加载数据。因此，在这种情况下，自动处理了几个指南。留给您的是：
-
设置适当的（即大于正常值的）
maintenance_work_mem和max_wal_size值。 -
如果使用 WAL 存档或流复制，请考虑在恢复期间禁用它们。为此，在加载转储之前将
archive_mode设置为off，将wal_level设置为minimal，将max_wal_senders设置为零。之后，将它们设置回正确的值并进行新的基本备份。 -
尝试使用
ux_dump和ux_restore的并行转储和恢复模式，并找到要使用的并发作业的最佳数量。通过使用-j选项并行转储和恢复应该比串行模式提供更高的性能。 -
考虑是否应将整个转储作为单个事务进行恢复。为此，请将 uxsql 或 ux_restore 的
-1或--single-transaction命令行选项传递。在使用此模式时，即使出现最小的错误也会回滚整个恢复，可能会丢弃许多小时的处理。根据数据之间的相互关系，这可能看起来比手动清理更可取，也可能不是。如果使用单个事务并关闭 WAL 存档，COPY命令将运行得最快。 -
如果数据库服务器中有多个 CPU，请考虑使用 ux_restore 的
--jobs选项。这允许并发数据加载和索引创建。 -
之后运行
ANALYZE。
仅数据转储仍将使用COPY，但它不会删除或重新创建索引，并且通常不会触及外键。因此，在加载仅数据的转储时，如果您希望使用这些技术，您需要删除和重新创建索引和外键。在加载数据时仍然有用增加max_wal_size，但不要费心增加maintenance_work_mem；相反，您可以在手动重新创建索引和外键之后这样做。完成后不要忘记ANALYZE。
您可以使用--disable-triggers选项来获得禁用外键的效果，但请注意，这会消除而不是仅仅推迟外键验证，因此如果使用它，则可能插入错误的数据。
4.5.非持久化设置
持久性是一种数据库功能，即使服务器崩溃或断电，也可以保证已提交的事务记录。然而，持久性会增加显著的数据库开销，因此，如果您的站点不需要这样的保证，UXDB可以配置为运行得更快。以下是您可以进行的配置更改，以在这种情况下提高性能。除非下面有说明，否则在数据库软件崩溃的情况下仍然保证持久性；只有操作系统突然崩溃才会在使用这些设置时存在数据丢失或损坏的风险。
-
将数据库集群的数据目录放在内存支持的文件系统中（即 RAM 磁盘）。这将消除所有数据库磁盘I/O，但将数据存储限制为可用内存的数量（以及可能的交换）。
-
关闭
fsync；无需将数据刷新到磁盘。 -
关闭
synchronous_commit；可能没有必要在每次提交时强制进行WAL 写入。这个设置会在数据库崩溃的情况下存在事务丢失的风险（尽管不会有数据损坏）。 -
关闭
full_page_writes；无需防止部分页面写入。 -
增加
max_wal_size和checkpoint_timeout；这将减少检查点的频率，但会增加/ux_wal的存储要求。 -
创建
unlogged tables以避免 WAL写入，尽管这会使表不具备崩溃安全性。
5.并行查询
UXDB 可以设计查询计划，利用多个 CPU以更快的速度回答查询。这个功能被称为并行查询。许多查询无法从并行查询中受益，要么是由于当前实现的限制，要么是因为没有比串行查询计划更快的查询计划。然而，对于可以受益的查询，从并行查询中获得的加速通常非常显著。许多查询在使用并行查询时可以运行得比串行查询快两倍以上，有些查询甚至可以运行得更快。通常，那些涉及大量数据但只返回少量行给用户的查询会受益最大。本章解释了并行查询的工作原理和可以使用的情况，以便希望使用它的用户了解预期的结果。
5.1.并行查询如何工作
当优化器确定并行查询是特定查询的最快执行策略时，它将创建一个查询计划，其中包括一个 Gather 或 Gather Merge节点。以下是一个简单的示例：
EXPLAIN SELECT * FROM uxbench_accounts WHERE filler LIKE '%x%';
QUERY PLAN
-------------------------------------------------------------------------------------
Gather (cost=1000.00..217018.43 rows=1 width=97)
Workers Planned: 2
-> Parallel Seq Scan on uxbench_accounts (cost=0.00..216018.33 rows=1 width=97)
Filter: (filler ~~ '%x%'::text)
(4 rows)
在所有情况下，Gather 或 Gather Merge 节点将仅有一个子计划，即将在并行执行的计划部分。如果 Gather 或Gather Merge节点位于计划树的顶部，则整个查询将并行执行。如果它位于计划树的其他位置，则只有它下面的计划部分将并行运行。在上面的示例中，查询仅访问一个表，因此除了Gather 节点本身之外，只有一个计划节点；由于该计划节点是 Gather 节点的子节点，因此它将并行运行。
使用 EXPLAIN，您可以查看规划器选择的工作进程数量。当在查询执行期间到达Gather 节点时，实现用户会话的进程将请求与规划器选择的工作进程数量相等的后台工作进程。规划器将考虑使用的后台工作进程数量最多为max_parallel_workers_per_gather。同时，可以同时存在的后台工作进程总数受到max_worker_processes和max_parallel_workers的限制。因此，可能会出现并行查询使用的工作进程少于计划中的数量，甚至完全没有工作进程的情况。最优的计划可能取决于可用的工作进程数量，因此可能导致查询性能不佳。如果这种情况经常发生，请考虑增加max_worker_processes和max_parallel_workers，以便可以同时运行更多的工作进程，或者减少max_parallel_workers_per_gather，以便规划器请求更少的工作进程。
成功启动的每个后台工作进程都将执行计划的并行部分。领导者也将执行该部分的计划，但它还有一个额外的责任：必须读取所有工作进程生成的元组。当计划的并行部分仅生成少量元组时，领导者通常会表现得非常像一个额外的工作进程，加速查询执行。相反，当计划的并行部分生成大量元组时，领导者可能几乎完全忙于读取工作进程生成的元组并执行任何上面级别的计划节点所需的进一步处理步骤，如Gather节点或Gather Merge节点。在这种情况下，领导者将不会执行计划的并行部分的大部分工作。
当计划的并行部分顶部的节点是Gather Merge而不是Gather时，它表示执行计划的每个进程都按排序顺序生成元组，领导者执行保持顺序的合并。相反，Gather从工作进程中以方便的任何顺序读取元组，破坏可能存在的任何排序顺序。
5.2.何时可以使用并行查询？
有几个设置可能会导致查询规划器在任何情况下都不生成并行查询计划。为了生成任何并行查询计划，必须按照以下指示配置以下设置。
max_parallel_workers_per_gather必须设置为大于零的值。这是更一般原则的特殊情况，即不应使用比通过max_parallel_workers_per_gather配置的工作进程更多的工作进程。
此外，系统不能在单用户模式下运行。由于整个数据库系统在此情况下在单个进程中运行，因此不会有后台工作进程可用。
即使在一般情况下可以生成并行查询计划，如果满足以下任何条件，则规划器不会为给定查询生成并行计划：
-
查询写入任何数据或锁定任何数据库行。如果查询包含顶层或 CTE中的数据修改操作，则不会为该查询生成并行计划。作为例外，以下命令可以使用底层
SELECT查询的并行计划，这些命令创建一个新表并填充它：-
CREATE TABLE ... AS -
SELECT INTO -
CREATE MATERIALIZED VIEW -
REFRESH MATERIALIZED VIEW
-
-
查询可能在执行过程中被暂停。在任何系统认为可能发生部分或增量执行的情况下，都不会生成并行计划。例如，使用
DECLARE CURSOR创建的游标永远不会使用并行计划。一个并行计划。同样，形如FOR x IN query LOOP .. END LOOP的PL/uxSQL循环永远不会使用并行计划，因为并行查询系统无法验证在并行查询处于活动状态时执行循环中的代码是否安全。 -
查询使用任何标记为
PARALLEL UNSAFE的函数。大多数系统定义的函数都是PARALLEL SAFE，但默认情况下，用户定义的函数会被标记为PARALLEL UNSAFE。请参见并行安全性的讨论。 -
查询正在另一个已经并行的查询内运行。例如，如果由并行查询调用的函数本身发出SQL查询，则该查询永远不会使用并行计划。这是当前实现的限制，但是删除此限制可能并不理想，因为这可能导致单个查询使用大量进程。
即使为特定查询生成了并行查询计划，也有几种情况下在执行时无法并行执行该计划。如果发生这种情况，领导者将完全自己执行Gather节点以下的计划部分，几乎就像不存在Gather节点一样。如果满足以下任何条件，则会发生这种情况：
-
由于总后台工作进程数不能超过
max_worker_processes的限制，因此无法获得任何后台工作进程。 -
由于用于并行查询的启动的总后台工作进程数不能超过
max_parallel_workers的限制，因此无法获得任何后台工作进程。 -
客户端发送带有非零获取计数的Execute消息。由于
libuxsql目前没有提供发送此类消息的方法，因此只有在使用不依赖于libuxsql的客户端时才会发生这种情况。如果这是一个频繁发生的情况，可能最好在可能的会话中将max_parallel_workers_per_gather设置为零，以避免生成在串行运行时可能不太优化的查询计划。
5.3.并行计划
由于每个工作进程都会执行计划的并行部分，因此不能简单地采用普通查询计划并使用多个工作进程运行它。每个工作进程将生成完整的输出结果集副本，因此查询不会比正常情况下运行更快，但会产生不正确的结果。相反，必须将计划的并行部分构造为查询优化器内部称为“部分计划”的内容；也就是说，必须构造出每个执行计划的进程仅生成子集输出行的计划，以确保每个所需的输出行都由合作进程中的一个生成。通常，这意味着查询的驱动表的扫描必须是并行感知的扫描。
5.3.1.并行扫描
目前支持以下类型的并行感知表扫描。
-
在并行顺序扫描中，表的块将被划分为范围并在合作进程之间共享。每个工作进程将在请求更多块范围之前完成其给定范围的扫描。
-
在并行位图堆扫描中，选择一个进程作为领导者。该进程执行一个或多个索引的扫描，并构建一个位图，指示需要访问哪些表块。然后，这些块被像并行顺序扫描一样分配给合作进程。换句话说，堆扫描是执行并行查询的一种方法，其中领导者进程扫描索引并将扫描结果分发给其他进程。
-
在并行索引扫描中，每个进程扫描索引的一个子集。这种扫描通常用于并行连接，其中每个进程都扫描一个表的索引，并将扫描结果发送到其他进程。
在并行查询中，扫描的驱动表必须是并行感知的扫描，以确保查询的正确性和性能。在某些情况下，UXDB可以自动选择并行计划，但在其他情况下，可能需要手动指定并行计划。
5.3.2.并行连接
与非并行计划一样，驱动表可以使用嵌套循环、哈希连接或合并连接连接到一个或多个其他表。连接的内部可以是任何类型的非并行计划，只要它在并行工作进程中运行是安全的，并且规划器支持。根据连接类型，内部可能也是并行计划。
-
在嵌套循环连接中，内部始终是非并行的。虽然它是完全执行的，但如果内部是索引扫描，则这是有效的，因为外部元组和查找索引中的值的循环被分配到协作进程中。
-
在合并连接中，内部始终是非并行计划，因此会完全执行。这可能是低效的，特别是如果必须执行排序，因为工作和生成的数据在每个协作进程中都会重复。
-
在哈希连接（没有“并行”前缀）中，内部由每个协作进程完全执行，以构建哈希表的相同副本。如果哈希表很大或计划很昂贵，则这可能是低效的。在并行哈希连接中，内部是一个并行哈希，它将构建共享哈希表的工作分配给协作进程。
5.3.3.并行聚合
UXDB通过两个阶段进行并行聚合。首先，参与查询并行部分的每个进程执行聚合步骤，为该进程知道的每个组生成部分结果。这在计划中反映为Partial Aggregate节点。其次，将部分结果通过Gather或Gather Merge传输到领导者。最后，领导者重新聚合所有工作进程的结果，以生成最终结果。这在计划中反映为Finalize Aggregate节点。
由于Finalize Aggregate节点在领导进程上运行，因此相对于输入行数产生相对较多的组的查询将对查询规划器产生较小的影响。例如，在最坏的情况下，Finalize Aggregate节点看到的组数可能与在Partial Aggregate阶段中由所有工作进程看到的输入行数一样多。对于这种情况，使用并行聚合显然不会带来性能优势。查询规划器在规划过程中会考虑这一点，并且不太可能在此场景中选择并行聚合。
并行聚合并非在所有情况下都受支持。每个聚合必须是安全的（请参见并行安全性），并且必须具有组合函数。如果聚合具有类型为internal的转换状态，则必须具有序列化和反序列化函数。如果任何聚合函数调用包含DISTINCT或ORDER BY子句，则不支持并行聚合，也不支持有序集聚合或当查询涉及GROUPING SETS时。只有当查询中涉及的所有连接也是计划的并行部分时，才能使用它。
5.3.4.并行追加
每当UXDB需要将来自多个源的行组合成单个结果集时，它会使用Append或MergeAppend计划节点。这通常发生在实现UNION ALL或扫描分区表时。这些节点可以像任何其他计划一样在并行计划中使用。但是，在并行计划中，规划器可能会使用Parallel Append节点。
当在并行计划中使用Append节点时，每个进程将按照它们出现的顺序执行子计划，以便所有参与进程协作执行第一个子计划，直到它完成，然后在大约相同的时间移动到第二个计划。当使用Parallel Append时，执行器将尽可能均匀地分布参与进程到其子计划中，以便同时执行多个子计划。这避免了争用，并且避免了那些从未执行它的进程中支付子计划的启动成本。
此外，与常规的Append节点不同，当在并行计划中使用Parallel Append节点时，它可以具有部分和非部分子计划。由于多次扫描非部分子计划会产生重复结果，因此非部分子计划将仅由单个进程扫描。因此，涉及追加多个结果集的计划即使没有有效的部分计划，也可以实现粗粒度并行性。例如，考虑针对分区表的查询，该查询只能通过使用不支持并行扫描的索引来有效实现。规划器可能会选择常规的Index Scan计划的Parallel Append；每个单独的索引扫描必须由单个进程执行完成，但是不同的扫描可以由不同的进程同时执行。
enable_parallel_append可用于禁用此功能。
5.3.5.并行计划提示
如果预期会产生并行计划的查询未能产生并行计划，则可以尝试减少parallel_setup_cost或parallel_tuple_cost。当然，这个计划可能会比规划器首选的串行计划更慢，但是可以通过调整这些参数来进行优化。这种情况并不总是发生。如果即使在这些设置的非常小的值（例如将它们都设置为零）下，您仍然没有得到一个并行计划，那么可能有一些原因导致查询规划器无法为您的查询生成并行计划。请参见何时可以使用并行查询？和并行安全性，了解为什么会出现这种情况的信息。
在执行并行计划时，您可以使用EXPLAIN(ANALYZE，VERBOSE)来显示每个计划节点的每个工作进程的统计信息。这可能有助于确定工作是否在所有计划节点之间均匀分布，并更普遍地了解计划的性能特征。
5.4.并行安全性
查询规划器将查询中涉及的操作分类为“并行安全”、“并行受限”或“并行不安全”。并行安全操作是指不与并行查询使用冲突的操作。并行受限操作是指不能在并行工作进程中执行，但可以在使用并行查询时在主进程中执行。因此，并行受限操作永远不会出现在“Gather”或“Gather Merge”节点下，但可以出现在包含这些节点的计划中的其他位置。并行不安全操作是指在使用并行查询时无法执行的操作，即使在主进程中也不行。当查询包含任何并行不安全的内容时，该查询的并行查询将完全被禁用。
以下操作始终是并行受限的：
-
公共表达式（CTE）的扫描。
-
临时表的扫描。
-
外部表的扫描，除非外部数据包装器具有“IsForeignScanParallelSafe”API表明否则。
-
附加了“InitPlan”的计划节点。
-
引用相关的“SubPlan”的计划节点。
5.4.1.函数和聚合的并行标记
规划器无法自动确定用户定义的函数或聚合是并行安全、并行受限还是并行不安全，因为这将需要预测函数可能执行的每个操作。一般来说，这相当于停机问题，因此是不可能的。即使对于可能实现的简单函数，我们也不会尝试，因为这将是昂贵和容易出错的。相反，所有用户定义的函数都被认为是并行受限的，除非它们明确地被标记为并行安全或并行不安全。
除非另有标记，否则函数和聚合在并行查询中是不安全的。使用CREATE FUNCTION或ALTER FUNCTION时，可以通过指定适当的PARALLEL SAFE、PARALLEL RESTRICTED或PARALLEL UNSAFE来设置标记。使用CREATE AGGREGATE时，可以使用PARALLEL选项，并将其对应值设置为SAFE、RESTRICTED或UNSAFE。
如果函数或聚合写入数据库、访问序列、甚至是暂时更改事务状态（例如建立用于捕获错误的EXCEPTION块的PL/uxSQL函数）或对设置进行持久更改，则必须将其标记为PARALLEL UNSAFE。同样，如果函数访问临时表、客户端连接状态、游标、预处理语句或系统无法在工作进程之间同步的其他后端本地状态，则必须将其标记为PARALLEL RESTRICTED。例如，由于这个原因，setseed和random是并行受限的。
通常情况下，如果将函数标记为受限或不安全时，其实际上是安全的，或者将其标记为受限时，其实际上是不安全的，那么在并行查询中使用时可能会抛出错误或产生错误的答案。C语言函数理论上可能会展现出完全未定义的行为，如果标记错误，因为系统无法保护自己免受任意C代码的攻击，但在大多数情况下，结果不会比其他函数更糟糕。如果不确定，最好将函数标记为UNSAFE。
如果在并行工作进程中执行的函数获取了领导者未持有的锁，例如通过查询未在查询中引用的表，那么这些锁将在工作进程退出时而不是事务结束时被释放。如果编写了这样的函数，并且这种行为差异对您很重要，请将此类函数标记为PARALLEL RESTRICTED，以确保它们仅在领导者中执行。
请注意，查询规划器不考虑推迟查询中涉及的并行受限函数或聚合的评估，以获得更好的计划。例如，如果应用于特定表的WHERE子句是并行受限的，则查询规划器不会考虑在计划的并行部分执行该表的扫描。在某些情况下，将该表的扫描包括在查询的并行部分中，并推迟WHERE子句的评估，使其在Gather节点之上发生，可能是可行的（甚至可能更有效），但规划器不会这样做。
6.资源限制
6.1.表空间最大限额
6.1.1.概述
表空间是数据库的逻辑划分，一个表空间只能属于一个数据库。所有的数据库对象都存放在指定的表空间中。但主要存放的是表，所以称作表空间。
表空间限额，指的是表空间的最大存储量，用于控制表空间的大小。当设置了表空间的最大限额后，在表空间中存储数据达到一定数量（告警阀值）会进行告警；继续向表空间存储数据，当达到最大限额时，系统会进行报错，提示用户表空间达到了最大限额。
UXDB默认提供了两个表空间ux_default和ux_global，ux_default存储用户数据，ux_global存储全局数据，因为二者是系统的表空间，所以不受表空间最大限额限制。
6.1.2.语法
ALTER TABLESPACE name SET ( tablespace_option = value [, ... ] ) ;
ALTER TABLESPACE name RESET ( tablespace_option [, ... ] );
表 ALTER TABLESPACE参数说明
| 参数 | 说明 |
|---|---|
| name | 一个现有表空间的名称。 |
| tablespace_option | 要设置或者重置的一个表空间参数。本次新增max_space_size参数。 |
6.1.3.示例
//设置表空间最大限额为10M bytes
uxdb=# alter tablespace myspace set (max_space_size='10M');
//重置表空间限额，即删除表空间限额
uxdb=# alter tablespace myspace reset (max_space_size);
max_space_size='10M'，其中10M代表10M bytes。另外，max_space_size的数值单位支持k K、m M、g G、t T，分别代表K bytes、M bytes、G bytes、T bytes。
6.1.4.支持平台
Linux、Windows
6.1.5.支持模式
标准模式、兼容模式、标准安全、兼容安全
6.1.6.使用限制
-
ux_default和ux_global 表空间不受限额控制。
-
集群处于恢复过程中，不受表空间限额控制，要确保恢复成功。
-
新增GUC参数和表空间（ux_tablespace）表选项，标准安全和兼容安全模式下只有uxsmo可以设置。标准模式下只有超级用户(uxdb)可以设置。
a. enable_resource_limit
开启关闭限额功能。该参数默认关闭。
b. tblspc_alarm_threshold
表空间最大限额告警阈值。取值为0.0到1.0，默认值为0.8。
c. 表空间（ux_tablespace）表选项max_space_size
用于设置表空间的最大限额值。设置方法为：alter tablespace myspace set (max_space_size='10M'); ---代表设置表空间myspace的最大限额为10M。
-
当前只有uxsmo可以创建表空间。Uxsmo可以将表空间的使用权限赋予普通用户。
6.1.7.用法示例
标准安全模式示例，如下所示。
//初始化标准安全集群的数据库实例、启动集群、连接数据库：
[uxdb@localhost bin]$ ./initdb -W -D test_sec --security
[uxdb@localhost bin]$ ./ux_ctl -D test_sec start
[uxdb@localhost bin]$ ./uxsql -d uxdb -u uxsmo
//创建表空间目录：
[uxdb@localhost ~]$ mkdir test8
[uxdb@localhost ~]$ cd test8
[uxdb@localhost test8]$pwd
/home/uxdb/test8
//创建表空间、创建表并指定表空、查看表空间信息：
uxdb=> create tablespace myspace8 location '/home/uxdb/test8';
CREATE TABLESPACE
uxdb=> create table test8 (id1 int,id2 int) tablespace myspace8;
CREATE TABLE
uxdb=> select *,ux_size_pretty(ux_tablespace_size(spcname)) from ux_tablespace where spcname='myspace8';
oid | spcname | spcowner | spcacl | spcoptions | ux_size_pretty
-------+----------+----------+--------+------------+----------------
16384 | myspace8 | 6015 | | | 19 bytes
(1 row)
//设置表空间限额、查看资源限额开关是否打开（默认关闭）并打开、重启集群或者reload：
uxdb=> alter tablespace myspace8 set (max_space_size='10M');
ALTER TABLESPACE
uxdb=> select *,ux_size_pretty(ux_tablespace_size(spcname)) from ux_tablespace where spcname='myspace8';
oid | spcname | spcowner | spcacl | spcoptions | ux_size_pretty
-------+----------+----------+--------+----------------------+----------------
16384 | myspace8 | 6015 | | {max_space_size=10M} | 19 bytes
(1 row)
uxdb=> show enable_resource_limit;
enable_resource_limit
-----------------------
off
(1 row)
uxdb=> alter system set enable_resource_limit to on;
ALTER SYSTEM
uxdb=> \q
[uxdb@localhost bin]$ ./ux_ctl -D test_sec/ reload
server signaled
//重新连接数据库：
[uxdb@localhost bin]$ ./uxsql -d uxdb -U uxsmo
//插入数据，当表空间大小超过0.8*max_size则报警告，当表空间大小超过max_size则报错：
//查询表空间大小，发现已经到达限额值：
uxdb=> insert into test8 select generate_series(1,100000),generate_series(1,100000);
INSERT 0 100000
uxdb=> insert into test8 select generate_series(1,100000),generate_series(1,100000);
WARNING: Tablespace myspace8 exceeds threshold size(maxsize * threshold)
HINT: Max size is 10485760, current size is 8355876, request size is 32768, threshold is 0.8
.............
ERROR: Tablespace myspace8 size exceeds maxsize
HINT: Max size is 10485760, current size is 10453028, request size is 32768, threshold is 0.8
uxdb=> select *,ux_size_pretty(ux_tablespace_size(spcname)) from ux_tablespace where spcname='myspace8';
oid | spcname | spcowner | spcacl | spcoptions | ux_size_pretty
-------+----------+----------+--------+----------------------+----------------
16384 | myspace8 | 6015 | | {max_space_size=10M} | 10208 kB
(1 row)
//重置表空间限额即取消限额，查看表空间大小，继续插入数据则可以成功：
uxdb=> alter tablespace myspace8 reset (max_space_size);
ALTER TABLESPACE
uxdb=> select *,ux_size_pretty(ux_tablespace_size(spcname)) from ux_tablespace where spcname='myspace8';
oid | spcname | spcowner | spcacl | spcoptions | ux_size_pretty
-------+----------+----------+--------+------------+----------------
16384 | myspace8 | 6015 | | | 10208 kB
(1 row)
uxdb=> insert into test8 select generate_series(1,100000),generate_series(1,100000);
INSERT 0 100000
兼容安全模式示例，如下所示。
//初始化兼容安全集群的数据库实例、启动集群、连接数据库：
[uxdb@localhost bin]$ ./initdb -W -D comp_test_sec --running-mode=compatible --security
[uxdb@localhost bin]$ ./ux_ctl -D comp_test_sec/ start
[uxdb@localhost bin]$ ./uxsql -d UXDB -U UXDB
//创建表空间目录：
[uxdb@localhost ~]$mkdir test9
[uxdb@localhost ~]$cd test9
[uxdb@localhost test9]pwd
/home/uxdb/test9
//创建表空间、创建表并指定表空、查看表空间信息：
UXDB=> create tablespace myspace9 location '/home/uxdb/test9';
CREATE TABLESPACE
UXDB=> create table test9 (id1 int,id2 int) tablespace myspace9;
CREATE TABLE
UXDB=> select *,ux_size_pretty(ux_tablespace_size(spcname)) from ux_tablespace where spcname='MYSPACE9';
OID | SPCNAME | SPCOWNER | SPCACL | SPCOPTIONS | UX_SIZE_PRETTY
-------+----------+----------+--------+------------+----------------
17072 | MYSPACE9 | 6015 | | | 19 bytes
//设置表空间限额、查看资源限额开关是否打开（默认关闭）并打开、重启集群或者reload：
UXDB=> alter tablespace myspace9 set (max_space_size='10M');
ALTER TABLESPACE
UXDB=> show enable_resource_limit;
enable_resource_limit
-----------------------
off
(1 row)
UXDB=> alter system set enable_resource_limit to on;
ALTER SYSTEM
UXDB=> \q
[uxdb@localhost bin]$ ./ux_ctl -D comp_test_sec/ reload
server signaled
//重新连接数据库：
[uxdb@localhost bin]$ ./uxsql -d UXDB -U UXSMO
//插入数据，当表空间大小超过0.8*max_size则报警告，当表空间大小超过max_size则报错：
//查询表空间大小，发现已经到达限额值：
UXDB=> insert into test9 select generate_series(1,100000),generate_series(1,100000);
INSERT 0 100000
UXDB=> insert into test9 select generate_series(1,100000),generate_series(1,100000);
WARNING: Tablespace MYSPACE9 exceeds threshold size(maxsize * threshold)
HINT: Max size is 10485760, current size is 8355876, request size is 32768, threshold is 0.8
.............
ERROR: Tablespace MYSPACE9 size exceeds maxsize
HINT: Max size is 10485760, current size is 10453028, request size is 32768, threshold is 0.8
UXDB=> select *,ux_size_pretty(ux_tablespace_size(spcname)) from ux_tablespace where spcname='MYSPACE9';
OID | SPCNAME | SPCOWNER | SPCACL | SPCOPTIONS | UX_SIZE_PRETTY
-------+----------+----------+--------+----------------------+----------------
17072 | MYSPACE9 | 6015 | | {MAX_SPACE_SIZE=10M} | 10208 kB
(1 row)
//重置表空间限额即取消限额，继续插入数据则可以成功，查看表空间大小：
UXDB=> alter tablespace myspace9 reset (max_space_size);
ALTER TABLESPACE
UXDB=> insert into test9 select generate_series(1,100000),generate_series(1,100000);
INSERT 0 100000
UXDB=> select *,ux_size_pretty(ux_tablespace_size(spcname)) from ux_tablespace where spcname='MYSPACE9';
OID | SPCNAME | SPCOWNER | SPCACL | SPCOPTIONS | UX_SIZE_PRETTY
-------+----------+----------+--------+------------+----------------
17072 | MYSPACE9 | 6015 | | | 16 MB
(1 row)