PostgreSQL能否优化UNION子查询？大记录量场景性能如何？

问题背景 #

在PostgreSQL中通过UNION合并两个查询，实现支持搜索条件、返回记录上限及偏移量的搜索类GET请求，示例SQL如下：

select q.*, count(*) Over() AS TotalRecords from
(
select name, birthdate, city from regularpeople where birthdate > @startDate and birthdate < @enddate
union
select name, birthdate, city from otherpeople where birthdate > @startDate and birthdate < @enddate
) as q
order by q.birthdate limit @limitCount offset @offset

当前写法功能正常，但疑问是：当两个子查询返回百万级记录时，PostgreSQL查询优化器能否处理？是否会出现性能问题？

核心结论 #

PostgreSQL优化器能处理这种场景，但大概率会出现明显的性能问题，具体取决于索引设计、硬件资源和查询逻辑细节。

性能问题的关键原因 #

• UNION的去重开销：UNION默认会对合并后的结果集做去重（等价于UNION DISTINCT），百万级数据的去重需要大量内存与CPU资源，若内存不足，PostgreSQL会将数据写入临时磁盘文件，导致查询速度骤降。如果业务场景中两个表的name,birthdate,city组合不存在重复，直接用UNION ALL替代UNION，可彻底消除去重的性能损耗。
• 全局排序的成本：外层的order by q.birthdate需要对合并后的百万级数据做全量排序，同样依赖大量内存，内存不足时会触发磁盘排序，IO开销会非常大。
• Count Over()的额外计算：count(*) Over()会扫描整个合并后的结果集计算总记录数，意味着即使只取limit条数据，也必须先捞出所有符合条件的百万级数据，额外增加了大量IO与计算开销。

优化器的处理逻辑 #

PostgreSQL优化器会尝试对该查询做针对性优化：

• 优先检查两个子查询的birthdate范围过滤条件，若regularpeople和otherpeople表在birthdate上有合适的索引，优化器会选择索引扫描快速过滤数据，而非全表扫描。
• 对于UNION的处理，优化器可能会先分别对两个子查询的结果排序，再执行合并排序以避免全量排序，但如果是UNION而非UNION ALL，仍无法跳过去重步骤。
• 不过当结果集达到百万级时，这些优化仅能缓解部分问题，无法消除排序、去重和全量计数带来的核心开销。

优化建议 #

• 优先使用UNION ALL：确认无重复数据的前提下，这是提升性能最直接的手段。
• 优化索引设计：给两个表的birthdate字段建立单独索引，或创建包含name,city的覆盖索引（例如：CREATE INDEX idx_regularpeople_birthdate ON regularpeople(birthdate) INCLUDE (name, city);），让子查询直接从索引获取数据，无需回表。
• 拆分总记录数查询：若业务允许，将总条数统计与分页查询分开。先分别统计两个表符合条件的记录数并相加得到总条数，再执行分页查询，避免扫描全量数据计数。
• 调整排序内存参数：适当调大work_mem参数（可针对会话或全局配置），让排序和去重操作能在内存中完成，减少磁盘IO。注意不要过度调大，避免引发内存竞争。

内容的提问来源于stack exchange，提问作者Michael Witt