PostgreSQL 5000表Union查询计划时间过长问题排查与优化

1. 为何查询计划时间远长于执行时间？ #

PostgreSQL生成查询计划时，必须为UNION ALL中的每一张表完成以下操作：

• 读取表的元数据，验证所有表的列数量、数据类型、顺序完全匹配
• 加载每张表的统计信息，用于评估扫描成本
• 为每张表单独生成扫描子计划

5000张表的元数据校验、统计信息读取是串行且重复的操作，这些步骤的累加导致计划耗时剧增。而执行阶段UNION ALL只是简单拼接各表的查询结果，没有复杂的计算或排序逻辑，因此执行耗时极短。

2. 缩短计划时间的优化技术 #

• 改用分区表：这是最彻底的解决方案。把5000张结构一致的表合并为一个分区表（范围、列表或哈希分区均可）。PostgreSQL处理分区表时，只需读取分区表的顶层元数据，无需逐个处理子表，计划时间会直接降到毫秒级，同时还能获得分区表的其他性能收益。
• 替换SELECT *为明确列名：避免PostgreSQL逐个解析每张表的列结构，减少元数据检查的工作量。比如把SELECT *改成SELECT id, name, create_time FROM table1。
• 用PL/pgSQL函数动态生成查询：通过函数自动拼接所有表的UNION ALL语句，减少手动编写重复代码的麻烦，同时让PostgreSQL在函数首次执行时一次性完成计划生成。示例：

CREATE OR REPLACE FUNCTION get_unioned_data()
RETURNS SETOF your_table_type AS $$
DECLARE
    tbl record;
    sql_str text := '';
BEGIN
    -- 替换成你的表筛选条件
    FOR tbl IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename LIKE 'table%' LOOP
        sql_str := sql_str || format('SELECT * FROM %s UNION ALL ', quote_ident(tbl.tablename));
    END LOOP;
    sql_str := rtrim(sql_str, ' UNION ALL ');
    RETURN QUERY EXECUTE sql_str;
END;
$$ LANGUAGE plpgsql;

• 调整计划缓存参数：在会话级别或全局设置plan_cache_mode = force_generic_plan，让PostgreSQL复用通用查询计划，避免每次查询都重新生成计划。注意：仅当所有表的统计信息稳定时适用。

3. 缓存或跳过计划过程的方法 #

• 使用物化视图：把原视图改成物化视图，物化视图会预先计算并存储UNION ALL的结果，查询时直接读取物化视图的数据，完全跳过原查询的计划与执行步骤。数据更新后，执行REFRESH MATERIALIZED VIEW my_view即可同步数据。

CREATE MATERIALIZED VIEW my_view AS
SELECT * FROM table1
UNION ALL
SELECT * FROM table2
-- ... 其余表
;

• 复用通用计划：设置plan_cache_mode = force_generic_plan，PostgreSQL首次生成计划后，后续查询会直接复用该计划。如果表的结构或统计信息发生变化，计划会自动失效并重新生成。
• 使用准备语句：将查询封装为准备语句，首次执行时生成计划，后续执行直接复用。示例：

PREPARE union_all_query AS
SELECT * FROM table1 UNION ALL SELECT * FROM table2 -- ... 其余表;
EXECUTE union_all_query;

内容的提问来源于stack exchange，提问作者David H. J.