Why index is not used for `daterange` condition?

Question

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)

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)

What can I do to force index usage for 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)

See [Range indexes](https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-INDEXING). — Adrian Klaver, Jun 26 '23 at 18:42
Found related question and answers: https://dba.stackexchange.com/a/310534/91706 and very detailed https://stackoverflow.com/a/22111524/4632019 — Eugen Konkov, Jun 26 '23 at 21:12

score 0 · Answer 1 · answered Jun 26 '23 at 19:37

You can't in any useful way.

Without rewriting the query, nothing will make it work. And if you are going to rewrite the query, you might as well rewrite in the way you already know how to do it.

You could create an index using gist (daterange(rdate,rdate,'[]')) and then use it by making the query where daterange(rdate,rdate,'[]') <@ daterange('2022-01-01', '2023-01-01')

But since this still requires you to rewrite your query, and is also slightly slower than the original query, there is probably not much point. (Also, it is not identical to your original query as it includes the exact lower boundary while your original query excludes it.)

Why index is not used for `daterange` condition?

1 Answers1