Re: Daterange question

[Andreas Joseph Krogh]

På lørdag 20. januar 2024 kl. 06:35:07, skrev Tom Lane mailto:t...@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
andr...@visena.com 
www.visena.com 
 

Re: Daterange question

[Tom Lane]

Adrian Klaver  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

Re: Daterange question

[Andreas Joseph Krogh]

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=100.00..101.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 
www.visena.com 
 

Re: Daterange question

[Adrian Klaver]

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
andr...@visena.com 
www.visena.com 



--
Adrian Klaver
adrian.kla...@aklaver.com

Daterange question

[Andreas Joseph Krogh]

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
andr...@visena.com 
www.visena.com 
 

Re: B-tree index balance?

[Ron Johnson]

On Fri, Jan 19, 2024 at 11:37 AM Tom Lane  wrote:

> Ron Johnson  writes:
> > On an RDMS which I used in the 1990s and 2000s, b-tree indices of
> sequences
> > would get unbalanced, since every new leaf was added to the far right
> > corner of the tree.
> > Sure, they would auto-balance *to a degree* during node splits, but all
> > those "far-right corner" inserts still left them pretty lopsided.
> > Thus, they provided a utility which we could use to determine the
> > lopsidedness, and thus decide when to rebuild an index.
>
> > Does Postgresql keep b-tree indexes on sequences fully balanced?  If not,
> > how do I see how unbalanced they are?  (Assume PG12+.)
>
> As far as I know, we don't have a problem of that sort.  Continued
> insertions will eventually force a split of the root node, which will
> rebalance the tree.
>

Thanks for the explanation.

Re: B-tree index balance?

[Tom Lane]

Ron Johnson  writes:
> On an RDMS which I used in the 1990s and 2000s, b-tree indices of sequences
> would get unbalanced, since every new leaf was added to the far right
> corner of the tree.
> Sure, they would auto-balance *to a degree* during node splits, but all
> those "far-right corner" inserts still left them pretty lopsided.
> Thus, they provided a utility which we could use to determine the
> lopsidedness, and thus decide when to rebuild an index.

> Does Postgresql keep b-tree indexes on sequences fully balanced?  If not,
> how do I see how unbalanced they are?  (Assume PG12+.)

As far as I know, we don't have a problem of that sort.  Continued
insertions will eventually force a split of the root node, which will
rebalance the tree.

regards, tom lane

B-tree index balance?

[Ron Johnson]

On an RDMS which I used in the 1990s and 2000s, b-tree indices of sequences
would get unbalanced, since every new leaf was added to the far right
corner of the tree.
Sure, they would auto-balance *to a degree* during node splits, but all
those "far-right corner" inserts still left them pretty lopsided.
Thus, they provided a utility which we could use to determine the
lopsidedness, and thus decide when to rebuild an index.

Does Postgresql keep b-tree indexes on sequences fully balanced?  If not,
how do I see how unbalanced they are?  (Assume PG12+.)

Re: Explain and filter over subplans

[Chantal Keller]

Thank you very much for your quick answer and patch!

I tested and this is exactly what I was looking for :-)

Best
Chantal




Le 18/01/2024 à 18:55, Tom Lane a écrit :

Chantal Keller  writes:

I would like "explain" to output formulas for filtering over
subplans. Is it possible?


No, and that's been a to-do item for a long time.

Currently, EXPLAIN just ignores the "testexpr" field of SubPlan
nodes, which is what you are after.  We could print it, if we
could figure out an intelligible representation.  In the example
you give, the testexpr would probably render as "t.a >= $0"
where $0 represents the subplan's output column.

A very rough sketch, perhaps, is that instead of just
"(SubPlan 1)", we could print "(ALL t.a >= $0 FROM SubPlan 1)".
Some of the other SubLinkTypes might be harder to represent
in a way that makes sense to users.

I made a quick-hack patch to play with, if you're interested.

regards, tom lane