I know it is less than a week from the end of the commitfest,
does it make sense to reduce the scope of this to get it in an
acceptable condition?
#1 GROUP BY
What motivated me to get this was queries with
GROUP BY date_trunc(x, timestamp)
GROUP BY timestamp::date
I tried to cover as many functions as possible, as I don't know how
other people might be creatively writing their queries.
#2 JOIN
There might be some joins that can be made a merge join
but that is not a big deal, and makes it much more complicated
happy to drop.
#3 ORDER BY
This case is a bit pointless, if we are ordering by f(x) we could
simply order by x and still use f(x) in the query. But is there
because I use the pathkey to get the grouping correct.
#3 min/max
This is a nice case where min(f(x)) is computed by an index scan
with limit 1, but again, one can simply use f(min(x)) to get the same
plan.
#4 Recursive slope analysis
While the recursive slope analysis is very elegant I know that
a lot of eyebrows will raise with a 15% planning time increase.
So I am happy to restrict this to the queries with GROUP BY
clause.
#5 Alternative explicit monotonicity
If you think that this is better, we could add two functions
increasing(x, f(x)) returns f(x)
decreasing(x, f(x)) returns f(x)
at planning time we accept the index paths, and at
run time we could check that the result does not violate the order
this could be even more general, e.g.
SELECT increasing(x, abs(x)) WHERE x >= 0
GROUP BY 1;
SELECT increasing(x, x * y) WHERE y >= 0
GROUP BY 1;
Do any of these give a good chance to have this in this commitfest
or should I just chill out?
On Wed, 25 Mar 2026 at 23:35, Alexandre Felipe
<o.alexandre.felipe@gmail.com> wrote:
>
> Good catch,
> Can you think of any other type that would wrap,
Arithmetic with intervals isn't monotonic in general because of the
way interval time units vary.
For example, '1 month - 29 days' is "positive" (in the sense that it
compares as greater than zero, and it compares equal to an interval of
'1 day'), but adding it to a date or timestamp may go forwards or
backwards, or not move at all, depending on the number of days in the
month.
Now that you say it makes sense.
So ordering by an interval column can produce different results than
ordering by a fixed date/timestamp plus the interval.
Removing interval arithmetic
Regards,
Alexandre
