35.13. 操作符优化信息 · PostgreSQL 中文文档 9.3

# 35.13\. 操作符优化信息 PostgreSQL的操作符定义可以包括几个可选的子句，这些子句告诉系统一些关于该操作符特性的有用信息。在可能的情况下，都应该提供这些子句，因为它们可能为使用这个操作符的查询带来可观的速度提升。不过要注意如果你声明了这些子句，就必须确保它们是正确的！对优化子句的错误使用将导致减慢查询速度、微小的输出错误、或者其它糟糕事情。如果你对这些事情不确定的话，可以总是忽略优化子句；唯一的后果就是查询可能运行的慢一些。附加的优化子句可能在今后的PostgreSQL版本里增加。这里描述的都是9.3.1版本可以理解的。 ## 35.13.1\. `COMMUTATOR` 如果提供了`COMMUTATOR`子句，则命名一个操作符是被定义的操作符的交换符。如果有两个操作符 A, B ，对于任何可能的输入数值 x, y 都有(x A y)等于(y B x)，那么就说 A 是 B 的交换符，同样 B 也是 A 的交换符。例如，操作符`<`和`>` 对于所使用的一定的数据类型通常都是对方的交换符，而操作符`+`通常是它自身的交换符。但是操作符`-`通常没有交换符。交换操作符的左操作数与右操作数类型必须相同。所以PostgreSQL 所需要的只是一个交换符操作符的名称用以查找该交换符，那也是`COMMUTATOR` 子句里所需要的唯一的东西。给那些会在索引和连接子句里面使用的操作符提供交换符是非常关键的，因为这样就允许查询优化器"移动"这样的子句，形成所需要的不同的规划类型的形式。比如，考虑一个有类似`tab1.x = tab2.y`的 WHERE 子句的查询，这里`tab1.x`和`tab2.y`是用户定义类型，并且假设`tab2.y`上面有索引。除非优化器知道如何在`tab2.y = tab1.x`周围四处移动该子句，否则它不能生成索引扫描，因为索引扫描机制期望看到索引字段在给出的操作符左边。PostgreSQL _不会_简单地假设这是一个合法的转换，`=`的创建者必须声明这是有效的，方法是给这个操作符标记交换器信息。当你定义一个自交换的操作符时，你简单的定义它就可以了。当你定义一对交换符操作符时，事情就有一点棘手：怎样定义一个操作符的交换符指向另一个你还没有定义的操作符呢? 对这个问题有两个解决方法： * 一个方法是省略你定义的第一个操作符的`COMMUTATOR`子句，然后在第二个操作符的定义里提供一个。因为PostgreSQL知道交换操作符是成对出现的，所以当它看到第二个定义时它会自动折回并填充第一个定义里空缺的`COMMUTATOR`子句。 * 另一个更直接的方法是在两个定义里面都包含`COMMUTATOR`子句。当PostgreSQL 处理第一个定义并意识到`COMMUTATOR`指向一个不存在的操作符时会在系统表里为该操作符记录一个虚拟记录。这个虚拟的记录只有操作符名，左和右操作数类型以及结果类型是有效的，因为这些是到目前为止 PostgreSQL可以推导出来的东西。第一个操作符类记录将和这个虚拟记录连接。稍后，当你定义第二个操作符时，系统将用来自第二个操作符的信息更新该虚拟记录。如果你试图在虚拟操作符被填充之前使用它，你将只能收到一条错误信息。 ## 35.13.2\. `NEGATOR` 如果提供了`NEGATOR`子句，则命名一个操作符是被定义的操作符的否定符。如果有两个都返回布尔变量的操作符 A 和 B ，对任何可能的输入 x 和 y ，都有 (x A y) 等于 NOT(x B y)，那么说 A 是 B 的否定符。当然 B 也是 A 的否定符。例如，`<`和`>=` 对大多数数据类型是一对否定符。一个操作符不可能是它自身的否定符。不像交换符，一对单目操作符可以互为否定符；那就意味着对于所有的 x 都有 (A x) 等于 NOT(B x) ，或者类似的右目操作符的这种情况。一个操作符的否定符必须有与正定义的操作符本身一样的左和/或右操作数类型，所以就像`COMMUTATOR`一样，只有操作符名需要在`NEGATOR`子句里面给出。提供否定符对查询优化器是非常有帮助的，因为这样就允许像`NOT (x = y)` 这样的表达式简化成`x <> y`。这样的情况比你想像的要频繁的多，因为`NOT`操作可能因为其它的重排列而被引入。否定符对可以用上面交换符对中解释的相同的方法来定义。 ## 35.13.3\. `RESTRICT` 如果提供了`RESTRICT`子句，则为操作符命名一个选择性限制计算函数(注意这里是一个函数名，而不是一个操作符名)。`RESTRICT`子句只是对返回`boolean`变量的双目操作符有意义。选择性限制计算符的概念是猜测一个表中所有行的哪一部分对于目前的操作符和特定的常量将满足一个像下面这样形式的 `WHERE`条件子句。 ``` column OP constant ``` 它可以给出这种类型的`WHERE`子句可以删除多少行的一个概念，这将帮助优化器进行优化。你可能会说，如果该常量(constant)在左边怎么办?哦，那是`COMMUTATOR`干的事... 书写新的选择性限制计算函数远远超出了本章的范围，不过很幸运的是，通常你对自己的操作符只需要使用系统标准的计算器之一就行了。下面是一些标准限制计算器： | `eqsel` 用于 `=` | |:--- | | `neqsel` 用于 `<>` | | `scalarltsel` 用于 `<` 或 `<=` | | `scalargtsel` 用于 `>` 或 `>=` | 这些都是分类，看起来有点奇怪，不过如果你仔细想想，就会觉得有道理。`=`大多将只接受表中的一小部分行； `<>`大多将拒绝一小部分行。`<`接受的行取决于给出的常量落在表的该列数据值的哪一个范围里 (该值碰巧是`ANALYZE`收集并且提供给选择性计算器的信息)。`<=`在同样的常量时会接受比 `<`略微大一些的行，不过它们也非常接近，几乎不值得区别开来，尤其是无论如何也比做盲猜好得多。类似的情况也适用于`>`和`>=`。你可能常习惯于把`eqsel`或`neqsel`用于那些非常高或者非常低选择性的操作符，即使它们并非真正相等或者不相等。例如，基于只会匹配整个表中一小部分记录的假设，几何操作符约等于就使用`eqsel`。你可以把`scalarltsel`和`scalargtsel` 用于比较那些为进行范围比较被转化为数字尺度后有明显意义的数据类型。如果可能，把该数据类型增加到可以被`src/backend/utils/adt/selfuncs.c`文件里的 `convert_to_scalar()`函数理解的部分。最终，这个过程将被放到由`pg_type` 表里的一个列标识的每种类型一个的函数代替，不过目前还没有这么做。如果你没有做这些，系统仍然能工作，不过优化器的估计不会像想像的那么好。在`src/backend/utils/adt/geo_selfuncs.c`里还有为几何操作符设计的额外选择性评估函数： `areasel`, `positionsel`, `contsel`。目前，它们都只是存根，但是你还是可以使用(最后是改良)它们。 ## 35.13.4\. `JOIN` 如果提供了`JOIN`子句，则为操作符命名一个连接选择性计算器函数(是函数名，不是操作符名)。`JOIN`子句只是对返回`boolean`的双目操作符有意义。一个连接选择性计算器后面的概念是猜测一对表上的哪部分行对目前的操作符将满足下面形式的 `WHERE`子句的条件： ``` table1.column1 OP table2.column2 ``` 和`RESTRICT`子句一样，这些很有可能帮助优化器用最少的处理勾画出要采取可能的连接顺序中的哪一个。和前面一样，本节不会试图解释如何书写一个连接选择性计算器函数，但是会建议你尽可能使用一个标准的计算器： | `eqjoinsel` 用于 `=` | | `neqjoinsel` 用于 `<>` | | `scalarltjoinsel` 用于 `<` 或 `<=` | | `scalargtjoinsel` 用于 `>` 或 `>=` | | `areajoinsel` 用于基于面积的二维比较 | | `positionjoinsel` 用于基于位置的二维比较 | | `contjoinsel` 用于基于包含的二维比较 | ## 35.13.5\. `HASHES` 如果出现了`HASHES`子句，则告诉系统对于一个基于此操作符的连接可以使用 Hash 连接。 `HASHES`只对返回`boolean`的双目操作符有意义，并且实际上该操作符最好是对某种数据类型的相等操作符。 Hash 连接的假设是：对于一对散列到同样的 Hash 代码的左和右操作数值，该连接操作符只能返回真。如果两个值被放到不同的 Hash 桶里，连接将根本不比较它们，隐含地意味着连接操作符的结果一定是假。所以对于不代表相等的操作符，声明`HASHES`是没有意义的。在大多数情况下，支持两端接受同样数据类型的操作符是唯一可行的。然而，有时为两个或更多的数据类型设计兼容的hash函数也是可能的；也就是，函数将为"相等的" 值产生相同的hash代码，即使值有不同的代表。例如，当哈希整数有不容的宽度时，排列这个属性是非常简单。要标记为`HASHES`，连接操作符必须出现在一个 Hash 索引操作符类中。在创建操作符时并不强制这样，因为引用操作符类不可能还存在。但是企图在 Hash 连接中使用尚不存在的操作符类将在运行时导致失败。系统需要操作符类根据操作符的输入数据类型确定特定于该数据类型的 Hash 函数。当然，你必须在创建操作符类之前首先提供合适的 Hash 函数。在编写 Hash 函数时必须小心，因为有一些硬件相关的因素会导致错误。比如，如果你的数据类型是一个存在间隙的结构体，你就不能简单的将其传递给某个`hash_any` 函数。除非你的其它操作符能够确保这些间隙总是零(这是建议的策略)。另一个例子是在符合IEEE浮点标准的机器上，负零和正零是不同的值(不同的位模式)，但是它们被定义为比较相等。如果一个浮点值可能包含负零，那么必须使用额外的步骤来确保产生和正零相同的 Hash 值。一个可 Hash 连接的操作符必须有一个在相同操作符类中的交换符（如果两个操作符数据类型相同则是它本身，如果不同则是一个相关的相等操作符）。如果不是这样，当使用操作符时会发生规划器错误。同样，一个hash操作符类支持多种数据类型以为数据类型的每种结合提供相等操作符是一个好主意（但不是严格要求）；这允许更好的优化。 > **Note:** 在一个可 Hash 连接的操作符下层的函数必须标明 immutable 或 stable 。如果它是 volatile ，那么系统将从不在 Hash 连接中使用这些操作符。 > **Note:** 如果一个可 Hash 连接的操作符有一个下层函数标记为严格的(strict)，那么该函数必须完整：也就是说，对于任何非 NULL 输入，它应该返回 TRUE 或 FALSE ，但绝不能是 NULL 。如果不遵循这个规则，`IN`操作的 Hash 优化可能会生成错误的结果。特别是根据规范正确答案是 NULL 的时候，`IN`可能会返回 FALSE ；或者它可能生成一个错误，抱怨说它对 NULL 结果没有思想准备。 ## 35.13.6\. `MERGES` 如果出现了`MERGES`子句，则告诉系统对基于目前操作符的连接可以使用融合连接方法。 `MERGES`只是对返回`boolean`的双目操作符有意义，实际上这个操作符对于某些数据类型或者某对数据类型必须表示相等。融合连接是以这样的概念为基础的：对左边和右边的表进行排序，然后并发地扫描它们。所以，两种数据类型都必须是能够完全排序的，并且连接操作符必须只对那些落在排序顺序中的 "某个位置"的数值对成功。实际上这意味着连接操作符必须表现得像等于。但是可以对两种不同数据类型进行融合连接(只要他们逻辑相等即可)。例如，`smallint` 对`integer`的相等操作符是可以用融合连接的。只需要可以把两种数据类型排列成逻辑可比序列的排序操作符即可。要标记为`MERGES`，连接操作符必须作为`btree`索引操作符类的一个相等的成员出现。在创建操作符时并不强制这么做，因为引用操作符类不可能还存在。但是操作符不会被实际用于融合连接，除非可以找到一个匹配操作符类。 `MERGES`标志因此作为一个对规划器的提示，查找一个匹配的操作符类是值得的。可融合连接的相等操作符必须有一个在同一个操作符类中的交换符（如果两个操作数数据类型相同则是它自身，如果不同则是一个相关的相等操作符）。如果不是这样，当使用操作符时会发生规划器错误。同样，一个`btree`操作符类支持多种数据类型以为数据类型的每种结合提供相等操作符是一个好主意（但不是严格要求）；这允许更好的优化。 > **Note:** 在一个可融合连接操作符下层的函数必须标记为永久(immutable)或者稳定(stable)。如果它是易失的(volatile)，那么系统将从不在融合连接中使用这些操作符。