Index not used for date range condition



  • 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 https://stackoverflow.com/questions/23335970/postgresql-query-between-date-ranges/48117316#48117316 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 

    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 

    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?



  • 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:

    • is what was used in CREATE INDEX

    • is an operator from the operator class of the index

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

    Additionally, PostgreSQL knows https://www.postgresql.org/docs/current/xfunc-optimization.html 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2