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?