查问布局的精髓 都在这里了！ PostgreSQL

引见

SQL 是一种申明性言语：一个查问会指定要检索的内容，但不指定如何检索它。

任何查问都可以经过多种方式来口头。在解析树中的每个操作，都有多个可选的口头方式。例如，您可以经过读取整个表并摈弃不须要的行，来检索表中的特定记载，也可以经常使用索引来查找与查问婚配的行。数据集一直成对衔接。衔接顺序的变动会发生少量可选的口头门路。而后有多种方法可以将两组行衔接在一同。例如，您可以一一访问第一个汇合中的行，而后在另一个汇合中查找婚配的行，或许可以先对两个汇合启动排序，而后将它们兼并在一同。不同的方法在某些状况下成果更好，而在其余状况下成果更差。

最优方案的口头速度，或许比非最优方案要快几个数量级。这也就是为什么提升解析查问的布局器是系统中最复杂的组件之一。

方案树

口头方案也可以示意为一棵树，但其节点是数据上的物理操作，而不是逻辑操作。

假设参数处于开启形态，则完整的方案树将显示在主机信息日志中。这是十分不实际践的，由于日志会十分错乱。更繁难的决定是经常使用 EXPLAIN 命令：

 schemaname tablename pg_tables tableowner ORDERBY tablenameQUERY Sortcost  widthSortKey: crelnameNested   cost  width Filter:noid  crelnamespaceSeqScan  pg_class ccost  widthFilter:relkind ::pg_get_userbyidrelowner::name SeqScan  pg_namespace ncost  width 

上图显示了方案树的重要节点。相反的节点在 EXPLAIN 输入中用箭头标志。

Seq Scan 节点示意表读取操作，而 Nested Loop 节点示意衔接操作。这里有两点值得留意：

方案搜查

为了找到最佳方案，PostgreSQL 驳回了基于老本的查问提升器。提升器会审核各种可用的口头方案，并预计所需的资源量，例如 I/O 操作和 CPU 计算量。计算进去的预计值转换为恣意单位，称为方案老本。总老本最低的方案会被决定启动口头。

疑问在于，随着衔接数量的参与，或许的方案数量会呈指数级增长，即使关于相对繁难的查问，也无法逐一挑选一切方案。因此，灵活布局和启示式算法会用于限度搜查范畴。这准许在正当的期间内，准确地处置查问中含有很多表的疑问，但不能保障所选方案是真正最优的，由于布局器驳回了简化的数学模型，并且或许会经常使用不准确的初始数据。

衔接顺序

可以以特定方式来构建查问，以清楚增加搜查范畴（但或许会错过找到最佳方案的时机）：

•公共表表白式通常会与主查问分开独自提升。从版本 12 开局，可以经常使用 MATERIALIZE 子句强迫口头此操作。

•来自非 SQL 函数的查问会与主查问分开独自提升。（在某些状况下，SQL 函数可以内联到主查问中。）

•参数与显式 JOIN 子句，以及参数与子查问，可一同定义某些衔接的顺序，详细取决于查问的语法。

最后一条或许须要解释。上方的查问在 FROM 子句中调用了多个表，没有显式的衔接：

这是该查问的解析树：

在此查问中，布局器将思考一切或许的衔接顺序。

在下一个示例中，一些衔接经过 JOIN 子句启动了显式的定义：

解析树反映了这一点：

布局器折叠衔接树，有效地将其转换为上一个示例中的树。该算法以递归方式遍历树，并将每个 JOINEXPR 节点交流为其组件的平铺列表。

然而，只要当生成的平铺列表蕴含不超越数目标元素（默以为 8 个）时，才会出现这种“扁平化”。在上方的示例中，假设设置为 5 或更小，则 JOINEXPR 节点不会折叠。关于布局器来说，这象征着两件事：

假设设置为 1，则将保管任何显式的 JOIN 顺序。

请留意，无论 join_collapse_limit 为何值，FULL OUTER JOIN 操作都不会折叠。

参数（默以为 8）以相似的方式限度子查问的扁平化。子查问仿佛与衔接没有太多独特之处，然而当它进入到解析树级别时，相似性是显而易见的。

示例：

这是树：

这里惟一的区别是，JOINEXPR 节点被交流为了 FROMEXPR（因此参数称号为 FROM）。

基因搜查

每当生成的扁平化方案树最终蕴含太多相反级别的节点（表或衔接结果）时，布局期间或许会飙升，由于每个节点都须要独自的提升。假设参数处于开启形态（默以为开启），则每当同级节点数量到达（默以为 12）时，PostgreSQL 将切换到基因搜查。

基因搜查比灵活布局方法快得多，但它并不能保障找到最好的方案。该算法有许多可调整的选项，但这应该是另一篇文章的主题。

决定最佳方案

最佳方案的定义因希冀的用途而异。当须要完整输入时（例如，生成报表），方案必定提升与查问婚配的一切行的检索。另一方面，假设您只想检查前几行婚配的行（例如，在屏幕上显示），则最佳方案或许齐全不同。

PostgreSQL 经过计算两个老本形成来处置这个疑问。它们显示在查问方案输入中的 “cost” 一词之后：

Sortcost  width

第一个局部，启动老本，是预备口头节点的老本；第二个局部，总老本，示意节点口头的总老本。

决定方案时，布局器首先审核能否正在经常使用游标（可以经常使用 DECLARE 命令设置游标，或在 PL/pgSQL 中显式申明）。假设没有，布局器将假设须要所有输入，并决定总老本最低的方案。

否则，假设在经常使用游标，则布局器将决定一个方案，该方案以最佳方式检索等于婚配行总数的（默以为 0.1）的行数。或许，更详细地说，具备最低值

启动老本 +× (总老本 − 启动老本)。

老本计算环节

要预算一个方案的老本，必定独自预算其每个节点。节点老本取决于节点类型（从表中直接读取的老本远低于对表数据启动排序的老本）和处置的数据量（通常，数据越多，老本越高）。只管节点类型是立刻就知道的，但要评价数据量，咱们首先须要预计节点的基数（输入行的数量）和决定率（残余用于输入的行的比例）。为此，咱们须要数据的统计信息：表大小、跨列的数据散布。

因此，提升依赖于准确的统计信息，这些统计信息由智能剖析进程搜集和坚持最新形态。

假设准确预计了每个方案节点的基数，则计算的总老本通常与实践老本婚配。经常出现的布局偏向通常是由基数和决定率预计不正确形成的。这些失误是由不准确、过期或无法用的统计数据惹起的，并且在较小水平上，也会由布局器所基于的固有不完善的模型惹起。

基数预算

基数预算是递归口头的。节点基数经常使用两个值计算：

•节点的子节点的基数，或输入行数。

•节点的决定率，或输入行与输入行的比例。

基数是这两个值的乘积。

决定率是介于 0 和 1 之间的数字。凑近零的决定率值称为高决定率，凑近 1 的值称为低决定率。这是由于高决定率消弭了较高比例的行，而较低的决定率值会降落阈值，因此摈弃的行更少。

具备数据访问方法的叶节点会首先处置。这就是表大小等统计信息的用武之地。

运行于一个表的条件的决定率取决于条件类型。在最繁难的方式中，决定率可以是一个恒定值，但布局器会试图经常使用一切可用信息来发生最准确的预计。最繁难条件的决定率预计会作为基础，经常使用布尔运算构建的复杂条件，可以经常使用以下繁难公式进后退一步计算：

sel= selsel

sel= 1−(1−sel)(1−sel) = sel+ sel− selsel。

在这些公式中，x 和 y 被以为是独立的。假设它们相关，公式仍会被经常使用，但预计值会不太准确。

关于衔接的基数预计，会计算两个值：笛卡尔乘积的基数（两个数据集的基数的乘积）和衔接条件的决定率，这反上来又取决于条件类型。

其余节点类型（如排序或聚合节点）的基数计算方式相似。

请留意，较低节点中的基数计算失误解向上行播，造成老本预算不准确，并最终决定出次优的方案。更蹩脚的是，布局器只要表上的统计数据，而没有衔接结果的统计数据。

老本预算

老本预算环节也是递归的。子树的老本包括其子节点的老本加上父节点的老本。

节点老本的预算基于其口头的操作的数学模型。曾经计算过的基数会用作输入。预算环节会计算启动老本和总老本。

某些操作不须要任何预备，可以立刻开局口头。关于这些操作，启动老本将为零。

其余操作或许具备前提条件。例如，排序节点通常须要其子节点中的一切数据能力开局操作。这些节点的启动老本不为零。即使下一个节点（或客户端）只要要一行输入，也必定付出此老本。

老本是布局器的最佳预计。任何布局失误都会影响到老本与实践口头期间的相关性。老本评价的重要目标是，准许布局器在相反条件下比拟同一查问的不同口头方案。在任何其余状况下，按老本比拟查问（更蹩脚的是，对不同的查问）是毫有意义和失误的。比如，思考下由于统计信息不准确而被低估的老本。降级统计信息 - 老本或许会出现变动，但预算会变得愈加准确，并且方案最终也会获取改良。