SLOPE - Planner optimizations on monotonic expressions.

Hi Hackers,

Is there anything else I can do to move this forward?
Anyone could review review? This one should be a quick review!

Regards,
Alexandre

v2 updates prosupport function OIDs, as the ones I initially used were
taken while this waits for review.

This patch adds paths where 'order by f(x)' is satisfied by an index on x
if f(x) is monotonic.

You can find examples of how this feature can be useful in the test file,
including
- Ordered outputs without a sorting node.
- GroupAggregate used directly on the index scan
- MinMaxAggregate replaced by Index scan + limit.

My favourite use case is

+explain (costs off, verbose)
+select date_trunc('month', ts), count(*)
+from src
+group by 1;
+                      QUERY PLAN
+------------------------------------------------------
+ GroupAggregate
+   Output: (date_trunc('month'::text, ts)), count(*)
+   Group Key: date_trunc('month'::text, src.ts)
+   ->  Index Only Scan using src_ts_idx on public.src
+         Output: date_trunc('month'::text, ts)
+(5 rows)

Where we can take advantage of an index on timestamp src(ts) to fulfill a
query aggregating monthly. With this feature the same timestamp column can
be used to either sort the rows, or aggregate by different intervals, e.g.
days, weeks, months or years. Great for reports or dashboards on busy
tables, as we only need one index.

On Wed, Feb 11, 2026 at 9:02 PM Alexandre Felipe <
o.alexandre.felipe@gmail.com> wrote:

+explain (costs off, verbose)
+select date_trunc('month', ts), count(*)
+from src
+group by 1;
+                      QUERY PLAN
+------------------------------------------------------
+ GroupAggregate
+   Output: (date_trunc('month'::text, ts)), count(*)
+   Group Key: date_trunc('month'::text, src.ts)
+   ->  Index Only Scan using src_ts_idx on public.src
+         Output: date_trunc('month'::text, ts)
+(5 rows)

That's a good one.

+/* Slope type for representing monotonicity */
+typedef int8 Slope;
+#define SLOPE_ANY   0   /* 0b00 - unknown/either (safe default) */
+#define SLOPE_ASC   1   /* 0b01 - ascending (descending blocked) */
+#define SLOPE_DESC  2   /* 0b10 - descending (ascending blocked) */
+#define SLOPE_CONST 3   /* 0b11 - constant (both blocked) */

The MonotonicFunction enum seems like a good pattern to follow here.

Nitpick: it's a slope sign (+/-) rather than a slope itself, which to me
implies a scalar. I can't think of a good singular word for it, either.

+ * If the result is SLOPE_ASC or SLOPE_DESC, *underlying_expr is set to the
+ * by checking the slopes of the function arguments and the expression
+ * passed combined as follows:

"is set to the by" - seems like you left out a word here.

Patches 0002-0003 would have to get committed at the same time, but I see
why you separated them for clarity.

0002 is missing the catversion bump but that's fine at this early stage.

So, how would this work with a function like left() with a positive 2nd
param (assuming specified collation matches the index)? I'm also curious if
NULLS FIRST/LAST will throw this off.

Thank you for your review Corey,

On Wed, Mar 25, 2026 at 5:18 AM Corey Huinker <corey.huinker@gmail.com>
wrote:

+explain (costs off, verbose)

+select date_trunc('month', ts), count(*)
+from src
+group by 1;
+                      QUERY PLAN
+------------------------------------------------------
+ GroupAggregate
+   Output: (date_trunc('month'::text, ts)), count(*)
+   Group Key: date_trunc('month'::text, src.ts)
+   ->  Index Only Scan using src_ts_idx on public.src
+         Output: date_trunc('month'::text, ts)
+(5 rows)

That's a good one.

I am glad you noted, I waited for a while for this moment :)

+/* Slope type for representing monotonicity */
+typedef int8 Slope;
+#define SLOPE_ANY   0   /* 0b00 - unknown/either (safe default) */
+#define SLOPE_ASC   1   /* 0b01 - ascending (descending blocked) */
+#define SLOPE_DESC  2   /* 0b10 - descending (ascending blocked) */
+#define SLOPE_CONST 3   /* 0b11 - constant (both blocked) */

The MonotonicFunction enum seems like a good pattern to follow here.

Learning about the existence of that now

typedef enum MonotonicFunction
{
MONOTONICFUNC_NONE = 0,
MONOTONICFUNC_INCREASING = (1 << 0),
MONOTONICFUNC_DECREASING = (1 << 1),
MONOTONICFUNC_BOTH = MONOTONICFUNC_INCREASING | MONOTONICFUNC_DECREASING,
} MonotonicFunction;

So, BOTH means that the function is both increasing and decreasing, thus it
is constant.
Simply replacing the SLOPE_* by the corresponding MONOTONICFUNC_* does the
job.
But now using an enum we will have 4 bytes per argument.

Nitpick: it's a slope sign (+/-) rather than a slope itself, which to me

implies a scalar. I can't think of a good singular word for it, either.

Solved if we use MonotonicFunction I guess. But the word slope isn't always
used to refer to derivatives.
And here we are mostly dealing with functions that are constant or
discontinuous, i.e. they never have a non-zero real derivative.

+ * If the result is SLOPE_ASC or SLOPE_DESC, *underlying_expr is set to the

+ * by checking the slopes of the function arguments and the expression
+ * passed combined as follows:

"is set to the by" - seems like you left out a word here.

rewording like this

* The contribution of each argument to the final slope of the function
* determined by the slope of the function with respect to an argument
* and the slope of the underlying expression expression passed to it
* as follows:

Patches 0002-0003 would have to get committed at the same time, but I see
why you separated them for clarity.

0002 is missing the catversion bump but that's fine at this early stage.

changed catversion this time

So, how would this work with a function like left() with a positive 2nd

param (assuming specified collation matches the index)?

That would require a separate prosupport function, it would have to do some
extra work, checking if the second argument is a positive constant at
planning time.
I'm happy to do that if there is interest, but I would keep after this.

I'm also curious if NULLS FIRST/LAST will throw this off.

All that this will do is, when considering index scans, check if the
requested order
matches the index order. And whether we have to do a backwards or forwards
scan.

+ if (mono_decreasing)
+ {
+ reverse_sort = !reverse_sort;
+ nulls_first = !nulls_first;
+ }

I think this covers all the cases under consideration.

Added more test cases at the end of 0003.

Is there a specialized nulls first/last sort or does it use a generic sort?

Regards,
Alexandre

The MonotonicFunction enum seems like a good pattern to follow here.

Learning about the existence of that now

typedef enum MonotonicFunction
{
MONOTONICFUNC_NONE = 0,
MONOTONICFUNC_INCREASING = (1 << 0),
MONOTONICFUNC_DECREASING = (1 << 1),
MONOTONICFUNC_BOTH = MONOTONICFUNC_INCREASING | MONOTONICFUNC_DECREASING,
} MonotonicFunction;

So, BOTH means that the function is both increasing and decreasing, thus
it is constant.
Simply replacing the SLOPE_* by the corresponding MONOTONICFUNC_* does the
job.
But now using an enum we will have 4 bytes per argument.

Sorry, I wasn't suggesting that you use MonotonicFunction outright, only
that you follow it's pattern (make an enum, if you're needing specific bit
patterns do it this way, etc. We often use enums as a way for switch
statements to warn us when we're missing values.

So, how would this work with a function like left() with a positive 2nd

param (assuming specified collation matches the index)?

That would require a separate prosupport function, it would have to do
some extra work, checking if the second argument is a positive constant at
planning time.
I'm happy to do that if there is interest, but I would keep after this.

I'm also curious if NULLS FIRST/LAST will throw this off.

All that this will do is, when considering index scans, check if the
requested order
matches the index order. And whether we have to do a backwards or forwards
scan.

What I meant was will we need a custom function to inspect parameters such
that we *do* try the index optimization on substr(foo,x,..) if and only if
x = 1?

It almost seems like what we need is "does f(x) preserve the leading sort
bits of x, and reduce the trailing bits to ties?"

Is there a specialized nulls first/last sort or does it use a generic sort?

No, it just fed into my wondering about situations where the ordering of
f(x) would be modified away from the ordering of x.

Oh, one other thing I am curious if any other reviewers like/dislike the

static const Slope pattern[2] = {SLOPE_ASC, SLOPE_DESC};

pattern. I haven't seen that one done before, and I'm wondering if we need
some comments to explain how it works so that people don't start looking
for the pfree that they'll never find.

Hello

-  proname => 'time_pl_interval', prorettype => 'time',
-  proargtypes => 'time interval', prosrc => 'time_pl_interval' },
+  proname => 'time_pl_interval', prosupport => 'addition_slope_support',
+  prorettype => 'time', proargtypes => 'time interval',
+  prosrc => 'time_pl_interval' },

I think this is incorrect, time can wrap around and isn't monotonic.

CREATE TABLE time_wrap_test (t time PRIMARY KEY);
INSERT INTO time_wrap_test VALUES
('20:00'), ('21:00'), ('22:00'), ('23:00'), ('23:30'),
('00:00'), ('01:00'), ('02:00'), ('03:00'), ('04:00');
SELECT t, t + interval '3 hours' AS t_plus_3h
FROM time_wrap_test
ORDER BY t + interval '3 hours';

+ /* Check each index on this relation */
+ foreach(lc, rel->indexlist)
+ {
+ IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);

Isn't a sortopfamily check missing from this?

Added one line to .gitignore file because I like to keep
data related to this in the project workarea, e.g.
".install", ".dbdata", ".patches", and I don't want those

+
+# ignore hidden files
+.*

This doesn't seem related to the patch.

On Wed, Mar 25, 2026 at 5:27 PM Corey Huinker <corey.huinker@gmail.com>
wrote:

The MonotonicFunction enum seems like a good pattern to follow here.

Learning about the existence of that now

typedef enum MonotonicFunction
{
MONOTONICFUNC_NONE = 0,
MONOTONICFUNC_INCREASING = (1 << 0),
MONOTONICFUNC_DECREASING = (1 << 1),
MONOTONICFUNC_BOTH = MONOTONICFUNC_INCREASING | MONOTONICFUNC_DECREASING,
} MonotonicFunction;

So, BOTH means that the function is both increasing and decreasing, thus
it is constant.
Simply replacing the SLOPE_* by the corresponding MONOTONICFUNC_* does
the job.
But now using an enum we will have 4 bytes per argument.

Sorry, I wasn't suggesting that you use MonotonicFunction outright, only
that you follow it's pattern (make an enum, if you're needing specific bit
patterns do it this way, etc. We often use enums as a way for switch
statements to warn us when we're missing values.

No worries, that was easy as the values coincided.

So, how would this work with a function like left() with a positive 2nd

param (assuming specified collation matches the index)?

That would require a separate prosupport function, it would have to do
some extra work, checking if the second argument is a positive constant at
planning time.
I'm happy to do that if there is interest, but I would keep after this.

I'm also curious if NULLS FIRST/LAST will throw this off.

All that this will do is, when considering index scans, check if the
requested order
matches the index order. And whether we have to do a backwards or
forwards scan.

What I meant was will we need a custom function to inspect parameters such
that we *do* try the index optimization on substr(foo,x,..) if and only if
x = 1?

What I would do in this case is
if(x is constant and x > 0) {
return {INCREASING, BOTH, NONE}
}else{
return {NONE, BOTH, NONE}
}

It almost seems like what we need is "does f(x) preserve the leading sort

bits of x, and reduce the trailing bits to ties?"

Yes, this is definitely the most common case, the few exceptions are there
for completeness, but I don't believe people will want to order by e.g.
atan(x) very often.

Is there a specialized nulls first/last sort or does it use a generic sort?

No, it just fed into my wondering about situations where the ordering of
f(x) would be modified away from the ordering of x.

There is one caveat, my current approach for nulls first/last is valid only
if f(NULL) is NULL

Oh, one other thing I am curious if any other reviewers like/dislike the

static const Slope pattern[2] = {SLOPE_ASC, SLOPE_DESC};

pattern. I haven't seen that one done before, and I'm wondering if we need
some comments to explain how it works so that people don't start looking
for the pfree that they'll never find.

OK, adding this
+ req.expr = (Node *) expr;
+ /* prosupport function sets req.slopes to a static
+ * pointer, no pfree is required.
+ */
+ req.slopes = NULL;

On Wed, Mar 25, 2026 at 8:57 PM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:

Hello

-  proname => 'time_pl_interval', prorettype => 'time',
-  proargtypes => 'time interval', prosrc => 'time_pl_interval' },
+  proname => 'time_pl_interval', prosupport => 'addition_slope_support',
+  prorettype => 'time', proargtypes => 'time interval',
+  prosrc => 'time_pl_interval' },

I think this is incorrect, time can wrap around and isn't monotonic.

CREATE TABLE time_wrap_test (t time PRIMARY KEY);
INSERT INTO time_wrap_test VALUES
('20:00'), ('21:00'), ('22:00'), ('23:00'), ('23:30'),
('00:00'), ('01:00'), ('02:00'), ('03:00'), ('04:00');
SELECT t, t + interval '3 hours' AS t_plus_3h
FROM time_wrap_test
ORDER BY t + interval '3 hours';

Good catch,
Can you think of any other type that would wrap,
int2, int4, int8, money, float4, float8, date, timestamp, timestamptz,
interval
all raise some error.
time and timetz wrap around.

@@ -4820,21 +4820,17 @@
prosrc => 'width_bucket_numeric' },

 { oid => '1747',
-  proname => 'time_pl_interval', prosupport => 'addition_slope_support',
-  prorettype => 'time', proargtypes => 'time interval',
-  prosrc => 'time_pl_interval' },
+  proname => 'time_pl_interval', prorettype => 'time',
+  proargtypes => 'time interval', prosrc => 'time_pl_interval' },
 { oid => '1748',
-  proname => 'time_mi_interval', prosupport => 'diff_slope_support',
-  prorettype => 'time', proargtypes => 'time interval',
-  prosrc => 'time_mi_interval' },
+  proname => 'time_mi_interval', prorettype => 'time',
+  proargtypes => 'time interval', prosrc => 'time_mi_interval' },
 { oid => '1749',
-  proname => 'timetz_pl_interval', prosupport => 'addition_slope_support',
-  prorettype => 'timetz', proargtypes => 'timetz interval',
-  prosrc => 'timetz_pl_interval' },
+  proname => 'timetz_pl_interval', prorettype => 'timetz',
+  proargtypes => 'timetz interval', prosrc => 'timetz_pl_interval' },
 { oid => '1750',
-  proname => 'timetz_mi_interval', prosupport => 'diff_slope_support',
-  prorettype => 'timetz', proargtypes => 'timetz interval',
-  prosrc => 'timetz_mi_interval' },
+  proname => 'timetz_mi_interval', prorettype => 'timetz',
+  proargtypes => 'timetz interval', prosrc => 'timetz_mi_interval' },

{ oid => '1764', descr => 'increment by one',
proname => 'numeric_inc', prorettype => 'numeric', proargtypes =>
'numeric',

+ /* Check each index on this relation */
+ foreach(lc, rel->indexlist)
+ {
+ IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);

Isn't a sortopfamily check missing from this?

You are probably right but I am confused here.
We could simply skip this analysis entirely if the index has a custom
operator family.
Do you think this would be enough?

Added one line to .gitignore file because I like to keep

data related to this in the project workarea, e.g.
".install", ".dbdata", ".patches", and I don't want those

+
+# ignore hidden files
+.*

This doesn't seem related to the patch.

Yes, it is not specific for this patch, just annoying that it doesn't
ignore hidden files.

PS.
The v3 patch failed on the CI checks, I was testing without assertions.
The problem was that v2-0001 had a
+ sortkey = mono_var;
modifying the sort key from the query, and this caused an assertion to fail.
v4 keeps the sort key untouched but has checks
ec_member_is_monotonic_in
in both pathkey_is_monotonic_of to determine if the available order is
useful,
and again in build_index_pathkeys that creates a trivial equivalence class
to be used in pathkey_is_monotonic_of with the index key if it doesn't
belong to one already.
Maybe this can be improved later, but it is likely better than a broken
build.

Regards,
Alexandre

Zsolt,

v5 aims to prevent the elimination of the sort node if the index has a
custom sort operator family.

+ /* Check each index on this relation */

+ foreach(lc, rel->indexlist)
+ {
+ IndexOptInfo *index = (IndexOptInfo *) lfirst(lc);

Isn't a sortopfamily check missing from this?
You are probably right but I am confused here.
We could simply skip this analysis entirely if the index has a custom
operator family.
Do you think this would be enough?

The benchmark table had these 24 indices:
19 single-column: a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p (all int),
ts (timestamp), v_float8 (float8), v_numeric (numeric)
4 multi-column: (a, b), (c, d, e), (ts, a), (v_float8, a)
1 primary key: id (serial)

Evaluating the effect of v5 on planning time (in microseconds)
ORDER BY Master Patched Delta Overhead
(nothing) 21.3 21.5 +0.2 ~1%
a 28.2 29.5 +1.3 ~5%
a + 1 29.2 32.4 +3.2 ~11%
a::float8 30.1 34.6 +4.5 ~15%
date_trunc(ts) 30.0 31.4 +1.4 ~5%

+ /*
+ * The query pathkey's opfamily is the default for
+ * the sort expression's type.  If it matches the
+ * index opfamily, the index uses the standard
+ * ordering — no catalog lookup needed.  Otherwise
+ * fall back to GetDefaultOpClass to handle the
+ * cross-type case (e.g. int4 cast to float8).
+ */
+ if (qpk->pk_opfamily != index->sortopfamily[i])
+ {
+ default_opclass = GetDefaultOpClass(
+ index->opcintype[i], BTREE_AM_OID);
+ default_opfamily = OidIsValid(default_opclass)
+ ? get_opclass_family(default_opclass)
+ : InvalidOid;
+
+ if (index->sortopfamily[i] != default_opfamily)
+ break;
+ }

Regards,
Alexandre

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.

Another example, is the interval '361 days', which compares as greater
than the interval '1 year' (because that's taken to be 12 months of 30
days each). But adding '361 days' to a date or timestamp advances it
by less than a year.

So ordering by an interval column can produce different results than
ordering by a fixed date/timestamp plus the interval.

Regards,
Dean

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 Thu, Mar 26, 2026 at 6:17 PM Dean Rasheed <dean.a.rasheed@gmail.com>
wrote:

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

On Thu, 26 Mar 2026 at 20:50, Alexandre Felipe
<o.alexandre.felipe@gmail.com> wrote:

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?

[snip various options]

Do any of these give a good chance to have this in this commitfest
or should I just chill out?

I don't know. I haven't looked at the patches themselves in any
detail. I was merely replying to a specific question about
monotonicity with respect to a particular data type.

However, my impression from reading this thread is that the patches
haven't had a great deal of in-depth review yet, there are still a
number of design ideas that people might wish to explore in more
detail, it probably needs more careful analysis to verify correctness,
and more testing. That makes me think that it's not feasible to get
anything committable in less than a week, and it would be better to
defer this.

Regards,
Dean

On Fri, Mar 27, 2026 at 8:47 AM Dean Rasheed <dean.a.rasheed@gmail.com>
wrote:

I don't know. I haven't looked at the patches themselves in any
detail. I was merely replying to a specific question about
monotonicity with respect to a particular data type.

However, my impression from reading this thread is that the patches
haven't had a great deal of in-depth review yet, there are still a
number of design ideas that people might wish to explore in more
detail, it probably needs more careful analysis to verify correctness,
and more testing. That makes me think that it's not feasible to get
anything committable in less than a week, and it would be better to
defer this.

Thank you for your feedback, either way I am happy that at least
now it received some attention.

I am testing an iterative single variable/expression and to find the
simplest expression x that is the cause of all the variation in the
pathkey expression, saving this in the plan info, and thus, reduce
the per index work.

Regards,
Alexandre

Hi All,

I hope I am early enough for PG20, so v6 maintains the full scope.

0001 as the name suggests is just a benchmark, to get a baseline.
0002 is just a refactoring to ensure build_index_pathkeys is called once
per index.
master is calling once to produce forward pathkeys and once to produce
backward pathkeys.

Other questions are: Should we maybe do this in a way to support
monotonicity
for user defined functions too? Maybe use some per argument flags that can
retrieved without the call by oid?

v6 splits the pathkey monotonicity analysis in two parts.

If the pathkey expression depends on a single table, the innermost
sub expression that contains all variable terms is extracted during the
index
creation, and stored in slope_info.
When considering indices for pathkeys, check if the index key matches
the slope info source of variation, if it does, check the monotonicity.

Limitations:

Not supporting pathkeys [f(x), x], maybe useful for
queries SELECT OVER (PARTITION f(x) ORDER BY x)
I have an implementation on a 0006 patch but I think it would hurt the
overall
patchset quality.

Not working with joins where I expected a MergeJoin to be used.
Any hints here? Because I am not properly using equivalence classes? or
something else?

I see that `make_canonical_pathkey` does a list search for every index key.
expressions, the complexity is roughly quadratic with the number of indices.
I suspect that pointer chasing having a preallocated array
would already be better, as it would probably improve the memory locality.
Would it be worth investigating other data structures here, like hash or a
tree?
(I guess the answer will be no as that could hurt the very simple plans
with
a handful of indices).

Regards,
Alexandre

On Fri, Mar 27, 2026 at 9:29 AM Alexandre Felipe <
o.alexandre.felipe@gmail.com> wrote:

Hi All,

I hope I am early enough for PG20, so v6 maintains the full scope.

0001 as the name suggests is just a benchmark, to get a baseline.
0002 is just a refactoring to ensure build_index_pathkeys is called once
per index.
master is calling once to produce forward pathkeys and once to produce
backward pathkeys.

Other questions are: Should we maybe do this in a way to support
monotonicity
for user defined functions too? Maybe use some per argument flags that can
retrieved without the call by oid?

v6 splits the pathkey monotonicity analysis in two parts.

If the pathkey expression depends on a single table, the innermost
sub expression that contains all variable terms is extracted during the
index
creation, and stored in slope_info.
When considering indices for pathkeys, check if the index key matches
the slope info source of variation, if it does, check the monotonicity.

Limitations:

Not supporting pathkeys [f(x), x], maybe useful for
queries SELECT OVER (PARTITION f(x) ORDER BY x)
I have an implementation on a 0006 patch but I think it would hurt the
overall
patchset quality.

Not working with joins where I expected a MergeJoin to be used.
Any hints here? Because I am not properly using equivalence classes? or
something else?

I see that `make_canonical_pathkey` does a list search for every index key.
expressions, the complexity is roughly quadratic with the number of indices.
I suspect that pointer chasing having a preallocated array
would already be better, as it would probably improve the memory locality.
Would it be worth investigating other data structures here, like hash or a
tree?
(I guess the answer will be no as that could hurt the very simple plans
with
a handful of indices).

PS. Only rebasing the previous patch set

Regards,
Alexandre

On Sun, Apr 5, 2026 at 10:28 PM Alexandre Felipe <
o.alexandre.felipe@gmail.com> wrote:

I think there's a bug with NULL handling:

CREATE TABLE slope_nulls_test (v int4);
INSERT INTO slope_nulls_test SELECT generate_series(1, 10);
INSERT INTO slope_nulls_test VALUES (NULL), (NULL);
CREATE INDEX slope_nulls_idx ON slope_nulls_test (v ASC NULLS FIRST);
ANALYZE slope_nulls_test;

SET enable_seqscan = off;

SELECT floor(v::float8), v FROM slope_nulls_test ORDER BY 1;

RESET enable_seqscan;
SET enable_indexscan = off;
SELECT floor(v::float8), v FROM slope_nulls_test ORDER BY 1;

 { oid => '2308', descr => 'nearest integer >= value',
-  proname => 'ceil', prorettype => 'float8', proargtypes => 'float8',
-  prosrc => 'dceil' },
+  proname => 'ceil', prosupport => 'arg0_asc_slope_support',
+  prorettype => 'float8', proargtypes => 'float8', prosrc => 'dceil' },
 { oid => '2320', descr => 'nearest integer >= value',
   proname => 'ceiling', prorettype => 'float8', proargtypes => 'float8',
   prosrc => 'dceil' },

Shouldn't 2320 also have the same change? Same for 1711/2167

 { oid => '183',
-  proname => 'int42mi', prorettype => 'int4', proargtypes => 'int4 int2',
-  prosrc => 'int42mi' },
+  proname => 'int42mi', prosupport => 'diff_slope_support',
+  prorettype => 'int4', proargtypes => 'int4 int2', prosrc => 'int42mi' },

Similarly shouldn't int24/int42 mul and div also be included?

Thank you,
Valuable feedback

On Tue, Apr 7, 2026 at 12:09 AM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:

I think there's a bug with NULL handling

I was genuinely surprised that this was not caught by any test in the
regression.

There are 32 cases, next patch will verify both the plan and the output
FOR r IN
SELECT idx_dir, idx_nf, qry_dir, qry_nf, sign
FROM unnest(ARRAY['+','-']) AS sign,
unnest(ARRAY['ASC','DESC']) AS idx_dir,
unnest(ARRAY['FIRST','LAST']) AS idx_nf,
unnest(ARRAY['ASC','DESC']) AS qry_dir,
unnest(ARRAY['FIRST','LAST']) AS qry_nf

{ oid => '2308', descr => 'nearest integer >= value',

-  proname => 'ceil', prorettype => 'float8', proargtypes => 'float8',
-  prosrc => 'dceil' },
+  proname => 'ceil', prosupport => 'arg0_asc_slope_support',
+  prorettype => 'float8', proargtypes => 'float8', prosrc => 'dceil' },
{ oid => '2320', descr => 'nearest integer >= value',
proname => 'ceiling', prorettype => 'float8', proargtypes => 'float8',
prosrc => 'dceil' },

Shouldn't 2320 also have the same change? Same for 1711/2167

{ oid => '183',
-  proname => 'int42mi', prorettype => 'int4', proargtypes => 'int4 int2',
-  prosrc => 'int42mi' },
+  proname => 'int42mi', prosupport => 'diff_slope_support',
+  prorettype => 'int4', proargtypes => 'int4 int2', prosrc => 'int42mi' },

Similarly shouldn't int24/int42 mul and div also be included?

Here I am genuinely NOT surprised :) I will take another look there before
submitting the next patch.

Regards,
Alexandre

Now including a 0006 patch supporting redundant pathkeys such as
[f(x), x], or [x, f(x)], motivated by queries like
queries SELECT OVER (PARTITION f(x) ORDER BY x)

On Tue, Apr 7, 2026 at 12:09 AM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:

I think there's a bug with NULL handling

There were too many possibilities for my brain to enumerate, so I scripted
the
extraction and presentation of that in regress/sql/slope.sql in commit 0005.

 sign |   index_order    |   query_order    | scan_method |  example
------+------------------+------------------+-------------+------------
 +    | ASC NULLS FIRST  | ASC NULLS FIRST  | Forward     | NULL,1,2
 +    | ASC NULLS LAST   | ASC NULLS FIRST  | Sort        | NULL,1,2
 +    | DESC NULLS FIRST | ASC NULLS FIRST  | Sort        | NULL,1,2
 +    | DESC NULLS LAST  | ASC NULLS FIRST  | Backward    | NULL,1,2
 -    | ASC NULLS FIRST  | ASC NULLS FIRST  | Sort        | NULL,-2,-1
 -    | ASC NULLS LAST   | ASC NULLS FIRST  | Backward    | NULL,-2,-1
 -    | DESC NULLS FIRST | ASC NULLS FIRST  | Forward     | NULL,-2,-1
 -    | DESC NULLS LAST  | ASC NULLS FIRST  | Sort        | NULL,-2,-1
 +    | ASC NULLS FIRST  | ASC NULLS LAST   | Sort        | 1,2,NULL
 +    | ASC NULLS LAST   | ASC NULLS LAST   | Forward     | 1,2,NULL
 +    | DESC NULLS FIRST | ASC NULLS LAST   | Backward    | 1,2,NULL
 +    | DESC NULLS LAST  | ASC NULLS LAST   | Sort        | 1,2,NULL
 -    | ASC NULLS FIRST  | ASC NULLS LAST   | Backward    | -2,-1,NULL
 -    | ASC NULLS LAST   | ASC NULLS LAST   | Sort        | -2,-1,NULL
 -    | DESC NULLS FIRST | ASC NULLS LAST   | Sort        | -2,-1,NULL
 -    | DESC NULLS LAST  | ASC NULLS LAST   | Forward     | -2,-1,NULL
 +    | ASC NULLS FIRST  | DESC NULLS FIRST | Sort        | NULL,2,1
 +    | ASC NULLS LAST   | DESC NULLS FIRST | Backward    | NULL,2,1
 +    | DESC NULLS FIRST | DESC NULLS FIRST | Forward     | NULL,2,1
 +    | DESC NULLS LAST  | DESC NULLS FIRST | Sort        | NULL,2,1
 -    | ASC NULLS FIRST  | DESC NULLS FIRST | Forward     | NULL,-1,-2
 -    | ASC NULLS LAST   | DESC NULLS FIRST | Sort        | NULL,-1,-2
 -    | DESC NULLS FIRST | DESC NULLS FIRST | Sort        | NULL,-1,-2
 -    | DESC NULLS LAST  | DESC NULLS FIRST | Backward    | NULL,-1,-2
 +    | ASC NULLS FIRST  | DESC NULLS LAST  | Backward    | 2,1,NULL
 +    | ASC NULLS LAST   | DESC NULLS LAST  | Sort        | 2,1,NULL
 +    | DESC NULLS FIRST | DESC NULLS LAST  | Sort        | 2,1,NULL
 +    | DESC NULLS LAST  | DESC NULLS LAST  | Forward     | 2,1,NULL
 -    | ASC NULLS FIRST  | DESC NULLS LAST  | Sort        | -1,-2,NULL
 -    | ASC NULLS LAST   | DESC NULLS LAST  | Forward     | -1,-2,NULL
 -    | DESC NULLS FIRST | DESC NULLS LAST  | Backward    | -1,-2,NULL
 -    | DESC NULLS LAST  | DESC NULLS LAST  | Sort        | -1,-2,NULL

I hope this covers everything (I hope nobody minds the lines starting with
" +" and " -")

Shouldn't 2320 also have the same change? Same for 1711/2167

Fixed that, and probably a bunch of others, included the math functions
that were in v5
and added regress/sql/slope_catalog.sql to format the results, this serves
a double purpose
(a) check that what we wrote had de desired effect (b) facilitate the
review by providing
a more readable format.

There you will find 4 tables
-- Operators with slope prosupport
oid | operator | left_type | right_type | prosupport
...

-- Functions (non-operator) with slope prosupport
oid | function | arguments | returns | prosupport

-- Operators whose name has slope support for some types but not others
e.g.

oid | operator | left_type | right_type

------+----------+-----------------------------+-----------------------------
4394 | * | anymultirange | anymultirange
3900 | * | anyrange | anyrange

-- Functions whose name has slope support for some signatures but not others
oid | function | arguments |
returns
------+--------------+--------------------------------------+-----------------------------
1218 | date_trunc | text, interval | interval
1736 | log | numeric, numeric | numeric
1961 | timestamp | timestamp without time zone, integer | timestamp
without time zone
1967 | timestamptz | timestamp with time zone, integer | timestamp
with time zone
1778 | to_timestamp | text, text | timestamp
with time zone
753 | trunc | macaddr | macaddr
4112 | trunc | macaddr8 | macaddr8

Regards,
Alexandre

Hi,

Fixing Meta ordering issue :), the table reporting orders was ordered in
different ways on
on different systems, specifying collation on the results table.

On Sun, Apr 12, 2026 at 4:01 PM Alexandre Felipe <
o.alexandre.felipe@gmail.com> wrote:

Now including a 0006 patch supporting redundant pathkeys such as
[f(x), x], or [x, f(x)], motivated by queries like
queries SELECT OVER (PARTITION f(x) ORDER BY x)

On Tue, Apr 7, 2026 at 12:09 AM Zsolt Parragi <zsolt.parragi@percona.com>
wrote:

I think there's a bug with NULL handling

There were too many possibilities for my brain to enumerate, so I scripted
the
extraction and presentation of that in regress/sql/slope.sql in commit 0005.

 sign |   index_order    |   query_order    | scan_method |  example
------+------------------+------------------+-------------+------------
 +    | ASC NULLS FIRST  | ASC NULLS FIRST  | Forward     | NULL,1,2
 +    | ASC NULLS LAST   | ASC NULLS FIRST  | Sort        | NULL,1,2
 +    | DESC NULLS FIRST | ASC NULLS FIRST  | Sort        | NULL,1,2
 +    | DESC NULLS LAST  | ASC NULLS FIRST  | Backward    | NULL,1,2
 -    | ASC NULLS FIRST  | ASC NULLS FIRST  | Sort        | NULL,-2,-1
 -    | ASC NULLS LAST   | ASC NULLS FIRST  | Backward    | NULL,-2,-1
 -    | DESC NULLS FIRST | ASC NULLS FIRST  | Forward     | NULL,-2,-1
 -    | DESC NULLS LAST  | ASC NULLS FIRST  | Sort        | NULL,-2,-1
 +    | ASC NULLS FIRST  | ASC NULLS LAST   | Sort        | 1,2,NULL
 +    | ASC NULLS LAST   | ASC NULLS LAST   | Forward     | 1,2,NULL
 +    | DESC NULLS FIRST | ASC NULLS LAST   | Backward    | 1,2,NULL
 +    | DESC NULLS LAST  | ASC NULLS LAST   | Sort        | 1,2,NULL
 -    | ASC NULLS FIRST  | ASC NULLS LAST   | Backward    | -2,-1,NULL
 -    | ASC NULLS LAST   | ASC NULLS LAST   | Sort        | -2,-1,NULL
 -    | DESC NULLS FIRST | ASC NULLS LAST   | Sort        | -2,-1,NULL
 -    | DESC NULLS LAST  | ASC NULLS LAST   | Forward     | -2,-1,NULL
 +    | ASC NULLS FIRST  | DESC NULLS FIRST | Sort        | NULL,2,1
 +    | ASC NULLS LAST   | DESC NULLS FIRST | Backward    | NULL,2,1
 +    | DESC NULLS FIRST | DESC NULLS FIRST | Forward     | NULL,2,1
 +    | DESC NULLS LAST  | DESC NULLS FIRST | Sort        | NULL,2,1
 -    | ASC NULLS FIRST  | DESC NULLS FIRST | Forward     | NULL,-1,-2
 -    | ASC NULLS LAST   | DESC NULLS FIRST | Sort        | NULL,-1,-2
 -    | DESC NULLS FIRST | DESC NULLS FIRST | Sort        | NULL,-1,-2
 -    | DESC NULLS LAST  | DESC NULLS FIRST | Backward    | NULL,-1,-2
 +    | ASC NULLS FIRST  | DESC NULLS LAST  | Backward    | 2,1,NULL
 +    | ASC NULLS LAST   | DESC NULLS LAST  | Sort        | 2,1,NULL
 +    | DESC NULLS FIRST | DESC NULLS LAST  | Sort        | 2,1,NULL
 +    | DESC NULLS LAST  | DESC NULLS LAST  | Forward     | 2,1,NULL
 -    | ASC NULLS FIRST  | DESC NULLS LAST  | Sort        | -1,-2,NULL
 -    | ASC NULLS LAST   | DESC NULLS LAST  | Forward     | -1,-2,NULL
 -    | DESC NULLS FIRST | DESC NULLS LAST  | Backward    | -1,-2,NULL
 -    | DESC NULLS LAST  | DESC NULLS LAST  | Sort        | -1,-2,NULL

I hope this covers everything (I hope nobody minds the lines starting with
" +" and " -")

Shouldn't 2320 also have the same change? Same for 1711/2167

Fixed that, and probably a bunch of others, included the math functions
that were in v5
and added regress/sql/slope_catalog.sql to format the results, this serves
a double purpose
(a) check that what we wrote had de desired effect (b) facilitate the
review by providing
a more readable format.

There you will find 4 tables
-- Operators with slope prosupport
oid | operator | left_type | right_type | prosupport
...

-- Functions (non-operator) with slope prosupport
oid | function | arguments | returns | prosupport

-- Operators whose name has slope support for some types but not others
e.g.

oid | operator | left_type | right_type

------+----------+-----------------------------+------------
-----------------
4394 | * | anymultirange | anymultirange
3900 | * | anyrange | anyrange

-- Functions whose name has slope support for some signatures but not others
  oid  |   function   |              arguments               |
returns
------+--------------+--------------------------------------
+-----------------------------
 1218 | date_trunc   | text, interval                       | interval
 1736 | log          | numeric, numeric                     | numeric
 1961 | timestamp    | timestamp without time zone, integer | timestamp
without time zone
 1967 | timestamptz  | timestamp with time zone, integer    | timestamp
with time zone
 1778 | to_timestamp | text, text                           | timestamp
with time zone
  753 | trunc        | macaddr                              | macaddr
 4112 | trunc        | macaddr8                             | macaddr8

Regards,
Alexandre