Postgresql 中文操作指南
38.16. Interfacing Extensions to Indexes #
到目前为止描述的过程可以定义新类型、新函数和新运算符。但是,我们还不能在新型数据类型的列上定义索引。要做到这一点,我们必须为该新型数据类型定义 operator class。在本节后面,我们将举例说明此概念:B 树索引方法的新运算符类,该类按升序绝对值顺序存储和排序复数。
The procedures described thus far let you define new types, new functions, and new operators. However, we cannot yet define an index on a column of a new data type. To do this, we must define an operator class for the new data type. Later in this section, we will illustrate this concept in an example: a new operator class for the B-tree index method that stores and sorts complex numbers in ascending absolute value order.
运算符类可以分组到 operator families 中以显示语义兼容类之间的关系。当只涉及单个数据类型时,一个运算符类就足够了,所以我们先把重点放在这种情况上,然后返回到运算符族。
Operator classes can be grouped into operator families to show the relationships between semantically compatible classes. When only a single data type is involved, an operator class is sufficient, so we’ll focus on that case first and then return to operator families.
38.16.1. Index Methods and Operator Classes #
_pg_am_表包含每个索引方法(在内部称为访问方法)的一行。对表进行常规访问的支持已内置于 PostgreSQL 中,但所有索引方法都在 _pg_am_中进行了描述。可以通过编写必要的代码然后在 _pg_am_中创建条目来添加新的索引访问方法 — 但这超出了本章的范围(请参见 Chapter 64)。
The pg_am table contains one row for every index method (internally known as access method). Support for regular access to tables is built into PostgreSQL, but all index methods are described in pg_am. It is possible to add a new index access method by writing the necessary code and then creating an entry in pg_am — but that is beyond the scope of this chapter (see Chapter 64).
索引方法的例程不会直接知道索引方法将操作的数据类型。相反,operator class 标识索引方法使用特定数据类型所需的一组操作。运算符类之所以这样命名,是因为它们指定的一件事是可以与索引一起使用的 WHERE 子句运算符(即,可以转换为索引扫描限定条件)。运算符类还可以指定索引方法的内部操作所需的 support function,但不要直接对应于可以与索引一起使用的任何 WHERE 子句运算符。
The routines for an index method do not directly know anything about the data types that the index method will operate on. Instead, an operator class identifies the set of operations that the index method needs to use to work with a particular data type. Operator classes are so called because one thing they specify is the set of WHERE-clause operators that can be used with an index (i.e., can be converted into an index-scan qualification). An operator class can also specify some support function that are needed by the internal operations of the index method, but do not directly correspond to any WHERE-clause operator that can be used with the index.
可以为相同的数据类型和索引方法定义多个运算符类。通过这样做,可以为单个数据类型定义多组索引语义。例如,B 树索引需要为其处理的每种数据类型定义一个排序顺序。让复数数据类型拥有一个按复绝对值排序的 B 树运算符类,另一个按实部排序的运算符类等等,可能会很有用。通常,其中一个运算符类将被认为最常用,并将标记为该数据类型和索引方法的默认运算符类。
It is possible to define multiple operator classes for the same data type and index method. By doing this, multiple sets of indexing semantics can be defined for a single data type. For example, a B-tree index requires a sort ordering to be defined for each data type it works on. It might be useful for a complex-number data type to have one B-tree operator class that sorts the data by complex absolute value, another that sorts by real part, and so on. Typically, one of the operator classes will be deemed most commonly useful and will be marked as the default operator class for that data type and index method.
相同的运算符类名称可用于多个不同的索引方法(例如,B 树和哈希索引方法都有名为 int4_ops 的运算符类),但每个此类都是一个独立的实体,并且必须单独定义。
The same operator class name can be used for several different index methods (for example, both B-tree and hash index methods have operator classes named int4_ops), but each such class is an independent entity and must be defined separately.
38.16.2. Index Method Strategies #
与运算符类关联的运算符由“策略编号”标识,该编号用于在运算符类的上下文中标识每个运算符的语义。例如,B 树对键强制执行严格的从较小到较大的排序顺序,因此“小于”和“大于或等于”之类的运算符对于 B 树来说很有趣。由于 PostgreSQL 允许用户定义运算符,因此 PostgreSQL 无法查看运算符的名称(例如 < 或 >=)并说出它是什么类型的比较。相反,索引方法定义了一组“策略”,可以将其视为通用运算符。每个运算符类指定哪种实际运算符对应于特定数据类型的每个策略和索引语义解释。
The operators associated with an operator class are identified by “strategy numbers”, which serve to identify the semantics of each operator within the context of its operator class. For example, B-trees impose a strict ordering on keys, lesser to greater, and so operators like “less than” and “greater than or equal to” are interesting with respect to a B-tree. Because PostgreSQL allows the user to define operators, PostgreSQL cannot look at the name of an operator (e.g., < or >=) and tell what kind of comparison it is. Instead, the index method defines a set of “strategies”, which can be thought of as generalized operators. Each operator class specifies which actual operator corresponds to each strategy for a particular data type and interpretation of the index semantics.
B-tree 索引方法定义了五个策略,如 Table 38.3所示。
The B-tree index method defines five strategies, shown in Table 38.3.
Table 38.3. B-Tree Strategies
Operation |
Strategy Number |
less than |
1 |
less than or equal |
2 |
equal |
3 |
greater than or equal |
4 |
greater than |
5 |
哈希索引仅支持相等性比较,因此仅使用一种策略,如 Table 38.4所示。
Hash indexes support only equality comparisons, and so they use only one strategy, shown in Table 38.4.
Table 38.4. Hash Strategies
Operation |
Strategy Number |
equal |
1 |
GiST 索引更为灵活:它们根本没有一组固定的策略。相反,每个特定 GiST 运算符类的“一致性”支持例程根据自己的喜好解释策略编号。作为一个示例,几个内置 GiST 索引运算符类索引二维几何对象,提供了 Table 38.5中所示的“R-tree”策略。其中四个是真正的二维测试(重叠、相同、包含、被包含);其中四个仅考虑 X 方向;其他四个在 Y 方向提供相同的测试。
GiST indexes are more flexible: they do not have a fixed set of strategies at all. Instead, the “consistency” support routine of each particular GiST operator class interprets the strategy numbers however it likes. As an example, several of the built-in GiST index operator classes index two-dimensional geometric objects, providing the “R-tree” strategies shown in Table 38.5. Four of these are true two-dimensional tests (overlaps, same, contains, contained by); four of them consider only the X direction; and the other four provide the same tests in the Y direction.
Table 38.5. GiST Two-Dimensional “R-tree” Strategies
Table 38.5. GiST Two-Dimensional “R-tree” Strategies
Operation |
Strategy Number |
strictly left of |
1 |
does not extend to right of |
2 |
overlaps |
3 |
does not extend to left of |
4 |
strictly right of |
5 |
same |
6 |
contains |
7 |
contained by |
8 |
does not extend above |
9 |
strictly below |
10 |
strictly above |
11 |
does not extend below |
12 |
SP-GiST 索引在灵活性上类似于 GiST 索引:它们没有一组固定的策略。相反,每个运算符类的支持例程根据运算符类的定义解释策略编号。作为一个示例,内置运算符类为点使用的策略编号如 Table 38.6所示。
SP-GiST indexes are similar to GiST indexes in flexibility: they don’t have a fixed set of strategies. Instead the support routines of each operator class interpret the strategy numbers according to the operator class’s definition. As an example, the strategy numbers used by the built-in operator classes for points are shown in Table 38.6.
Table 38.6. SP-GiST Point Strategies
Operation |
Strategy Number |
strictly left of |
1 |
strictly right of |
5 |
same |
6 |
contained by |
8 |
strictly below |
10 |
strictly above |
11 |
GIN 索引类似于 GiST 和 SP-GiST 索引,因为它们也没有一组固定的策略。相反,每个运算符类的支持例程根据运算符类的定义解释策略编号。作为一个示例,内置运算符类为数组使用的策略编号如 Table 38.7所示。
GIN indexes are similar to GiST and SP-GiST indexes, in that they don’t have a fixed set of strategies either. Instead the support routines of each operator class interpret the strategy numbers according to the operator class’s definition. As an example, the strategy numbers used by the built-in operator class for arrays are shown in Table 38.7.
Table 38.7. GIN Array Strategies
Operation |
Strategy Number |
overlap |
1 |
contains |
2 |
is contained by |
3 |
equal |
4 |
BRIN 索引类似于 GiST、SP-GiST 和 GIN 索引,因为它们也没有一组固定的策略。相反,每个运算符类的支持例程根据运算符类的定义解释策略编号。作为一个示例,内置 _Minmax_运算符类使用的策略编号如 Table 38.8所示。
BRIN indexes are similar to GiST, SP-GiST and GIN indexes in that they don’t have a fixed set of strategies either. Instead the support routines of each operator class interpret the strategy numbers according to the operator class’s definition. As an example, the strategy numbers used by the built-in Minmax operator classes are shown in Table 38.8.
Table 38.8. BRIN Minmax Strategies
Operation |
Strategy Number |
less than |
1 |
less than or equal |
2 |
equal |
3 |
greater than or equal |
4 |
greater than |
5 |
请注意,上面列出的所有运算符都返回布尔值。在实践中,所有定义为索引方法搜索运算符的运算符必须返回 boolean_类型,因为它们必须出现在 _WHERE_子句的顶层才能与索引一起使用。(某些索引访问方法还支持 _ordering operators,通常不返回布尔值;该功能在 Section 38.16.7中进行了讨论。)
Notice that all the operators listed above return Boolean values. In practice, all operators defined as index method search operators must return type boolean, since they must appear at the top level of a WHERE clause to be used with an index. (Some index access methods also support ordering operators, which typically don’t return Boolean values; that feature is discussed in Section 38.16.7.)
38.16.3. Index Method Support Routines #
策略通常对于系统来说并不足以了解如何使用索引。实际上,索引方法需要额外的支持例程才能工作。例如,B 树索引方法必须能够比较两个键并确定一个键是否大于、等于或小于另一个键。类似地,哈希索引方法必须能够计算键值的哈希码。这些操作与 SQL 命令中的限定条件中使用的运算符不对应;它们是索引方法在内部使用的管理例程。
Strategies aren’t usually enough information for the system to figure out how to use an index. In practice, the index methods require additional support routines in order to work. For example, the B-tree index method must be able to compare two keys and determine whether one is greater than, equal to, or less than the other. Similarly, the hash index method must be able to compute hash codes for key values. These operations do not correspond to operators used in qualifications in SQL commands; they are administrative routines used by the index methods, internally.
与策略一样,运算符类标识哪些特定函数应针对给定的数据类型和语义解释来扮演这些角色。索引方法定义它需要的一组函数,而运算符类通过将它们分配给索引方法指定的“支持函数号”来标识要使用的正确函数。
Just as with strategies, the operator class identifies which specific functions should play each of these roles for a given data type and semantic interpretation. The index method defines the set of functions it needs, and the operator class identifies the correct functions to use by assigning them to the “support function numbers” specified by the index method.
此外,某些 opclass 允许用户指定控制其行为的参数。每个内置索引访问方法都有一个可选的 options 支持函数,该函数定义一组特定于 opclass 的参数。
Additionally, some opclasses allow users to specify parameters which control their behavior. Each builtin index access method has an optional options support function, which defines a set of opclass-specific parameters.
B-树需要一个比较支持函数,并且允许以操作符类作者的选择在 Table 38.9 中所示提供四个额外的支持函数。这些支持函数的需求在 Section 67.3 中有进一步的解释。
B-trees require a comparison support function, and allow four additional support functions to be supplied at the operator class author’s option, as shown in Table 38.9. The requirements for these support functions are explained further in Section 67.3.
Table 38.9. B-Tree Support Functions
Function |
Support Number |
Compare two keys and return an integer less than zero, zero, or greater than zero, indicating whether the first key is less than, equal to, or greater than the second |
1 |
Return the addresses of C-callable sort support function(s) (optional) |
2 |
Compare a test value to a base value plus/minus an offset, and return true or false according to the comparison result (optional) |
3 |
Determine if it is safe for indexes that use the operator class to apply the btree deduplication optimization (optional) |
4 |
Define options that are specific to this operator class (optional) |
5 |
哈希索引需要一个支持函数,并且允许以操作符类作者的选择在 Table 38.10 中所示提供两个。
Hash indexes require one support function, and allow two additional ones to be supplied at the operator class author’s option, as shown in Table 38.10.
Table 38.10. Hash Support Functions
Function |
Support Number |
Compute the 32-bit hash value for a key |
1 |
Compute the 64-bit hash value for a key given a 64-bit salt; if the salt is 0, the low 32 bits of the result must match the value that would have been computed by function 1 (optional) |
2 |
Define options that are specific to this operator class (optional) |
3 |
GiST 索引有 11 个支持函数,其中 6 个是可选的,如图 Table 38.11 所示。(更多信息请见 Chapter 68。)
GiST indexes have eleven support functions, six of which are optional, as shown in Table 38.11. (For more information see Chapter 68.)
Table 38.11. GiST Support Functions
Function |
Description |
Support Number |
consistent |
determine whether key satisfies the query qualifier |
1 |
union |
compute union of a set of keys |
2 |
compress |
compute a compressed representation of a key or value to be indexed (optional) |
3 |
decompress |
compute a decompressed representation of a compressed key (optional) |
4 |
penalty |
compute penalty for inserting new key into subtree with given subtree’s key |
5 |
picksplit |
determine which entries of a page are to be moved to the new page and compute the union keys for resulting pages |
6 |
same |
compare two keys and return true if they are equal |
7 |
distance |
determine distance from key to query value (optional) |
8 |
fetch |
compute original representation of a compressed key for index-only scans (optional) |
9 |
options |
define options that are specific to this operator class (optional) |
10 |
sortsupport |
provide a sort comparator to be used in fast index builds (optional) |
11 |
SP-GiST 索引有 6 个支持函数,其中 1 个是可选的,如图 Table 38.12 所示。(更多信息请见 Chapter 69。)
SP-GiST indexes have six support functions, one of which is optional, as shown in Table 38.12. (For more information see Chapter 69.)
Table 38.12. SP-GiST Support Functions
Function |
Description |
Support Number |
config |
provide basic information about the operator class |
1 |
choose |
determine how to insert a new value into an inner tuple |
2 |
picksplit |
determine how to partition a set of values |
3 |
inner_consistent |
determine which sub-partitions need to be searched for a query |
4 |
leaf_consistent |
determine whether key satisfies the query qualifier |
5 |
options |
define options that are specific to this operator class (optional) |
6 |
GIN 索引有 7 个支持函数,其中 4 个是可选的,如图 Table 38.13 所示。(更多信息请见 Chapter 70。)
GIN indexes have seven support functions, four of which are optional, as shown in Table 38.13. (For more information see Chapter 70.)
Table 38.13. GIN Support Functions
Function |
Description |
Support Number |
compare |
compare two keys and return an integer less than zero, zero, or greater than zero, indicating whether the first key is less than, equal to, or greater than the second |
1 |
extractValue |
extract keys from a value to be indexed |
2 |
extractQuery |
extract keys from a query condition |
3 |
consistent |
determine whether value matches query condition (Boolean variant) (optional if support function 6 is present) |
4 |
comparePartial |
compare partial key from query and key from index, and return an integer less than zero, zero, or greater than zero, indicating whether GIN should ignore this index entry, treat the entry as a match, or stop the index scan (optional) |
5 |
triConsistent |
determine whether value matches query condition (ternary variant) (optional if support function 4 is present) |
6 |
options |
define options that are specific to this operator class (optional) |
7 |
BRIN 索引有 5 个基本支持函数,其中 1 个是可选的,如图 Table 38.14 所示。某些版本的基本函数需要提供附加的支持函数。(更多信息请见 Section 71.3。)
BRIN indexes have five basic support functions, one of which is optional, as shown in Table 38.14. Some versions of the basic functions require additional support functions to be provided. (For more information see Section 71.3.)
Table 38.14. BRIN Support Functions
Function |
Description |
Support Number |
opcInfo |
return internal information describing the indexed columns' summary data |
1 |
add_value |
add a new value to an existing summary index tuple |
2 |
consistent |
determine whether value matches query condition |
3 |
union |
compute union of two summary tuples |
4 |
options |
define options that are specific to this operator class (optional) |
5 |
不同于搜索运算符,支持函数返回特定索引方法期望的任何数据类型;例如在 B 树的比较函数的情况下,为有符号整数。每个支持函数的参数数量和类型同样依赖于索引方法。对于 B 树和哈希,比较和哈希支持函数采用与运算符类中包含的运算符相同的数据类型作为输入,但对于大多数 GiST、SP-GiST、GIN 和 BRIN 支持函数,情况并非如此。
Unlike search operators, support functions return whichever data type the particular index method expects; for example in the case of the comparison function for B-trees, a signed integer. The number and types of the arguments to each support function are likewise dependent on the index method. For B-tree and hash the comparison and hashing support functions take the same input data types as do the operators included in the operator class, but this is not the case for most GiST, SP-GiST, GIN, and BRIN support functions.
38.16.4. An Example #
既然我们了解了这些概念,这里有创建新的操作符类的示例。(此示例的可工作副本位于源代码分发版中的 src/tutorial/complex.c 和 src/tutorial/complex.sql。)操作符类封装按绝对值顺序对复数排序的操作符,因此我们选择名称 complex_abs_ops。首先,我们需要一组操作符。在 Section 38.14 中讨论了定义操作符的过程。对于 B-树上的操作符类,我们需要以下操作符:
Now that we have seen the ideas, here is the promised example of creating a new operator class. (You can find a working copy of this example in src/tutorial/complex.c and src/tutorial/complex.sql in the source distribution.) The operator class encapsulates operators that sort complex numbers in absolute value order, so we choose the name complex_abs_ops. First, we need a set of operators. The procedure for defining operators was discussed in Section 38.14. For an operator class on B-trees, the operators we require are:
最不容易出错的定义相关比较运算符的方法是先编写 B 树比较支持函数,然后将其他函数作为对支持函数的一行包装器编写。这会减少在最坏情况下得到不一致结果的可能性。按照这一方法,我们首先编写:
The least error-prone way to define a related set of comparison operators is to write the B-tree comparison support function first, and then write the other functions as one-line wrappers around the support function. This reduces the odds of getting inconsistent results for corner cases. Following this approach, we first write:
#define Mag(c) ((c)->x*(c)->x + (c)->y*(c)->y)
static int
complex_abs_cmp_internal(Complex *a, Complex *b)
{
double amag = Mag(a),
bmag = Mag(b);
if (amag < bmag)
return -1;
if (amag > bmag)
return 1;
return 0;
}
现在,小于函数看起来像:
Now the less-than function looks like:
PG_FUNCTION_INFO_V1(complex_abs_lt);
Datum
complex_abs_lt(PG_FUNCTION_ARGS)
{
Complex *a = (Complex *) PG_GETARG_POINTER(0);
Complex *b = (Complex *) PG_GETARG_POINTER(1);
PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) < 0);
}
其他四个函数仅在如何将内部函数的结果与零进行比较方面不同。
The other four functions differ only in how they compare the internal function’s result to zero.
接下来,我们将函数和基于函数的运算符声明为 SQL:
Next we declare the functions and the operators based on the functions to SQL:
CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
AS 'filename', 'complex_abs_lt'
LANGUAGE C IMMUTABLE STRICT;
CREATE OPERATOR < (
leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
指定正确的交换运算符和否定运算符以及合适的限制和联接选择性函数非常重要,否则优化器将无法有效利用索引。
It is important to specify the correct commutator and negator operators, as well as suitable restriction and join selectivity functions, otherwise the optimizer will be unable to make effective use of the index.
这里还发生了其他值得注意的事情:
Other things worth noting are happening here:
下一步是注册 B 树所要求的支持例程。实现这一点的 C 代码示例与包含运算符函数的文件相同。我们就是如此声明函数的:
The next step is the registration of the support routine required by B-trees. The example C code that implements this is in the same file that contains the operator functions. This is how we declare the function:
CREATE FUNCTION complex_abs_cmp(complex, complex)
RETURNS integer
AS 'filename'
LANGUAGE C IMMUTABLE STRICT;
既然我们有了所要求的运算符和支持例程,我们就可以最终创建运算符类了:
Now that we have the required operators and support routine, we can finally create the operator class:
CREATE OPERATOR CLASS complex_abs_ops
DEFAULT FOR TYPE complex USING btree AS
OPERATOR 1 < ,
OPERATOR 2 <= ,
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
FUNCTION 1 complex_abs_cmp(complex, complex);
大功告成!现在应该可以在 complex 列上创建和使用 B 树索引了。
And we’re done! It should now be possible to create and use B-tree indexes on complex columns.
我们可以对运算符条目书写得更为详尽,例如:
We could have written the operator entries more verbosely, as in:
OPERATOR 1 < (complex, complex) ,
但是在运算符采用与我们为运算符类定义的相同数据类型时则不需要这样做。
but there is no need to do so when the operators take the same data type we are defining the operator class for.
上述示例假设您希望将这个新运算符类设为 complex 数据类型的默认 B 树运算符类。如果不希望这样做,只需省略单词 DEFAULT 即可。
The above example assumes that you want to make this new operator class the default B-tree operator class for the complex data type. If you don’t, just leave out the word DEFAULT.
38.16.5. Operator Classes and Operator Families #
到目前为止,我们隐式地假设一个运算符类只处理一种数据类型。虽然在一个特定索引列中当然只能有一种数据类型,但通常对将索引列与不同数据类型的值进行比较的操作进行索引很有用。此外,如果将跨数据类型运算符与运算符类结合使用,那么经常会遇到这样的情况:另一个数据类型有它自己的相关运算符类。显式地建立相关类之间的连接非常有用,因为这可以帮助规划器优化 SQL 查询(特别是对于 B 树运算符类而言,因为该规划器包含了大量有关如何使用它们方面的知识)。
So far we have implicitly assumed that an operator class deals with only one data type. While there certainly can be only one data type in a particular index column, it is often useful to index operations that compare an indexed column to a value of a different data type. Also, if there is use for a cross-data-type operator in connection with an operator class, it is often the case that the other data type has a related operator class of its own. It is helpful to make the connections between related classes explicit, because this can aid the planner in optimizing SQL queries (particularly for B-tree operator classes, since the planner contains a great deal of knowledge about how to work with them).
为了满足这些需求,PostgreSQL 使用了一个名为 operator family 的概念。一个运算符族包含一个或多个运算符类,还可以包含可索引的运算符以及属于整个族的但不属于族中的任何单个类的相应支持函数。我们称这些运算符和函数在族中是“松散的”,而不是被绑定到特定类中。通常,每个运算符类包含单数据类型运算符,而跨数据类型运算符则在族中松散。
To handle these needs, PostgreSQL uses the concept of an operator family. An operator family contains one or more operator classes, and can also contain indexable operators and corresponding support functions that belong to the family as a whole but not to any single class within the family. We say that such operators and functions are “loose” within the family, as opposed to being bound into a specific class. Typically each operator class contains single-data-type operators while cross-data-type operators are loose in the family.
运算符族中的所有运算符和函数必须具有兼容的语义,其中兼容性要求由索引方法设定。因此,您可能想知道为什么还要将族的特定子集单独列为运算符类;事实上,对于许多目的而言,类划分是不相关的,而族是唯一的有趣分组。定义运算符类的目的是指定支持某个特定索引需要族的多少部分。如果存在一个使用运算符类的索引,那么在不删除索引的情况下不能删除该运算符类——但可以删除运算符族的其他部分,即其他运算符类和松散运算符。因此,应指定一个运算符类来包含与使用特定数据类型上的索引合理需要的运算符和函数的最小集合,然后可以将相关但非必要的运算符作为运算符族的松散成员添加进来。
All the operators and functions in an operator family must have compatible semantics, where the compatibility requirements are set by the index method. You might therefore wonder why bother to single out particular subsets of the family as operator classes; and indeed for many purposes the class divisions are irrelevant and the family is the only interesting grouping. The reason for defining operator classes is that they specify how much of the family is needed to support any particular index. If there is an index using an operator class, then that operator class cannot be dropped without dropping the index — but other parts of the operator family, namely other operator classes and loose operators, could be dropped. Thus, an operator class should be specified to contain the minimum set of operators and functions that are reasonably needed to work with an index on a specific data type, and then related but non-essential operators can be added as loose members of the operator family.
作为一个示例,PostgreSQL 有一个内置的 B 树运算符族 integer_ops,它包括 int8_ops、int4_ops 和 int2_ops 运算符类,分别用于 bigint (int8)、integer (int4) 和 smallint (int2) 列上的索引。该族还包含跨数据类型比较运算符,允许比较这两种类型中的任何两种类型,这样就可以使用另一种类型的比较值来搜索在一种类型上的索引。该族可以通过以下定义进行复制:
As an example, PostgreSQL has a built-in B-tree operator family integer_ops, which includes operator classes int8_ops, int4_ops, and int2_ops for indexes on bigint (int8), integer (int4), and smallint (int2) columns respectively. The family also contains cross-data-type comparison operators allowing any two of these types to be compared, so that an index on one of these types can be searched using a comparison value of another type. The family could be duplicated by these definitions:
CREATE OPERATOR FAMILY integer_ops USING btree;
CREATE OPERATOR CLASS int8_ops
DEFAULT FOR TYPE int8 USING btree FAMILY integer_ops AS
-- standard int8 comparisons
OPERATOR 1 < ,
OPERATOR 2 <= ,
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
FUNCTION 1 btint8cmp(int8, int8) ,
FUNCTION 2 btint8sortsupport(internal) ,
FUNCTION 3 in_range(int8, int8, int8, boolean, boolean) ,
FUNCTION 4 btequalimage(oid) ;
CREATE OPERATOR CLASS int4_ops
DEFAULT FOR TYPE int4 USING btree FAMILY integer_ops AS
-- standard int4 comparisons
OPERATOR 1 < ,
OPERATOR 2 <= ,
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
FUNCTION 1 btint4cmp(int4, int4) ,
FUNCTION 2 btint4sortsupport(internal) ,
FUNCTION 3 in_range(int4, int4, int4, boolean, boolean) ,
FUNCTION 4 btequalimage(oid) ;
CREATE OPERATOR CLASS int2_ops
DEFAULT FOR TYPE int2 USING btree FAMILY integer_ops AS
-- standard int2 comparisons
OPERATOR 1 < ,
OPERATOR 2 <= ,
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
FUNCTION 1 btint2cmp(int2, int2) ,
FUNCTION 2 btint2sortsupport(internal) ,
FUNCTION 3 in_range(int2, int2, int2, boolean, boolean) ,
FUNCTION 4 btequalimage(oid) ;
ALTER OPERATOR FAMILY integer_ops USING btree ADD
-- cross-type comparisons int8 vs int2
OPERATOR 1 < (int8, int2) ,
OPERATOR 2 <= (int8, int2) ,
OPERATOR 3 = (int8, int2) ,
OPERATOR 4 >= (int8, int2) ,
OPERATOR 5 > (int8, int2) ,
FUNCTION 1 btint82cmp(int8, int2) ,
-- cross-type comparisons int8 vs int4
OPERATOR 1 < (int8, int4) ,
OPERATOR 2 <= (int8, int4) ,
OPERATOR 3 = (int8, int4) ,
OPERATOR 4 >= (int8, int4) ,
OPERATOR 5 > (int8, int4) ,
FUNCTION 1 btint84cmp(int8, int4) ,
-- cross-type comparisons int4 vs int2
OPERATOR 1 < (int4, int2) ,
OPERATOR 2 <= (int4, int2) ,
OPERATOR 3 = (int4, int2) ,
OPERATOR 4 >= (int4, int2) ,
OPERATOR 5 > (int4, int2) ,
FUNCTION 1 btint42cmp(int4, int2) ,
-- cross-type comparisons int4 vs int8
OPERATOR 1 < (int4, int8) ,
OPERATOR 2 <= (int4, int8) ,
OPERATOR 3 = (int4, int8) ,
OPERATOR 4 >= (int4, int8) ,
OPERATOR 5 > (int4, int8) ,
FUNCTION 1 btint48cmp(int4, int8) ,
-- cross-type comparisons int2 vs int8
OPERATOR 1 < (int2, int8) ,
OPERATOR 2 <= (int2, int8) ,
OPERATOR 3 = (int2, int8) ,
OPERATOR 4 >= (int2, int8) ,
OPERATOR 5 > (int2, int8) ,
FUNCTION 1 btint28cmp(int2, int8) ,
-- cross-type comparisons int2 vs int4
OPERATOR 1 < (int2, int4) ,
OPERATOR 2 <= (int2, int4) ,
OPERATOR 3 = (int2, int4) ,
OPERATOR 4 >= (int2, int4) ,
OPERATOR 5 > (int2, int4) ,
FUNCTION 1 btint24cmp(int2, int4) ,
-- cross-type in_range functions
FUNCTION 3 in_range(int4, int4, int8, boolean, boolean) ,
FUNCTION 3 in_range(int4, int4, int2, boolean, boolean) ,
FUNCTION 3 in_range(int2, int2, int8, boolean, boolean) ,
FUNCTION 3 in_range(int2, int2, int4, boolean, boolean) ;
请注意,此定义“重载”了运算符策略和支持函数编号:每个编号在族中多次出现。只要特定编号的每个实例具有不同的输入数据类型,这种重载就是允许的。输入类型都等于运算符类输入类型的实例是该运算符类的主要运算符和支持函数,并且在大多数情况下应该声明为运算符类的部分,而不是作为族的松散成员。
Notice that this definition “overloads” the operator strategy and support function numbers: each number occurs multiple times within the family. This is allowed so long as each instance of a particular number has distinct input data types. The instances that have both input types equal to an operator class’s input type are the primary operators and support functions for that operator class, and in most cases should be declared as part of the operator class rather than as loose members of the family.
在 B-树操作符族中,该族中的所有操作符都必须在兼容的顺序中进行排序,如 Section 67.2 中详细指定的。对于该族中的每个操作符,必须存在一个支持函数,具有与该操作符相同的两种输入数据类型。建议族是完整的,即,对于每种数据类型的组合,都包括所有操作符。每个操作符类应仅包括其数据类型的非交叉类型操作符和支持函数。
In a B-tree operator family, all the operators in the family must sort compatibly, as is specified in detail in Section 67.2. For each operator in the family there must be a support function having the same two input data types as the operator. It is recommended that a family be complete, i.e., for each combination of data types, all operators are included. Each operator class should include just the non-cross-type operators and support function for its data type.
要构建一个多数据类型哈希运算符族,必须为族支持的每种数据类型创建兼容的哈希支持函数。此处,兼容性是指这些函数保证对任何两个值返回相同的哈希代码,这些值被族的相等运算符视为相等,即使这些值是不同类型的也是如此。当类型具有不同的物理表示方式时,通常很难执行此操作,但在某些情况下可以执行。此外,通过隐式或二进制强制转换转换从运算符族中表示为一种数据类型到另一种数据类型的值,不得更改计算的哈希值。请注意,每个数据类型只有一个支持函数,而不是每个相等运算符一个。建议创建一个完整的族,即,为每个数据类型组合提供一个相等运算符。每个运算符类应仅包括非跨类型相等运算符和对数据类型的支持函数。
To build a multiple-data-type hash operator family, compatible hash support functions must be created for each data type supported by the family. Here compatibility means that the functions are guaranteed to return the same hash code for any two values that are considered equal by the family’s equality operators, even when the values are of different types. This is usually difficult to accomplish when the types have different physical representations, but it can be done in some cases. Furthermore, casting a value from one data type represented in the operator family to another data type also represented in the operator family via an implicit or binary coercion cast must not change the computed hash value. Notice that there is only one support function per data type, not one per equality operator. It is recommended that a family be complete, i.e., provide an equality operator for each combination of data types. Each operator class should include just the non-cross-type equality operator and the support function for its data type.
GiST、SP-GiST 和 GIN 索引没有任何显式的跨数据类型操作概念。所支持的运算符集只是给定运算符类的主要支持函数可以处理的任何内容。
GiST, SP-GiST, and GIN indexes do not have any explicit notion of cross-data-type operations. The set of operators supported is just whatever the primary support functions for a given operator class can handle.
在 BRIN 中,需求取决于提供运算符类的框架。对于基于 minmax 的运算符类,所需行为与 B 树运算符族的行为相同:族中的所有运算符都必须兼容排序,并且强制转换不能更改关联的排序顺序。
In BRIN, the requirements depends on the framework that provides the operator classes. For operator classes based on minmax, the behavior required is the same as for B-tree operator families: all the operators in the family must sort compatibly, and casts must not change the associated sort ordering.
Note
在 PostgreSQL 8.3 之前,没有运算符系列的概念,所以任何打算与索引一起使用的跨数据类型运算符都必须直接绑定到索引的操作符类中。虽然此方法仍然可行,但它已被弃用,因为它使得索引的依赖性太广泛,并且当两个数据类型都具有同一操作符系列中的运算符时,计划程序可以更有效地处理跨数据类型的比较。
Prior to PostgreSQL 8.3, there was no concept of operator families, and so any cross-data-type operators intended to be used with an index had to be bound directly into the index’s operator class. While this approach still works, it is deprecated because it makes an index’s dependencies too broad, and because the planner can handle cross-data-type comparisons more effectively when both data types have operators in the same operator family.
38.16.6. System Dependencies on Operator Classes #
除了是否可以将运算符与索引一起使用之外,PostgreSQL 还使用运算符类来推断更多方面的运算符特性。因此,即使您无意为任何数据类型列建立索引,您可能也想创建运算符类。
PostgreSQL uses operator classes to infer the properties of operators in more ways than just whether they can be used with indexes. Therefore, you might want to create operator classes even if you have no intention of indexing any columns of your data type.
特别是,有 ORDER BY 和 DISTINCT 等 SQL 特性,它们需要比较和排序值。为了在一个用户定义的数据类型中实现这些特性,PostgreSQL 将查找该数据类型的默认 B 树运算符类。此运算符类的“等于”成员定义了系统对 GROUP BY 和 DISTINCT 值相等的理解,并且运算符类施加的排序顺序定义了默认 ORDER BY 顺序。
In particular, there are SQL features such as ORDER BY and DISTINCT that require comparison and sorting of values. To implement these features on a user-defined data type, PostgreSQL looks for the default B-tree operator class for the data type. The “equals” member of this operator class defines the system’s notion of equality of values for GROUP BY and DISTINCT, and the sort ordering imposed by the operator class defines the default ORDER BY ordering.
如果还没有数据类型的默认 B 树运算符类,系统将查找一个默认哈希运算符类。但由于该类型的运算符类仅提供相等性,因此它只能支持分组而不是排序。
If there is no default B-tree operator class for a data type, the system will look for a default hash operator class. But since that kind of operator class only provides equality, it is only able to support grouping not sorting.
如果还没有数据类型的默认运算符类,当您尝试将这些 SQL 特性与该数据类型一起使用时,您将收到“无法识别排序运算符”之类的错误。
When there is no default operator class for a data type, you will get errors like “could not identify an ordering operator” if you try to use these SQL features with the data type.
Note
在 7.4 之前的 PostgreSQL 版本中,排序和分组操作将隐式使用名为 =、< 和 > 的运算符。依赖默认运算符类的新行为避免了对具有一定名称的运算符的行为做出任何假设。
In PostgreSQL versions before 7.4, sorting and grouping operations would implicitly use operators named =, <, and >. The new behavior of relying on default operator classes avoids having to make any assumption about the behavior of operators with particular names.
可以通过在 USING 选项中指定类的小于运算符,以按非默认 B 树运算符类进行排序,例如
Sorting by a non-default B-tree operator class is possible by specifying the class’s less-than operator in a USING option, for example
SELECT * FROM mytable ORDER BY somecol USING ~<~;
或者,在 USING 中指定类的大于运算符将选择降序排序。
Alternatively, specifying the class’s greater-than operator in USING selects a descending-order sort.
对用户定义类型数组的比较也依赖于该类型默认 B 树运算符类定义的语义。如果没有默认 B 树运算符类,但有默认哈希运算符类,那么支持数组相等性,但不支持排序比较。
Comparison of arrays of a user-defined type also relies on the semantics defined by the type’s default B-tree operator class. If there is no default B-tree operator class, but there is a default hash operator class, then array equality is supported, but not ordering comparisons.
需要更多数据类型特定知识的另一项 SQL 特性是对窗口函数的 RANGE offset PRECEDING/FOLLOWING 框架选项(见 Section 4.2.8)。对于诸如以下查询:
Another SQL feature that requires even more data-type-specific knowledge is the RANGE offset PRECEDING/FOLLOWING framing option for window functions (see Section 4.2.8). For a query such as
SELECT sum(x) OVER (ORDER BY x RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING)
FROM mytable;
知道如何按 x 排序是不够的;数据库还必须了解如何“从 x 的当前行值中减去 5”或“加上 10”以识别当前窗口框架的边界。将结果边界与 x 的其他行值进行比较可以使用 B 树运算符类提供的比较运算符来完成,该运算符类定义了 ORDER BY 排序,但加法和减法运算符不是运算符类的一部分,所以应该使用哪种运算符?硬编码该选择是不合适的,因为不同的排序顺序(不同的 B 树运算符类)可能需要不同的行为。因此,B 树运算符类可以指定一个 in_range 支持函数,该函数封装了与其排序顺序相符的加法和减法行为。它甚至可以提供多个 in_range 支持函数,以防有多个数据类型可以作为 RANGE 子句中的偏移量。如果与窗口的 ORDER BY 子句关联的 B 树运算符类没有匹配的 in_range 支持函数,则 RANGE offset PRECEDING/FOLLOWING 选项不受支持。
it is not sufficient to know how to order by x; the database must also understand how to “subtract 5” or “add 10” to the current row’s value of x to identify the bounds of the current window frame. Comparing the resulting bounds to other rows' values of x is possible using the comparison operators provided by the B-tree operator class that defines the ORDER BY ordering — but addition and subtraction operators are not part of the operator class, so which ones should be used? Hard-wiring that choice would be undesirable, because different sort orders (different B-tree operator classes) might need different behavior. Therefore, a B-tree operator class can specify an in_range support function that encapsulates the addition and subtraction behaviors that make sense for its sort order. It can even provide more than one in_range support function, in case there is more than one data type that makes sense to use as the offset in RANGE clauses. If the B-tree operator class associated with the window’s ORDER BY clause does not have a matching in_range support function, the RANGE offset PRECEDING/FOLLOWING option is not supported.
另一重要的一点是,出现在散列运算符族中的等运算符是散列连接、散列聚合及相关优化的候选者。此处散列运算符族必不可少,因为它可识别要使用的散列函数。
Another important point is that an equality operator that appears in a hash operator family is a candidate for hash joins, hash aggregation, and related optimizations. The hash operator family is essential here since it identifies the hash function(s) to use.
38.16.7. Ordering Operators #
有些索引访问方法(目前仅有 GiST 和 SP-GiST)支持 ordering operators 的概念。到目前为止,我们一直在讨论的是 search operators。搜索运算符是指可针对索引进行搜索的运算符,以查找满足 WHERE indexed_column operator constant 的所有行。请注意,不会对返回匹配行的顺序做出任何承诺。而排序运算符不会限制可返回的行集,而是确定它们自己的顺序。排序运算符是指可针对索引进行扫描以按 ORDER BY indexed_column operator constant 所示顺序返回行的运算符。 定义排序运算符的方式如此,其原因是它支持最近邻搜索,如果运算符是衡量距离的运算符。例如,如下查询:
Some index access methods (currently, only GiST and SP-GiST) support the concept of ordering operators. What we have been discussing so far are search operators. A search operator is one for which the index can be searched to find all rows satisfying WHERE indexed_column operator constant. Note that nothing is promised about the order in which the matching rows will be returned. In contrast, an ordering operator does not restrict the set of rows that can be returned, but instead determines their order. An ordering operator is one for which the index can be scanned to return rows in the order represented by ORDER BY indexed_column operator constant. The reason for defining ordering operators that way is that it supports nearest-neighbor searches, if the operator is one that measures distance. For example, a query like
SELECT * FROM places ORDER BY location <-> point '(101,456)' LIMIT 10;
找到了与给定目标点最近的十个点。位置列上的 GiST 索引能有效地做到这一点,因为 <→ 是一个排序运算符。
finds the ten places closest to a given target point. A GiST index on the location column can do this efficiently because <→ is an ordering operator.
搜索运算符必须返回布尔结果,而排序运算符通常会返回其他一些类型,如测量距离时使用的 float 或 numeric。此类型通常与正在编制索引的数据类型不同。为避免对不同数据类型行为的硬编码假设,排序运算符的定义需要指定一个 B 树运算符族,该运算符族指定结果数据类型的排序顺序。正如我在上一部分所说,B 树运算符族定义了 PostgreSQL 的排序概念,因此这是一个自然的表示方法。由于点 <→ 运算符返回 float8,因此它可以像这样在运算符类创建命令中进行指定:
While search operators have to return Boolean results, ordering operators usually return some other type, such as float or numeric for distances. This type is normally not the same as the data type being indexed. To avoid hard-wiring assumptions about the behavior of different data types, the definition of an ordering operator is required to name a B-tree operator family that specifies the sort ordering of the result data type. As was stated in the previous section, B-tree operator families define PostgreSQL’s notion of ordering, so this is a natural representation. Since the point <→ operator returns float8, it could be specified in an operator class creation command like this:
OPERATOR 15 <-> (point, point) FOR ORDER BY float_ops
其中 float_ops 是内置的运算符族,其中包括对 float8 的操作。此声明表明该索引能够按照 <→ 运算符增大值返回有序行。
where float_ops is the built-in operator family that includes operations on float8. This declaration states that the index is able to return rows in order of increasing values of the <→ operator.
38.16.8. Special Features of Operator Classes #
运营商类别有两项特殊功能尚未讨论,主要是因为它们对于最常用的索引方法不是有用的。
There are two special features of operator classes that we have not discussed yet, mainly because they are not useful with the most commonly used index methods.
通常,将运算符声明为运算符类(或族)的成员意味着索引方法可以使用该运算符获取精确满足 WHERE 条件的行集。例如:
Normally, declaring an operator as a member of an operator class (or family) means that the index method can retrieve exactly the set of rows that satisfy a WHERE condition using the operator. For example:
SELECT * FROM table WHERE integer_column < 4;
可以使用整数列上的 B 树索引精确满足。但在某些情况下,索引可用作匹配行的非精确指南。例如,如果 GiST 索引仅存储几何对象的外接框,那么它无法精确满足 WHERE 条件,该条件测试非矩形对象(如多边形)之间的重叠。不过,我们可以使用索引找到其外接框与目标对象的外接框重叠的对象,然后仅对索引找到的对象执行精确的重叠测试。如果此场景适用,则该索引对于该运算符来说是“有损失”的。索引方法通过在行可能或可能确实满足查询条件时返回 recheck 标志来实现有损索引搜索。然后,核心系统将针对检索到的行测试原始查询条件以查看是否应该将其作为有效的匹配返回。如果索引保证返回所有必需的行以及通过执行原始运算符调用可以消除的一些附加行,那么这种方法就有效。支持有损搜索的索引方法(目前为 GiST、SP-GiST 和 GIN)允许各个运算符类的支持函数设置复查标志,因此这基本上是一种运算符类功能。
can be satisfied exactly by a B-tree index on the integer column. But there are cases where an index is useful as an inexact guide to the matching rows. For example, if a GiST index stores only bounding boxes for geometric objects, then it cannot exactly satisfy a WHERE condition that tests overlap between nonrectangular objects such as polygons. Yet we could use the index to find objects whose bounding box overlaps the bounding box of the target object, and then do the exact overlap test only on the objects found by the index. If this scenario applies, the index is said to be “lossy” for the operator. Lossy index searches are implemented by having the index method return a recheck flag when a row might or might not really satisfy the query condition. The core system will then test the original query condition on the retrieved row to see whether it should be returned as a valid match. This approach works if the index is guaranteed to return all the required rows, plus perhaps some additional rows, which can be eliminated by performing the original operator invocation. The index methods that support lossy searches (currently, GiST, SP-GiST and GIN) allow the support functions of individual operator classes to set the recheck flag, and so this is essentially an operator-class feature.
再次考虑这种情况,即我们仅将复杂对象(如多边形)的外接框存储在索引中。在这种情况下,将整个多边形存储在索引项中并没有多大价值——我们也可以存储 box 类型的简单对象。此情况由 CREATE OPERATOR CLASS 中的 STORAGE 选项表示:我们将编写类似内容:
Consider again the situation where we are storing in the index only the bounding box of a complex object such as a polygon. In this case there’s not much value in storing the whole polygon in the index entry — we might as well store just a simpler object of type box. This situation is expressed by the STORAGE option in CREATE OPERATOR CLASS: we’d write something like:
CREATE OPERATOR CLASS polygon_ops
DEFAULT FOR TYPE polygon USING gist AS
...
STORAGE box;
目前,只有 GiST、SP-GiST、GIN 和 BRIN 索引方法支持与列数据类型不同的 STORAGE 类型。当使用 STORAGE 时,GiST compress 和 decompress 支持例程必须处理数据类型转换。同样,当存储类型不同时,SP-GiST 也需要一个 compress 支持函数来转换到存储类型;如果 SP-GiST opclass 也支持检索数据,则反向转换必须由 consistent 函数来处理。在 GIN 中,STORAGE 类型标识“键”值类型,该类型通常不同于被索引列的类型——例如,整数数组列的运算符类可能具有仅为整数的键。GIN extractValue 和 extractQuery 支持例程负责从索引值中提取键。BRIN 与 GIN 类似:STORAGE 类型标识存储的摘要值类型,运算符类的支持程序负责正确解释摘要值。
At present, only the GiST, SP-GiST, GIN and BRIN index methods support a STORAGE type that’s different from the column data type. The GiST compress and decompress support routines must deal with data-type conversion when STORAGE is used. SP-GiST likewise requires a compress support function to convert to the storage type, when that is different; if an SP-GiST opclass also supports retrieving data, the reverse conversion must be handled by the consistent function. In GIN, the STORAGE type identifies the type of the “key” values, which normally is different from the type of the indexed column — for example, an operator class for integer-array columns might have keys that are just integers. The GIN extractValue and extractQuery support routines are responsible for extracting keys from indexed values. BRIN is similar to GIN: the STORAGE type identifies the type of the stored summary values, and operator classes' support procedures are responsible for interpreting the summary values correctly.