Postgresql 中文操作指南

67.2. Behavior of B-Tree Operator Classes #

如图所示，btree 运算符类必须提供五个比较运算符 <、⇐、=、>= 和 >。有人可能认为 <> 也应该是运算符类的一部分，但事实并非如此，因为在索引搜索中使用 <> WHERE 子句几乎毫无用处。（出于某些目的，计划程序将 <> 视为与 btree 运算符类相关；但它找到运算符是通过 = 运算符的否定链接，而不是通过 pg_amop。）

As shown in Table 38.3, a btree operator class must provide five comparison operators, <, ⇐, =, >= and >. One might expect that <> should also be part of the operator class, but it is not, because it would almost never be useful to use a <> WHERE clause in an index search. (For some purposes, the planner treats <> as associated with a btree operator class; but it finds that operator via the = operator’s negator link, rather than from pg_amop.)

当多种数据类型共享近乎相同排序语义时，它们的运算符类可以组合到一个运算符族中。这样做是有利的，因为它允许规划器推断跨类型比较。族中的每个运算符类都应该包含针对其输入数据类型的单类型运算符（和关联的支持函数），而跨类型比较运算符和支持函数则在族中是“松散”的。建议在族中包括一组完整的跨类型运算符，从而确保规划器可以表示从传递性推论出的任何比较条件。

When several data types share near-identical sorting semantics, their operator classes can be grouped into an operator family. Doing so is advantageous because it allows the planner to make deductions about cross-type comparisons. Each operator class within the family should contain the single-type operators (and associated support functions) for its input data type, while cross-type comparison operators and support functions are “loose” in the family. It is recommendable that a complete set of cross-type operators be included in the family, thus ensuring that the planner can represent any comparison conditions that it deduces from transitivity.

btree 运算符族必须满足一些基本假设：

There are some basic assumptions that a btree operator family must satisfy:

其他三个运算符根据 = 和 < 以明显的方式定义，并且必须与它们一致。

The other three operators are defined in terms of = and < in the obvious way, and must act consistently with them.

对于支持多种数据类型的运算符族，当 A、B、C 从族中的任何数据类型获取时，必须满足上述定律。传递定律最难确保，因为在跨类型情况下，它们表示两个或三个不同运算符行为一致的声明。例如，将 float8 和 numeric 放入同一个运算符族中是行不通的，至少不会使用 numeric 值转换为 float8 以与 float8 进行比较的当前语义。由于 float8 的精度有限，这意味着不同的 numeric 值将与相同的 float8 值相比较，因此传递定律将失败。

For an operator family supporting multiple data types, the above laws must hold when A, B, C are taken from any data types in the family. The transitive laws are the trickiest to ensure, as in cross-type situations they represent statements that the behaviors of two or three different operators are consistent. As an example, it would not work to put float8 and numeric into the same operator family, at least not with the current semantics that numeric values are converted to float8 for comparison to a float8. Because of the limited accuracy of float8, this means there are distinct numeric values that will compare equal to the same float8 value, and thus the transitive law would fail.

对于多数据类型族而言，另一个要求是，包括在运算符族中的数据类型之间定义的任何隐含或二进制强制转换都不得更改关联的排序顺序。

Another requirement for a multiple-data-type family is that any implicit or binary-coercion casts that are defined between data types included in the operator family must not change the associated sort ordering.

很明显，btree 索引为何需要这些定律在单一数据类型内成立：如果没有它们，将无法对其键进行排序。此外，使用不同数据类型的比较键进行索引搜索要求比较在两种数据类型之间合理地进行。族中扩展到三个或更多数据类型并不是 btree 索引机制本身严格要求的，但是规划器依赖它们进行优化。

It should be fairly clear why a btree index requires these laws to hold within a single data type: without them there is no ordering to arrange the keys with. Also, index searches using a comparison key of a different data type require comparisons to behave sanely across two data types. The extensions to three or more data types within a family are not strictly required by the btree index mechanism itself, but the planner relies on them for optimization purposes.