create table order_line ( id serial primary key, start_date DATE NOT NULL, end_date DATE, drange daterange NOT NULL GENERATED ALWAYS AS (daterange(start_date, end_date, '[)')) STORED ); CREATE INDEX order_line_not_end_idx ON order_line using gist(drange); INSERT INTO order_line(start_date, end_date) values('2023-01-01', null); INSERT INTO order_line(start_date, end_date) values('2023-01-01', '2024-01-01'); INSERT INTO order_line(start_date, end_date) values('2024-01-01', null); INSERT INTO order_line(start_date, end_date) values('2025-01-01', null); set enable_seqscan to false; explain analyse select * from order_line WHERE (drange << daterange(CURRENT_DATE, NULL, '[)')); -- Uses index ┌────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Index Scan using order_line_not_end_idx on order_line (cost=0.14..8.15 rows=1 width=44) (actual time=0.008..0.008 rows=1 loops=1) │ │ Index Cond: (drange << daterange(CURRENT_DATE, NULL::date, '[)'::text)) │ │ Planning Time: 0.043 ms │ │ Execution Time: 0.013 ms │ └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ explain analyse select * from order_line WHERE NOT (drange << daterange(CURRENT_DATE, NULL, '[)')); -- Does not use index ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ Seq Scan on order_line (cost=10000000000.00..10000000001.07 rows=3 width=44) (actual time=0.007..0.008 rows=3 loops=1) │ │ Filter: (NOT (drange << daterange(CURRENT_DATE, NULL::date, '[)'::text))) │ │ Rows Removed by Filter: 1 │ │ Planning Time: 0.077 ms │ │ Execution Time: 0.015 ms │ └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
-- Andreas Joseph Krogh CTO / Partner - Visena AS Mobile: +47 909 56 963 andr...@visena.com <mailto:andr...@visena.com> www.visena.com <https://www.visena.com> <https://www.visena.com>