pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Daterange question
5+ messages / 3 participants
[nested] [flat]

* Daterange question
@ 2024-01-20 04:08  Andreas Joseph Krogh <andreas@visena.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Andreas Joseph Krogh @ 2024-01-20 04:08 UTC (permalink / raw)
  To: pgsql-general@lists.postgresql.org



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 <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
 <https://www.visena.com;

^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Daterange question
@ 2024-01-20 05:13  Adrian Klaver <adrian.klaver@aklaver.com>
  parent: Andreas Joseph Krogh <andreas@visena.com>
  0 siblings, 2 replies; 5+ messages in thread

From: Adrian Klaver @ 2024-01-20 05:13 UTC (permalink / raw)
  To: Andreas Joseph Krogh <andreas@visena.com>; pgsql-general@lists.postgresql.org

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






^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Daterange question
@ 2024-01-20 05:28  Andreas Joseph Krogh <andreas@visena.com>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 0 replies; 5+ messages in thread

From: Andreas Joseph Krogh @ 2024-01-20 05:28 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general@lists.postgresql.org


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 <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
 <https://www.visena.com;


^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Daterange question
@ 2024-01-20 05:35  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Adrian Klaver <adrian.klaver@aklaver.com>
  1 sibling, 1 reply; 5+ messages in thread

From: Tom Lane @ 2024-01-20 05:35 UTC (permalink / raw)
  To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Andreas Joseph Krogh <andreas@visena.com>; pgsql-general@lists.postgresql.org

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





^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: Daterange question
@ 2024-01-20 06:10  Andreas Joseph Krogh <andreas@visena.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 5+ messages in thread

From: Andreas Joseph Krogh @ 2024-01-20 06:10 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Adrian Klaver <adrian.klaver@aklaver.com>; pgsql-general@lists.postgresql.org



På lørdag 20. januar 2024 kl. 06:35:07, skrev Tom Lane <tgl@sss.pgh.pa.us 
<mailto: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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com;
 <https://www.visena.com;

^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~2024-01-20 06:10 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-01-20 04:08 Daterange question Andreas Joseph Krogh <andreas@visena.com>
2024-01-20 05:13 ` Adrian Klaver <adrian.klaver@aklaver.com>
2024-01-20 05:28   ` Andreas Joseph Krogh <andreas@visena.com>
2024-01-20 05:35   ` Tom Lane <tgl@sss.pgh.pa.us>
2024-01-20 06:10     ` Andreas Joseph Krogh <andreas@visena.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox