PostgreSQL SELECT组合条件查询超时/无限运行问题及原理咨询

问题现象 #

运行下面的SELECT查询时碰到了怪事：单独用WHERE里的任一条件，查询只需要几毫秒；但同时用两个条件，耗时直接飙到半分钟以上，甚至卡着不动。

查询语句如下：

SELECT id, creation_date, first_name, last_name, domains 
FROM api.filter_users('85') as searchedUsers(id text, first_name text, last_name text, creation_date bigint) 
LEFT JOIN api.domain_aggregation_view 
ON id = user_id 
WHERE (creation_date BETWEEN 0 AND 1728306773664) AND (domains && '{CAT}')

相关背景信息 #

• api.domain_aggregation_view：基于api.user_domains表按user_id聚合domain数组生成的视图
• api.filter_users_by_search_with_extra：实现全文搜索的PL/pgSQL函数，单独执行仅需数毫秒
• api.users和api.user_domains表目前无额外自定义索引

已验证的解决方案 #

以下三种方法均能解决性能问题：

1. 在WHERE子句后添加ORDER BY id
2. 将条件中的数值转换为numeric类型（如1728306773664::numeric）
3. 不使用全文搜索函数，直接查询api.users表（未加search_value过滤）

问题根源 #

这其实是PostgreSQL查询优化器选错执行路线导致的，核心原因有三点：

1. 函数返回结果的统计信息缺失：api.filter_users作为PL/pgSQL函数，PostgreSQL默认无法准确获取其返回行数、数据分布等统计信息。优化器可能误判该函数仅返回少量数据，从而选择「先执行函数→关联视图→过滤条件」的顺序；但实际函数返回行数较多，后续关联和过滤变成全量扫描，性能直接崩盘。
2. 聚合视图的关联代价被放大：api.domain_aggregation_view是聚合生成的视图，关联时需要对api.user_domains做全表聚合或匹配，当关联数据集过大时，这个操作的耗时会成倍增长。
3. 类型隐式转换干扰优化判断：creation_date是bigint类型，查询中的常量1728306773664可能被PostgreSQL在统计阶段误判类型匹配度，导致优化器无法准确评估该条件的过滤效率，进一步选错执行计划。

各解决方案生效原理 #

1. 添加ORDER BY id：
   排序操作需要先将数据加载到内存或临时表，优化器会重新评估执行计划——它会意识到「先过滤再排序」的代价更低，于是调整执行顺序：先通过两个条件过滤出小范围数据，再做关联和排序，避免处理全量数据。同时，排序可能触发优化器使用更高效的连接算法（如合并连接），降低关联开销。

2. 将数值转换为numeric类型：
   核心是消除隐式类型转换对优化器的干扰。显式转成numeric后，PostgreSQL能准确计算creation_date BETWEEN ...条件的过滤效率，优化器会优先执行该过滤操作，减少后续需要处理的数据量，避免全量关联视图的高代价操作。

3. 直接查询api.users表：
   直接查询基础表时，PostgreSQL能获取完整的表统计信息（如行数、creation_date的数据分布），优化器可以基于这些准确数据选择最优路线：先通过creation_date条件过滤出少量数据，再关联api.user_domains做聚合，最后过滤domains条件，整体开销大幅降低。而使用函数时，统计信息缺失导致优化器无法做出正确判断。

内容的提问来源于stack exchange，提问作者Ran Marashi