AQO-PostgreSQL自适应查询优化

在执行查询时，现代的DBMS使用成本优化模型-基于存储在配置文件中的系数和收集的统计数据，计算获得的“成本”和所得行集的数量。再次运行查询时，将重新计算成本和选择性。您可以执行查询并查看这些参数的实际值，但是，在（标准）重新计划的过程中，DBMS优化器不会以任何方式使用此信息。



但是如果优化器保存了执行查询的成本，选择性和其他必要参数的真实值，并且再次执行时，不仅由标准收集的统计信息指导，而且还由上一次执行后保存的参数指导呢？



这称为自适应查询优化，这种优化方法很有希望。一些DBMS已经使用了此类技术。



公司Postgres Professional多年来致力于PostgreSQL的AQO扩展，该扩展实现了（以某种形式）自适应优化。工作仍在进行中，但是已经有一些测试。



首先，让我们仔细看看查询优化的主题领域。

为什么计划者可以选择次优计划

SQL查询可以以不同的方式执行。例如，当有两个表的联接时，可以用几种不同的方式完成-使用嵌套循环，合并，哈希。参与查询的表越多，其联接的变化就越大。计划者的任务是从多种变体中选择成本最低的查询执行计划。



如前所述，许多DBMS的计划人员在工作中会使用自动或手动收集的统计信息。计划者根据这些统计数据计算估计成本。



通常，现代DBMS的计划者在大多数情况下都能很好地工作。但是，在某些情况下，选择的计划可能远非最佳。



例如，缺乏最新的统计信息会导致这样一个事实，即计划者在工作中受到（最有可能）关于要连接的表中的行数的错误数据的指导。基数的过度低估（或高估）导致选择非最佳方法来访问表中的数据。



另一个重要原因可能是缺少必要的索引。在没有索引的情况下，调度程序只能选择有限的数据访问方法。



使用相关（相关）条件也会对DBMS的运行产生负面影响。调度程序（默认情况下）认为请求中的所有条件都是彼此独立的，也就是说，一个条件的值不会以任何方式影响另一个条件。这并非总是如此。如果使用相关条件（例如，邮政编码和城市），则计划者还将计算错误的成本和连接基数。在条件中使用功能



也会影响调度程序。计划人员的功能是“黑匣子”，他不知道该功能将返回多少行，这也可能导致错误的计划成本。

影响调度程序的方法

实际统计信息是调度程序正常工作必不可少的条件。首先，请确保将系统配置为定期收集统计信息。

有几种方法可以解决上述情况，并帮助计划者选择更优化的查询执行计划。



没有索引，调度程序只有一种获取数据的方法-顺序表扫描（这并不总是很糟糕和昂贵）。在某些情况下，创建必要的索引有助于加快对数据的访问-无需扫描整个表。但是使用索引（搜索必要的索引，创建，维护）并不是一种免费的乐趣。理想情况下，应在需要的地方准确使用它们。在没有必要的地方-请勿使用。



在查询中使用关联联接条件时，可以生成扩展统计信息-清楚地“告诉”优化器条件是相互关联的。为此，DBA（或开发人员）需要很好地了解其数据并监视查询中的依赖条件，因为依赖列的组合数量很难预先预测。必须为每个此类选项手动创建高级统计信息。



创建函数时，可以指定执行成本和/或函数产生的行数的估计值。在版本12中，可以根据参数使用辅助功能来改进调度程序估计。这也是一种手动方法，并不总能获得最佳效果。



如果所有其他方法均失败，则可以手动重写查询，例如，使用实例化视图，通用表表达式（CTE）。或者澄清主题领域的要求，并可能从根本上重写请求的逻辑。



还有另一种方式来“暗示”的策划者-自适应查询优化（一 daptive q uery Ø ptimization）。该方法背后的思想是，在执行查询之后，将存储实际的统计信息，并且在再次执行给定（或类似的）查询时，优化器可以依靠它。



DBMS Postgres Pro Enterprise是称为AQO的自适应查询优化的扩展。该扩展名发布在github上：github.com/postgrespro/aqo，您可以在香草PostgreSQL上尝试使用，更多内容请参见下文。

模块的工作方式

AQO模块在其工作中使用机器学习。您可以在Oleg Ivanov的文章“ 使用机器学习提高PostgreSQL性能”中阅读有关操作原理的更多信息，并在Adaptive Query Optimization演示文稿（YouTube上的报告）中获得更多详细信息。



下面简要描述此方法的本质：



要估算成本，计划者需要估算基数，为此，还需要估算条件的选择性。



对于简单条件（例如“属性=常数”或“属性>常数”），调度程序具有一个模型，通过该模型可以评估选择性。为此，他使用统计信息：唯一属性值的数量，直方图等。

对于使用逻辑连接词由简单元素组成的条件，计划者使用易于计算的公式：

• sel（不是A）= 1-sel（A）
• sel（不是A）= 1-sel（A）
• sel（A和B）= sel（A）* sel（B）
• sel（A或B）= sel（不是（不是A而不是B））= 1-（1- sel（A））*（1- sel（B））

这些公式假设条件A和B的独立性（非相关性），因此在违反该假设的情况下，我们将得到不正确的估计。

AQO使公式变得复杂：它为每个简单条件引入了自己的系数。通过机器学习（使用最近邻回归），AQO调整这些系数，以使由公式计算出的选择性与AQO先前观察到的实际选择性最佳匹配。



为此，模块保存：

• , ;
• .

在其工作中，AQO区分条件直至常量。这样就可以降低所解决问题的复杂性，此外，在大多数情况下，信息仍然不会丢失：AQO不会“看到”该常数的值，而是“看到”了条件的选择性。

确实会发生损耗的情况，这些条件是通过常数评估的，而与特定值无关。例如，对于某些条件，计划人员无法进行任何合理的估算，而是选择默认常量（例如，条件“ expression1 = expression2”的选择性总评估为0.005，而“ expression1> expression2”的选择性为1/3）。



因此，AQO可以改善对复杂条件选择性的评估（因此可以改善成本评估，从而可以选择更合适的实施计划）。

安装模块

要在Vanilla PostgreSQL上尝试该模块的功能，您需要使用特殊的补丁程序，然后从源代码构建系统。在github上的README文件中了解更多信息。



如果使用Postgres Pro Enterprise，则AQO模块的安装将以标准模式进行：



shared_preload_libraries = 'aqo'



之后，您可以在所需的数据库中创建扩展。

数据库准备

考虑一个有关AQO模块如何在演示数据库中工作的特定示例。我们将使用一个大型数据库，其中包含2016年9月至2017年9月这一年的航班信息。



首先，创建一个扩展：

CREATE EXTENSION aqo;

接下来，关闭并行查询处理-以便并行计划的显示不会偏离主要任务：



max_parallel_workers_per_gather = 0;



为了让PostgreSQL计划者有更多的选项来联接表，我们将创建两个索引：

CREATE INDEX ON flights (scheduled_departure );
CREATE INDEX ON ticket_flights (fare_conditions );

分析结果时，我们将重点放在BUFFERS的价值上，即完成工作需要阅读的页面数。我们还看一下执行时间（但是在加载的系统和家用笔记本电脑上的时间可能会大不相同）。



增加缓冲区高速缓存和work_mem，以便所有工作都在RAM中进行：



shared_buffers = '256MB';

work_mem = '256MB';

使用AQO模块

让我们提出一个要求：您需要让从特定日期开始乘坐商务舱的乘客，并且延迟不超过一个小时。

让我们在不使用AQO的情况下执行查询（以下，从计划中删除了一些不影响模块理解的信息）：

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT t.ticket_no
  FROM flights f
   	JOIN ticket_flights tf ON f.flight_id = tf.flight_id
   	JOIN tickets t ON tf.ticket_no = t.ticket_no
 WHERE f.scheduled_departure > '2017-08-01'::timestamptz
   AND f.actual_arrival < f.scheduled_arrival + interval '1 hour'
   AND tf.fare_conditions = 'Business';

让我们看一下最终的计划： 在这种情况下，计划者考虑了最佳计划，其中，首先，使用位图扫描（），从flights表中获取一组行，而flight 表中的一个节点（节点）是来自ticket_flights表中使用索引获得的一组行扫描（）。结果结果将用作最终嵌套循环联接（节点）的外部行集。该连接的内部集是通过对ticket（）表进行排它索引扫描而获得的。 最“笨拙”的操作是获取嵌套循环的内部行集-将106205缓冲区读入其中。



Nested Loop (rows=33210) (actual rows=31677)

  Buffers: shared hit=116830 read=1

  ->  Hash Join (rows=33210) (actual rows=31677)

        Hash Cond: (tf.flight_id = f.flight_id)

        ->  Index Scan ... on ticket_flights tf  

              Index Cond: fare_conditions = 'Business'

        ->  Hash

              ->  Bitmap Heap Scan on flights f (rows=8331) (actual rows=7673)

                    Recheck Cond: scheduled_departure > '2017-08-01'

                    Filter: actual_arrival < scheduled_arrival + '01:00:00'::interval

                    ->  Bitmap Index Scan on ... [flights]

                          Index Cond: scheduled_departure > '2017-08-01'

                          Buffers: shared hit=44 read=1

  ->   Index Only Scan  ... on tickets t (rows=1 width=14) (actual rows=1 loops=31677)

        Index Cond: (ticket_no = tf.ticket_no)

        Buffers: shared hit=106205

 Planning Time: 9.326 ms

 Execution Time: 675.836 ms



Bitmap Heap Scan on flightsHash JoinIndex Scan ... on ticket_flightsNested LoopIndex Only Scan ... on tickets





可以将这个计划称为相对好的计划，因为外部集中相对较少的行是由嵌套循环联接处理的。



现在让我们进行一个实验，看看拟议的计划将如何（或不会）根据请求中日期的变化而变化。选择日期的方式应依次增加满足条件的Flights表的行范围，这会导致计划者在评估访问该表的基数时出错。在上面的计划中，您可以看到，从第一个日期开始，优化器几乎就不会误认为基数（）。 让我们一一替换以下日期：Bitmap Heap Scan on flights f (rows=8331) (actual rows=7673)

• 2017-04-01
• 2017-01-01
• 2016-08-01

让我们看看结果：





如果您收集简短摘要，而不使用AQO模块，则调度程序的工作方式如下：



让我们再次看一下结果：

启用AQO后，调度程序很快意识到需要将其从嵌套循环联接和索引用法切换为哈希联接和顺序扫描。

总结

使用AQO模块进行自适应查询优化既有优点也有缺点。



使用模块的好处之一是，您不必跟踪查询中的依赖条件。在某些情况下，查询执行速度可能会提高。并且有多种使用模块的模式。例如，您可以使用AQO仅优化某些类型的查询。



在模块的缺点中，我们可以区分出额外的开销成本，用于在模块结构中训练和存储统计信息。并且模块收集的统计信息不会传输到副本。



AQO模块并不是解决所有可能的调度问题的灵丹妙药。例如，在某些情况下，该模块可以替换扩展的统计信息（如果不是手动创建的），或者不关注无关的统计信息。但是该模块不会创建必要的索引，而且不会重写查询文本。



因此，您不应为所有请求启用该模块。AQO的理想优化候选对象是查询，这些查询中计划者在计算节点基数时的错误会导致错误的计划。并且，由于某种原因，不可能影响该估计的改进。