Index not used for date range condition

Question

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?

Answer 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.

To support <@, you'd need a GiST index.