Обсуждение: Daterange question

I have order-lines with start-end like this:

start_date  DATE      NOT NULL,
end_date    DATE,
drange      daterange NOT NULL GENERATED ALWAYS AS (daterange(start_date, end_date, '[)')) STORED

and have an index on using gist(drange)

I want to list all order-lines which does not have end-date set in the past, but want to show lines with start-dates in future.

This seems to do what I want:

NOT (drange << daterange(CURRENT_DATE, NULL, '[)'))

But this doesn't use the index.

Any idea how to write a query so it uses the index on drange?

Thanks.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

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

andreas@visena.com

www.visena.com

[…]
Well, we can definitively state that the NOT makes this unindexable.
You need a WHERE clause that looks like
indexed-column indexable-operator pseudo-constant
which this isn't, nor does << have a negator operator that could
allow the NOT to be simplified out.

Wouldn't
drange && daterange(CURRENT_DATE, NULL, '[)')
serve the purpose?  That should be indexable.

regards, tom lane

Yes it will, thanks.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com