PostgreSQL大表to_date与BETWEEN慢查询及索引失效原因咨询

1. 对你原有推测的纠正 #

首先明确两个基础事实，打破你之前的认知偏差：

• PostgreSQL 的B树索引完全支持BETWEEN范围匹配，只要索引列没有嵌套无对应索引的函数、可以直接和常量值做比较，就可以正常命中，不存在「用BETWEEN就无法命中索引」的通用规则。
• 把函数套在输入参数侧（比如你写的to_date(dateinput,'YYYYMMDD')）不会影响表列上的索引命中，因为这部分计算是在拿到常量值后一次性完成的，不会产生逐行计算的额外开销。

2. 原查询执行偏慢的核心叠加问题 #

你第一个查询耗时1543ms，是三个问题共同叠加导致的，并非单一写法的问题：

（1）括号缺失导致过滤逻辑错位 #

原SQL的WHERE子句存在括号不匹配的问题，按照PostgreSQL AND优先级高于OR 的执行规则，实际拆分的过滤逻辑和你预期的完全不一致：
原写法实际执行的逻辑等价于：

-- 第一分支：无name限制的日期范围判断
(to_date(dateinput,'YYYYMMDD') between r.start_date and r.end_date)
OR
-- 第二分支：带name限制的非零点结束时间判断
(to_char(r.end_time,'HH24:MI:SS') <> '00:00:00' AND r.name in ('nameA','nameB'))

也就是说第一分支的日期范围判断完全没有加上name in ('nameA','nameB')的过滤条件，哪怕这个分支匹配的行根本不是你要统计的nameA/nameB，也会被纳入扫描范围。你在name列上建的单列索引，只能优化第二分支的查询，第一分支因为没有name过滤，只能扫描所有符合日期范围的行，自然开销很高。这种逻辑错位不仅放大了扫描范围拉低性能，最终统计的count值也会和业务预期存在偏差。

（2）多余的列侧函数包裹导致索引失效 #

你写的to_char(r.end_time,'HH24:MI:SS') <> '00:00:00'，是对表的end_time列嵌套了格式转换函数。对于普通B树索引来说，索引存储的是end_time列的原始时间值，经过to_char转换后的字符串值和索引存储值完全不匹配，这个条件根本没法命中end_time列的索引，必须逐行读取出字段值做转换计算才能判断，逐行计算的开销远高于索引匹配。

（3）单列索引的匹配效率偏低 #

你目前是在start_date、end_date、name三个列上分别创建了独立的单列索引，执行查询时优化器最多只能选择其中一个单列索引做扫描，比如先通过name索引筛出nameA/nameB的行，再逐行判断日期范围，没法同时利用三个列的过滤条件做快速定位，整体扫描的行数量远高于最优执行计划。

3. 修改后查询性能提升的具体原因 #

你修改后的查询耗时降到786ms，来自三个层面的明确优化：

• 修正了逻辑错位问题：把原来「无name限制的日期范围 OR 带name限制的非零点判断」的结构，调整为两个独立的带name过滤的逻辑分支，每个分支都可以利用你建的name列索引，先把待扫描的行范围从全表90万行压缩到nameA/nameB对应的少量行，不需要扫描无关的行。
• 优化了范围边界写法：你把原来的闭区间BETWEEN start_date AND end_date，改成了>= start_date AND < end_date，不仅修正了业务上可能存在的「把end_date当天零点计入范围」的逻辑偏差，也让优化器可以更清晰的识别两个独立的范围边界，不会因为闭区间的特殊匹配规则选择更差的执行计划。
• 降低了常量计算开销：你把原来的to_date格式转日期，换成了cast(dateinput as date)，如果dateinput本身就是date类型，这个转换是零开销的常量操作；如果是字符串类型的dateinput，原生类型直接转换的计算开销也远低于格式化字符串转日期的函数调用。

4. 进一步性能优化的建议 #

• 去掉end_time判断上多余的to_char函数，直接写r.end_time <> '00:00:00'即可，这个写法不需要任何转换，就可以直接利用列上的索引匹配。
• 把三个独立的单列索引改建成(name, start_date, end_date, end_time)的复合索引，执行查询时可以直接通过索引拿到所有需要的字段值，不需要回表读取原始行，性能还能再提升一个量级。

内容的提问来源于stack exchange，提问作者BaoTrung Tran