postgresql - REFRESH MATERIALIZED VIEW 突然需要更多时间才能完成

问题描述

我们在 Postgres DB（11.12，由 AWS RDS 管理）中有一个物化视图。我们有一个计划任务，它每 5 分钟使用REFRESH MATERIALIZED VIEW <view_name>. 在上周的某个特定时间点，刷新视图所需的时间突然从约 1 秒变为约 20 秒。该视图包含大约 70k 行，大约 15 列，它们都是整数、布尔值或 UUID。

在此之前：

服务器配置没有变化。
视图本身没有变化。事实上，运行EXPLAIN ANALYZE <expression used to create the view>返回查询仍然会在不到一秒的时间内执行。如果查询是使用像 Postico 这样的客户端运行的，则需要大约 20 秒来获取所有结果（与实现它所需的时间有点一致，尽管我们假设这是由于网络传输所需的时间）。
架构没有变化，计算视图所需的表内容没有任何显着的记录增加。
RDS Performance Insights 表明查询主要使用 CPU 资源

我知道这可能不足以获得解决方案，但是：

是否有任何服务器性能指标或日志可以让我们更好地理解这种情况？
这只是服务器需要将视图持久化到磁盘的正常时间吗？如果是这样，您知道为什么最近开始需要这么长时间的可能原因吗？

这是执行计划的链接。

编辑：创建另一个具有相同JOINS但选择较少列的物化视图按预期执行（~1s）。

编辑2：设置enable_nestloop = false大大加快了REFRESH操作（与以前的性能相同）。这是否表明重构底层查询可以解决问题？

标签： postgresql

解决方案

每次（或每 5 分钟）更新materialized view一次，这不是刷新物化的好方法。那么使用的意义materialized view就不存在了。让我根据我自己的经验，用我自己的逻辑给你解释一下我找到的一种方法，以便你以后找到更优化的方法。假设，我们在中使用了两个表materialized view，我们需要的是我们将刷新的两个表之一的更改数据materialized view。要在更新或删除表期间执行此操作，我们必须向表（例如refresh_materialized表）插入一条记录（也可以使用触发器），通过该记录将执行刷新materialized view

例如：

insert into refresh_materialized 
(
    refresh_status, 
    insert_date, 
    executed_date 
)
values (
    false,
    now(), 
    null 
)

所以在我们的调度任务中，我们可以使用这个查询：

select count(*) from refresh_materialized 
where refresh_status = false

if count(*) will be > 0then 我们必须刷新materialized viewelse 什么都不做。在刷新物化视图之后，我们必须更新这个表：

update refresh_materialized 
set 
    refresh_status = true, 
    executed_date = now() 
where 
    refresh_status = false;

postgresql - REFRESH MATERIALIZED VIEW 突然需要更多时间才能完成

问题描述

解决方案

推荐阅读