Index not used for date range condition

All we need is an easy explanation of the problem, so here it is.

I was querying like this:

SELECT count(*)
FROM orders
WHERE planned_shipping_date >= '2022-04-04'
AND planned_shipping_date < '2022-04-05'

Then I came across this answer and, because in more complicated queries it made the query easier to read, I rewrote the query like this:

SELECT count(*)
FROM orders
WHERE planned_shipping_date <@ daterange('2022-04-04', '2022-04-05')

I believe they are semantically identical, but look at the plans:

Aggregate  (cost=76.91..76.92 rows=1 width=8) (actual time=1.066..1.068 rows=1 loops=1)
  ->  Index Only Scan using orders_planned_shipping_date_idx on orders  (cost=0.29..69.73 rows=2872 width=0) (actual time=0.067..0.646 rows=2813 loops=1)
        Index Cond: ((planned_shipping_date >= '2022-04-04'::date) AND (planned_shipping_date < '2022-04-05'::date))
        Heap Fetches: 0
Aggregate  (cost=2753.57..2753.58 rows=1 width=8) (actual time=18.309..18.311 rows=1 loops=1)
  ->  Index Only Scan using orders_planned_shipping_date_idx on orders  (cost=0.29..2751.93 rows=655 width=0) (actual time=17.520..18.132 rows=2813 loops=1)
        Filter: (planned_shipping_date <@ '[2022-04-04,2022-04-05)'::daterange)
        Rows Removed by Filter: 128138
        Heap Fetches: 0

The use of the date range seems to preclude the use of an index.

Do I need a different index or should I just not use date ranges like this?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Yes, your queries are semantically identical, but syntax matters too.

In order to be supported by an index, a WHERE condition has to look like this:

<indexed expression> <operator> <constant>

  • <indexed expression> is what was used in CREATE INDEX

  • <operator> is an operator from the operator class of the index

  • <constant> has to be constant for the duration of the index scan (at least STABLE)

Additionally, PostgreSQL knows support functions that allow it to use index scans in certain other cases, but that does not apply here.

Your problem is the operator <@, which is not supported by B-tree indexes. As a consequence, PostgreSQL cannot use the index to check your condition. So keep using your original query.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply