Re: SLOPE - Planner optimizations on monotonic expressions.

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(dot)alexandre(dot)felipe(at)gmail(dot)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).
>
>
> Regards,
> Alexandre
>
>
>
> On Fri, Mar 27, 2026 at 9:29 AM Alexandre Felipe <
> o(dot)alexandre(dot)felipe(at)gmail(dot)com> wrote:
>
>>
>>
>> On Fri, Mar 27, 2026 at 8:47 AM Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)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
>>
>>
>>