物化视图（Mat View）性能优化咨询：900万行刷新耗时20分钟

问题背景 #

我有一个基于两张表的物化视图，创建语句如下：

CREATE MATERIALIZED VIEW unilever_ozon_parser.sell_out_ozon_view
AS SELECT sell_out_ozon.contractor,
    sell_out_ozon.year,
    sell_out_ozon.month,
    sell_out_ozon.week,
    ...
    sell_out_ozon.is_express
   FROM unilever_ozon_parser.sell_out_ozon
UNION ALL
 SELECT sell_out_ozon_daily.contractor,
    sell_out_ozon_daily.year,
    sell_out_ozon_daily.month,
    sell_out_ozon_daily.week,
    ...
    sell_out_ozon_daily.is_express
   FROM unilever_ozon_parser.sell_out_ozon_daily
     LEFT JOIN unilever_ozon_parser.sell_out_ozon ON sell_out_ozon_daily.accepted_date = sell_out_ozon.accepted_date
  WHERE sell_out_ozon.* IS NULL
WITH DATA;

该物化视图包含900万条数据，刷新耗时约20分钟，性能表现不佳。已在两张表的accepted_date字段创建索引，执行EXPLAIN ANALYZE得到以下执行计划：

Append  (cost=0.00..26126519.58 rows=118553286 width=538) (actual time=0.450..364143.225 rows=9139195 loops=1)
  ->  Seq Scan on sell_out_ozon  (cost=0.00..713367.14 rows=8849314 width=510) (actual time=0.449..4499.518 rows=8849314 loops=1)
  ->  Gather  (cost=761321.93..23723346.29 rows=109703972 width=540) (actual time=99374.355..359335.107 rows=289881 loops=1)
        Workers Planned: 4
        Workers Launched: 4
        ->  Parallel Hash Left Join  (cost=760321.93..12751949.09 rows=27425993 width=540) (actual time=32054.078..103554.179 rows=57976 loops=5)
              Hash Cond: (sell_out_ozon_daily.accepted_date = sell_out_ozon_1.accepted_date)
              Filter: (sell_out_ozon_1.* IS NULL)
              Rows Removed by Filter: 2886423437
              ->  Parallel Seq Scan on sell_out_ozon_daily  (cost=0.00..46786.98 rows=160798 width=540) (actual time=1.156..761.472 rows=128639 loops=5)
              ->  Parallel Hash  (cost=639622.86..639622.86 rows=1474886 width=538) (actual time=4116.381..4116.382 rows=1769863 loops=5)
                    Buckets: 524288 (originally 524288)  Batches: 64 (originally 32)  Memory Usage: 181600kB
                    ->  Parallel Seq Scan on sell_out_ozon sell_out_ozon_1  (cost=0.00..639622.86 rows=1474886 width=538) (actual time=121.311..1884.654 rows=1769863 loops=5)
Planning Time: 6.924 ms
JIT:
  Functions: 60
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 2.809 ms, Inlining 147.698 ms, Optimization 274.151 ms, Emission 184.829 ms, Total 609.486 ms
Execution Time: 364323.906 ms

请问性能瓶颈在哪里？能否进行进一步优化？

性能瓶颈分析 #

• 海量中间结果过滤：执行计划中Parallel Hash Left Join后过滤掉了28亿+行数据，这是核心性能问题。当前左连接逻辑会先将两张表全量关联生成巨量中间数据，再筛选无匹配的行，极大消耗CPU、内存与IO资源。
• 索引未生效：尽管已在accepted_date建索引，但两次对sell_out_ozon的扫描都是全表顺序扫描，说明优化器未选择使用索引。可能是统计信息过时，或索引选择性不足导致优化器认为全表扫描成本更低。
• 哈希连接内存不足：哈希表使用了64个批次（远超初始计划的32），说明内存无法容纳完整哈希表，不得不写入磁盘，额外增加IO开销。

优化建议 #

1. 重构查询逻辑，避免全量连接 #

将LEFT JOIN ... WHERE sell_out_ozon.* IS NULL替换为NOT EXISTS，直接检查sell_out_ozon_daily的行是否在sell_out_ozon中无匹配，避免生成海量中间结果：

CREATE MATERIALIZED VIEW unilever_ozon_parser.sell_out_ozon_view
AS SELECT sell_out_ozon.contractor,
    sell_out_ozon.year,
    sell_out_ozon.month,
    sell_out_ozon.week,
    ...
    sell_out_ozon.is_express
   FROM unilever_ozon_parser.sell_out_ozon
UNION ALL
 SELECT sell_out_ozon_daily.contractor,
    sell_out_ozon_daily.year,
    sell_out_ozon_daily.month,
    sell_out_ozon_daily.week,
    ...
    sell_out_ozon_daily.is_express
   FROM unilever_ozon_parser.sell_out_ozon_daily
  WHERE NOT EXISTS (
    SELECT 1 FROM unilever_ozon_parser.sell_out_ozon
    WHERE sell_out_ozon.accepted_date = sell_out_ozon_daily.accepted_date
  )
WITH DATA;

NOT EXISTS会逐个检查目标行，一旦找到匹配就终止查询，大幅减少不必要的计算。

2. 确保索引被有效利用 #

• 更新表统计信息，让优化器能准确评估执行计划：

  ANALYZE unilever_ozon_parser.sell_out_ozon;
  ANALYZE unilever_ozon_parser.sell_out_ozon_daily;
  

• 若accepted_date重复值较多，可创建联合索引提升选择性（比如结合contractor等字段）；
• 必要时可使用索引扫描提示（谨慎使用，避免依赖提示导致版本兼容性问题）：

  WHERE NOT EXISTS (
    SELECT 1 FROM unilever_ozon_parser.sell_out_ozon
    WHERE sell_out_ozon.accepted_date = sell_out_ozon_daily.accepted_date
    INDEX idx_sell_out_ozon_accepted_date
  )
  

3. 调整内存参数优化哈希连接 #

如果服务器内存充足，临时调高work_mem参数，让哈希表能在内存中完整存储，减少磁盘IO：

SET work_mem = '256MB'; -- 根据实际内存配置调整，默认一般为4MB/64MB

注意：work_mem是会话级参数，全局调整需评估对其他查询的影响。

4. 增量刷新物化视图（可选） #

若源表数据为增量更新，可使用增量刷新替代全量刷新：

• 为源表创建物化视图日志：

  CREATE MATERIALIZED VIEW LOG ON unilever_ozon_parser.sell_out_ozon;
  CREATE MATERIALIZED VIEW LOG ON unilever_ozon_parser.sell_out_ozon_daily;
  

• 使用增量刷新（PostgreSQL 12+支持，需验证是否适配当前UNION ALL逻辑）：

  REFRESH MATERIALIZED VIEW unilever_ozon_parser.sell_out_ozon_view WITH DATA;
  

内容的提问来源于stack exchange，提问作者Mark Avreliy