4

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?

2
  • "I believe they are semantically identical" -- no, they are certainly not.
    – mustaccio
    Apr 5, 2022 at 0:07
  • @mustaccio What makes you say that? Apr 5, 2022 at 1:35

1 Answer 1

5

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.

2
  • Are there plans to implement support for <@ operator by B-tree indexes? Or are there other indexes, which support <@? Jun 26, 2023 at 18:15
  • 1
    Yes, there are GiST indexes. Jun 27, 2023 at 5:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.