为何daterange条件未触发索引？如何强制其使用索引？

测试环境与现象 #

环境版本：

select version();

执行结果：

version                                                  
---------------------------------------------------------------------------------------------------------
 PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

使用普通范围条件的查询（触发索引） #

SQL语句：

db=> explain analyze SELECT rep_id, rmonth, grs_sales_tot AS grs, net_sales_tot AS net, cost_tot AS cost 
from sales_report 
WHERE rdate > '2022-01-01' and rdate < '2023-01-01' ;

执行计划：

QUERY PLAN                                                         
       
--------------------------------------------------------------------------------------------------------------------------------
-------
 Bitmap Heap Scan on sales_report  (cost=119.73..1220.82 rows=8140 width=31) (actual time=0.971..6.869 rows=8032 loops=1)
   Recheck Cond: ((rdate > '2022-01-01'::date) AND (rdate < '2023-01-01'::date))
   Heap Blocks: exact=941
   ->  Bitmap Index Scan on sales_report_rdate_idx  (cost=0.00..117.69 rows=8140 width=0) (actual time=0.804..0.805 rows=8032 lo
ops=1)
         Index Cond: ((rdate > '2022-01-01'::date) AND (rdate < '2023-01-01'::date))
 Planning Time: 0.124 ms
 Execution Time: 7.430 ms
(7 rows)

该查询使用了sales_report_rdate_idx索引，执行效率较高。

使用daterange包含操作的查询（全表扫描） #

SQL语句：

explain analyze SELECT rep_id, rmonth, grs_sales_tot AS grs, net_sales_tot AS net, cost_tot AS cost 
from sales_report 
WHERE rdate <@ daterange('2022-01-01', '2023-01-01') ;

执行计划：

QUERY PLAN                                                    
----------------------------------------------------------------------------------------------------------------
 Seq Scan on sales_report  (cost=0.00..1577.85 rows=240 width=31) (actual time=0.021..12.524 rows=8032 loops=1)
   Filter: (rdate <@ '[2022-01-01,2023-01-01)'::daterange)
   Rows Removed by Filter: 39876
 Planning Time: 0.106 ms
 Execution Time: 12.983 ms
(5 rows)

该查询执行了全表扫描，未使用索引，效率较低。

原因分析 #

1. 索引类型不匹配：现有的sales_report_rdate_idx是针对date类型的B-tree索引，B-tree索引仅支持=、>、<等常规比较操作符，无法直接支持date类型与daterange类型之间的<@（包含）操作符的索引扫描。
2. 统计信息预估偏差：优化器预估符合条件的行数仅240行，远低于实际的8032行，导致优化器判断全表扫描的成本更低，因此选择了顺序扫描。

解决方案 #

方法1：创建支持范围操作的GiST/GIN索引 #

针对date字段创建GiST索引（GiST索引支持date与daterange的包含操作）：

CREATE INDEX sales_report_rdate_gist_idx ON sales_report USING GIST (rdate);

创建完成后，再次执行使用<@操作符的查询，优化器会自动选择该GiST索引进行扫描，提升查询效率。

方法2：将daterange条件转换为等价的date范围比较 #

修改查询语句，把daterange的包含条件转换成原有的>和<组合条件，复用现有的B-tree索引：

explain analyze SELECT rep_id, rmonth, grs_sales_tot AS grs, net_sales_tot AS net, cost_tot AS cost 
from sales_report 
WHERE rdate > lower(daterange('2022-01-01', '2023-01-01')) 
  AND rdate < upper(daterange('2022-01-01', '2023-01-01'));

该写法与原daterange逻辑等价，且能触发现有的B-tree索引。

方法3：临时强制使用索引（不推荐） #

如果需要临时强制优化器使用现有索引，可以关闭顺序扫描开关（仅当前会话有效）：

SET enable_seqscan = off;

执行查询后再恢复设置：

SET enable_seqscan = on;

注意：该方法属于全局会话设置，可能影响其他查询的执行计划，仅用于测试场景，不建议在生产环境长期使用。

内容的提问来源于stack exchange，提问作者Eugen Konkov