37.13. 运算符优化信息

PostgreSQL 运算符定义可以包括几个可选的子句,这些子句告诉系统有关运算符行为的有用信息。应当在适当的时候提供这些子句,因为它们可以大大提高使用运算符的查询的执行速度。但是,如果提供它们,则必须确保它们是正确的!错误使用优化子句可能导致查询缓慢,输出错误或其他不良情况。如果您不确定优化子句,则可以始终忽略该子句。唯一的结果是查询的运行速度可能比所需的慢。

PostgreSQL 的 Future 版本中可能会添加其他优化条款。此处描述的内容都是 10.13 版可以理解的内容。

37.13.1. COMMUTATOR

COMMUTATOR子句(如果提供)将命名运算符,该运算符是要定义的运算符的换向器。我们说如果对于所有可能的 Importing 值 x,y(x A y)等于(y B x),则算符 A 是算符 B 的交换子。注意,B 也是 A 的交换子。例如,特定数据类型的运算符<>通常是彼此的交换子,而+通常与其自身可交换。但是运算符-通常不与任何东西交换。

可交换运算符的左操作数类型与其换向器的右操作数类型相同,反之亦然。因此,换向器运算符的名称就是 PostgreSQL 查找换向器所需要的全部,而这一切都需要在COMMUTATOR子句中提供。

为在索引和连接子句中使用的运算符提供换向器信息至关重要,因为这使查询优化器可以将这样的子句“翻转”到不同计划类型所需的形式。例如,考虑一个带有 WHERE 子句的查询,例如tab1.x = tab2.y,其中tab1.xtab2.y是用户定义的类型,并假定tab2.y已被索引。除非可以确定如何将子句翻转到tab2.y = tab1.x,否则优化器无法生成索引扫描,因为索引扫描机制希望看到给定运算符左侧的索引列。 PostgreSQL 不会仅仅假设这是一个有效的转换— =运算符的创建者必须通过用交换子信息标记运算符来指定它是有效的。

在定义自交换运算符时,只需执行此操作即可。在定义一对可交换运算符时,事情有些棘手:要定义的第一个运算符如何引用尚未定义的另一个?有两个解决此问题的方法:

  • 一种方法是在您定义的第一个运算符中省略COMMUTATOR子句,然后在第二个运算符的定义中提供一个。由于 PostgreSQL 知道交换运算符是成对出现的,因此当它看到第二个定义时,它将自动返回并在第一个定义中填写缺少的COMMUTATOR子句。

  • 另一种更直接的方法是在两个定义中都包含COMMUTATOR子句。当 PostgreSQL 处理第一个定义并意识到COMMUTATOR表示不存在的运算符时,系统将在系统目录中为该运算符创建一个虚拟条目。该伪条目将仅对运算符名称,左侧和右侧操作数类型以及结果类型具有有效数据,因为这是 PostgreSQL 此时可以推导出的全部信息。第一个操作员的目录条目将链接到该虚拟条目。稍后,当您定义第二个运算符时,系统将使用第二个定义中的附加信息更新虚拟条目。如果尝试在填充虚拟运算符之前使用它,则会收到一条错误消息。

37.13.2. NEGATOR

NEGATOR子句(如果提供)将命名一个运算符,该运算符是要定义的运算符的否定符。我们说如果对于所有可能的 Importingx,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操作可能由于其他重排而被插入。

可以使用与上述换向器对相同的方法来定义取反器对。

37.13.3. RESTRICT

RESTRICT子句(如果提供)为操作员命名了一个限制选择性估计函数。 (请注意,这是一个函数名称,而不是运算符名称.)RESTRICT子句仅对返回boolean的二进制运算符有意义。限制选择性估计器背后的想法是猜测表中哪几行满足以下形式的WHERE子句条件:

column OP constant

用于当前运算符和特定的常量值。这有助于优化器了解一些具有这种形式的WHERE子句将消除多少行的信息。 (如果常量在左边,会发生什么呢?您可能会想知道?那是COMMUTATOR用于...的原因之一.)

编写新的限制选择性估计函数远远超出了本章的范围,但是幸运的是,对于许多您自己的运算符,您通常可以只使用系统的标准估计器之一。这些是标准限制估计量:

eqsel =
neqsel <>
scalarltsel代表<<=
scalargtsel代表>>=

这些是类别似乎有些奇怪,但是如果您考虑一下,它们就很有意义。 =通常只接受表中一小部分的行; <>通常只会拒绝一小部分。 <将接受一个小数,该分数取决于给定常数在该表列的值范围内的位置(恰好是ANALYZE收集的信息,可供选择性估计器使用)。对于相同的比较常数,<=将接受比<稍大的分数,但是它们足够接近以至于不值得区分,特别是因为无论如何我们都不可能做得比粗略的猜测更好。类似的说明适用于>>=

对于具有非常高或非常低选择性的运算符,即使它们实际上不是相等或不相等的,您也可以经常使用eqselneqsel。例如,假设等式几何运算符通常只匹配表中一小部分条目,就使用eqsel

您可以使用scalarltselscalargtsel进行数据类型的比较,这些数据类型具有一些明智的转换方式,可以转换为数值标量以进行范围比较。如果可能,将数据类型添加到src/backend/utils/adt/selfuncs.c中的函数convert_to_scalar()理解的数据类型中。 (最终,应将此功能替换为通过pg_type系统目录的列标识的按数据类型的功能;但这尚未发生.)如果不执行此操作,则仍然可以使用,但优化程序的估算值不会那么好。

src/backend/utils/adt/geo_selfuncs.careaselpositionselcontsel中还有其他针对几何 operator 设计的选择性估计函数。在撰写本文时,这些只是存根,但您可能仍想使用它们(甚至更好,对其进行改进)。

37.13.4. JOIN

JOIN子句(如果提供)为操作员命名联接选择性估计函数。 (请注意,这是一个函数名称,而不是运算符名称.)JOIN子句仅对返回boolean的二进制运算符有意义。连接选择性估计器背后的想法是猜测一对表中行的哪一部分将满足WHERE子句形式的条件:

table1.column1 OP table2.column2

对于当前的运算符。与RESTRICT子句一样,它可以让优化器找出几个可能的连接序列中哪一个可能花费最少的工作,从而极大地帮助了优化器。

和以前一样,本章将不尝试解释如何编写联接选择性估计器函数,而仅建议您使用一种标准估计器(如果适用):

eqjoinsel =
neqjoinsel <>
scalarltjoinsel代表<<=
scalargtjoinsel代表>>=
areajoinsel用于基于二维区域的比较
positionjoinsel用于基于位置的 2D 比较
contjoinsel用于基于 2D 包含的比较

37.13.5. HASHES

HASHES子句(如果存在)告诉系统允许使用哈希联接方法基于该运算符进行联接。 HASHES仅对返回boolean的二进制运算符有意义,实际上,该运算符必须表示某些数据类型或一对数据类型的相等性。

哈希联接的基础假设是,联接运算符只能对哈希到同一哈希代码的一对左值和右值返回 true。如果将两个值放在不同的哈希存储桶中,则联接将根本不会比较它们,这隐含地假定联接运算符的结果必须为 false。因此,对于不表示某种形式相等的运算符,指定HASHES毫无意义。在大多数情况下,仅支持在两侧采用相同数据类型的运算符支持散列。但是,有时可以为两个或更多数据类型设计兼容的哈希函数;也就是说,即使值具有不同的表示形式,这些函数也会为“相等”的值生成相同的哈希码。例如,在散列不同宽度的整数时,安排此属性非常简单。

要标记为HASHES,联接运算符必须出现在哈希索引运算符族中。创建运算符时不会强制执行此操作,因为当然引用操作符族尚不存在。但是,如果不存在此类运算符系列,则尝试在哈希联接中使用运算符将在运行时失败。系统需要操作员家族来查找操作员 Importing 数据类型的特定于数据类型的哈希函数。当然,在创建运算符系列之前,还必须创建合适的哈希函数。

准备散列函数时应格外小心,因为有一些依赖于机器的方式可能无法完成正确的操作。例如,如果您的数据类型是一个结构,其中可能有一些令人讨厌的填充位,则不能简单地将整个结构传递给hash_any。 (除非您编写其他运算符和函数以确保未使用的位始终为零,这是推荐的策略.)另一个示例是,在符合 IEEE 浮点标准的计算机上,负零和正零是不同的值(不同的位模式),但它们被定义为相等。如果浮点值可能包含负零,则需要采取额外的步骤来确保它产生与正零相同的哈希值。

散列可连接运算符必须具有出现在同一运算符系列中的换向符(如果两个操作数数据类型相同,则为自身,或者如果不同,则为相关的相等运算符)。如果不是这种情况,则在使用操作员时可能会发生计划程序错误。同样,对于支持多种数据类型的哈希运算符家族来说,这是一个好主意(但并非严格要求),以为数据类型的每种组合提供相等运算符。这样可以实现更好的优化。

Note

哈希可连接运算符基础的函数必须标记为不可变或稳定的。如果它是易失性的,则系统将永远不会尝试将运算符用于哈希联接。

Note

如果可哈希连接的运算符具有标记为严格的基础函数,则该函数还必须是完整的:也就是说,对于任何两个非空 Importing,它应返回 true 或 false,从不为 null。如果不遵循此规则,则IN操作的哈希优化可能会产生错误的结果。 (具体来说,IN可能返回 false,其中根据标准的正确答案将为 null;否则它可能会产生错误,抱怨它没有为 null 结果做准备.)

37.13.6. MERGES

MERGES子句(如果存在)告诉系统允许使用 merge-join 方法进行基于此运算符的联接。 MERGES仅对返回boolean的二进制运算符有意义,实际上,该运算符必须表示某些数据类型或一对数据类型的相等性。

合并联接基于将左手表和右手表排序,然后并行扫描它们的想法。因此,这两种数据类型都必须能够完全排序,并且联接运算符必须是仅对按排序 Sequences 位于“相同位置”的值对成功的运算符。实际上,这意味着联接运算符必须表现得像相等。但是,可以合并联接两个不同的数据类型,只要它们在逻辑上是兼容的。例如,smallint -vers- integer相等运算符是可合并合并的。我们只需要将两个数据类型都放入逻辑兼容序列的排序运算符。

要标记为MERGES,联接运算符必须显示为btree索引运算符族的相等成员。创建运算符时不会强制执行此操作,因为当然引用操作符族尚不存在。但是除非可以找到匹配的运算符系列,否则运算符实际上不会用于合并联接。 MERGES标志因此向计划者提示,值得寻找匹配的运算符系列。

可合并合并运算符必须具有出现在同一运算符族中的换向器(如果两个操作数数据类型相同,则为自身,如果不同,则为相关的相等运算符)。如果不是这种情况,则在使用操作员时可能会发生计划程序错误。同样,对于支持多种数据类型的btree运算符家族是一个好主意(但并非严格要求),它可以为每种数据类型组合提供相等的运算符。这样可以实现更好的优化。

Note

可合并合并运算符的基础函数必须标记为不可变或稳定的。如果它是易失性的,则系统将永远不会尝试使用运算符进行合并联接。