带ORDER BY与LIMIT的UNION视图查询过慢的原因及优化咨询

一、查询过慢的核心原因 #

1. UNION的强制去重开销：UNION关键字默认会对两个表的结果集做去重处理，这意味着PostgreSQL需要先把两个表中所有符合received_time >= '2023-02-10'条件的数据全部提取出来，再进行全局排序去重，最后才能执行外层的ORDER BY和LIMIT。哪怕你的两个表实际没有重复数据，数据库也会执行这个全量数据的去重逻辑，面对3亿+3000万级别的数据，这个过程会消耗大量IO和计算资源，导致查询超时。
2. 执行计划的低效选择：视图本质是逻辑层的封装，查询视图时数据库会将其展开为底层的UNION语句。此时优化器无法识别出“分别取两个表的前500条再合并排序”的最优逻辑，反而会选择先合并所有符合条件的数据，再做全局排序和分页，完全没有利用到单表查询时的索引高效取数能力。

二、优化方法 #

• 替换UNION为UNION ALL：如果archived_orders和orders不存在重复数据（比如归档表只存放历史数据，当前表存放未归档的新数据），直接将视图改为UNION ALL，避免不必要的去重开销：

  CREATE OR REPLACE VIEW all_orders AS 
  SELECT * FROM archived_orders 
  UNION ALL 
  SELECT * from orders;
  

  不过即使改了视图，部分情况下优化器还是可能不会自动生成最优执行计划，建议结合下面的手动改写方式。

• 手动改写查询逻辑，模拟手动操作流程：直接编写查询语句，让数据库分别对两个表执行带LIMIT的查询，再合并结果集排序取前500，完全复用单表的索引高效查询能力：

  SELECT * FROM (
      -- 从归档表取前500条符合条件的数据
      SELECT * FROM archived_orders 
      WHERE received_time >= '2023-02-10 00:00:00' 
      ORDER BY received_time LIMIT 500
      UNION ALL
      -- 从当前表取前500条符合条件的数据
      SELECT * FROM orders 
      WHERE received_time >= '2023-02-10 00:00:00' 
      ORDER BY received_time LIMIT 500
  ) AS combined_results
  ORDER BY received_time LIMIT 500;
  

  这种方式只会处理1000条数据的合并排序，查询速度能和手动操作一致。

• 更新统计信息，辅助优化器决策：如果优化器没有正确选择索引，执行ANALYZE archived_orders;和ANALYZE orders;更新表的统计数据，让优化器能更准确地判断执行计划。

• 考虑物化视图（非实时场景）：如果业务允许非实时查询，可以创建物化视图定期刷新，预先合并两个表的数据。但如果需要实时获取最新数据，此方法不适用。

内容的提问来源于stack exchange，提问作者Lahiru Chandima