PostgreSQL UNION查询条件无法下推至子查询的性能优化问题

问题背景 #

使用PostgreSQL 13.3创建了多个结构一致的视图，每个视图由大表pg_table_<id>与共享元数据表pg_metadata关联生成，视图定义如下：

CREATE VIEW pg_table_<id>_view AS SELECT
    time AS time,
    pg_table_<id>.source_id AS source_id,
    pg_metadata.source AS source
FROM pg_table_<id>, pg_metadata
WHERE pg_metadata.table_id = (<id>)::INT4 AND pg_metadata.source_id = pg_table_<id>.source_id

现象 #

• 单独查询单个视图时，正则过滤条件可被优化器下推至JOIN阶段，查询性能优异；
• 对多个视图的UNION ALL结果进行查询时，正则条件无法下推，导致全量扫描大表，性能骤降；
• 手动将过滤条件添加到每个UNION ALL分支的子查询中时，条件可正常下推，性能恢复。

解决方案 #

针对PostgreSQL 13.3的优化器限制，可尝试以下几种方法实现条件自动下推：

1. 升级PostgreSQL版本 #

PostgreSQL 14及后续版本对UNION ALL分支的条件下推逻辑做了显著优化，修复了部分场景下无法下推的问题。升级到更高版本（如14+）是最彻底的解决方案，能直接利用优化器的改进特性。

2. 修改视图定义为显式INNER JOIN #

原视图使用隐式JOIN（逗号分隔表），可能导致优化器难以识别关联逻辑，进而影响条件下推。将视图改为显式INNER JOIN语法，帮助优化器更清晰地解析关联关系：

CREATE VIEW pg_table_<id>_view AS SELECT
    time AS time,
    t.source_id AS source_id,
    m.source AS source
FROM pg_table_<id> t
INNER JOIN pg_metadata m
    ON m.table_id = (<id>)::INT4 
    AND m.source_id = t.source_id;

3. 调整优化器参数 #

调整以下优化器参数，提升优化器对复杂UNION ALL查询的处理能力：

• from_collapse_limit：控制优化器将子查询合并到上层查询的最大数量，默认值为8，可适当调大（如SET from_collapse_limit = 32;）；
• join_collapse_limit：控制优化器将JOIN语句合并的最大数量，默认值为8，同样调大至与from_collapse_limit一致。

这些参数调整后，优化器更有可能将外层的过滤条件下推到每个UNION ALL分支的视图查询中。注意：参数调整需根据实际查询复杂度和服务器性能评估，避免过度增大导致优化器耗时过长。

4. 使用物化视图（可选） #

如果上述方法效果有限，可考虑将每个视图转换为物化视图，并在创建时预计算关联结果。查询时对物化视图的UNION ALL结果应用过滤条件，此时条件可直接下推到物化视图的扫描阶段。但需注意物化视图的数据刷新成本，适合数据更新不频繁的场景。

内容的提问来源于stack exchange，提问作者Vlad Keel