Re: SLOPE - Planner optimizations on monotonic expressions. - Mailing list pgsql-hackers

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:

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