Re: SLOPE - Planner optimizations on monotonic expressions.

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(dot)parragi(at)percona(dot)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