Thread: 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
Attachment
On 1/19/24 20:08, Andreas Joseph Krogh wrote: > 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|? Without the full query and the EXPLAIN output I don't see that there is much that can be offered in way of an answer. > > Thanks. > > -- > *Andreas Joseph Krogh* > CTO / Partner - Visena AS > Mobile: +47 909 56 963 > andreas@visena.com <mailto:andreas@visena.com> > www.visena.com <https://www.visena.com> > <https://www.visena.com> -- Adrian Klaver adrian.klaver@aklaver.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
Attachment
Adrian Klaver <adrian.klaver@aklaver.com> writes: > On 1/19/24 20:08, Andreas Joseph Krogh wrote: >> 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|? > Without the full query and the EXPLAIN output I don't see that there is > much that can be offered in way of an answer. 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
På lørdag 20. januar 2024 kl. 06:35:07, skrev Tom Lane <tgl@sss.pgh.pa.us>:
[…]
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